[SOLVED] Selecting Multi Table issues

[NerdConcepts]

Ok, I am trying to do a news display...big deal right. Let me give the info

 

I am building myself a portfolio website and have ran into the following problem.

 

Tables are "Projects" "News" and "Categories" that I need to tap into to get all the information needed to display my project update news.

projects = project_id, project_icon, cat_id, project_title

news = news_id, news_title, news_text, cat_id, news_date

categories = cat_id, cat_title

 

Well the issue I am having is when I go to grab all that information and relate it to a certain section of the site I am having all kinds of problems...from MySQL errors to it multi displaying 1 piece of news.

 

Example...When someone is viewing the "DigitalArtwork" section of the site it gives a pageid=5. That is permanent to that section and that section only.

 

Yet when I run the query it's all messed up...Here is what I am running...well the one I got to actually run.

 

$qGetAllInfo = "SELECT projects.*, categories.*, news.* FROM projects, categories, news WHERE projects.cat_id='5' AND categories.cat_id='5' AND news.cat_id='5'";
$rGetAllInfo = mysql_query($qGetAllInfo) or trigger_error("Query: $qGetAllInfo\n<br />MySQL Error: " . mysql_error());
while ($rowInfo = mysql_fetch_array($rGetAllInfo, MYSQL_ASSOC)) {
echo $rowInfo['project_id'];
echo '<br />';
echo $rowInfo['news_title'] . " - " . $rowInfo['project_title'];
echo '<br />';
}

 

I get the following return:

 

1
Completed Photos - Tint FX Mustang
2
Completed Photos - Tint FX Mustang
1
Completed Photos - CIM - Vinyl Banners
2
Completed Photos - CIM - Vinyl Banners

 

no idea why it is retuning like that...should be

1 // project_id
Completed Photos - Tint FX Mustang
2 // project_id
Completed Photos - CIM - Vinyl Banners

 

I've tried lots of different code and nothing works right, this is as close as I could come. BTW, the return is just a test, the final piece will use all the data from all 3 tables each time it is displayed (each piece of news).

 

I hope i have explained this enough someone can understand what is going on...

[spamoom]

I'm still a bit confused on what you want, but I have used something similar...

 

<?php
$sql2 = "SELECT neaura_member.member_displayname, neaura_member.member_flag, neaura_member.member_id, ".$config['tp']."squadmembers.squadmembers_rank, ".$config['tp']."squadmembers.squadmembers_ingameid
FROM ".$config['tp']."squadmembers LEFT JOIN neaura_member
ON ".$config['tp']."squadmembers.squadmembers_memberid = neaura_member.member_id
WHERE ".$config['tp']."squadmembers.squadmembers_squadid = ".$squads['squads_id']."
ORDER BY ".$config['tp']."squadmembers.squadmembers_rank DESC";
?>

[NerdConcepts]

Nice, very nice, thanks a bunch. I was trying to do basically the same but with 3 tables. Figured out a way around using 3 tables. I just reference the news to the projects, find the matches then move onto each news piece. Well here is the starter code I have working perfectly now.

 

if (isset($_GET['page'])) { // viewing a section - index.php?page=
$qGetAllInfo = "SELECT news.*, projects.* FROM news LEFT JOIN projects ON news.project_id = projects.project_id WHERE projects.cat_id='$pageid' AND news.cat_id='$pageid' ORDER BY news.news_id DESC";
$rGetAllInfo = mysql_query($qGetAllInfo) or trigger_error("Query: $qGetAllInfo\n<br />MySQL Error: " . mysql_error());
} elseif (isset($_GET['project'])) { // viewing a project - index.php?project=
$qGetAllInfo = "SELECT news.*, projects.* FROM news LEFT JOIN projects ON news.project_id = projects.project_id WHERE projects.project_id='$proid' AND news.project_id='$proid' ORDER BY news.news_id DESC";
$rGetAllInfo = mysql_query($qGetAllInfo) or trigger_error("Query: $qGetAllInfo\n<br />MySQL Error: " . mysql_error());
} else { // viewing all news from index.php
$qGetAllInfo = "SELECT news.*, projects.* FROM news LEFT JOIN projects ON news.project_id = projects.project_id ORDER BY news.news_id DESC";
$rGetAllInfo = mysql_query($qGetAllInfo) or trigger_error("Query: $qGetAllInfo\n<br />MySQL Error: " . mysql_error());
}

while ($rowInfo = mysql_fetch_array($rGetAllInfo, MYSQL_ASSOC)) {
$nid = $rowInfo['news_id'];	

$qProDate = "SELECT DATE_FORMAT(news_date, '%b. %D, %Y') AS dr FROM news WHERE news_id='$nid'"; // Format Date
$rProDate = mysql_query($qProDate);
$rowDate = mysql_fetch_array($rProDate, MYSQL_ASSOC);

$qCatTitle = "SELECT cat_id, cat_title FROM categories WHERE cat_id='$nid'"; // take cat_id and get cat_title
$rCatTitle = mysql_query($qCatTitle);
$rowCatTitle = mysql_fetch_array($rCatTitle, MYSQL_ASSOC);

 

rest of the code doesn't matter since it is the way everything looks. Thanks a bunch again.