How To Display All Records From Tbl Using PREP STMT ?

[sunny_pro]

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. :shake: