How To Display All Records From Tbl Using PREP STMT ?

[saversites]

Php Gurus,

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

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.

NON-PREP STMT CODE

$stmt = mysqli_prepare($conn, 'SELECT id, recipient_username, sender_username, message FROM notices WHERE recipient_username=?');
mysqli_stmt_bind_param($stmt, 's', $recipient_username);
mysqli_stmt_execute($stmt);
	//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> 
    </tr> 
    <?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> 
    </tr> 
    <?php } ?> 
    <tr name="pagination"> 
    <td colspan="10" bgcolor="#FFFFFF"> Result Pages: 
    <?php 
        if($rows_num <= $page_size) 
        { 
            echo "Page 1";  
        } 
        else 
        { 
            for($i=1;$i<=$page_count;$i++) 
            echo "<a href=\"{$_SERVER['PHP_SELF']}?page_number={$i}\">{$i}</a>  "; 
        }     
        ?>     
    </td> 
    </tr> 
    <?php } else { ?> 
    <tr> 
    <td bgcolor="FFFFFF">No record found! Try another time.</td> 
    </tr> 
    <?php }?> 
    </table> 
    <br> 
    <br> 
    <center><span style="font-weight: bold;"><?php $user ?>Notices in <?php $server_time ?> time.</span></center> 
    <br> 
    <br> 
</div> 
<br> 
</body> 
</html>
	

 

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 */
$result->free();
/* close connection */
$conn->close();

$stmt = "SELECT id,date_and_time,recipient_username,sender_username,message FROM notices WHERE recipient_username = ?"; 
    
mysqli_stmt_bind_param($stmt, 's', $username);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);        
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
        

mysqli_stmt_bind_param($stmt, 's', $username);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_bind_result($stmt,$db_id,$db_date_and_time,$db_recipient_username,$db_sender_username,$db_message); 
        
mysqli_stmt_fetch($stmt);
mysqli_stmt_close($stmt);

$query = "SELECT id, recipient_username, sender_username, message FROM notices";
	if ($stmt = mysqli_prepare($conn, $query)) {
	    //execute statement
    mysqli_stmt_execute($stmt);
	    //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 
    mysqli_stmt_close($stmt);
}
	close connection 
mysqli_close($conn);
close connection 
mysqli_close($conn);

$stmt = mysqli_prepare($conn, 'SELECT id, recipient_username, sender_username, message FROM notices WHERE recipient_username=?');
mysqli_stmt_bind_param($stmt, 's', $recipient_username);
mysqli_stmt_execute($stmt);
$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 February 22, 2018 by saversites