orange orange - 9 months ago 38
MySQL Question

Combining 3 tables in one single select query in php

how do I combine these 3 tables in one single query in php? When I use this query I get the values from matches and sport as null. But when I change the last right join to left join the value of schedule becomes null. How can I fix this? Thanks.


$db_name = "atfest_db";
$mysql_user = "root";
$mysql_pass = "";
$server_name = "localhost";
$sql = "SELECT teamone,teamtwo,s_name,start,end,venue FROM `sport` right JOIN `matches` ON `matches`.`s_id` = `sport`.`s_id` right JOIN `schedule` ON `schedule`.`m_id` = `matches`.`m_id`";
$con = mysqli_connect($server_name,$mysql_user,$mysql_pass,$db_name);
$result = mysqli_query($con,$sql);

$response = array();


array_push($response, array("teamone"=>$row[0], "teamtwo"=>$row[1],


echo json_encode (array("schedule_response"=>$response));


Sport table is connected to matches b s_id while matches table is connected to schedule table by m_id.

Matches Table
enter image description here

Schedule Table
enter image description here

Sport Table
enter image description here

This is the output i get


What i need is for the start,end and venue to get the values from the schedules table. Something like this

{"schedule_response":[{"start":2016-11-30 00:00:00,"end":2016-12-01

Sample data of all tables
enter image description here

Answer Source

INNER JOIN sport and matches then LEFT JOIN schedule

Matches.m_id says 150 and 151 while schedule.m_id says null and null.

Try and replace the NULL and NULL in schedule.m_id to 150 and 151 then check your result