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

user_15987536 0
Viewed 84

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.

mysql html php jquery database
1 Answers

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);
?>

user_37720910
Related