View Full Version : Selecting Related Info?

06-29-2011, 02:14 AM
I have two tables that are related. A Video table and a Information table. I use a video_id column in the Infomoration table to link the videos with their relevant pieces of information. The problem I have is trying to get them both out at the same time.

For example, if I select all the videos I need on a given page and store it as $videos, how would I then go about selecting all the pieces of information per-video? Would I need a two dimensional array?

For example:

$vid_info[0][0] = first vid, first info
$vid_info[0][1] = first vid, second info

What would be my select statement to even grab the info?


06-29-2011, 02:35 AM
By the way, I'm not just asking for answers, I've tried several different methods that failed, and one that worked.

The one that worked seems really inefficient though as it requires a select statement to be inside of a while loop. This was just a test but the idea works. I am very open to other suggestions!

while($row = mysql_fetch_array($video)){
$vid_id = $row['id'];
$info= mysql_query("SELECT * FROM Video_Information
WHERE video_id='$vid_id' ORDER BY info_order");
while($row2 = mysql_fetch_array($info)){
echo $row2['video_info'];

06-29-2011, 03:31 PM
I found a solution for anyone who comes across this:


//Get the id for each video
//Create an Array of foundation information based on the video ID
$foundation[$i] = mysql_query("SELECT * FROM Foundation
WHERE video_id='$video_id' ORDER BY foundation_order");



while($row = mysql_fetch_array($foundation[$i])){
echo '<li>'.$row['foundation_info'].'</li>';