Why do i double my display in json

Question

So i fetch my data from two tables in my php and encode it in one json object. I got everything i needed except that it doubles the display. And my teamone is located in the matches tables. instead of starting from array 0, it starts after the schedules tables. Which is array 7. I dont know why this happen.

Here is my php.

  $sql = "SELECT * from schedule, matches;";  

 $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("start"=>$row[4],"end"=>$row[5],"venue"=>$row[6], "teamone"=>$row[8], "teamtwo"=>$row[9], 



"s_name"=>$row[17]));



}





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



mysqli_close($con);

?>

Here is my display. As you can see there are four displays but in my database it only has 2. It doubles the display. How do i fix this? Thanks

{ "schedule_response":[

             {"start":"2016-11-10 00:00:00","end":"2016-11-04 00:00:00","venue":"bbbb","teamone":"aaa","teamtwo":"bbb",

            "s_name":"hehehe"},

            {"start":"2016-11-23 00:00:00","end":"2016-11-24 00:00:00","venue":"bbbbbbbb","teamone":"aaa","teamtwo":"bbb",

            "s_name":"hehehe"},

            {"start":"2016-11-10 00:00:00","end":"2016-11-04 00:00:00","venue":"bbbb","teamone":"ehe","teamtwo":"aha",

            "s_name":"aaa"},

            {"start":"2016-11-23 00:00:00","end":"2016-11-24 00:00:00","venue":"bbbbbbbb","teamone":"ehe","teamtwo":"aha",

            "s_name":"aaa"}]}

I need to get the teamone, teamtwo and s_name values from the matches while i need the start, end and the venue from the schedule table in one query.
Schedule table

Matches Table

Answer 1

Because of your SQL query, you should not forget to perform some form of a grouping (the way you select results from both table defines that):

 $sql = "SELECT * from schedule s, matches m GROUP BY s.id";  //I assume that your table schedule has an `id`

Or, you can rework the query to be more readable:

$sql = "SELECT s.*, m.* FROM schedule s 
       INNER JOIN matches m ON m.schedule_id = s.id 
       GROUP BY s.id"; //I assume that you have such database design that you have defined foreign key on table `matches`.

Of course, the INNER JOIN above could be LEFT OUTER JOIN - it all depends on your database design.

Why do i double my display in json

Sign up