Retrieve data from 2 Different Mysql tables

Hi

I have a recipe website with two tables, table 1: mr_pictures, and table 2: mr_recipes

I need to retrieve 6 random recipes from the database and display them.

My problem is that the first database has the following columns id, pic_path and the second table has the following columns id, name.

I want to display 6 random images from the databases where it will randomly chose 6 id from the first table mr_pictures and displays them them pulls the name of the recipes corresponding to the first table from the second table.

Here is the code I'm using now.

Thank you in advance for your help.

<?
    $host="XXXXXXXX"; // Host name
$username="XXXXXXXX"; // Mysql username
$password="XXXXXXXX"; // Mysql password
$db_name="XXXXXXXX"; // Database name
$tbl_name="mr_pictures"; // Table name

mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");


$sql="SELECT * FROM  $tbl_name ORDER BY RAND()  LIMIT 6";
$result=mysql_query($sql);
    $cols=3;        // Here we define the number of columns
    echo "<table>";    // The container table with $cols columns
    do{
        echo "<tr>";
        for($i=1;$i<=$cols;$i++){    // All the rows will have $cols columns even if
                                    // the records are less than $cols
            $row=mysql_fetch_array($result);
            if($row){
                $img = $row['picPath'];
?>
        <td>
            <center><table>
                <tr valign="top">
                    <td width="50">&nbsp;</td>
                    <td>
                   
               
                   
                   
                    <a href="http://www.recipetryouts.com/recipes/?p=recipe&recipe=<?=$row['recipe'] ?>" target="_blank">
                    <img src="http://www.recipetryouts.com/recipes/templates/images/recipes/<?=$img ?>" width="188" height="141"/></a>
                    </td>
                    </tr>
                   
                    <!-- columns can have both text and images -->
                     <tr>
                     <td width="5">&nbsp;</td>
                     <td><?=$row['recipe'] ?></br>
                     <?=$row['vid_title'] ?></br>
                     <td width="50">&nbsp;</td>
                     </tr>
                     </td>
                  
                </tr>
           </table></center>
        </td>
<?
            }
            else{
                echo "<td>&nbsp;</td>";    //If there are no more records at the end, add a blank column
            }
        }
    } while($row);
    echo "</table>";
?>

PHP:

 

$sql="SELECT * FROM $tbl_name LEFT JOIN $tbl_name2 ON $tbl_name.id=$tbl_name2.id  ORDER BY RAND() LIMIT 6";

PHP:

http://www.w3schools.com/sql/sql_join_left.asp

 

Granted, there are quite a few bits and pieces that are bad with that code.
First, your database structure - the way it seems it's set up, you can only ever have 1 picture correspond to 1 recipe. Normally, pictures won't correspond to different recipes, but you might want to be able to have more than one picture pr recipe. This should be catered for when designing the database.
Second, you're using mysql_ - really not recommended - it's deprecated, will be removed in future versions of PHP, and we've been told for a decade (or more) to stop using it. You should either use mysqli_ or PDO to connect to the database.
Third, you're skipping in and out of PHP all the time - why not just have it all in PHP, and just echo out what you need?
Fourth: you're using tables within a td - usually, this means you've designed something wrong - I don't really think a recipe / picture combo belong in a table, and it seems you're using the table for layout - that's 10-15 year old thinking - this should just be constructed as a container with a list in it - maybe with some paragraphs explaining the procedure. Basically, a div containing the recipe and picture, picture either on top or floated/positioned where it needs to be, then a list of ingredients, and paragraphs telling the viewer how to proceed. No need for tables anywhere.

 

Thank you guys for the great help.

Thank you Markus for the script and thank you PoPSICLe for the great info

 

That's being polite... this alone:

mysql_connect("$host", "$username", "$password")or die("cannot connect");

Had me going "Oh for pissing on christmas?!?" - from there the table for nothing, center tag, target and width attributes like it's 1997, bizzaro 'fetch' method, absolute URL's...

 

Just a quick rewrite to modernize and secure that:

<?php

/*
	avoid soft-coding un/pw/host/db/table,
	variables can be dicked with and/or exposed
*/

$db = new PDO(
	'mysql:host=localHost;dbname=xxxxxx',
	'XXXXXXXX', // username
	'XXXXXXXX' // password
);


$statement = $db->query('
	SELECT * FROM mr_pictures 
	LEFT JOIN mr_othertablename
	ON mr_pictures.id = mr_othertablename.id
	ORDER BY RAND()
	LIMIT 6
');

echo '
	<ul id="recipeList">';


while ($row = $statement->fetch()) echo '
		<li>
			<a href="/recipes/?p=recipe&recipe=', $row['recipe'], '">
				<img
					src="/recipes/templates/images/recipes/', $row['picPath'], '"
					alt="', $row['vid_title'], '"
				>
				<span>
					', $row['recipe'], '<br>
					', $row['vid_title'], '
				</span>
			</a>
		</li>';
		
echo '
	</ul>';
		
?>

Code (markup):

Everything else should be handled in the CSS:

#recipeList {
	list-style:none;
	text-align:center;
}

#recipeList * {
	vertical-align:middle;
}

#recipeList li {
	display:inline;
}

#recipeList a {
	display:inline-block;
	overflow:hidden;
	padding:25px;
	white-space:nowrap; /* keep img and span together side-by-side */
	text-align:left;
}

#recipeList a img {
	margin-right:5px;
}

#recipeList span {
	display:inline-block;
}

Code (markup):