orange orange - 11 days ago 4
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.

PHP

<?php
$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();


while($row=mysqli_fetch_array($result))

{
array_push($response, array("teamone"=>$row[0], "teamtwo"=>$row[1],
"s_name"=>$row[2]));

}

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




mysqli_close($con);
?>


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

{"schedule_response":[{"start":null,"end":null,"venue":null,"teamone":
"hehe","teamtwo":"haha","s_name":"Soccer"}


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
00:00:00,"venue":aaaaaa,"teamone":"hehe","teamtwo":"haha","s_name":"Soccer"},


Sample data of all tables
enter image description here

Answer

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

Comments