#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    830
    Rep Power
    0

    Question How To Display All Records From Tbl Using PREP STMT ?


    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
    PHP 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:

    PHP Code:
    $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(); 
    PHP Code:
    $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($resultMYSQLI_ASSOC); 
    PHP Code:
    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); 
    PHP Code:
    $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); 
    PHP 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);

    $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:
    Last edited by UniqueIdeaMan; February 22nd, 2018 at 08:10 AM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    136
    Rep Power
    65
    Pagination involves two queries. The first query gets a total count of the rows that match any FROM/JOIN/WHERE/GROUP BY/HAVING terms. The second query retrieves the data that corresponds to the requested logical page number. The only differences between the two queries is in what is SELECTed (COUNT(*) v.s. a list of column names) and adding an ORDER BY and LIMIT term to the data retrieval query. The core part of the queries making up the FROM/JOIN/WHERE/GROUP BY/HAVING terms needs to be the same for both queries, by building it once, then using it in the code for both queries.

    Your task is to form, execute, and retrieve the data from these two queries, using the php database extension of your choice. If you have mastered the code to do this for any SELECT query, you should be able to apply that same leaning to this task.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    136
    Rep Power
    65
    BTW - you should be storing the user ids, not the user names in your notices table. What happens if a user name wants (by the user) or needs (by an admin if an offensive name was used) to be edited?

    You should also be storing the datetime of the message so you will know when messages were posted.

    Comments on this post

    • UniqueIdeaMan agrees : Thanks for reminding to add the date & time column!
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    830
    Rep Power
    0
    Originally Posted by DSmabismad
    Pagination involves two queries. The first query gets a total count of the rows that match any FROM/JOIN/WHERE/GROUP BY/HAVING terms. The second query retrieves the data that corresponds to the requested logical page number. The only differences between the two queries is in what is SELECTed (COUNT(*) v.s. a list of column names) and adding an ORDER BY and LIMIT term to the data retrieval query. The core part of the queries making up the FROM/JOIN/WHERE/GROUP BY/HAVING terms needs to be the same for both queries, by building it once, then using it in the code for both queries.

    Your task is to form, execute, and retrieve the data from these two queries, using the php database extension of your choice. If you have mastered the code to do this for any SELECT query, you should be able to apply that same leaning to this task.
    Thanks. I managed to grasp 50% of what you said but I'll grasp 100% (and I'm sure other newbies pouring this way too) if you care to show a sample code as that way we'll learn faster.
    So, how-about editing my code and showing us rawbies (raw in php) a sample ? Thanks in advance for your procedural style mysqli sample(s).
    I gave 3-4 samples of my attempts. I'd still appreciate it if you give one sample code but if you edit all my 3-4 then we get a chance to learn 3-4 ways of achieving the same thing. This will get us to ripe in php a little faster.

    And, thanks for reminding about offensive usernames. Now, I'm gonna add the banned words filter onto the reg page on the username section. You saved us newbies (who will build similar sites in the future) from embarrassments here. And REALLY BAD embarrassments AT THAT I must add!
    Last edited by UniqueIdeaMan; February 23rd, 2018 at 02:42 AM.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    830
    Rep Power
    0
    Googling for 2 days:
    https://www.google.com/search?q=fetc...w=1366&bih=637
    But no real luck in finding the right tutorial on how to fetch matching rows or all rows using prep stmt and then display the results like google serp.

IMN logo majestic logo threadwatch logo seochat tools logo