0

Hello to this great community!

I've already learned a lot by reading many of the questions and answers here. So here is my current problem:

I'm currently creating a simple code which includes PHP, MySQL and jQuery for a simple news ticker. The aim is to check the database for changes without the need of reloading the whole webpage. If there is a change in the database a javascript is executed to reload the whole webpage. With this I want to avoid, that the user has to reload the webpage manually.

This works nicely!

But there is one problem: When too many users are on the webpage a "Too many connections"-Mysql-error occurs. I think this happens because of the many parallel running updates()-functions.

Do you have an idea how to optimize this code?

<script>
function updates(){
 $('#updates').load('updates.php');
}
setInterval("updates()", 1000);
</script>
<div id="updates"></div>

updates.php

<?php 

// Get latest value the database
$result = mysql_query("SELECT update FROM updates_db");
$row = mysql_fetch_object($result);

// Compare the value from the database with the current value which is saved in a session
if($_SESSION['update'] != $row->update) {

// If the values do not match, update the session and reload the whole webpage
$_SESSION['update'] = $row->update;

echo '<script>
location.reload();
</script>'

}
phpfelix
  • 11
  • 1
  • 3
    It will probably not make much difference since non-persistent MySQL connections are automatically closed when a PHP script finishes its execution, but closing your mysql connection when you're done using it in `updates.php` script *could* buy a few miliseconds. `mysql_close();` – James Jul 06 '17 at 20:46
  • Ok, thank you James! – phpfelix Jul 06 '17 at 20:48
  • 2
    ***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Jul 06 '17 at 20:49
  • Also, and this will probably come as no shock, but increasing your interval would dramatically increase performance; 1 request to the server every second is quite frequent! – James Jul 06 '17 at 20:50
  • Thanks Jay! Do you think PDO would also increase the performance? @James the code of updates.php is posted above – phpfelix Jul 06 '17 at 20:51
  • the primary cause of performance problems is not the module you use to talk to the database, it's the communication to the database itself. the complexity of the query, amount of data stored, amount of data returned, amount of work needed to be done within the database to get to the result, etc. Optimizing a select all is.... probably not the right thing to be worrying about at this point. – Kevin B Jul 06 '17 at 20:53
  • Instead you need to be thinking about the capacity of your server/network, whether you can increase the maximum number of connections to accommodate your user base, or if you can move to connection pools to reuse connections, or implement a caching mechanism to reduce load on the database. Or, if updates.php even updates often enough for it to be worth checking for updates every second. – Kevin B Jul 06 '17 at 20:57
  • @KevinB Currently the database only contains one entry for the 'update'-cell. This cell is updated by the author of a new article. Then the webpages of all users who are online are updated – phpfelix Jul 06 '17 at 20:58
  • then... i don't see a reason to even have this script. Is the author going to be sitting there updating the article every few seconds 24/7? – Kevin B Jul 06 '17 at 21:00
  • @James How should I edit the code then? Create a trigger for jQuery to reload the whole page? – phpfelix Jul 06 '17 at 21:00
  • I think you'd be better off not having the client initiate the query. Schedule a script on your server to generate the content you want to load. Then when the browser requests it, your server can send the new content, or if it hasn't changed, send notmodified so the browser can use its cached content instead. – Don't Panic Jul 06 '17 at 21:03
  • @phpfelix. Ignore my comment r.e. `location.reload()` turns out jQuery executes the script tags before stripping them out. My bad! – James Jul 06 '17 at 21:03
  • You're better off hitting `updates.php` through [ajax](http://api.jquery.com/jquery.ajax/), returning the update at the end of `updates.php` (json?), and updating your DOM with the response rather than refreshing the whole page which is rather costly. – James Jul 06 '17 at 21:06
  • @KevinB The reason behind it is that I'm also thinking about to use this kind of reloader if there are multiple authors, then there might be many updates to an article – phpfelix Jul 06 '17 at 21:10
  • Right, but, you're checking every second, how many updates would each author make in a day? in a week? you're going to have so many hits that do nothing but take up bandwidth. – Kevin B Jul 06 '17 at 21:11
  • @Don'tPanic Thanks! Do you have a example for this? – phpfelix Jul 06 '17 at 21:11
  • @KevinB Ok, sure. It would be just nice to have some "live" feeling – phpfelix Jul 06 '17 at 21:13
  • @KevinB, James Or should is there another completely different solution possible? Something like Websockets? – phpfelix Jul 06 '17 at 21:34
  • Yeah, there's websockets, but you could still run into other capacity problems. I don't think websockets would solve the problem, it would just change how it looks. – Kevin B Jul 06 '17 at 21:34
  • I think you're causing yourself more problems than you need for a feature your users won't notice. – Kevin B Jul 06 '17 at 21:36
  • Thanks guys for your help! – phpfelix Jul 07 '17 at 05:15

1 Answers1

-1

Try something like this instead of refreshing the whole page.

PHP

<?php

header('Content-type: application/json');

$result = mysql_query("SELECT update FROM updates_db");
$row = mysql_fetch_object($result);

if (($update = $_SESSION['update']) != $row->update) {
    $_SESSION['update'] = $row->update;
    echo json_encode(['update' => null]);
    exit;
}

echo json_encode(['update' => $update]);

Javascript

$.post('updates.php')
    .done(function( data ){
        if( data.update){
            //update DOM with new update
        }
    });

And increase your timeout interval. One request/1000ms is very frequent.

James
  • 1,138
  • 9
  • 13
  • Thanks! Would this also reduce the load on the mysql database? – phpfelix Jul 06 '17 at 21:15
  • *"And increase your timeout interval. One request/1000ms is very frequent."* would, but the other is just better logic/code. Increasing it to 2000ms for example would immediately cut the traffic in half. – Kevin B Jul 06 '17 at 21:16
  • @phpfelix if you have a lot of database logic in the page you're refreshing once per second, then yes! But do increase your timeout for sure! I'm surprised the page is usable if it refreshes constantly. – James Jul 06 '17 at 21:33
  • @KevinB and James, I just had I new idea. How about not having the frequent MySQL-Checks by all readers at all? Wouldn't it be possible to have direkt communication between all readers and the author via websockets and if there is a change the author triggers all readers' browsers to reload and do a single MySQL query. – phpfelix Jul 08 '17 at 09:13