Member Avatar for buzz-lightyear

Hi,

I'm a bit of a noob with PHP.

I am trying to retrieve information from multiple tables with the following code

<?php
	function check_input($id)
				{
				// Stripslashes
				if (get_magic_quotes_gpc())
				  {
				  $id = stripslashes($id);
					}
				// Quote if not a number
				if (!is_numeric($id))
				  {
				  $id = "'" . mysql_real_escape_string($id) . "'";
				  }
				return $id;
				}
				include 'private/auth.php'; //sql login file
			if (!$con)
  				{
  				die('Could not connect: ' . mysql_error());
				  				}
				mysql_select_db("$database");
				$id=$_GET["id"];
				$id=check_input($id);
				$query="SELECT * FROM products WHERE id=$id";
				
				$result=mysql_query($query);
				while($row = mysql_fetch_array($result))
				{
				echo "<table>";
				echo "<tr><td colspan=2><h3>" . $row['Name'] . " - " . $row['Referance'] . "</h3></td></tr>";
				echo "<tr><td><p>Other Details</p></td><td><p>" . $row['Details'] . "</p></td></tr></table>";
				}

{
					$id2=$row['id'];
				  $query2="SELECT * FROM transactions WHERE ProdId='$id2' limit 10";
					$result2=mysql_query($query2);
					echo "Last 10 Hires:<table>";
					while ($trans = mysql_fetch_array($result2)); 
					{
					$DocketId=$trans['DocketId'];
					$query3="SELECT * FROM dockets WHERE id='$DocketId'";
					$result3=mysql_query($query3);
					while ($DocketInfo = mysql_fetch_array($result3))							
						{
						echo "<tr><td><a href='docket.php?id=" . $DocketId . "'>" . $DocketId . "</a></td>";
						$query4="Select * FROM customers WHERE id='$DocketInfo[CustId]'";
						$result4=mysql_query($query4);
						while ($CustInfo=mysql_fetch_array($result4))
							{
							echo "<tr><td><a href='docket.php?id=" . $DocketInfo['CustId'] . "'>" . $CustInfo['Name'] . "</a></td></tr>";
							}
						}
					}
					echo "</table>";		
					echo $query2;		
					echo $query3;		
					echo $query4;		
				  }

				mysql_close($con);
	?>

My Problem is that all the queries seem to be executed at the same time.
The query in $query2 is using information retrieved from $query, and the query in $query3 is using information from $query2, and the query in $query4 is using information retrieved in $query3, this means $query2, $query3, and $query4 is not working.

Any help would be greatly apreciated, it may be that I am doing this completely wrong!

Thanks in advance!

  • 2 Contributors
  • 2 Replies
  • 230 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by buzz-lightyear

Dani AI

Generated

The thread shows the exact kind of bug that silently breaks loop logic: a stray semicolon after a control statement. confirmed removing that semicolon fixed the problem, and 's suggestion to keep assignments (like the product id) inside the producing loop and to guard query calls with an if(...) check are sensible debugging steps.

Why the semicolon matters: while ($row = fetch(...)); ends the loop immediately (an empty loop body). The block that follows then runs once after the loop, so any variables expected to be set per iteration are either unset or reflect the loop’s final state. Common related pitfalls are misplaced braces and not checking query results, which make debugging harder.

Recommended fixes and safer approach:

  • Remove the stray semicolon and ensure the opening { for the loop immediately follows the while(...).
  • Validate the incoming id (for example with filter_input(INPUT_GET,'id',FILTER_VALIDATE_INT) or casting to (int)).
  • Check every query result and inspect errors during development ($stmt/$result checks or exceptions).
  • Prefer a single JOIN query (or prepared statements) to avoid deeply nested queries and repeated round-trips to the database.

Example (PDO + JOIN, concise pattern):

$pdo = new PDO($dsn,$user,$pass,[PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION]);
$id = filter_input(INPUT_GET,'id',FILTER_VALIDATE_INT);
$sql = "
  SELECT p.Name, p.Reference, p.Details, t.DocketId, c.Name AS CustomerName
  FROM products p
  LEFT JOIN transactions t ON t.ProdId = p.id
  LEFT JOIN dockets d ON d.id = t.DocketId
  LEFT JOIN customers c ON c.id = d.CustId
  WHERE p.id = :id
  ORDER BY t.hire_date DESC
  LIMIT 10
";
$stmt = $pdo->prepare($sql);
$stmt->execute([':id'=>$id]);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { /* render safely */ }

Additional notes: the old mysql_* functions are deprecated/removed in modern PHP—use PDO or mysqli with prepared statements to avoid SQL injection and to work on current PHP versions. Temporary query echoes are useful for debugging but must be removed before production.

Recommended Answers

All 2 Replies

Member Avatar for mathieu89

On line 36 you appear to have $id2 outside the braces of the first query so i dont think it would have a value. Try moving $id2 to line 32 and make sure all other variables are with the while loop of the query.

one thing you could try is to use an if statement like below, it may help to identify any other breakdowns in the code because it will stop you there.

if ($result=mysql_query($sql)) {
  while ($row=mysql_fetch_assoc($result)) {

Another thing is that you may need to have your where clause variable in this format '".$var."' rather than '$var'

Edited by mathieu89 because: n/a
Member Avatar for buzz-lightyear

Hi and thanks for your reply, I just got it sorted, the problem was with the semi-colon at the end of line 40, d'oh!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.