baldiajaib Posted July 26, 2011 Share Posted July 26, 2011 this is the first time that i've come across such error. When i tried to refresh my browser to refresh my site, the computer starts eating up my ram, and this error came out about 2 minutes later: Warning: mysql_query() [function.mysql-query]: Unable to save result set in D:\Dropbox\htdocs\bercy\gestion\abonnes_aman\function_search.php on line 64 Fatal error: Maximum execution time of 60 seconds exceeded in D:\Dropbox\htdocs\bercy\gestion\abonnes_aman\function_search.php on line 64 The problem can be solved if i comment out both these part: LINE 64: $result = mysql_query($sql_query) or die(mysql_error()); but, these errors came out: Warning: mysql_fetch_row() expects parameter 1 to be resource, null given in D:\Dropbox\htdocs\bercy\gestion\abonnes_aman\function_search.php on line 65 Warning: mysql_fetch_array() expects parameter 1 to be resource, string given in D:\Dropbox\htdocs\bercy\gestion\abonnes_aman\function_search.php on line 123 <?php include "storescripts/connectdb.php"; //Run Query For Body List---------------------------------SEARCH FORM------------------------------------------------------- $bodylist=""; $sql = mysql_query ( "SELECT * FROM choixorganisme ORDER BY org_id ASC LIMIT 30" ); $rowcount = mysql_num_rows ($sql); if ($rowcount > 0) { while ($row = mysql_fetch_array ($sql)) { $id = $row['org_id']; $body = $row['org_libelle']; $news_id = $row['news_id']; $bodylist .="<option value=\"$id\">" . $body . "</option>" ; } } //Run query for Format list $formatlist=""; $sql2 = mysql_query ( "SELECT distinct `format` FROM `subscriber` WHERE 1" ); $rowcount2 = mysql_num_rows ($sql2); if ($rowcount2 > 0) { while ($row2 = mysql_fetch_array ($sql2)) { $format = $row2['format']; $formatlist .="<option value=\"". $format . "\">" . $format . "</option>" ; } } // --------------------------------------------------PAGINATION(SEARCH RESULT)--------------------------------------------------- // database connection info include "storescripts/connectdb.php"; // find out how many rows are in the table $sql_query = "SELECT * FROM annuser, subscriber"; $result = mysql_query($sql_query) or die(mysql_error()); $r = mysql_fetch_row($result); $numrows = $r[0]; // number of rows to show per page $rowsperpage = 10; // find out total pages $totalpages = ceil($numrows / $rowsperpage); // get the current page or set a default if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) { // cast var as int $currentpage = (int) $_GET['currentpage']; } else { // default page num $currentpage = 1; } // end if // if current page is greater than total pages... if ($currentpage > $totalpages) { // set current page to last page $currentpage = $totalpages; } // end if // if current page is less than first page... if ($currentpage < 1) { // set current page to first page $currentpage = 1; } // end if // the offset of the list, based on current page $offset = ($currentpage - 1) * $rowsperpage; //SHOW FIRST 100 DATA FROM DATABASE TO PAGE AS DEFAULT $annuser_list = ''; $sql_query .= " WHERE annuser.annuser_id = subscriber.annuser_id LIMIT $offset, $rowsperpage"; /*"SELECT * FROM annuser, subscriber WHERE annuser_firstname LIKE '%". $form_name . "%' OR `annuser_lastname` LIKE '%" . $form_name . "%' AND annuser.annuser_id = subscriber.annuser_id LIMIT 10"*/ //echo $sql_query; $result = mysql_query($sql_query) or die(mysql_error()); $rowcount2 = mysql_num_rows ($result) or die (mysql_error()); //ERROR if ($rowcount2 > 0 ) { while ($row2 = mysql_fetch_array ($sql_query)) //ERROR { $annuser_firstName = stripslashes ($row2['annuser_firstname']); $annuser_lastName = stripslashes ($row2['annuser_lastname']); $annuser_email = stripslashes ($row2['annuser_email']); $annuser_dept = stripslashes ($row2['annuser_email']); $annuser_title = stripslashes ($row2['annuser_title']); $annuser_liste = stripslashes ($row2['format']); $annuser_date = stripslashes ($row2['subsc_date']); $annuser_list .= "<tr> <td>" . $annuser_title . " " . $annuser_firstName . " " . $annuser_lastName . " </td> <td>" . $annuser_email . " </td> <td>" . $annuser_dept . " </td> <td>" . $annuser_liste . " </td> <td>" . $annuser_date . " </td></tr>"; } } mysql_close(); //RUN SEARCH if (isset($_GET ['submit_search'])) { $form_name = $_GET['form_name']; $form_body = $_GET['form_dropdown_body']; $form_body = $_GET['form_dropdown_format']; $sql_query .= " WHERE annuser_firstname LIKE '%". $form_name . "%' OR `annuser_lastname` LIKE '%" . $form_name . "%'" ; $rowcount3 = mysql_num_rows ($sql_query); if ($rowcount3 > 0 ) { while ($row3 = mysql_fetch_array($sql)) { $annuser_firstName = stripslashes ($row3['annuser_firstname']); $annuser_lastName = stripslashes ($row3['annuser_lastname']); $annuser_email = stripslashes ($row3['annuser_email']); $annuser_dept = stripslashes ($row3['annuser_email']); $annuser_title = stripslashes ($row3['annuser_title']); $annuser_liste = stripslashes ($row3['format']); $annuser_date = stripslashes ($row3['subsc_date']); $annuser_list .= "<tr> <td>" . $annuser_title . " " . $annuser_firstName . " " . $annuser_lastName . " </td> <td>" . $annuser_email . " </td> <td>" . $annuser_dept . " </td> <td>" . $annuser_liste . " </td> <td>" . $annuser_date . " </td></tr>"; } } } ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <link rel="stylesheet" type="text/css" href="css/style.css" /> <title>Untitled Document</title> </head> <!-- LIST OUT FORM GENERATED FROM DATABASE--> <table id="table_form" class="table_form" cellpadding="2" cellspacing="3" border="0"> <form id="archive" class="archive" method="get" action="function_search.php"> <tr> <td> Name / Surname </td> <td> Body </td> <td> Format </td> <td> </td> </tr> <tr> <td> <input type="text" id="form_name" name="form_name" size="20"/> </td> <td> <select name="form_dropdown_body" id="form_dropdown_format" name="form_dropdown_format"> <option value="Tous"> Tous </option> <?php echo $bodylist; ?> </select></td> <td> <select name="form_dropdown_format" id="form_dropdown_format" name="form_dropdown_format"> <option value="Tous">Tous</option> <?php echo $formatlist; ?> </select> </td> <td> <input type="submit" id="submit_search" class="submit_search" name="submit_search" value="Rechercher"> </tr> </form> </table> </br></br></br> <table align="center" class="table_list" id="table_list" > <tr> <th> Abonne <img src="images/sort_asc.png" alt="sort ascending" /> <img src="images/sort_dsc.png" alt="sort descending" /> </th> <th> Email <img src="images/sort_asc.png" alt="sort ascending"/> <img src="images/sort_dsc.png" alt="sort descending" /></th> <th> Dept <img src="images/sort_asc.png" alt="sort ascending"/> <img src="images/sort_dsc.png" alt="sort descending" /></th> <th> Liste <img src="images/sort_asc.png" alt="sort ascending" /> <img src="images/sort_dsc.png" alt="sort descending" /></th> <th> Depuis <img src="images/sort_asc.png" alt="sort ascending"/> <img src="images/sort_dsc.png" alt="sort descending" /></th> </tr> <?php //echo $annuser_list_search; ?> <?php echo $annuser_list; ?> </table> <?php //----------------------------------PAGINATION LINKS----------------------------------------------------- // range of num links to show $range = 3; // if not on page 1, don't show back links if ($currentpage > 1) { // show << link to go back to page 1 $arrowWayBack = " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> "; // get previous page num $prevpage = $currentpage - 1; // show < link to go back to 1 page $arrowBack = " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> "; } // end if // loop to show links to range of pages around current page for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) { // if it's a valid page number... if (($x > 0) && ($x <= $totalpages)) { // if we're on current page... if ($x == $currentpage) { // 'highlight' it but don't make a link $currentPageNumber = " [<b>$x</b>] "; // if not current page... } else { // make it a link $page = " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> "; } // end else } // end if } // end for // if not on last page, show forward and last page links if ($currentpage != $totalpages) { // get next page $nextpage = $currentpage + 1; // echo forward link for next page $arrowForward = " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> "; // echo forward link for lastpage $arrowWayForward = " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> "; } // end if //Show pagination links at the middle of page echo "</br><div align='center'>" . $arrowWayBack, $arrowBack, $currentPageNumber, $page, $arrowForward, $arrowWayForward . "</div>"; /****** end build pagination links ******/ ?> Quote Link to comment Share on other sites More sharing options...
teynon Posted July 26, 2011 Share Posted July 26, 2011 How big is your database? Quote Link to comment Share on other sites More sharing options...
teynon Posted July 26, 2011 Share Posted July 26, 2011 Looking around the web I am seeing a couple of possibilities for this error. Try this: 1) In PHPMyAdmin, repair your tables. If that doesn't work, go to plan b: add this: or die(mysql_errno($link)); instead of mysql_error and see what the error number is. Quote Link to comment Share on other sites More sharing options...
baldiajaib Posted July 26, 2011 Author Share Posted July 26, 2011 quite big i must say...about 40000 ++ data inside the database i did what u've suggested..the same error came out without any series of number. i did both: $rowcount2 = mysql_num_rows ($result) or die(mysql_errno()) $rowcount2 = mysql_num_rows ($result) or die(mysql_errno($link)) how do i repair the tables? Quote Link to comment Share on other sites More sharing options...
LeadingWebDev Posted July 26, 2011 Share Posted July 26, 2011 The run is spent may be cause of big result sets are returned. as bigger arrays u store -> more ram u need to store them. or you may have an infinite loop, but i didn't really checked the script as i am on the work i suggest using 1 or 2 queries for all those stuff you retrieve and retrieve only needed information with limit, joins, etc. Quote Link to comment Share on other sites More sharing options...
teynon Posted July 26, 2011 Share Posted July 26, 2011 I just set up a test on my local machine. (Which is still freaking out about the huge data request I just sent.) Here is what I did: 1) Created a test database with 2 tables: "table1" and "table2" 2) I inserted a crap ton of stuff to both tables. (1million per) 3) I tried to select all the data from just one of the tables. (Goes off fine with no hitches. It takes a little bit longer than normal, but not horrible.) 4) I tried selecting both tables. (DEATH) Here is how I did it: <?php $id_link=mysql_connect("localhost", "root", ""); mysql_select_db("testing"); $sql='INSERT INTO `table` (test, value) VALUES ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"), ("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value"),("test", "value")'; for ($x=0; $x < 300; $x++) { mysql_query($sql); } ?> Then, select it: <?php $id_link=mysql_connect("localhost", "root", ""); mysql_select_db("testing"); $sql="SELECT * FROM `table`"; $bm=microtime(); $query=mysql_query($sql); $bm=microtime()-$bm; echo $bm."<hr>"; echo mysql_num_rows($query); $sql="SELECT * FROM `table`, `table2`"; $bm=microtime(); $query=mysql_query($sql); $bm=microtime()-$bm; echo $bm."<hr>"; echo mysql_num_rows($query); ?> The result: 0.054226 1100391 Warning: mysql_query() [function.mysql-query]: Unable to save result set in C:\ARSENAL\www\mysql.php on line 16 -0.75471 Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\ARSENAL\www\mysql.php on line 20 Findings: You need to find a better way to count that data. Update: I changed the SQL a little bit on the second query: <?php $id_link=mysql_connect("localhost", "root", ""); mysql_select_db("testing"); $sql="SELECT * FROM `table`"; $bm=microtime(); $query=mysql_query($sql); $bm=microtime()-$bm; echo $bm."<hr>"; echo mysql_num_rows($query); $sql="SELECT * FROM `table` AS t1, `table2` AS t2 WHERE t1.ID = t2.ID ORDER BY t1.ID ASC"; $bm=microtime(); $query=mysql_query($sql); $bm=microtime()-$bm; echo $bm."<hr>"; echo mysql_num_rows($query); ?> Result: 0.116082 11003910.277651 943200 So, it took a while, but it did work. That could be an indicator of how you could make it work better. Update again: One thing you could also do to the query, if you are just counting: SELECT 1 <-------------------------------------------- See the 1? FROM `table` AS t1, `table2` AS t2 WHERE t1.ID = t2.ID ORDER BY t1.ID ASC Running this has helped increase the runtime dramatically. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.