Jump to content

Foreign Key help please


Mr Chris

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.