LEFT JOIN maybe?

[Kenny Pollock]

Alright... I need data from a bunch of tables based on the results of one table. I don't know a thing about LEFT JOINs, and some tables have the same names. Any suggestions for cleaning this up?

 

<?php

$order_query = mysql_query( "SELECT * FROM order_header ORDER BY id DESC LIMIT 10" );

while ( $or = mysql_fetch_array( $order_query ) )
{
	$client_query = mysql_query( "SELECT * FROM client_master WHERE id = '" . $or['client_id'] . "'" );

	while ( $c = mysql_fetch_array( $client_query ) )
	{
		$origin_query = mysql_query( "SELECT * FROM order_origin WHERE order_id = '" . $or['id'] . "'" );

		while ( $o = mysql_fetch_array( $origin_query ) )
		{
			$destination_query = mysql_query( "SELECT * FROM order_destination WHERE order_id = '" . $or['id'] . "'" );

			while ( $d = mysql_fetch_array( $destination_query ) )
			{
				$status_query = mysql_query( "SELECT * FROM order_status WHERE id = '" . $or['status'] . "'" );

				while ( $s = mysql_fetch_array( $status_query ) )
				{
					$driver_query = mysql_query( "SELECT * FROM driver_master WHERE id = '" . $or['driver_id'] . "'" );

					while ( $d = mysql_fetch_array( $driver_query ) )
					{
						if( $or['driver_id'] == '1' || $or['driver_id'] == '' )
						{
							$driver = 'N/A';
						}
						else
						{
							$driver = $d['name'];
						}

?>

	<tr onclick="location.href='index.php?action=update_order&id=<?php echo $or['load_number']; ?>';">
		<td><?php echo $or['id']; ?></td>
		<td><?php echo $s['status']; ?></td>
		<td><?php echo $c['name']; ?></td>
		<td><?php echo $driver; ?></td>
		<td><?php echo $o['city']; ?>, <?php echo $o['state']; ?></td>
		<td><?php echo $d['city']; ?>, <?php echo $d['state']; ?></td>
		<td><?php echo $u['userid']; ?></td>
		<td><?php echo date( 'M d, Y', $or['date_entered'] ); ?></td>
		<td><?php echo date( 'M d, Y', $or['pickup_date'] ); ?></td>
		<td><?php echo date( 'M d, Y', $or['delivery_date'] ); ?></td>
	</tr>

<?php
					}
				}
			}
		}
	}
}

?>

[pocobueno1388]

Okay, your master query is going to look like this

 

SELECT oh.*, cm.*, oo.*, od.*, os.*, dm.* 
FROM order_header oh
LEFT JOIN client_master cm
ON cm.client_ID=oh.client_id
LEFT JOIN order_origin oo
ON oo.client_id=oh.client_id
LEFT JOIN order_destination od
ON od.client_id=oh.client_id
LEFT JOIN order_status os
ON os.client_id=oh.client_id
LEFT JOIN driver_master dm
ON dm.client_id=oh.client_id
ORDER BY oh.id DESC
LIMIT 10

 

I'm assuming all your tables are linked by client_id. Also, your selecting ALL (*) from every single table, are you really using every bit of that data? You should go through and actually only select the fields your going to use.

 

Not positive if that query will give you what you want, but it's worth a try.

[Kenny Pollock]

Thanks to your example, I modified it how it went (all tables relate in different ways)... but the page isn't loading now...

 

	<?php

$order_query = mysql_query( "SELECT oh.*, cm.*, oo.*, od.*, os.*, dm.* FROM order_header oh LEFT JOIN client_master cm ON cm.id=oh.client_id LEFT JOIN order_origin oo ON oo.order_id=oh.id LEFT JOIN order_destination od ON od.order_id=oh.id LEFT JOIN order_status os ON os.id=oh.status LEFT JOIN driver_master dm ON dm.id=oh.driver_id ORDER BY oh.id DESC LIMIT 10" );

while ( $or = mysql_fetch_array( $order_query ) )
{

?>

	<tr onclick="location.href='index.php?action=update_order&id=<?php echo $or['load_number']; ?>';">
		<td><?php echo $or['id']; ?></td>
		<td><?php echo $s['status']; ?></td>
		<td><?php echo $c['name']; ?></td>
		<td><?php echo $driver; ?></td>
		<td><?php echo $o['city']; ?>, <?php echo $o['state']; ?></td>
		<td><?php echo $d['city']; ?>, <?php echo $d['state']; ?></td>
		<td><?php echo $u['userid']; ?></td>
		<td><?php echo date( 'M d, Y', $or['date_entered'] ); ?></td>
		<td><?php echo date( 'M d, Y', $or['pickup_date'] ); ?></td>
		<td><?php echo date( 'M d, Y', $or['delivery_date'] ); ?></td>
	</tr>

<?php

}

?>

[Barand]

A few observations

 

SELECT * is seldom a good practice.

 

A LEFT JOIN B is only used when there may not be a matching record in B but you want to list A regardless. Mostly you need INNER JOIN ( or just JOIN ) to select matching records

 

All the data is fetched into the $or array; $s, $c, $o etc do not exist

[Kenny Pollock]

No results coming up.... here's what I have now

 

$order_query = mysql_query( "SELECT oh.client_id, oh.id, oh.status, oh.driver_id, cm.id, cm.name, oo.order_id, oo.city, oo.state, od.city, od.state, od.order_id, os.id, os.status, dm.id, dm.name FROM order_header oh JOIN client_master cm ON cm.id=oh.client_id JOIN order_origin oo ON oo.order_id=oh.id JOIN order_destination od ON od.order_id=oh.id JOIN order_status os ON os.id=oh.status JOIN driver_master dm ON dm.id=oh.driver_id ORDER BY oh.id DESC LIMIT 10" );

while ( $or = mysql_fetch_array( $order_query ) )
{




?>

	<tr onclick="location.href='index.php?action=update_order&id=<?php echo $or['id']; ?>';">
		<td><?php echo $or['id']; ?></td>
		<td><?php echo $or['status']; ?></td>
		<td><?php echo $or['name']; ?></td>
		<td><?php echo $driver; ?></td>
		<td><?php echo $or['city']; ?>, <?php echo $o['state']; ?></td>
		<td><?php echo $or['city']; ?>, <?php echo $d['state']; ?></td>
		<td><?php echo $or['userid']; ?></td>
		<td><?php echo date( 'M d, Y', $or['date_entered'] ); ?></td>
		<td><?php echo date( 'M d, Y', $or['pickup_date'] ); ?></td>
		<td><?php echo date( 'M d, Y', $or['delivery_date'] ); ?></td>
	</tr>

<?php

}

[Barand]

<?php 
$order_query = mysql_query( "SELECT oh.client_id, oh.id, oh.status, oh.driver_id, 
        cm.id, cm.name, 
        oo.order_id, oo.city, oo.state, 
        od.city, od.state, od.order_id, 
        os.id, os.status, 
        dm.id, dm.name 
        FROM order_header oh 
            JOIN client_master cm ON cm.id=oh.client_id 
            JOIN order_origin oo ON oo.order_id=oh.id 
            JOIN order_destination od ON od.order_id=oh.id 
            JOIN order_status os ON os.id=oh.status 
            JOIN driver_master dm ON dm.id=oh.driver_id 
        ORDER BY oh.id DESC 
        LIMIT 10" );
?>  

 

More observations

 

You pull records where oh.id = oo.order_id. As these will always have the same value, why select both of them?

 

Same goes for od_order_id, os_id and oh_status etc.

 

You still have $o['state'] and $d['state']. $o and $d do not exist. If you have columns in different tables with the same name then give them column aliases to distinguish.

 

eg "select oo.state as ostate, od.state as dstate" then output as $or['ostate'], $or['dstate']

[Kenny Pollock]

Right... I need data from oo where the order ID is the same ID that's in the oh table. Same goes for od.

 

I understand those variables don't exist... and I will eventually fix them... but I'm wondering why my page pretty much freezes when I load it.

[Kenny Pollock]

Latest... page isn't loading still

 

	<?php

$order_query = mysql_query( "SELECT oh.client_id, oh.id AS load_number, oh.status, oh.order_uid, oh.driver_id, cm.id, cm.name AS client_name, oo.order_id, oo.city AS origin_city, oo.state AS origin_state, od.city AS destination_city, od.state AS destination_state, od.order_id, os.id, os.status, dm.id, dm.name FROM order_header oh INNER JOIN client_master cm ON cm.id=oh.client_id INNER JOIN order_origin oo ON oo.order_id=oh.id INNER JOIN order_destination od ON od.order_id=oh.id INNER JOIN order_status os ON os.id=oh.status INNER JOIN driver_master dm ON dm.id=oh.driver_id ORDER BY oh.id DESC LIMIT 10" );

while ( $or = mysql_fetch_array( $order_query ) )
{

?>

	<tr onclick="location.href='index.php?action=update_order&id=<?php echo $or['load_number']; ?>';">
		<td><?php echo $or['load_number']; ?></td>
		<td><?php echo $or['status']; ?></td>
		<td><?php echo $or['client_name']; ?></td>
		<td><?php echo $driver; ?></td>
		<td><?php echo $or['origin_city']; ?>, <?php echo $or['origin_state']; ?></td>
		<td><?php echo $or['destination_city']; ?>, <?php echo $or['destination_state']; ?></td>
		<td><?php echo $or['order_uid']; ?></td>
		<td><?php echo date( 'M d, Y', $or['date_entered'] ); ?></td>
		<td><?php echo date( 'M d, Y', $or['pickup_date'] ); ?></td>
		<td><?php echo date( 'M d, Y', $or['delivery_date'] ); ?></td>
	</tr>

<?php

}

echo mysql_error();

?>

[Kenny Pollock]

Anyone? I'm stumped!

[Kenny Pollock]

I've tried everything  ???

[Kenny Pollock]

Figured it out, was a bad MySQL index.