Help with select query

[drfong]

God! where do I start, I'm not great with MYSQL/PHP, if I don't make sense, sorry in advance. My problem is this.

I have a url paramater (a news article number - unique) that I have passed as $recordID in my PHP file.

I am doing a select on TABLE1 to select all records that are equal to this $recordID via a column called 'ArticleID'
(SELECT * FROM categories WHERE ArticleID ='$recordID')
this works ok.

This will bring out aproximately three rows from two columns 'ArticleID' and 'CategoryID'.

ArticleID

123
123
123

CategoryID

34546
54555
44567

Right, this is where i have no idea what to do, I now want to take the three rows from the column 'CategoryID' somehow store them and query TABLE1 and another table TABLE2 using a join.

The other table TABLE2 has a column called ArticleID that has the unique number that the $recordID was pulled from.

So what I want to do is select * from TABLE2.ArticleID where it it is equal to TABLE1.ArticleID only where that row in TABLE1 contains one of the 3 categoryID numbers pulled out in the original query.

Any IDEAS how to go about this?

SORRY, I know this is a long posting but I am lost and my neighbours dog has been howling all night and I can't concentrate, I may have to take the dog out if I can't get this sorted.

Thanks

 

[r937]

give the dog a bone, because this may take a while

you have an articleid and you run a query against TABLE1 and get 3 categoryids

now you want to query TABLE2 with the same articlid, "only where that row in TABLE1 contains one of the 3 categoryID numbers pulled out in the original query"

that doesn't make sense, at least to me, because that's exactly where the categoryids came from

perhaps you could show your real tables?

r937.com | rudy.ca

 

[drfong]

Thanks for the reply, your right it doesnt make sense, not to me anyway, I'm ripping my hair out. I've craeted a page and illustraed the tables and the problem. If anyone wants to take a look and help me on this one I'd be eternally grateful.

http://www.kevindrysdale.co.uk/tektips/

Thanks in advance

 

[r937]

select distinct
       t2B.ID
     , t2B.Content
     , t2B.Heading
  from Table2 as t2A
inner
  join Table1 as t1A
    on t1A.ArticleID = t2A.ID
inner
  join Table1 as t1B
    on t1B.CategoryID = t1A.CategoryID
   and t1B.ArticleID <> t1A.ArticleID
inner
  join Table2 as t2B
    on t2B.ID = t1B.ArticleID
 where t1A.ID = 18232612

r937.com | rudy.ca

 

[drfong]

Hey thanks for that, I'm away from home til friday, I'll get back and try that straight away and post back incase anyone else has the same issue. Thanks again.

 

[drfong]

I'm sooooo far away from this level, I'm actually unsure what to do with the code, I can sort of see what's happening in the script but this is the most complex select query I've ever come accross ( I reckon I need to go back and learn to walk first. Anyway can I just put this in a php/mysql_query like this, as I've tried this and I don't seem seem to be getting anywhere, no errors though just nothing is echoed out:

$result = mysql_query("
select distinct
t2B.ID
, t2B.Content
, t2B.Heading
from Table2 as t2A
inner
join Table1 as t1A
on t1A.ArticleID = t2A.ID
inner
join Table1 as t1B
on t1B.CategoryID = t1A.CategoryID
and t1B.ArticleID <> t1A.ArticleID
inner
join Table2 as t2B
on t2B.ID = t1B.ArticleID
where t1A.ID = 18232612");

while($row = mysql_fetch_array($result))
{
$headingLink = $row['Heading'];
echo $headingLink;
}

 

[r937]

to test a query, don't run it inside your php code, run it outside, right in mysql, through your front end app du jour (mine is HeidiSQL, yours might be phpmyadmin, the mysql query browser, mebbe even the command line...)

r937.com | rudy.ca

 

[drfong]

Yeah, I've tried it through Navicat MYSQL and on the MYSQL command line and it brings back 0 results. Thanks for for the help its probably something I'm doing wrong, I'll keep trying and see if I can get it working. Thanks again