Populating divs with MySQL data, matching row to a numbered ID

Question

I am trying to use a database of books to display relevant data on a page by matching an id of a clickable div with a sort of serial number for each book in the database. my table is thus:

+-------------------+----------------+------------------+------------------+
| booknumber (INT)  | title (VCHAR)  | author (VCHAR)   | publisher (VCHAR)|
+-------------------+----------------+------------------+------------------+
| 123               | title of book  | name             | publisher name   |
| 124               | title of book  | name             | publisher name   |
| 125               | title of book  | name             | publisher name   |
| 127               | title of book  | name             | publisher name   |
| 128               | title of book  | name             | publisher name   |
| 130               | title of book  | name             | publisher name   |
 --------------------------------------------------------------------------

I was able to get data to display thanks to the answer in this thread.

HTML

    <html>
    <head>
        <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
        <script type="text/javascript" src="book_db.js"></script>
    </head>
    <body>  
      
      <div class="index-wrapper">
        <div class="book" id="123">Book title</div>
        <div class="book" id="124">Book title</div>
        <div class="book" id="125">Book title</div>
        <div class="book" id="127">Book title</div>
        <div class="book" id="128">Book title</div>
        <div class="book" id="130">Book title</div>
      </div>

    <div class="book-info">
          <h2 id='title'></h2>
          <span id='booknumber'></span>
          <span id='author'></span>
          <span id='pubhisher'></span>
      </div>
    
    </body>
    </html>

php

<?php
$hostname = "localhost";
$username = "***";
$password = "***";
$databaseName = "book_db";


$connect = mysqli_connect($hostname, $username, $password, $databaseName);

$query = "SELECT booknumber, title, author, publisher FROM booknumber";

$result = mysqli_query($connect, $query);

while($row = mysqli_fetch_array($result))
{
  $dataRow[] = $row;
}

echo json_encode($dataRow);
?>

and jquery

$(document).ready(function() {
    $.post("book_db.php", {
       
    }, function(server_data) {
       data = JSON.parse(server_data);
       $(document).on('click', '.book', function(){
       var number = $(this).attr('id');
   
        $("#title").html(data[number]['title']);
        $("#booknumber").html(data[number]['booknumber']);
        $("#author").html(data[number]['author']);
        $("#publisher").html(data[number]['publisher']);
   
        });    
    });
});

This works great for the first 125 or so books, but then there's a section where the numbers on the actual books skip a number as those books aren't used. That's when I realized that this code is pulling from the row number and not my INT data (the actual number of the book).

So what I want to accomplish is filling in the text inside the info div with the data of the row corresponding to the book number (INT) in the database (not the row number). I have the booknumber column set as my primary key, if that has any bearing on the solution.

Hi, can you show output of `server_data` ? Simply do `console.log(data)` you will see result inside your browser console. — Swati, May 21 '21 at 04:09
using console.log(data) as you mentioned gives me an endless string of [object, Object], but console.log(server_data) shows the output of the entire database. — Niwa, May 21 '21 at 04:57

dspillai · Accepted Answer · 2021-05-23T06:54:42.273

You are actually wasting the server resources by getting complete table data where you actually required one book data to display. Make your post request after the click even triggered. Transfer the "number" data to the server and get the corresponding row data from the table, then display.

$(document).ready(function() {
  $(document).on('click', '.book', function(){
     var number = $(this).attr('id');
     
     $.post("book_db.php", {result: number}, function(server_data) {
       data = JSON.parse(server_data);
   
        $("#title").html(data[number]['title']);
        $("#booknumber").html(data[number]['booknumber']);
        $("#author").html(data[number]['author']);
        $("#publisher").html(data[number]['publisher']);
   
        });    
    });
});

You have to rewrite your server code according to this. Also, try to dynamically create these html elements

<div class="index-wrapper">
        <div class="book" id="123">Book title</div>
        <div class="book" id="124">Book title</div>
        <div class="book" id="125">Book title</div>
        <div class="book" id="127">Book title</div>
        <div class="book" id="128">Book title</div>
        <div class="book" id="130">Book title</div>
      </div>

PHP

?php
$hostname = "localhost";
$username = "***";
$password = "***";
$databaseName = "book_db";


$connect = mysqli_connect($hostname, $username, $password, $databaseName);

$dataRow = array();
if(isset($_POST['result'])) {
$post = $_POST['result'];
$sql = "SELECT * FROM booknumber WHERE booknumber=".$post;
$result = $connect->query($sql);


while($row = $result->fetch_assoc())
{
  $dataRow[] = $row;
}
   }

echo json_encode($dataRow);
?>

Thank you! I had a feeling it was something simple, but most of what I was finding online was pretty complex. This also helps me understand how fetching data from the database works a little better, too. The way I had it was fine on the local server, but I can see how pulling the entire database wouldn't be so good in an online situation. We have over 5500 books to put into this database! — Niwa, May 21 '21 at 06:46
Also, you mentioned rewriting the server code. Is that where I would be able to fetch the number of the book as opposed to the number of the row itself? — Niwa, May 21 '21 at 06:58
Hi, you have transferred the required book number to the server. Collect the information of the book from the database by using the WHERE clause. SELECT * FROM your_table_name WHERE booknumber = $_POST['result']; — dspillai, May 21 '21 at 15:34
Now I'm getting the following error in my php: Warning: Undefined array key "result" in C:\xampp\htdocs\test\book_db.php on line 10 Fatal error: Uncaught Error: Call to a member function fetch_assoc() on bool in C:\xampp\htdocs\test\book_db.php:15 Stack trace: #0 {main} thrown in C:\xampp\htdocs\test\book_db.php on line 15 — Niwa, May 22 '21 at 05:11

Populating divs with MySQL data, matching row to a numbered ID

1 Answers1