LEFT JOIN not displaying all ID's

[Moorcam]

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.

 

[Moorcam]

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;
										";

 

[Phi11W]

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. 

 

[Moorcam]

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

[Moorcam]

Ok, fixed.

I changed the charter ID from 'id' to 'chtr_id' in the database. Everything is now working.