Jump to content

Archived

This topic is now archived and is closed to further replies.

Mr Chris

Foreign Key help please

Recommended Posts

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

Share this post


Link to post
Share on other sites
SELECT FIELD1, FIELD2 FROM cms_stories, cms_pictures WHERE story_id = '$story_id' AND section = 'news'

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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] .'">';

Share this post


Link to post
Share on other sites
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].

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.