How to fetch results from multiple mysql tables with only 1 holding all relationships

Question

I have the following 3 tables that I'm trying to join using a single query to read the data. The tables are users, vehicles and operators. The main table holding all the relationships is vehicles

users
id | name 
1 | Driver 1
2 | Driver 2
3 | Conductor 1
4 | Conductor 2
5 | Owner 1
6 | Owner 2


vehicles
id | reg_num|driver_id | conductor_id | owner_id | operator_id
1 | KAN100X | 2 | 4 | 6 | 2
2 | KCN200Y | 1 | 3 | 5 | 1


operators
id | name | type
1 | Operator 1 | Intercity
2 | Operator 2 | Intracity

I have tried this

class Vehicle{ 
    private $conn;

    // object properties
    public $id;
    public $name;
    public $reg_num;
    public $driver_id;
    public $conductor_id;
    public $owner_id;
    public $operator_id;
    public $type;

    function read(){
      
        $query = "
                SELECT * FROM vehicles
                LEFT JOIN users ON 'users.id' = 'vehicles.owner_id'
                LEFT JOIN users ON 'users.id' = 'vehicles.driver_id'
                LEFT JOIN users ON 'users.id = vehicles.coductor_id'
                LEFT JOIN users ON 'operators.id = vehicles.operator_id'
                ORDER BY 
                    'vehicles.id'
                DESC";

        // prepare query statement
        $stmt = $this->conn->prepare($query);

        // execute query
        $stmt->execute();

        return $stmt;
    }
}

// instantiate database and vehicle object
$database = new Database();
$db = $database->getConnection();

// initialize object
$vehicle = new Vehicle($db);




// query vehicle
$stmt = $vehicle->read();
$num = $stmt->rowCount();
  
// check if more than 0 record found
if($num>0){
  
    // vehicle array
    $vehicle_arr=array();
    $vehicle_arr["vehicle"]=array();
  
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){

        extract($row);                                                                          
  
        $vehicle_item = array(
            "vehicle_id" => $id,
            "vehicle_reg_num" => html_entity_decode($reg_num),
            "operator_name" => html_entity_decode($name),
            "operator_type" => html_entity_decode($type),
            "driver_name" => html_entity_decode($driver_name),
            "owner_name" => html_entity_decode($owner_name),
            "conductor_name" => html_entity_decode($conductor_name)

        );

        array_push($vehicle_arr["vehicle"], $vehicle_item);
    }
  
    // set response code - 200 OK
    http_response_code(200);
  
    // show vehicle data in json format

    echo json_encode(
        array("vehicle" => $vehicle_arr["vehicle"])
    );
}
else{
  
    // set response code - 404 Not found
    http_response_code(404);
  
    // tell the user no vehicle found
    echo json_encode(
        array("response_code" => 6, "response_message" => "No vehicle found.")
    );
}

How could i rewrite the query to return results?

remove the single quotes around `'users.id'` and the others. They are taken as string — Cid, Sep 03 '20 at 06:42
Whenever you join multiple instances of the same table, you need to assign a unique alias to each instance. — Strawberry, Sep 03 '20 at 06:55
Why are you storing `html_entity_encoded` data in your database? Iterated calls of `extract()` seems like poor economy for not much benefit in terms of code styling. I don't feel that this is a well-designed database structure -- it is forcing you to write convoluted and unnecessarily heavy queries. — mickmackusa, Sep 03 '20 at 07:12

score 1 · Answer 1 · answered Sep 03 '20 at 07:03

Here's an example of a query. You've not provided enough information for us to say categorically that it's the query you're looking for:

SELECT c.olumns
     , y.ou
     , a.ctually
     , n.eed
  FROM vehicles v
  LEFT 
  JOIN users o
    ON o.id = v.owner_id
  LEFT 
  JOIN users d
    ON d.id = v.driver_id
  LEFT 
  JOIN users c
     ON c.id = v.conductor_id
  LEFT 
  JOIN users u
    ON u.id = v.operator_id
 ORDER 
    BY v.id DESC

Ivo P · Accepted Answer · 2020-09-03T10:40:39.530

Your problem comes from the fact you retrieve columns with the same name from multiple tables (and even multiple times from one table, users). You will not only need to apply aliases to the column names, but also to the tables, as SQL will not know what joined version of the table Users you are referring to

Use Aliases

 SELECT owners.name AS owner_name,
        drivers.name AS driver_name,
        conductors.name as conductor_name,
        operators.name AS operator_name,
        vehicle.id
 FROM vehicles
 LEFT JOIN users AS owners ON owner.id = vehicles.owner_id
 LEFT JOIN users AS drivers ON driver.id = vehicles.driver_id
 LEFT JOIN users AS conductors ON conductors.id = vehicles.conductor_id
 LEFT JOIN users AS operators ON operators.id = vehicles.operator_id
 ORDER BY 
     vehicles.id  DESC

Add other fields you may also need to the SELECT part of this query.

And in PHP you will need the alias of these fields as well

btw: your query contains a lot of single quotes. You could use backtics, but to my opinion one should not need those backtics.

single quote: '
backtic: `

How to fetch results from multiple mysql tables with only 1 holding all relationships

2 Answers2