Select is posting info that doesnt exist

[flemingmike]

hello all,

 

i have a quick question.  i have a select all from toolout with toolid = $tid.  the problem is, if there isnt an entry yet in toolout, it is giving me the info from the row above.  is there any way that i can do a (if it doesnt exist, set $status to 1, rather than using the previous rows code)

 

my code is below.

 

if(isset($_POST['search']))
{

$tool=$_POST['tool'];

echo "<table border='1' style='border-collapse: collapse' bordercolorlight='#000000' 

bordercolordark='#000000' width='98%' align='center'>";
echo "<tr><td width='100%' colspan='5' align='center'><b>Tool List - $tool</b></td></tr>";

echo "<tr>

		<th align='center'>Tool</th>
		<th>Location</th>
		<th>Status</th>


</tr>";

$result = mysql_query("SELECT * FROM tools WHERE toolgroup LIKE '%$tool%' ORDER BY tool");
while($row = mysql_fetch_array($result))
{

$id=$row['id'];
$tool=$row['tool'];
$barcode=$row['barcode'];
$location=$row['location'];

$result2 = mysql_query("SELECT * FROM toolout WHERE toolid = '$id' ORDER BY id ASC");
while($row2 = mysql_fetch_array($result2))
{
$status=$row2['status'];
$who=$row2['who'];
$datechanged=$row2['datechanged'];

$datechanged1 = date( 'M j, Y', strtotime($datechanged) );


    
if("$status" == "1") {
$statusout="<font color='#0000FF' size='4'>IN</font><br /><a href='toolout.php?tool=" . $id . "' target='action'>SIGN OUT</a>";
 }  

if("$status" == "2") {
$statusout="<font color='#FF0000' size='4'>OUT</font><br />$who - $datechanged1";
 } 

if("$status" == "3") {
$statusout="<font color='#00FF00' size='4'>PENDING OUT</font><br />$who - $datechanged1";
 } 

if("$status" == "4") {
$statusout="<font color='#00FF00' size='4'>PENDING IN</font><br />$who - $datechanged1";
 }


}

  echo "<tr>";
  echo "<td align='center'>" . $tool . "</td>";
  echo "<td align='center'>" . $location . "</td>";
  echo "<td align='center'>";
  if (isset($status)) {
     echo "$statusout";
  } else {
 echo "<font color='#0000FF' size='4'>IN</font><br /><a href='toolout.php?tool=" . $id . "' target='action'>SIGN OUT</a>";
  } 
  echo "</td>";
   
  
  

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

}

 

Thank you so much.

[ttocskcaj]

You mean something like

//If result2 contains less than 1 row (0), set status to 1
if(1>mysql_num_rows($result2)) $status=1;

[flemingmike]

thats what i initially thought, but no luck.

 

if i have 2 tools in table tools, and the first one also has a toolid in table toolout with lets just say a status=2, the  second one is also going to be displayed as a status=2 even though there is no record with the second toolid in toolout yet.

[flemingmike]

is there a way i can write if $status doesnt return a proper value, do something?

[flemingmike]

if(mysql_num_rows($result2)>0){

[jcbones]

I would approach this as a table join.  Something like this.

 

UN-TESTED.

<?php
if(isset($_POST['search']))
{

$tool = (isset($_POST['tool'])) ? $_POST['tool'] : 1;

$sql = "SELECT 
             a.id,a.tool,a.barcode,a.location,
		 b.status,b.who,DATE_FORMAT(a.datechanged,'%b %e, %Y) AS datechanged1
		FROM
		 tools AS a
		JOIN
		 toolout AS b
		ON
		 a.id = b.toolid
		WHERE
		 a.toolgroup LIKE '%$tool%'
		ORDER BY b.id, a.tool ";

$result = mysql_query($sql) or trigger_error( mysql_error() );

if(mysql_num_rows($result) > 0) {
	echo "<table border='1' style='border-collapse: collapse' bordercolorlight='#000000'
			bordercolordark='#000000' width='98%' align='center'>";
	echo "<tr><td width='100%' colspan='5' align='center'><b>Tool List - $tool</b></td></tr>";

	echo "<tr>

			 <th align='center'>Tool</th>
			 <th>Location</th>
			 <th>Status</th>


	</tr>";
	while( $row = mysql_fetch_assoc($result) ) {
		extract($row);

		switch( (int) $status ) {
			case 1:
				$statusout="<font color='#0000FF' size='4'>IN</font><br /><a href='toolout.php?tool=" . $id . "' target='action'>SIGN OUT</a>";
				break;
			case 2:
				$statusout="<font color='#FF0000' size='4'>OUT</font><br />$who - $datechanged1";
				break;
			case 3:
				$statusout="<font color='#00FF00' size='4'>PENDING OUT</font><br />$who - $datechanged1";
				break;
			case 4:
				$statusout="<font color='#00FF00' size='4'>PENDING IN</font><br />$who - $datechanged1";
				break;
			default:
				$statusout =  "Status Unkown!";
		}

		echo "<tr>\n<td align='center'>" . $tool . "</td>\n"
		. "<td align='center'>" . $location . "</td>\n"
		. "<td align='center'>" . $statusout . "</td>\n</tr>\n"; 
	}

echo "</table>";
}
else {
	echo 'Search Results returned NULL';
}
}
?>

 

Hit me back (with a db dump)  if that query doesn't work, and I'll get it sorted for you.