Foreign Key help please

[Mr Chris]

Hi All

On the frontpage of my site I call out a link:

[code=php:0]
<?
$SQL = "select * from cms_stories WHERE section = 'sport' ORDER BY story_id DESC LIMIT 1";
$result = mysql_query($SQL) OR die(mysql_error());
$row = mysql_fetch_array($result, MYSQL_ASSOC);
echo "<a href='../live_site/sport/story.php?story_id={$row[story_id]}' class='morelink'>Read More...</a>";
?>
[/code]

Which gets the last [b]story_id[/b] story added to the database…

[code=php:0]
<?
$SQL = "select * from cms_stories WHERE section = 'sport' ORDER BY story_id DESC LIMIT 1,1";
$result = mysql_query($SQL) OR die(mysql_error());
$row = mysql_fetch_array($result, MYSQL_ASSOC);
echo "<a href='../live_site/sport/story.php?story_id={$row[story_id]}' class='greyarrow'>{$row[headline]}</a>";
?>
[/code]

Which gets the second from last [b]story_id[/b] story added to the database…

Etc…

Which is fine,

But now, I not only want to output data from my [b]cms_stories[/b] table, but also from a table called [b]cms_pictures[/b] which holds [b]story_id[/b] as a foreign key so I can link the two tables together.

Now what I want to do is to get the story_id for each of the two php queries (above) and [B]THEN[/B] ask the query to also look in the [b]cms_photos[/b] table once it has got the story_id from the [b]cms_stories[/b] table and output the variable $photo from the [b]cms_photos[/b] tble where story_id = '$story_id'?

Now I thought it may be something like this:

[code=php:0]

<?
$SQL = "SELECT * FROM cms_stories, cms_pictures WHERE section = 'news' AND story_id = '$story_id'";
$result = mysql_query($SQL) OR die(mysql_error());
$row = mysql_fetch_array($result, MYSQL_ASSOC);
echo "<img src='../pics/story_id={$row[story_id]}'>;
?>
[/code]

But that does not seem right – can anyone please advise

Thanks

Chris

[brown2005]

SELECT FIELD1, FIELD2 FROM cms_stories, cms_pictures WHERE story_id = '$story_id' AND section = 'news'

[Mr Chris]

Cheers but,

[code=php:0]
<?
$SQL = "SELECT * FROM cms_stories, cms_pictures WHERE story_id = '$story_id' AND section = 'news';
$result = mysql_query($SQL) OR die(mysql_error());
$row = mysql_fetch_array($result, MYSQL_ASSOC);
echo '<img src="new_site/pictures/big/'. $row[story_id] .'">';
?>
[/code]

Throws up an error.  It must either be in my select statement or <img> but can't see where?

[GingerRobot]

What is the error?

[Mr Chris]

Parse error: parse error, unexpected T_STRING in /**********/index3.php on line 43

Which is this line:

echo '<img src="new_site/pictures/big/'. $row[story_id] .'">';

[akitchin]

the error is that you forgot to add a closing double quote to the end of your SQL query.  that will fix the parse error, but it won't fix your query.

for good practice, one should only select from the database the fields one wants.  * should only be used when you will literally use every field from a table.  furthermore, during multi-table calls, you must explicitly specify which field is coming from which table via table.field notation.  this INCLUDES where clauses.  it sounds like the type of query you're after is:

[code]SELECT cms_stories.story_id AS sid, cms_pictures.photo AS photo
FROM cms_stories, cms_pictures
WHERE cms_stories.story_id = cms_pictures.story_id
ORDER BY sid DESC LIMIT 1[/code]

this ties every 'photo' field in cms_pictures to its matching story_id in cms_stories.  that being said, it seems like you could just run this query on cms_pictures alone and order by the story_id in cms_pictures (unless you're actually pulling the story from cms_stories).

i'll note that i'm not certain whether you can use aliases in an ordering clause (i'm pretty sure you can) - if that comes back with an SQL error, try changing "sid" in the ORDER BY clause to cms_stories.story_id.

hope this helps.  what i want to stress most is to get into the habit of [u]only pulling what you need to pull from the database[/u].