Databases
StackOverflow2013
Postsdbo
POToo many Database Calls
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POToo many Database Calls
    text
    copied!<p>I have an auto refresh script that refreshes a page every 3 seconds. The page it refreshes ha few database queries. When I get 20 or more members on my site the server crashes because of all the calls to the database. Is there another way to check for changes in the database?</p> <pre><code>//Can't Chat $strFind="SELECT * FROM cantchat"; $result=mysql_query($strFind) or die(mysql_error()); while($row=mysql_fetch_array($result)) { $id=$row['memid']; $strsql="DELETE FROM cometchat_chatroommessages WHERE userid=\"$id\""; $chkrow1=mysql_query($strsql,$connect) or die(mysql_error()); } if($banned&lt;1){ $strFind="SELECT * FROM ttourmember WHERE memberid=\"$curmemid\""; $result=mysql_query($strFind) or die(mysql_error()); $row=mysql_fetch_array($result); $membername=$row['membername']; $console=$row['system']; # timeout - how long should it take before visitors are no longer 'online'? (in minutes) define ('timeout', 20); // check if visitor is already in the table $lastactive =time(); $strFind="SELECT COUNT(*) AS total FROM online WHERE `memberid`=\"$curmemid\""; $result=mysql_query($strFind) or die(mysql_error()); $row=mysql_fetch_array($result); $imonline=$row['total']; if ($imonline&lt;1) { // Insert new visitor $strsql="INSERT INTO online(lastactive,memberid,membername,console,ipaddress,accountactive) VALUES(\"$lastactive\",\"$curmemid\",\"$membername\",\"$console\",\"$ip\",'Y')"; $chkrow1=mysql_query($strsql,$connect) or die(mysql_error()); $onlineid=mysql_insert_id();//copied } else { // Update exisiting visitor $strsql="UPDATE online SET `lastactive`=\"$lastactive\",membername=\"$membername\",console=\"$console\",ipaddress=\"$i p\" WHERE `memberid`=\"$curmemid\""; mysql_query($strsql,$connect) or die(mysql_error()); $chkrow5=mysql_affected_rows($connect); $strsql="UPDATE ttourmember SET `ipadd`=\"$ip\" WHERE `memberid`=\"$curmemid\""; mysql_query($strsql,$connect) or die(mysql_error()); $chkrow5=mysql_affected_rows($connect); } // Remove any inactive visitors $inactive = time()-21; //Who's Online list $strFind="SELECT * FROM online WHERE lastactive&lt; $inactive AND `stay`&lt;&gt;'Y'"; $result=mysql_query($strFind) or die(mysql_error()); while($row=mysql_fetch_array($result)) { $friendid=$row['memberid'].","; $strsql="DELETE FROM friends WHERE toid=\"$friendid\" OR fromid=\"$friendid\""; $chkrow1=mysql_query($strsql,$connect) or die(mysql_error()); } $strsql="DELETE FROM online WHERE lastactive &lt; $inactive AND stay&lt;&gt;'Y'"; mysql_query($strsql,$connect) or die(mysql_error()); $chkrow5=mysql_affected_rows($connect); //Can Chat $query_chat = "SELECT * FROM online WHERE `memberid`&lt;&gt;\"$curmemid\" ORDER BY membername DESC"; $chat = mysql_query($query_chat) or die(mysql_error()); $row_chat = mysql_fetch_assoc($chat); $totalRows_chat = mysql_num_rows($chat); //Count Pending Games $strFind="SELECT COUNT(*) AS total FROM tgamertournament WHERE `memberid` = \"$curmemid\" AND `pending`='Y'"; $result=mysql_query($strFind) or die(mysql_error()); $row=mysql_fetch_array($result); $tpgames=$row['total']; $strFind="SELECT COUNT(*) AS total FROM tgamertournament WHERE `targetto` = \"$curmemid\" AND `pending`='Y'"; $result=mysql_query($strFind) or die(mysql_error()); $row=mysql_fetch_array($result); $topgames=$row['total']; $pgames=$tpgames+$topgames; //Steam Received Sent Challenge $query_stgames = "SELECT * FROM tgtournamentrequest WHERE `gamer`=\"$curmemid\" AND `active`&lt;&gt;'D' AND `scored`&lt;&gt;'Y' AND `type`='S' AND `startfteam`='Y' AND `isaccepted`='N' AND `startsteam`='N' ORDER BY tournamentid DESC LIMIT 3"; $stgames = mysql_query($query_stgames) or die(mysql_error()); $row_stgames = mysql_fetch_assoc($stgames); $totalRows_stgames = mysql_num_rows($stgames); //Waiting For Fteam Sent Challenge To Get Accepted $query_wftgames = "SELECT * FROM tgtournamentrequest WHERE `fteam`=\"$curmemid\" AND `active`&lt;&gt;'D' AND `scored`&lt;&gt;'Y' AND `type`='S' AND `startfteam`='Y' AND `isaccepted`='N' AND `startsteam`='N' ORDER BY tournamentid DESC LIMIT 3"; $wftgames = mysql_query($query_wftgames) or die(mysql_error()); $row_wftgames = mysql_fetch_assoc($wftgames); $totalRows_wftgames = mysql_num_rows($wftgames); //List Posted Pending Games Accepted $query_apostedgames = "SELECT * FROM tgtournamentrequest WHERE `gamer` = \"$curmemid\" OR `fteam`=\"$curmemid\" AND `active`&lt;&gt;'D' AND `scored`&lt;&gt;'Y' AND `type`='P' ORDER BY tournamentid DESC LIMIT 3"; $apostedgames = mysql_query($query_apostedgames) or die(mysql_error()); $row_apostedgames = mysql_fetch_assoc($apostedgames); $totalRows_apostedgames = mysql_num_rows($apostedgames); //List Posted Pending Games $query_postedgames = "SELECT * FROM tgtournamentrequest WHERE `gamer` = \"$curmemid\" OR `fteam`=\"$curmemid\" AND `active`&lt;&gt;'D' AND `scored`&lt;&gt;'Y' AND `type`='P' ORDER BY tournamentid DESC LIMIT 3"; $postedgames = mysql_query($query_postedgames) or die(mysql_error()); $row_postedgames = mysql_fetch_assoc($postedgames); $totalRows_postedgames = mysql_num_rows($postedgames); //Report Score $query_score = "SELECT * FROM tgtournamentrequest WHERE `fteam` = \"$curmemid\" AND `gamer`&lt;&gt;'0' AND `isaccepted`='Y' AND `active`='Y' AND `startfteam`='Y' AND `startsteam`='Y' AND `scored` &lt;&gt;'Y' ORDER BY tournamentid DESC LIMIT 3"; $score = mysql_query($query_score) or die(mysql_error()); $row_score = mysql_fetch_assoc($score); $totalRows_score = mysql_num_rows($score); //Steam Pending Score $query_pscore = "SELECT * FROM tgtournamentrequest WHERE `gamer` = \"$curmemid\" AND `fteam`&lt;&gt;'0' AND `active`='Y' AND `startfteam`='Y' AND `startsteam`='Y' AND scored &lt;&gt;'Y' ORDER BY tournamentid DESC LIMIT 3"; $pscore = mysql_query($query_pscore) or die(mysql_error()); $row_pscore = mysql_fetch_assoc($pscore); $totalRows_pscore = mysql_num_rows($pscore); //Delete Games $query_dmoline = "SELECT * FROM tgamertournament WHERE `memberid` = \"$curmemid\" AND `deleted`&lt;&gt;'Y' AND `scored`&lt;&gt;'Y' AND `accepted` &lt;&gt;'Y' AND `targetto`='0' AND `isactive`='Y'"; $dmoline = mysql_query($query_dmoline) or die(mysql_error()); $row_dmoline = mysql_fetch_assoc($dmoline); $totalRows_dmoline = mysql_num_rows($dmoline); </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload