Okay so this code may not be the best formatted or used and there are probably more efficient ways of retrieving this information, ether way I'm not a coder and just know enough basic php to do what is required. I have three sites running identical databases with different content in them and I want to total up the games, game plays and visits. The total games and total visits calculations are fine and output the expected result however my total plays output does not add up. <?php mysql_connect [database connection info here]... $querygames = mysql_query("SELECT * FROM games"); $totgames = mysql_num_rows($querygames); $allgames = $totgames + 0; $selectplays = "SELECT SUM(plays) AS sumTotal FROM games"; $queryplays = mysql_query($selectplays); while($fetchplays = mysql_fetch_assoc($queryplays)) { $plays_format_number = number_format($fetchplays['sumTotal']); } $hits = mysql_query("SELECT hits FROM hits WHERE id='1'"); $hits = mysql_result($hits,0); $hits_format_number = number_format($hits); $games1 = $allgames; $plays1 = $plays_format_number; $visits1 = $hits; mysql_connect [database connection info here]... $querygames = mysql_query("SELECT * FROM games"); $totgames = mysql_num_rows($querygames); $allgames = $totgames + 0; $selectplays = "SELECT SUM(plays) AS sumTotal FROM games"; $queryplays = mysql_query($selectplays); while($fetchplays = mysql_fetch_assoc($queryplays)) { $plays_format_number = number_format($fetchplays['sumTotal']); } $hits = mysql_query("SELECT hits FROM hits WHERE id='1'"); $hits = mysql_result($hits,0); $hits_format_number = number_format($hits); $games2 = $allgames; $plays2 = $plays_format_number; $visits2 = $hits; mysql_connect [database connection info here]... $querygames = mysql_query("SELECT * FROM games"); $totgames = mysql_num_rows($querygames); $allgames = $totgames + 0; $selectplays = "SELECT SUM(plays) AS sumTotal FROM games"; $queryplays = mysql_query($selectplays); while($fetchplays = mysql_fetch_assoc($queryplays)) { $plays_format_number = number_format($fetchplays['sumTotal']); } $hits = mysql_query("SELECT hits FROM hits WHERE id='1'"); $hits = mysql_result($hits,0); $hits_format_number = number_format($hits); $games3 = $allgames; $plays3 = $plays_format_number; $visits3 = $hits; $allgames = $games1 + $games2 + $games3; $plays_format_number = number_format($plays1 + $plays2 + $plays3); $hits_format_number = number_format($visits1 + $visits2 + $visits3); echo " Total Games: {$allgames}.<br /> Total Plays: {$plays_format_number}.<br /> Total Visits: {$hits_format_number}.<br /><br />"; ?> PHP:
Logic seems fine to me? How are the numbers different (or by how much). What is the output from the DB as opposed from the above script?
Well... as you said it's not efficient; that's being WAY polite. It's grossly inefficient; the "SELECT *" just to get numRows is passing a ton of data that would drag your performance into the ninth ring of hell. I can't quite make sense of the 'logic' of it -- doesn't make any sense to me whatsoever in fact... IF I understand what you are trying to do, I THINK it might be this: <?php $db = new PDO( 'mysql:dbname=database1;host=localhost', '', // username '', // password ); $statement = $db->exec('SELECT COUNT (*) FROM games'); $allGames = $games1 = $statement->fetchColumn(); $statement = $db->exec('SELECT SUM(plays) FROM games'); $allPlays = $plays1 = $statement->fetchColumn(); $statement = $db->exec('SELECT hits FROM hits WHERE id = 1'); $allHits = $hits1 = $statement->fetchColumn(); $db = new PDO( 'mysql:dbname=database2;host=localhost', '', // username '', // password ); $statement = $db->exec('SELECT COUNT (*) FROM games'); $allGames += $games2 = $statement->fetchColumn(); $statement = $db->exec('SELECT SUM(plays) FROM games'); $allPlays += $plays2 = $statement->fetchColumn(); $statement = $db->exec('SELECT hits FROM hits WHERE id = 1'); $allHits += $hits2 = $statement->fetchColumn(); $db = new PDO( 'mysql:dbname=database3;host=localhost', '', // username '', // password ); $statement = $db->exec('SELECT COUNT (*) FROM games'); $allGames += $games3 = $statement->fetchColumn(); $statement = $db->exec('SELECT SUM(plays) FROM games'); $allPlays += $plays3 = $statement->fetchColumn(); $statement = $db->exec('SELECT hits FROM hits WHERE id = 1'); $allHits += $hits3 = $statement->fetchColumn(); echo ' Total Games: ', $allGames, '<br /> Total Plays: ', number_format($allPlays), '<br /> Total Visits: ', number_format($allHits), '<br /> <br />'; ?> Code (markup): I dragged it kicking and screaming out of 2006 by switching to PDO (since nothing written after 2007 really has any business using mysql_ functions), wait to do the number_format until it's actually needed (so not wasting variables on something not even used and/or corrupted at EVERY operation), and simplified the queries. ALL of your queries only return ONE value on one row, so a full row fetch is a waste of time. ::fetchColumn just gets the first value in the results, which is all there is in ANY of those queries. Because that code is so redundant, I'd be tempted to make it a singleton. Something like this: class gameInfo { public static $games = 0, $plays = 0, $hits = 0; public static function reset() { self::$games = 0, self::$plays = 0, self::$hits = 0; } public static function addFromDb($dsn, $username, $password) { $db = new PDO($dsn, $username, $password); $result = []; $statement = $db->exec('SELECT COUNT (*) FROM games'); self::$games += $result['games'] = $statement->fetchColumn(); $statement = $db->exec('SELECT SUM(plays) FROM games'); self::$plays += $result['plays'] = $statement->fetchColumn(); $statement = $db->exec('SELECT hits FROM hits WHERE id = 1'); self::$hits += $result['hits'] = $statement->fetchColumn(); return $result; } } Code (markup): (tossed a reset function in there should you want to call it a second time in the same execution) Then pulling the data would be as simple as: $info1 = gameInfo::addFromDb( 'mysql:dbname=database1;host=localhost', '', // username '', // password ); $info2 = gameInfo::addFromDb( 'mysql:dbname=database2;host=localhost', '', // username '', // password ); $info3 = gameInfo::addFromDb( 'mysql:dbname=database2;host=localhost', '', // username '', // password ); Code (markup): That's assuming you also want all the values from each game stored for showing as well. It would be simpler if all you want is the total. Made a fake DB full of garbage for testing, had this as a nice clean output: echo ' <table> <caption>Game Statistics</caption> <thead> <tr> <th scope="col">Source</th> <th scope="col">Games</th> <th scope="col">Plays</th> <th socpe="col">Hits</th> </tr> </thead><tfoot> <tr class="totals"> <th scope="row">Total:</th> <td>', gameInfo::$games, '</td> <td>', number_format(gameInfo::$plays), '</td> <td>', number_format(gameInfo::$hits), '</td> </tr> </tfoot><tbody> <tr> <th scope="row">Game 1</th> <td>', $info1['games'], '</td> <td>', number_format($info1['plays']), '</td> <td>', number_format($info1['hits']), '</td> </tr><tr> <th scope="row">Game 2</th> <td>', $info2['games'], '</td> <td>', number_format($info2['plays']), '</td> <td>', number_format($info2['hits']), '</td> </tr><tr> <th scope="row">Game 3</th> <td>', $info3['games'], '</td> <td>', number_format($info3['plays']), '</td> <td>', number_format($info3['hits']), '</td> </tr> </tbody> </table>'; Code (markup): Aka a semantic properly formed semantic table used to do exactly what tables are actually for I'd even consider the possibility of automating it down even further -- make an array of DSN/UN/PW connection info that automatically fills another array with the totals, to automatically output the rows. That would let you add and remove games if desired in the future just by adding/removing their connection info at the start -- That would be what I'd consider doing if you plan on outputting each game's values as well as the totals. If all you care about is the totals, this would be even simpler.