Moorcam Posted July 22 Share Posted July 22 Hi again, I bet you guys have missed me. I am trying to join two tables using LEFT JOIN to get the first name of a driver. This is kinda related to my previous topic about bootstrap modals. I am using the following to join the tables: <?php $sql = "SELECT chtr.id, chtr.charter_name, chtr.fleet_number, chtr.driver, chtr.customer_name, chtr.customer_number, chtr.dep_date, chtr.status, usr.id, usr.fname FROM charters AS chtr LEFT JOIN users AS usr ON chtr.id = usr.id "; if($result = mysqli_query($conn, $sql)){ if(mysqli_num_rows($result) > 0){ while($row = mysqli_fetch_array($result)){ ?> However, it shows all data in the table except for chtr.id bar one, which does show. I need a Guinness but I am not allowed anymore Any help would be appreciated. Quote Link to comment Share on other sites More sharing options...
Moorcam Posted July 22 Author Share Posted July 22 Ok, Changed the query a little as shown below. Shows all results now but output is multiplied by four. $sql = "SELECT *, usr.id, usr.fname FROM charters AS chtr LEFT OUTER JOIN users AS usr ON(chtr.id = usr.id) IS NOT NULL; "; Quote Link to comment Share on other sites More sharing options...
Phi11W Posted July 22 Share Posted July 22 Which field in Charters identifies the driver in question? SELECT chtr.id, chtr.charter_name, chtr.fleet_number, chtr.driver, chtr.customer_name, chtr.customer_number, chtr.dep_date, chtr.status /* \/ \____/ Which of these is the id into the users table? */ , usr.id, usr.fname FROM charters AS chtr LEFT JOIN users AS usr ON chtr.id = usr.id /* \/ Should this, perhaps, be chtr.driver? */ I would expect every table to have its own, unique id field and those ids are completely independent of one another (by which I mean Charter .id=6 is a completely different thing to Users .id=6). Regards, Phill W. 1 Quote Link to comment Share on other sites More sharing options...
Moorcam Posted July 22 Author Share Posted July 22 1 minute ago, Phi11W said: Which field in Charters identifies the driver in question? SELECT chtr.id, chtr.charter_name, chtr.fleet_number, chtr.driver, chtr.customer_name, chtr.customer_number, chtr.dep_date, chtr.status /* \/ \____/ Which of these is the id into the users table? */ , usr.id, usr.fname FROM charters AS chtr LEFT JOIN users AS usr ON chtr.id = usr.id /* \/ Should this, perhaps, be chtr.driver? */ I would expect every table to have its own, unique id field and those ids are completely independent of one another (by which I mean Charter .id=6 is a completely different thing to Users .id=6). Regards, Phill W. charter.driver contains the ID of the driver in question. Driver is selected from users table. Obviously a different ID and both tables have unique IDs Quote Link to comment Share on other sites More sharing options...
Solution Moorcam Posted July 22 Author Solution Share Posted July 22 Ok, fixed. I changed the charter ID from 'id' to 'chtr_id' in the database. Everything is now working. 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.