Could this be cleaned up

[TheCase]

Hi,

This is my code

<?php

//Query for the shedule

$q = " SELECT time, day, month, year, presenter1id, presenter2id, engineerid, presenter1status, presenter2status, engineerstatus, presenter1comment, presenter2comment, egineercomment FROM showbeta ORDER BY month, day, time ASC ";

//Create the table head

echo '<table border="0" width="120%" cellspacing="3">
<tr>

<td align="center"><b>Date / Time</b></td>
<td align="center" width="20%"><b>Presenter 1</b></td>
<td align="center" width="20%"><b>Presenter 2</b></td>
<td align="center" width="20%"><b>Engineer</b></td>
<td align="center" width="60%"><b>Comment</b></td>

</tr>';

$r = mysql_query ($q);

while ($row = mysql_fetch_array ($r, MYSQL_ASSOC)) {

echo "\t<tr>

<td align=\"center\">{$row['time']}</td> ";

// Relationship database presenter1
$qp1 = mysql_query("SELECT username FROM users, showbeta WHERE users.id = {$row['presenter1id']} AND users.id = showbeta.presenter1id ") or die('Query failed: ' . mysql_error());
list ($presenter1username) = mysql_fetch_array ($qp1);

echo "
	<td align=\"center\">$presenter1username</td>
	";

// Relationship database presenter2
$qp2 = mysql_query("SELECT username FROM users, showbeta WHERE users.id = {$row['presenter2id']} AND users.id = showbeta.presenter2id ") or die('Query failed: ' . mysql_error());
list ($presenter2username) = mysql_fetch_array ($qp2);

echo "
<td align=\"center\">$presenter2username</td> ";

// Relationship database engineer
$qe = mysql_query("SELECT username FROM users, showbeta WHERE users.id = {$row['engineerid']} AND users.id = showbeta.engineerid ") or die('Query failed: ' . mysql_error());
list ($engineerid) = mysql_fetch_array ($qe);	

echo "
<td align=\"center\">$engineerid</td>
<td align=\"center\">{$row['month']}</td>
</tr>\n";

}

echo '</table>';

?>

The reason why Im looking for another way is because there are 3 queries within a while loop so it will be huge on the resources once the database grows, so if you have any alternative better suggestions I would be greatfull to see them.

Thanks

[NogDog]

If I have correctly analyzed the issue, I believe you could just use one query:

$sql = "
SELECT
   time, day, month, year, presenter1status, presenter2status, engineerstatus,
   presenter1comment, presenter2comment, egineercomment,
   u1.username AS presenter_1,
   u2.username AS presenter_2,
   u3.username AS engineer
FROM showbeta
   INNER JOIN users AS u1 ON u1.userid = showbeta.presenter1id
   INNER JOIN users AS u2 ON u2.userid = showbeta.presenter2id
   INNER JOIN users AS u3 ON u3.userid = showbeta.engineerid
ORDER BY month, day, time ASC
";

[TheCase]

Thanks this looks really good. I set the query up like this jus to test the time for now

	$query = mysql_query("SELECT
   time, day, month, year, presenter1status, presenter2status, engineerstatus,
   presenter1comment, presenter2comment, egineercomment,
   u1.username AS presenter_1,
   u2.username AS presenter_2,
   u3.username AS engineer
FROM showbeta
   INNER JOIN users AS u1 ON u1.userid = showbeta.presenter1id
   INNER JOIN users AS u1 ON u2.userid = showbeta.presenter2id
   INNER JOIN users AS u1 ON u3.userid = showbeta.engineerid
ORDER BY month, day, time ASC ") or die('Query failed: ' . mysql_error());

 list ($time) = mysql_fetch_array ($query);   

but I got error Query failed: Not unique table/alias: 'u1'

Any ideas

Thanks

[NogDog]

Yeah, typo on my part...the JOIN section should read:

   INNER JOIN users AS u1 ON u1.userid = showbeta.presenter1id
   INNER JOIN users AS u[color=red]2[/color] ON u2.userid = showbeta.presenter2id
   INNER JOIN users AS u[color=red]3[/color] ON u3.userid = showbeta.engineerid 

I'll go fix it in my first reply now.

[TheCase]

Thanks for the correction. I get now

Query failed: Unknown column 'u1.userid' in 'on clause'

Fixed:

INNER JOIN users AS u1 ON u1.id = showbeta.presenter1id
INNER JOIN users AS u2 ON u2.id = showbeta.presenter2id
INNER JOIN users AS u3 ON u3.id = showbeta.engineerid

Seems to be working now, thanks alot.