Kenny Pollock Posted March 11, 2008 Share Posted March 11, 2008 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 } } } } } } ?> Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted March 11, 2008 Share Posted March 11, 2008 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. Quote Link to comment Share on other sites More sharing options...
Kenny Pollock Posted March 11, 2008 Author Share Posted March 11, 2008 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 } ?> Quote Link to comment Share on other sites More sharing options...
Barand Posted March 11, 2008 Share Posted March 11, 2008 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 Quote Link to comment Share on other sites More sharing options...
Kenny Pollock Posted March 11, 2008 Author Share Posted March 11, 2008 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 } Quote Link to comment Share on other sites More sharing options...
Barand Posted March 11, 2008 Share Posted March 11, 2008 <?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'] Quote Link to comment Share on other sites More sharing options...
Kenny Pollock Posted March 11, 2008 Author Share Posted March 11, 2008 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. Quote Link to comment Share on other sites More sharing options...
Kenny Pollock Posted March 11, 2008 Author Share Posted March 11, 2008 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(); ?> Quote Link to comment Share on other sites More sharing options...
Kenny Pollock Posted March 11, 2008 Author Share Posted March 11, 2008 Anyone? I'm stumped! Quote Link to comment Share on other sites More sharing options...
Kenny Pollock Posted March 11, 2008 Author Share Posted March 11, 2008 I've tried everything ??? Quote Link to comment Share on other sites More sharing options...
Kenny Pollock Posted March 11, 2008 Author Share Posted March 11, 2008 Figured it out, was a bad MySQL index. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.