Php + Mysql Query Results used to create new query

PHP
1

Hi all,

My Goal is simple, I have one table where multiple users submit forms and update the table with their information. What I am looking for is to bring up previous entries to come up alongside the current entry that has been recently put in.

So I have


<?php

 mysql_connect("*****","*****","*******") or die(mysql_error()); 
 mysql_select_db("******") or die(mysql_error()); 
 
 
$sql2 = "Select * From formdata WHERE rc ='' AND mf='' AND pf=''";
$result2 = MYSQL_QUERY($sql2);
while($row2=mysql_fetch_array($result2))


if($row2[priors] == 'yes') {
	
$sql3 = "Select * From formdata WHERE loc == $row2[loc] and lastname == $row2[lastname] and firstname == $row2[firstname]";
$result3 = MYSQL_QUERY($sql3);
$row3=mysql_fetch_array($result3);
	
if ($row2[statread] == 'yes'){echo "
<tr><td bgcolor='red'><center><input type='checkbox' name='completed[]' value='$row2[form_id]'></center></td>
<td bgcolor='red'><center><input type='checkbox' name='strans[]' value='$row2[form_id]'></center></td>
<td bgcolor='red'>$row2[loc]</td>
<td bgcolor='red'>$row2[doe]</td>
<td bgcolor='red'>$row2[lastname]</td>
<td bgcolor='red'>$row2[firstname]</td>
<td bgcolor='red'>$row2[dob]</td>
<td bgcolor='red'>$row2[et]</td>
<td bgcolor='red'>$row2[v]</td>
<td bgcolor='red'><a href='#' onclick='showhide('div$row2[form_id]');('first time click');{return false;}; return false;'>$row2[priors]</td>

	<div id='div$row2[form_id]' style='display: none;'>
	<tr><td></td>
	<td></td>
	<td>$row3[loc]</td>
	<td>$row3[doe]</td>
	<td>$row3[lastname]</td>
	<td>$row3[firstname]</td>
	<td>$row3[dob]</td>
	<td>$row3[et]</td>
	<td>$row3[v]</td>
	<td><a href='' onClick='document.frm1.submit();return false'><input type='checkbox' name='report[]' value='$row3[form_id]'>$row3[priors]</td>
	<td>$row3[history]</td>
	<td>$row3[notes]</td>
	<td>$row3[op]</td><
	td>$row3[contactphone]</td>
	<td>$row3[sr]</td>
	<td><a href='/req/uploads/$row3[filepath]' target='_blank'>$row3[filename]</a></td>
	</tr>
	</div>
	
<td bgcolor='red'>$row2[history]</td>
<td bgcolor='red'>$row2[notes]</td>
<td bgcolor='red'>$row2[op]</td>
<td bgcolor='red'>$row2[contactphone]</td>
<td bgcolor='red'>$row2[sr]<td bgcolor='red'>$row2[filename]</td>
<td bgcolor='red'><center><input type='checkbox' name='missing[]' value='$row2[form_id]'></td></tr>";} 


else {echo "<tr><td><center><input type='checkbox' name='completed[]' value='$row2[form_id]'></center></td>
<td><center><input type='checkbox' name='strans[]' value='$row2[form_id]'></center></td>
<td>$row2[loc]</td>
<td>$row2[doe]</td>
<td>$row2[lastname]</td>
<td>$row2[firstname]</td>
<td>$row2[dob]</td>
<td>$row2[et]</td>
<td>$row2[v]</td>
<td><a href='#' onclick='showhide('div$row2[form_id]');('first time click');{return false;}; return false;'>$row2[priors]</td>
<td>$row2[history]</td>
<td>$row2[notes]</td>
<td>$row2[op]</td>
<td>$row2[contactphone]</td>
<td>$row2[sr]<td>$row2[filename]</td>
<td><center><input type='checkbox' name='missing[]' value='$row2[form_id]'></td></tr>

	<div id='div$row2[form_id]' style='display: none;'>
	<tr><tr><td></td>
	<td></td>
	<td>$row3[loc]</td>
	<td>$row3[doe]</td>
	<td>$row3[lastname]</td>
	<td>$row3[firstname]</td>
	<td>$row3[dob]</td>
	<td>$row3[et]</td>
	<td>$row3[views]</td>
	<td><a href='' onClick='document.frm1.submit();return false'><input type='checkbox' name='report[]' value='$row3[form_id]'>$row3[priors]</td>
	<td>$row3[history]</td>
	<td>$row3[notes]</td>
	<td>$row3[op]</td><
	td>$row3[contactphone]</td>
	<td>$row3[sr]</td>
	<td><a href='/req/uploads/$row3[filepath]' target='_blank'>$row3[filename]</a></td>
	</tr>
	</div>
";}
}

else {
		
if ($row2[statread] == 'yes'){echo <tr><td bgcolor='red'><center><input type='checkbox' name='completed[]' value='$row2[form_id]'></center></td>
<td bgcolor='red'><center><input type='checkbox' name='strans[]' value='$row2[form_id]'></center></td>
<td bgcolor='red'>$row2[loc]</td>
<td bgcolor='red'>$row2[doe]</td>
<td bgcolor='red'>$row2[lastname]</td>
<td bgcolor='red'>$row2[firstname]</td>
<td bgcolor='red'>$row2[dob]</td>
<td bgcolor='red'>$row2[et]</td>
<td bgcolor='red'>$row2[v]</td>
<td bgcolor='red'>$row2[priors]</td>
<td bgcolor='red'>$row2[history]</td>
<td bgcolor='red'>$row2[notes]</td>
<td bgcolor='red'>$row2[op]</td>
<td bgcolor='red'>$row2[contactphone]</td>
<td bgcolor='red'>$row2[sr]<td bgcolor='red'>$row2[filename]</td>
<td bgcolor='red'><center><input type='checkbox' name='missing[]' value='$row2[form_id]'></td></tr>";} 
";} 
else {echo "<tr><td bgcolor='red'><center><input type='checkbox' name='completed[]' value='$row2[form_id]'></center></td>
<td><center><input type='checkbox' name='strans[]' value='$row2[form_id]'></center></td>
<td>$row2[loc]</td>
<td>$row2[doe]</td>
<td>$row2[lastname]</td>
<td>$row2[firstname]</td>
<td>$row2[dob]</td>
<td>$row2[et]</td>
<td>$row2[v]</td>
<td>$row2[priors]</td>
<td>$row2[history]</td>
<td>$row2[notes]</td>
<td>$row2[op]</td>
<td>$row2[contactphone]</td>
<td>$row2[sr]<td bgcolor='red'>$row2[filename]</td>
<td><center><input type='checkbox' name='missing[]' value='$row2[form_id]'></td></tr>";} 
";}
}
?>

It does not work because it does not provide the results of $sql3, I changed the code while ($row3=mysql_fetch_array($result3)) {

but it then did not show $row2 results that fell in the category of having priors…

Any Ideas? I understand that nesting queries is not always the best procedure but since I already have one table I figured it should be too difficult.

2

First of all, you have many problems and even I found some syntax errors as well. Haven’t you seen any fatal errors when you run the script? You have to check the strings quotes. Some are not started and some are closed without opening ". Please check them.

Secondly, why have you used while here?


while($row2=mysql_fetch_array($result2))

This statement is wrong. If you expect multiple results from the query then you must use while with a block of code to be executed in the loop. Otherwise you don’t have to use while if it is supposed to be one result:

$row2=mysql_fetch_array($result2);

Check if the query has retrieved results/rows or not:


if(mysql_num_rows($result2) >= 1){
    $row2=mysql_fetch_array($result2);
}
else{
    echo "no results found";
}

And another big problem is, no double equals to (==) are used in the database query. Also all the string values have to be quoted by double or single quotes.


$sql3 = "Select * From formdata WHERE loc='" . $row2['loc'] . "' and lastname='" . $row2['lastname'] . "' AND firstname='" . $row2['firstname'] . "'";

For better practice, always put all the array indexes inside single or double quotes like $row2[‘loc’] not $row2[loc].

3

Speaking from painful experience, consider renaming your result sets:

from

$row2
$row3

to something more meaningful like

$old
$new

even these, which are longer I admit, but at least hint at what they contain…

$existing_details
$updated_details

It’ll make your code easier for you to read, and if you don’t get out of the habit you will end up somewhere with vars like $row9 $row10 and not know where the hell they came from or what they are supposed to contain. Ouch. Still hurts.

HTH