two "while" statements

[wkilc]

Hello,

 

I'm a noob... trying to put together two existing scripts...  one looks for a hidden value ($data) needed for updating a value in a MySQL row, the other is a tutorial I found online that allows "paging" and "sorting" of LONG tables.  They both use a "while" statement, fetch array... and it seems they both can't function at the same time.  Removing one makes the other one function.  Is there an easy way around this?

 

while($noticia = mysql_fetch_array($result))

{
if($bgcolor=='#f1f1f1'){$bgcolor='#ffffff';}
else{$bgcolor='#f1f1f1';}

  	$sql = "SELECT * FROM bocj";
    	$result = mysql_query($sql);
        while ($data = mysql_fetch_array($result))

    	{

echo "<tr >";
echo "<td align=left bgcolor=$bgcolor id='title'>$noticia[sponsor]</td>"; 
echo "<td align=left bgcolor=$bgcolor id='title'>$noticia[student]</td>"; 
echo "<td align=left bgcolor=$bgcolor id='title'>$noticia[rank]</td>"; 
echo "<td align=left bgcolor=$bgcolor id='title'><a href='index.php?p=index&a=edit&id=".$data['id']."'>edit</a></td>";

 

Thanks.

 

~Wayne

[kenrbnsn]

You're using the same variable to hold the results of the query, so the second is clobbering the first. Use a different variable:

<?php
while($noticia = mysql_fetch_array($result))

{
if($bgcolor=='#f1f1f1'){$bgcolor='#ffffff';}
else{$bgcolor='#f1f1f1';}

  	$sql = "SELECT * FROM bocj";
    	$r1 = mysql_query($sql);
        while ($data = mysql_fetch_array($r1   ))

    	{

             echo "<tr >";
             echo "<td align=left bgcolor=$bgcolor id='title'>$noticia[sponsor]</td>"; 
             echo "<td align=left bgcolor=$bgcolor id='title'>$noticia[student]</td>"; 
             echo "<td align=left bgcolor=$bgcolor id='title'>$noticia[rank]</td>"; 
             echo "<td align=left bgcolor=$bgcolor id='title'><a href='index.php?p=index&a=edit&id=".$data['id']."'>edit</a></td>";
         }
}
?>

 

Ken

[wkilc]

Thank you...

 

now no matter what I do, I get thirteen copies of each row from the table.  (There are 13 rows in the table, but I'm getting 13x 13)

 

<?
$page_name="index.php"; //  If you use this code with a different page ( or file ) name then change this

@$column_name=$_GET['column_name']; // Read the column name from query string.

////// starting of drop down to select number of records per page /////

@$limit=$_GET['limit']; // Read the limit value from query string.

$start=$_GET['start'];								// To take care global variable if OFF
if(!($start > 0)) {                         // This variable is set to zero for the first page
$start = 0;
}

$eu = ($start - 0);
$limit = 10;                                 // No of records to be shown per page.
$this1 = $eu + $limit;
$back = $eu - $limit;
$next = $eu + $limit;

/////////////// WE have to find out the number of records in our table. We will use this to break the pages///////
$query12=" SELECT * FROM bocj  ";
$result2=mysql_query($query12);
echo mysql_error();
$nume=mysql_num_rows($result2);
/////// The variable nume above will store the total number of records in the table////


/////////// Now let us print the table headers ////////////////
$bgcolor="#f1f1f1";
echo "<table width=710 align=left cellpadding=0 cellspacing=0> <tr>";
echo "<td  bgcolor='dfdfdf' > <font face='arial,verdana,helvetica' color='#000000' size='4'><a href='$page_name?column_name=sponsor'>Sponsor</a></font></td>";
echo "<td  bgcolor='dfdfdf' > <font face='arial,verdana,helvetica' color='#000000' size='4'><a href='$page_name?column_name=student'>Student</a></font></td>";
echo "<td  bgcolor='dfdfdf' > <font face='arial,verdana,helvetica' color='#000000' size='4'><a href='$page_name?column_name=instrument'>Instrument</a></font></td>";
echo "<td  bgcolor='dfdfdf'> <font face='arial,verdana,helvetica' color='#000000' size='4'>Rank</font></td></tr>";
echo "<td  bgcolor='dfdfdf'> <font face='arial,verdana,helvetica' color='#000000' size='4'> </font></td></tr>";


////////////// Now let us start executing the query with variables $eu and $limit  set at the top of the page///////////
$query1=" SELECT * FROM bocj  ";

if(isset($column_name) and strlen($column_name)>0){
$query1 = $query1. " order by $column_name";
}
$query1 = $query1. " limit $eu, $limit ";
$result1 = mysql_query($query1);
echo mysql_error();


//////////////// Now we will display the returned records in side the rows of the table/////////

while($noticia = mysql_fetch_array($result1 ))
{
if($bgcolor=='#f1f1f1'){$bgcolor='#ffffff';}
else{$bgcolor='#f1f1f1';}

$sql = "SELECT * FROM bocj";
    	$result = mysql_query($sql);
        while ($data = mysql_fetch_array($result ))

   	{


echo "<tr >";
echo "<td align=left bgcolor=$bgcolor id='title'> <font face='Verdana' size='2'>$noticia[sponsor]</font></td>";
echo "<td align=left bgcolor=$bgcolor id='title'> <font face='Verdana' size='2'>$noticia[student]</font></td>";
echo "<td align=left bgcolor=$bgcolor id='title'> <font face='Verdana' size='2'>$noticia[instrument]</font></td>";
echo "<td align=left bgcolor=$bgcolor id='title'> <font face='Verdana' size='2'>$noticia[rank]</font></td>";
echo "<td align=left bgcolor=$bgcolor id='title'> <font face='Verdana' size='2'><a href='index.php?p=index&a=edit&id=".$data['id']."'>edit</a><font></td>";

	//  This is the unused delete feature
	//   if ($_GET['a'] == "delete" AND $_GET['id'] == $data['id'])
	//   {
        //	echo " (<a href='index.php?p=index'>No</a> - <a href='index.php?p=index&a=cdelete&id=".$data['id']."'>Yes</a>) ";
	//   }
	//   else
	//  echo "<a href='index.php?p=index&a=delete&id=".$data['id']."'>delete</a>";

echo "</tr>";
}
}
echo "</table>";

/////////////// Start the buttom links with Prev and next link with page numbers /////////////////
echo "<table align = 'left' width='50%'><tr><td  align='left' width='30%'>";
//// if our variable $back is equal to 0 or more then only we will display the link to move back ////////
if($back >=0) {
print "<a href='$page_name?start=$back&column_name=$column_name'><font face='Verdana' size='2'>PREV</font></a>";
}

//////////////// Let us display the page links at  center. We will not display the current page as a link ///////////
echo "</td><td align=left width='30%'>";
$i=0;
$l=1;
for($i=0;$i < $nume;$i=$i+$limit){
if($i <> $eu){
echo " <a href='$page_name?start=$i&column_name=$column_name'><font face='Verdana' size='2'>$l</font></a> ";
}
else { echo "<font face='Verdana' size='4' color=red>$l</font>";}        /// Current page is not displayed as link and given font color red
$l=$l+1;
}


echo "</td><td  align='right' width='30%'>";
///////////// If we are not in the last page then Next link will be displayed. Here we check that /////
if($this1 < $nume) {
print "<a href='$page_name?start=$next&column_name=$column_name'><font face='Verdana' size='2'>NEXT</font></a>";}
echo "</td></tr></table>";

?>


<?
    if (isset($_POST['updatestudentbt']))
{
	if ($_POST['rank'] != '')
	{
		if (mysql_query("UPDATE bocj SET `rank` = '".$_POST['rank']."' WHERE `id`= '".$_POST['id']."'"))
			echo "Student was successfully updated!<br /><br />";
		else
			echo "Student was NOT updated!<br /><br />";
	}

}

    if ($_GET['a'] == "cdelete")
    {
	mysql_query("DELETE FROM bocj WHERE id='".$_GET['id']."'");
}


if ($_GET['a'] == "edit")
    {
	$sql = "SELECT * FROM bocj WHERE id='".$_GET['id']."'";
    	$result = mysql_query($sql);
    	$data = mysql_fetch_array($result);
?>
    <form action="index.php?p=index" method="post" name="editstudent">
     Name
 <? echo $data['student']?>
      <p>Rank<br>
        <input name="rank" type="text" id="rank" value="<? echo $data['rank']?>">
        <br />
        <br />
        <br />
        <br />
        <br />
        <br />
        <input type="hidden" name="id" value="<? echo $data['id']?>">
        <input type="submit" value="Update" name="updatestudentbt">
      </p>
  </form>
<br />
<a href="index.php?p=index">Go back</a>
<?
}
    else
    {
    ?>

 
<? } ?>

 

I'm sorry to paste so much code... but I've been at this for 8 hours and I'm losing my mind.

 

~Wayne