Displaying SQL Data With jQuery

Question

In a PHP file, I'm running a query on my database that returns specific values from every new record inserted into a table. Using a while loop, the query fetches the data and echos it out in a table I have created.

Now, using jQuery's ajax load method with setInterval on 1 second, the data from the php file appears on my page the way I want it to.

However, the problem I'm having now is figuring out how to display more details about individual users when their respective "table" is clicked. These details would appear via jQuery's show/hide. How would I link the ID of the record to what I click so it shows details about that specific user?

I've created a diagram explaining what I'm trying to do, as I'm relatively new to PHP and not sure if I'm explaining myself correctly.

Working Demo

container.php

<html lang="en">
<head>
  <meta charset="utf-8">

  <title>Show and Hide User Info</title>
  <meta name="description" content="">
  <meta name="author" content="">

  <script src="http://code.jquery.com/jquery-latest.min.js"></script>
  <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>

  <style>
  body {
    background-color: #000;
    color: #fff;
    font-family: tahoma, arial, verdana;
    font-size: 12px;
  }

  #users {
    background-color: #828282;
    width: 300px;
  }

  #userdetails {
    background-color: #828282;
    width: 300px;
    margin-top: 10px;
  }

  .user {
    padding: 5px;
    border-bottom: 1px solid white;
    width: 300px;
    background-color: #828282;
  }

  .user:hover {
    background-color: #282828;
    cursor: pointer;
  }

  #back:hover {
    background-color: #ffcc00;
    cursor: pointer;
    color: #000;
  }

  </style>

</head>

<body>

<div id="userlist"></div>

<div id="userdetails"></div>

<script>
setInterval(function(){
   $("#userlist").load("userlist.php");
}, 1000);
$('#userdetails').hide();
$(document).on("click",".user", function() {
    var data_id = $(this).attr("data-id");
      $.get("userdetails.php", {id: data_id}).done(function(data) {
      $('#userdetails').html(data);
      $('#userdetails').show();
      $('#userlist').hide();
  });
})
$(document).on("click","#back", function() {
  $('#userlist').show();
  $('#userdetails').hide();
});
 </script>


</body>
</html>

userlist.php

<?php

include 'dbh.php';

$result = $conn->query("SELECT * FROM users");

if ($result->num_rows > 0) {

  while ($row = $result->fetch_assoc()) {

    $color = array("ADMIN"=>"#ebc45b", "MOD"=>"#8fce61", "USER"=>"#9b9ed2");

?>

  <table data-id="<?php echo $row['id']; ?>" class="user">
    <tr>
      <td align="left"><?php echo $row['address']; ?></td>
      <td align="right"><?php echo $row['zip']; ?></td>
    </tr>
    <tr>
      <td align="left"><?php echo $row['city']; ?></td>
      <td align="right"><?php echo $row['state']; ?></td>
    </tr>
    <tr>
      <td align="left"><span style="color: <?php echo $color[$row['user_level']]; ?>"><?php echo $row['user_level']; ?></span></td>
      <td align="right">"member since..."</td>
    </tr>
  </table>

<?php
  }
}
?>

userdetails.php

<?php

include 'dbh.php';

$result = $conn->query("SELECT * FROM users WHERE id=" . $_GET["id"]);

if ($result->num_rows > 0) {

  while ($row = $result->fetch_assoc()) {

?>
<div>
<span id="back">BACK</span>
</div>
<table data-id="<?php echo $row['id']; ?>" class="user">
<tr>
  <td align="left">First Name:</td>
  <td align="right"><?php echo $row['first_name']; ?></td>
</tr>
<tr>
  <td align="left">Last Name:</td>
  <td align="right"><?php echo $row['last_name']; ?></td>
</tr>
<tr>
  <td align="left">Age:</td>
  <td align="right"><?php echo $row['age']; ?></td>
<tr>
</tr>
  <td align="left">Sex:</td>
  <td align="right"><?php echo $row['sex']; ?></td>
</tr>
</table>

<?php
 }
}
?>

Instead of a heap of if statements, why not an associative array to look up customizations based on role? — tadman
– tadman, Commented Mar 25, 2017 at 0:51
Good idea, I'm all for shorter code that does the same thing. I'm an amateur but, is this close? $color = array("ADMIN"=>"#ebc45b", "MOD"=>"#8fce61", "USER"=>"#9b9ed2"); — glokul
– glokul, Commented Mar 25, 2017 at 1:23
That's totally on the right track. You can also abstract that out later and push it to a config file, or even a database, at your discretion. Simply avoiding having it hard-coded gives you those options. Use it like $color[$row['user_level']] in that case. — tadman
– tadman, Commented Mar 25, 2017 at 2:03

liquidacid · Accepted Answer · 2017-03-24 01:52:45Z

1

Use Java to get the I'd of the div you want to display the data. Use the XML function to open the phone file. W3SCHOOLS has a good tutorial.

W3SCHOOLS tutorial

answered Mar 24, 2017 at 1:52

liquidacid

1181 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Damilola Olowookere · Accepted Answer · 2017-03-24 01:56:28Z

0

You can add data-id and class attributes to each <table style='width: 295px; border-bottom: 1px solid white;'> such that you can now have onclick handlers set for the table's class.

e.g.

Your table tag will now be something like:

"<table style='width: 295px; border-bottom: 1px solid white;' data-id='" . $row['user_unique_id'] . "' class='user_entry' >..."

and you will now have handler set like:

$('.user_entry').click( function()
{
   var data_id = $( this ).attr( "data-id" );
   $.get
   ( "get_user_details.php" , { user_id: data_id } ).done( function( data )
    {
        $( '#container_for_viewing_html' ).html( data );
    });
   })

You now use $_GET['user_id'] in the get_user_details.php script to know the unique user to get the data from your db and return your beautifully escaped html (don't forget you may need to do things like encoding html characters to prevent XXS and related attacks)

answered Mar 24, 2017 at 1:56

Damilola Olowookere

2,4132 gold badges26 silver badges34 bronze badges

7 Comments

glokul Over a year ago

OK I tried to follow your suggestion and played around with the code but I'm probably doing something wrong. I updated my code above and changed the relevant parts (I think). I also included the entire HTML code instead of just the jQuery snippet. I'm not sure where to place your jQuery snippet in my html though and how to link it to my show/hide divs. Also, is there a reason why there is so many spaces in your jQuery snippet?

Damilola Olowookere Over a year ago

Take it a step at a time man, you've got your code mixed up beautifully well! Okay, the first .php file is the main UI file. It is the one you use to display the <table>'s for those users. It is this same file that will have your jQuery code. The second .php file is the one you will call with your jQuery load (i.e. the $.get(...) thing). You do not need the $('#userlist').load('userdatamin.php') thing. Apparently you need to do some readup on jQuery/JS. For the 'so many' spaces in my jQuery snippet, I am just a big fan of readability so I naturally use lots of whitespaces :)

Damilola Olowookere Over a year ago

And then you've screwed up some of your html on this line "<table style='width: 295px; border-bottom: 1px solid white; data-id='" . $row['id'] . "' class='user''> because you've got your class='user' quoted. Try and inspect your code in Firebug or Chrome Dev Tools (Ctrl+I)

glokul Over a year ago

OK so I've been working on this the past couple days and finally got it working halfway, thanks to you and my programming buddy. As you can see, we added WHERE id=" . $_GET["id"] in the select statement to get the id. I included a link to my site in my original post with a working demo. Now the problem is getting the users div to refresh every second so any new database insert happens automatically, along with showing the user details in the same space after clicking a user.

Damilola Olowookere Over a year ago

@glokul Fantastic! Now all you have to do is create a new pure php script (say name if fetcher.php) that will have only the php code to display users (the code snippet in your users.php script, the part that starts with <?phpinclude 'dbh.php'; $result = $conn->query("SELECT * FROM users");... so move that to that new fetcher.php so that you will now use javascript's setInterval to call fetcher.php as frequently as you deem reasonable for your case and load it to your html

|

Pratyush · Accepted Answer · 2017-03-29 02:58:32Z

0

I think your userlist gets loaded after one second, so your table click may not be triggering. Can you try this.

$(document).on("click",".user", function() {
    var data_id = $(this).attr("data-id");
      $.get("userdetails.php", {id: data_id}).done(function(data) {
      $('#userdetails').html(data);
      $('#userdetails').show();
      $('.user').hide();
  });
})

Even your userdetails's table class name is same as userlist's table class name.

answered Mar 29, 2017 at 2:58

Pratyush

5351 gold badge8 silver badges18 bronze badges

1 Comment

glokul Over a year ago

Thanks!! That worked!! I had to use setInterval instead too.

Collectives™ on Stack Overflow

Displaying SQL Data With jQuery

3 Answers 3

Comments

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related