Live PHP Data without refreshing Page?

General PHP Help
#1

Hey guys,

So lately I have been working on a radio page for my community. It contains information which is echoed from a MYSQL DB, on to the page. This shows stuff such as song name, artist, latest songs played etc.

Some of this is displayed in tables. I have been looking around, and I can not find anything which is understandable for what I am looking for - I would like to be able to have the tables and song title / artist update automatically without refreshing the page. I guess that this would have to be done via AJAX or JS, but I can’t find anything helpful for it and wondered if any of you could work some magic and help me solve this.

Below is the data I am showing and how I am showing it:

Example of some PHP Data which is being shown:

[php]<?php

db_conn();

$query = “SELECT artist, title, count(*) AS tracks FROM history WHERE TIMESTAMPDIFF(DAY, date_played, NOW()) <= " . $resDays . " AND song_type=0 GROUP BY title ORDER BY tracks DESC LIMIT 0,” . $resLimit;

$result = mysql_query($query);

if (!$result) {
echo mysql_error();
exit;
}

if (mysql_num_rows($result) == 0) {
echo “<div class=“noticediv”>No results found!”;
require_once(‘footer.php’);
exit;
}

$inc = 1;

while($row = mysql_fetch_assoc($result)) {

echo " <tr>" . "\n";

echo "  <td>" . $row['artist'] . "</td>\n";
echo "  <td>" . $row['title'] . "</td>\n";
echo "  <td>". $row['tracks'] . "</td>\n";
echo " </tr>" . "\n";

$inc += 1;

}

@mysql_free_result($result);
db_close($opened_db);
?>[/php]

This PHP code is put inside a table, allowing it to be shown within the table. An example of the table is shown below:

So -

Does anybody know how I would go with this in terms of allowing the PHP Data to be updated/refreshed, without actually refreshing the page? In other words, “Live” data.

Thanks for any help,

Lewis.

#2

Well, first, most programmers will not help if you are still using MySQL functions. There were replaced 8 or 10 years ago
with the improved versions MySQLi… A lot of servers are dumping it soon if they have not already and your code will no
longer work then. You need to upgrade at least to MySQLi which is an easy update or to the better PDO which will be a bit
harder to update to, but, is more secure.

Now with that said, you need to learn how to use AJAX. Basically, it allows you to run an external PHP page and load the
results of the page into a

or other area on your page. If you need to display a table from a query, you set up a code
to send to a page, such as what you want it to load. Normally you do not send the live data as it might be intercepted, but,
instead give the PHP page a code like a range or encrypt the data and then, the PHP page can sort it out and query the
database, build the table and echo it back to the calling code. Then, that loads the values into the
.

This sounds complicated, but, in real life, it is very simple. Here is the WWW-School’s tutorial on it. As you will see, it is
not very hard to alter to fit your uses. If you have issues, post back showing us some code, just do not post your passwords
or other private connection strings… Hope it helps!
http://www.w3schools.com/php/php_ajax_database.asp

#3
#4

Thanks for the Karma! Always nice to get thanked for helping! I would do it anyways…

I found that there are other ways to do this live-update type of display. I have used it before as most programmers seem to
need to learn this process. A lot of sites want to use it. I have one that loads code snippets dynamically so I can review
notes I made to myself.

Let us know how it works out for you!

#5

Thanks to your advice, I have managed to get it working like a charm!
I took your advice and changed the MYSQL to MYSQLi. I will change it to PDO at a later time, but I’m sure this will do for now.

So I changed my php script to the following:

[php]

<?php require_once('serv_inc.php'); /* ============================================================================= */ // EDIT BELOW /* ============================================================================= */ $pageTitle = "Most Played Songs This Week"; $resDays = 7; // On how many days to build the top? $resLimit = 6; // How many results to display? /* ============================================================================= */ // END EDIT /* ============================================================================= */ $servername = "localhost"; $username = "lewis"; $password = ""; $dbname = ""; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "SELECT `artist`, title, count(*) AS tracks FROM `history` WHERE TIMESTAMPDIFF(DAY, `date_played`, NOW()) <= " . $resDays . " AND `song_type`=0 GROUP BY title ORDER BY tracks DESC LIMIT 0," . $resLimit; $result = $conn->query($sql); if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { echo ""; } echo "
Artist Track Plays
".$row["artist"]." ".$row["title"]." ".$row["tracks"]."
"; } else { echo "0 results"; } $conn->close(); ?> [/php]

On my index.php I then just simply added the following script:

[code]

[/code]

That then allowed me to use the following div to grab that data:

<div id="load_updates"> </div>

Feel free to comment on anything that you think should be changed or isn’t done correctly. I am always up for learning!

Thanks for your help :slight_smile:

#6

Great, for the future you should never concatenate/insert variables into the query string, it may be done if you’re 100% sure the data is safe (meaning you’re maintaining the code base on your own, and have hard coded the variables somewhere). For all other cases (most) you should use parameterized queries. Ie: SELECT * FROM user WHERE id = ?

I would suggest to separate the logic and view parts of the code. Meaning you start off the file with plain PHP only, where you fetch the data you need and do the data manipulation you might need to do. Then you stop PHP execution and output html as normally.

[php]

<?php session_start(); include 'fancy_file.php'; $users = isset($_GET['id']) ? $store->find($_GET['id']) : $store->findAll(); include 'header.php'; ?> <?php foreach ($users as $user): ?> <?php endforeach; ?> [/php]

You don’t use the auto_refresh variable you create so it’s not needed.

ID Name
<?= $user->id ?> <?= $user->name ?>
#7

Glad you sorted it out. And, good you updated to at least MySQLi. Since you got it working now, I will mark this one solved.
We will be looking forward to your next question… There is always one more thing… LOL I always have questions!

See you in the “bitstream”…

#8

Thanks for the help guys :slight_smile: I will be sure to make those edits you have mentioned JimL - +Karma to both of you :wink:

Sponsor our Newsletter | Privacy Policy | Terms of Service