Php mySQL loop within loop from 2 tables

Question

So, I have 2 tables and I want to try and do an iterative loop within a loop...

Table one is a list of Football Games Table 2 is a list of The channels these are on.

Table 1 has an auto increment ID for each game & then table two has a column which relates to this...

Heres an example of table 1

	
id | title                 | highlight |  date       |  time	 	|	logo	|	background
1  | Sevilla vs Celta Vigo | No        |  2017-04-28 |  18:00:00	|	La Liga	|	"background": "url"

and here of table 2

	
id | name
1  | Sports Pick 2
1  | Sports Pick 2 HD
1  | Sports Pick UHD

Now what I am wanting to do is display a loop of table 1, however for each value, also loop the channels where the ID matches in table 2

This is the code I have written so far

<?php

	echo "<pre>{
	\"fixtures\": [<pre>";


    //connect to mysql db
    $con = mysql_connect("localhost","DATABASEUSERHERE","DATABASEPASSWORDHERE") or die('Could not connect: ' . mysql_error());
    //connect to the employee database
    mysql_select_db("DATABASENAMEHERE", $con);

	$sql = "SELECT * FROM fixture_list";
	$result = mysql_query($sql);

	while ($row = mysql_fetch_array($result)) {
	    echo '{
			"title": "'.$row[title].'",
			"highlight": "'.$row[highlight].'",
			"date": "'.$row[date].'",
			"time": "'.$row[time].'",
			"logo": "'.$row[logo].'",'.$row[background].'
			';
		echo '"channels": [
				{ "name": "Sports Pick 2" },
				{ "name": "Sky Sports 2" }
			]
		},';
	}
	
	echo "
	]
}";


?>

so this works well, but i've only got the 1st loop running here and am struggling to see how or where I'd pop the second loop and how i'd do a foreach or something like that to get the relevant values in the second loop...

Here is a copy of the desired output I am looking for

{
	"fixtures": [
{
			"title": "Sevilla vs Celta Vigo",
			"highlight": "No",
			"date": "2017-04-28",
			"time": "18:00:00",
			"logo": "La Liga",
			"background": "URL",
			"channels": [
				{ "name": "Sports Pick 2" },
				{ "name": "Sports Pick 2 HD" },
				{ "name": "Sports Pick UHD" }
			]
		},
	]
}

I'm looking forward to some guidance on this and seeing how this works out... thanks in anticipation !

Junius L · Accepted Answer · 2017-04-28 09:48:11Z

1

Use mysqli as msql has been deprecated

<?php

echo "<pre>{
    \"fixtures\": [<pre>";


//connect to mysql db
$con = mysqli_connect("localhost","root","000000");
// Check connection
if (mysqli_connect_errno())
{
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
    die();
}
//connect to the employee database
mysqli_select_db($con, 'ng_app');

$sql = "SELECT * FROM fixture_list";
$result = mysqli_query($con, $sql);
$json = '';
while ($row = mysqli_fetch_array($result)) {
    $json .= '{
            "title": "'.$row['title'].'",
            "highlight": "'.$row['highlight'].'",
            "date": "'.$row['date'].'",
            "time": "'.$row['time'].'",
            "logo": "'.$row['logo'].'",
            "background" : "' . $row['background'] . '",
            ';
    $json .= '"channels": [
                ' . getChannels($row['id'], $con) . '
            ]
        },';
}
echo rtrim($json, ',');
echo "
    ]
}";


function getChannels($fixture_id, $con) {
    $sql = "SELECT * FROM `channels` WHERE `id` = $fixture_id";
    $result = mysqli_query($con, $sql);
    $data = '';
    while ($row = mysqli_fetch_array($result)) {
        $data .= '{"name": "' . $row['name'] .'"},';
    }
    return rtrim($data, ',');
}

?>

Output

{
"fixtures": [{
  "title": "Sevilla vs Celta Vigo",
  "highlight": "No",
  "date": "2017-04-28 00:00:00",
  "time": "18:00:00",
  "logo": "La Liga",
  "background": "URL",
  "channels": [{
    "name": "Sports Pick 2"
  }, {
    "name": "Sports Pick 2 HD"
  }, {
    "name": "Sports Pick UHD"
   }]
 }]
}

edited Apr 28, 2017 at 9:48

answered Apr 27, 2017 at 18:24

Junius L

16.2k6 gold badges61 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Henry Aspden Over a year ago

this is almost spot on... except I need there to be a comma betweet each of the fixtures but not after the last one... any thoughts>

Junius L Over a year ago

@HenryAspden see my updated answer, save the results in a variable then use rtrim to remove the trailing comma.

Paul Spiegel · Accepted Answer · 2017-04-27 17:56:13Z

I would select both tables in two queries and do the assignments in PHP.

Since I don't know the mysql_* functions - Here is a mysqli solution:

$con = new mysqli('localhost', 'DATABASEUSERHERE', 'DATABASEPASSWORDHERE', 'DATABASENAMEHERE');

$data = [];
$result = $con->query("SELECT * FROM fixture_list");
while($row = $result->fetch_assoc()) {
    $id = $row['id'];
    unset($row['id']);
    $row['channels'] = [];
    $data[$id] = $row;
}

$result = $con->query("SELECT * FROM channels");
while($row = $result->fetch_assoc()) {
    $data[$row['id']]['channels'][] = ['name' => $row['name']];
}

You can now loop throug the $data array and print the values. Or just use json_encode:

echo json_encode($data);

Or

echo json_encode($data, JSON_PRETTY_PRINT);

Collectives™ on Stack Overflow

Php mySQL loop within loop from 2 tables

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related