Jump to content

How To Display All Records From Tbl Using PREP STMT ?


Recommended Posts

Php Gurus,

My following code shows all the results of the "notices" tbl. That tbl has columns:

This code works but it does not use the PREP STMT. I need your help to convert the code so it uses PREP STMT.
On this code, all the records are spreadover 10 pages.
On the PREP STMT version, I need 10 records spreadover each page. So, if there is 3,000 records then all records would be spreadover 300 pages.


$stmt = mysqli_prepare($conn, 'SELECT id, recipient_username, sender_username, message FROM notices WHERE recipient_username=?');
mysqli_stmt_bind_param($stmt, 's', $recipient_username);
	//bind result variables
mysqli_stmt_bind_result($stmt, $id, $recipient_username, $sender_username, $message);
    //Get Data from Tbl "notices" 
    $sql = "SELECT * FROM notices"; 
    $result = mysqli_query($conn,$sql); 
    //Total Number of Records 
    $rows_num = mysqli_num_rows($result); 
    //Total number of pages records are spread-over 
    $page_count = 10; 
    $page_size = ceil($rows_num / $page_count); 
    //Get the Page Number, Default is 1 (First Page) 
    $page_number = $_GET["page_number"]; 
    if ($page_number == "") $page_number = 1; 
        $offset = ($page_number -1) * $page_size; 
        $sql .= " limit {$offset},{$page_size}"; 
        $result = mysqli_query($conn,$sql); 
    <table width="1500" border="0" cellpadding="5" cellspacing="2" bgcolor="#666666"> 
    <?php if($rows_num) {?> 
    <tr name="headings"> 
    <td bgcolor="#FFFFFF" name="column-heading_submission-number">Submission Number</td> 
    <td bgcolor="#FFFFFF" name="column-heading_logging-server-date-&-time">Date & Time in <?php $server_time ?></td> 
    <td bgcolor="#FFFFFF" name="column-heading_username">To</td> 
    <td bgcolor="#FFFFFF" name="column-heading_gender">From</td> 
    <td bgcolor="#FFFFFF" name="column-heading_age-range">Notice</td> 
    <?php while($row = mysqli_fetch_array($result)){ ?> 
    <tr name="user-details"> 
    <td bgcolor="#FFFFFF" name="submission-number"><?php echo $row['id']; ?></td> 
    <td bgcolor="#FFFFFF" name="logging-server-date-&-time"><?php echo $row['date_and_time']; ?></td> 
    <td bgcolor="#FFFFFF" name="username"><?php echo $row['recipient_username']; ?></td> 
    <td bgcolor="#FFFFFF" name="gender"><?php echo $row['sender_username']; ?></td> 
    <td bgcolor="#FFFFFF" name="age-range"><?php echo $row['message']; ?></td> 
    <?php } ?> 
    <tr name="pagination"> 
    <td colspan="10" bgcolor="#FFFFFF"> Result Pages: 
        if($rows_num <= $page_size) 
            echo "Page 1";  
            echo "<a href=\"{$_SERVER['PHP_SELF']}?page_number={$i}\">{$i}</a>  "; 
    <?php } else { ?> 
    <td bgcolor="FFFFFF">No record found! Try another time.</td> 
    <?php }?> 
    <center><span style="font-weight: bold;"><?php $user ?>Notices in <?php $server_time ?> time.</span></center> 


The following codes are my attempts to convert the above code to PREP STMT but I see arrays with no records:

$query = "SELECT id, recipient_username, sender_username, message FROM notices ORDER by id";
$result = $conn->query($query);
	/* numeric array */
$row = $result->fetch_array(MYSQLI_NUM);
printf ("%s (%s)\n", $row[0], $row[1]);
	/* associative array */
$row = $result->fetch_array(MYSQLI_ASSOC);
printf ("%s (%s)\n", $row["id"], $row["recipient_username"], $row["sender_username"], $row["message"]);
	/* associative and numeric array */
$row = $result->fetch_array(MYSQLI_BOTH);
printf ("%s (%s)\n", $row[0], $row["id"], $row[1], $row["recipient_username"], $row[2], $row["sender_username"], $row[3], $row["message"]);
	/* free result set */
/* close connection */
$stmt = "SELECT id,date_and_time,recipient_username,sender_username,message FROM notices WHERE recipient_username = ?"; 
mysqli_stmt_bind_param($stmt, 's', $username);
$result = mysqli_stmt_get_result($stmt);        
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
mysqli_stmt_bind_param($stmt, 's', $username);
$result = mysqli_stmt_bind_result($stmt,$db_id,$db_date_and_time,$db_recipient_username,$db_sender_username,$db_message); 
$query = "SELECT id, recipient_username, sender_username, message FROM notices";
	if ($stmt = mysqli_prepare($conn, $query)) {
	    //execute statement
	    //bind result variables
    mysqli_stmt_bind_result($stmt, $id, $recipient_username, $sender_username, $message);
	    //fetch values
    while (mysqli_stmt_fetch($stmt)) {
        printf ("%s (%s)\n", $id, $recipient_username, $sender_username, $message);
	    //close statement 
	close connection 
close connection 
$stmt = mysqli_prepare($conn, 'SELECT id, recipient_username, sender_username, message FROM notices WHERE recipient_username=?');
mysqli_stmt_bind_param($stmt, 's', $recipient_username);
$notices_row = array();
mysqli_stmt_bind_result($stmt, $notices_row['id'], $notices_row['recipient_username'], $notices_row['sender_username'], $notices_row['message']);
while (mysqli_stmt_fetch($stmt)) 
  echo '<p>' . $notices_row['recipient_username'] . '</p>';

I give-up. They all show results like the following, even though the tbl rows have data:

28 () 29 () 30 (30)

If you know of a simpler way (cut down version) for the tbl to display all rows from all columns using PREP STMT using Precedural Style then be my guest to show a sample.

Thanks for your helpS. :)

Edited by saversites
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...