Jump to content

Lost Records?

yangkai9999

By yangkai9999
in PHP

Recommended Posts

yangkai9999 Newbie

yangkai9999

Posted
Posted

hello,I ran the code below. this code will do:1. clean all of records from existing table p1_select;2. insert new records to p1_select;3. present the p1_select data page by page.When the first page of the results looks fine. but when I click the other page link, the records in the table p1_select are gone!Where of the code I should change?Thank you,<html> <head> <link rel="stylesheet" type="text/css" href="example.css"/> <script type="text/javascript" src="sortable.js"></script> <title>Browse all records from P2</title> </head> <body><?php$hostname='localhost';$user='root';$pass='Openit4me';$dbase='mytest';$connection = mysql_connect("$hostname" , "$user" , "$pass") or die ("Can't connect to MySQL");$db = mysql_select_db($dbase , $connection) or die ("Can't select database.");if (!isset($_POST['getid'])) ( $chk=$_POST["getid"] ) ;$chk=$_POST["getid"];//delete all records from p1_select table;$_del = mysql_query("delete from p1_select");//insert the selected recorcd into p1_select table;$_ins = mysql_query("insert into p1_select select * from p1 where CHR=$chk");///////////////end;/// Variables$max_results = 53; /// Number of results per pageif(!isset($_GET['pg'])){ $pg = 1; } else { $pg = $_GET['pg']; } $from = (($pg * $max_results) - $max_results); /// Count total$totals = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM mytest.p1_select;"),0); $total_pgs = ceil($totals / $max_results);/// Page limiter & result builder$sql = "SELECT * FROM mytest.p1_select LIMIT $from, $max_results;";$result1 = mysql_query($sql);$num_sql = mysql_num_rows ($result1);$result_disp = mysql_query( "SELECT * FROM mytest.p1_select LIMIT $from, $max_results;" ) or die("SELECT Error: ".mysql_error());echo "result1: $sql<br>";echo "num_sql: $num_sql<br>";echo "Results: $totals <br>";echo "Viewing page $pg of $total_pgs<br>";// Build paginator if($pg > 1){ $prev = ($pg - 1); // Previous Link //$paginator ="<a href="".$_SERVER['PHP_SELF']."?pg=$prev">"Previous page</a>""; }$paginator ="<a href=\"{$_SERVER['PHP_SELF']}?pg=$prev\">Previous page</a>";}for($i = 1; $i <= $total_pgs; $i++){ /// Numbersif(($pg) == $i) { $paginator .= "<i>$i</i> "; } else { //$paginator .="<a href="".$_SERVER['PHP_SELF']."?pg=$i">$i</a> "; }}$paginator .="<a href=\"{$_SERVER['PHP_SELF']}?pg=$i\">$i</a> "; }}if($pg < $total_pgs){ $next = ($pg + 1); // Next Link //$paginator .="<a href="".$_SERVER['PHP_SELF']."?pg=$next">"Next page."</a>"; }$paginator .="<a href=\"{$_SERVER['PHP_SELF']}?pg=$next\">Next page.</a>"; }echo "$paginator<br><br>"; echo " <table class='sortable' id='sortabletable' align=center height=50 ><tr><th width=10><font face=arial size=2/><div align=center><strong>CHR</strong></div></font></th> <th width=200><font face=arial size=2/><div align=center><strong>SNP</strong></div></font></th> <th width=10><font face=arial size=2/><div align=center><strong>minorA</strong></div></font></th> <th width=10><font face=arial size=2/><div align=center><strong>majorA</strong></div></font></th> <th width=20><font face=arial size=2/><div align=center><strong>MAF</strong></div></font></th> <th width=10><font face=arial size=2/><div align=center><strong>NCHROBS</strong></div></font></th> <th width=10><font face=arial size=2/><div align=center><strong>_dom</strong></div></font></th> <th width=10><font face=arial size=2/><div align=center><strong>_rec</strong></div></font></th> <th width=10><font face=arial size=2/><div align=center><strong>_add</strong></div></font></th> <th width=50><font face=arial size=2/><div align=center><strong>position</strong></div></font></th> <th width=50><font face=arial size=2/><div align=center><strong>AddBETA</strong></div></font></th> <th width=50><font face=arial size=2/><div align=center><strong>addPvalue</strong></div></font></th> <th width=50><font face=arial size=2/><div align=center><strong>domBETA</strong></div></font></th> <th width=50><font face=arial size=2/><div align=center><strong>domPvalue</strong></div></font></th> <th width=50><font face=arial size=2/><div align=center><strong>RecBETA</strong></div></font></th> <th width=50><font face=arial size=2/><div align=center><strong>RecPvalue</strong></div></font></th> <th width=50><font face=arial size=2/><div align=center><strong>bestP</strong></div></font></th> <th width=50><font face=arial size=2/><div align=center><strong>ModelUsed</strong></div></font></th> </tr>"; /// Display resultsif ($num_sql > 0 ) {$i=0;while ($i < $num_sql) { $row= mysql_fetch_array($result_disp); echo "<tr>"; echo "<td width=10><font face=arial size=2/><div align=center><strong>". $row[ 'CHR' ] ."</strong></div></font></td> "; echo "<td width=50><font face=arial size=2/><div align=center><strong>". $row[ 'SNP' ] ."</strong></div></font></td> "; echo "<td width=10><font face=arial size=2/><div align=center><strong>". $row[ 'minorA' ] ."</strong></div></font></td> "; echo "<td width=10><font face=arial size=2/><div align=center><strong>". $row[ 'majorA' ] ."</strong></div></font></td> "; echo "<td width=20><font face=arial size=2/><div align=center><strong>". $row[ 'MAF' ] ."</strong></div></font></td> "; echo "<td width=10><font face=arial size=2/><div align=center><strong>". $row[ 'NCHROBS' ] ."</strong></div></font></td> "; echo "<td width=10><font face=arial size=2/><div align=center><strong>". $row[ '_dom' ] ."</strong></div></font></td> "; echo "<td width=10><font face=arial size=2/><div align=center><strong>". $row[ '_rec' ] ."</strong></div></font></td>"; echo "<td width=10><font face=arial size=2/><div align=center><strong>". $row[ '_add' ] ."</strong></div></font></td>"; echo "<td width=50><font face=arial size=2/><div align=center><strong>". $row[ 'position' ] ."</strong></div></font></td> "; echo "<td width=50><font face=arial size=2/><div align=center><strong>". $row[ 'AddBETA' ] ."</strong></div></font></td> "; echo "<td width=50><font face=arial size=2/><div align=center><strong>". $row[ 'addPvalue' ] ."</strong></div></font></td> "; echo "<td width=50><font face=arial size=2/><div align=center><strong>". $row[ 'domBETA' ] ."</strong></div></font></td> "; echo "<td width=50><font face=arial size=2/><div align=center><strong>". $row[ 'domPvalue' ] ."</strong></div></font></td>"; echo "<td width=50><font face=arial size=2/><div align=center><strong>". $row[ 'RecBETA' ] ."</strong></div></font></td>"; echo "<td width=50><font face=arial size=2/><div align=center><strong>". $row[ 'RecPvalue' ] ."</strong></div></font></td> "; echo "<td width=50><font face=arial size=2/><div align=center><strong>". $row[ 'bestP' ] ."</strong></div></font></td> "; echo "<td width=50><font face=arial size=2/><div align=center><strong>". $row[ 'ModelUsed' ] ."</strong></div></font></td>";echo "</tr>";++$i;} echo "</table>";}echo "<br>$paginator";?> <p> </p><p> </p><p> </p> <! go back to main page> <form action="./index.html"> <p><input type="submit" value="Back to Home Page" name="Back to Home Page" /n> </p> </form> </body></html>

Link to comment
Share on other sites
jlhaslip Devoted Member

jlhaslip

Posted
Posted

$chk=$_POST["getid"];//delete all records from p1_select table; <-- this line and $_del = mysql_query("delete from p1_select"); <-- this line deletes//insert the selected recorcd into p1_select table;$_ins = mysql_query("insert into p1_select select * from p1 where CHR=$chk"); <-- this line isn't making much sense eitherThe script deleted them because it is doing what you asked it to.You need to block out or remove the Delete function from this code.I would suggest having 2 scripts, one for deleting and another for displaying. Or have this script look for the delete function based on a specific request in the query string. ie: act=delete

Link to comment
Share on other sites
justsomeguy w3schools Guru

justsomeguy

Posted
Posted

I think that code should be fine, this looks a little strange though:/// Count total$totals = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM mytest.p1_select;"),0); $total_pgs = ceil($totals / $max_results);/// Page limiter & result builder$sql = "SELECT * FROM mytest.p1_select LIMIT $from, $max_results;";$result1 = mysql_query($sql);$num_sql = mysql_num_rows ($result1);$result_disp = mysql_query( "SELECT * FROM mytest.p1_select LIMIT $from, $max_results;" ) or die("SELECT Error: ".mysql_error());In the first line, $totals is a MySQL result, it's not the count of records. You still need to get a row from the result and then get the count from the row. For the next part, the query to count the number of records shouldn't use limit, you just want the total number of rows in the table. You can use another count query for that instead of selecting everything and then checking the number of rows. If you're trying to use $num_sql to figure out how many records to loop through, that's not necessary either. If you use a while loop instead of a for loop you can loop through all of the records in the result without needing to know how many there are.

Link to comment
Share on other sites
yangkai9999 Newbie

yangkai9999

Posted
  • Author
Posted
$chk=$_POST["getid"];//delete all records from p1_select table; <-- this line and $_del = mysql_query("delete from p1_select"); <-- this line deletes//insert the selected recorcd into p1_select table;$_ins = mysql_query("insert into p1_select select * from p1 where CHR=$chk"); <-- this line isn't making much sense eitherThe script deleted them because it is doing what you asked it to.You need to block out or remove the Delete function from this code.I would suggest having 2 scripts, one for deleting and another for displaying. Or have this script look for the delete function based on a specific request in the query string. ie: act=delete
I tried them in two ways. One is using 2 scripts and another one is the code I listed.But the samething happen.I didn't put the $_del and $_ins in display loop. the first page of display is OK. but when I click other page, the code delete the records form p1_select. I want to know where the code perform the action.thank you,
Link to comment
Share on other sites
alexnofue Member

alexnofue

Posted
Posted

Hi, are you trying to delete that table only in the first page? as it is, the table is deleted every time you run the code.maybe you need to put that after you check for page = 1

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...