SQL Query question

[Kenazo]

I'm writing a query on a postnuke sql database. In one table (nuke_stories) I have the article and some related information, each article has a field for the associated category. However, the category field is just a number field, another table (nuke_stories_cat) has a field with the category numbers and another with the category text name.

My question is how do I write a query that will display the article along with its category name? I can obviously easily write the query to display the category number but how do I tie that to its text name stored in the other table?

Thanks 🙂

 

[MrChad]

You need to write a JOIN on the two tables. You can find a good tutorial on SQL at W3Schools.

 

[KLin]

SELECT nuke_stories.article, nuke_stories.category, nuke_stories_cat.CategoryDesc
FROM
nuke_stories inner join nuke_stories_cat on nuke_stories.category = nuke_stories_cat.category

 

[Kenazo]

Great! Thanks guys 🙂 While you're on a roll can you link me in or give me a hint on one more thing?

I'm displaying the result in a table with a TR per entry and I want to alternate the TR background. I'm guessing I want to play with the $i variable? even results in #ffffff and odd in #eeeeee?

 

[KLin]

Originally posted by: Kenazo
Great! Thanks guys 🙂 While you're on a roll can you link me in or give me a hint on one more thing?

I'm displaying the result in a table with a TR per entry and I want to alternate the TR background. I'm guessing I want to play with the $i variable? even results in #ffffff and odd in #eeeeee?

sorry, can't help you there.

 

[Cerebus451]

Originally posted by: Kenazo
Great! Thanks guys 🙂 While you're on a roll can you link me in or give me a hint on one more thing?

I'm displaying the result in a table with a TR per entry and I want to alternate the TR background. I'm guessing I want to play with the $i variable? even results in #ffffff and odd in #eeeeee?

Yup. The exact syntax would depend on your language. For PHP:

EDIT: I thought the Attach Code wouldn't mangle '<', '>' and such 😕

 

[Kenazo]

Thanks Cerebus 🙂 I don't quite have it though...I keep throwing it into an infinite loop. I feel dumb but I don't know exactly where to put it?

$i=0;
while ($i < $num) {

$title=mysql_result($result,$i,"nuke_stories_cat.pn_title");
$sid=mysql_result($result,$i,"nuke_stories.pn_sid");
$short=mysql_result($result,$i,"nuke_stories.pn_bodytext");
$expiry=mysql_result($result,$i,"nuke_stories.pn_notes");

for ($i = 0; $i < $num_rows; $i++)
{
if ($i % 2 == 0)
{
print '<tr bgcolor=#FFFFFF>';
}
else
{
print '<tr bgcolor=#eeeeee>';
}
}
echo "<tr bgcolor='#FFFFFF'><td width='100'>$title </td><td width='100'>$short</td><td width='50'>$expiry</td><td>-<a href='http://www.hotukdeals.com/Article$sid.phtml'>Display Details</a></td></tr>";

$i++;
}

 

[Cerebus451]

Originally posted by: Kenazo
Thanks Cerebus 🙂 I don't quite have it though...I keep throwing it into an infinite loop. I feel dumb but I don't know exactly where to put it?

$i=0;
while ($i < $num) {

$title=mysql_result($result,$i,"nuke_stories_cat.pn_title");
$sid=mysql_result($result,$i,"nuke_stories.pn_sid");
$short=mysql_result($result,$i,"nuke_stories.pn_bodytext");
$expiry=mysql_result($result,$i,"nuke_stories.pn_notes");

for ($i = 0; $i < $num_rows; $i++)
{
if ($i % 2 == 0)
{
print '<tr bgcolor=#FFFFFF>';
}
else
{
print '<tr bgcolor=#eeeeee>';
}
}
echo "<tr bgcolor='#FFFFFF'><td width='100'>$title </td><td width='100'>$short</td><td width='50'>$expiry</td><td>-<a href='http://www.hotukdeals.com/Article$sid.phtml'>Display Details</a></td></tr>";

$i++;
}

Get rid of the for loop. I put that in there since you are typically looping through the results at that point. Your outer while loop takes care of that. Take the if test that I had and remove the <tr ...> portion from your echo statement. Lastly, make sure that you set $num before your loop (not sure what you have above the code you listed).

 

[Kenazo]

Just figured it out too 🙂 Thanks for your help!