Page 1 of 1

Query Help

Posted: Fri Oct 15, 2004 9:49 am
by neophyte
I've got the following query that I'm using to create an expanding navigation tree.

Code: Select all

SELECT * FROM article_categories LEFT JOIN article_subcat ON article_categories.category_id = article_subcat.category_id
		 AND article_subcat.category_id = '$sec' ORDER BY article_categories.category_order  ASC
The problem is with this statement "article_categories.category_id = article_subcat.category_id". I need the category_id to come through in each resulting row to build the links. Currently the category_id only comes through when this condition "article_subcat.category_id = '$sec'" is met Does any body have a suggestion of how I can do this?

Thank-you

crap

Posted: Fri Oct 15, 2004 12:49 pm
by neophyte
[0] => 4
[category_id] =>
[1]

I already had the result I needed it just wasn't in the variable $row['category_id'] it was hiding in $row['0']. I don' really understand why. But it seems to have solved my problem none the less.

Thanks to all you who looked at my n00b question scratched your head and said, $%^$#?

Posted: Fri Oct 15, 2004 1:02 pm
by Weirdan

Code: Select all

SELECT * FROM article_categories LEFT JOIN article_subcat ON article_categories.category_id = article_subcat.category_id
WHERE article_subcat.category_id = '$sec' ORDER BY article_categories.category_order  ASC
?

TRUE

Posted: Fri Oct 15, 2004 1:08 pm
by neophyte
Weirdan wrote: ?
Wow that's almost as ambiguous as my orginal post.

But any way that's the SQL. I ran a while( fetch_array) statement on it. ['category_id'] was empty unless there was a matching ['subcat_id]. But category_id value showed up in the return array under ['0'] ...

Posted: Fri Oct 15, 2004 1:10 pm
by Weirdan
what does you schemata looks like? and what results do you expect to get?
It's hard to understand particularly because you used * instead of naming the fields explicitly.

Posted: Fri Oct 15, 2004 1:41 pm
by neophyte
Here it is:

Code: Select all

table 1  article_categories   ---|---  table 2 article_subcat
-------------------------------------------------------------------
key -    category_id          ---|---  key -   subcat_id
         category_name        ---|---          subcat_name
         category_description ---|---          category_id
         category_order       ---|---          subcat_description
Okay those are the fields involved.

I queried the database using the above sql like so:

Code: Select all

while ($row = $db->sql_fetchrow($result)){
I expected to be able to access the category_id with a variable like "$row['category_id]. But when I did so it turned up empty except where the two tables matched category_id's. But category_id was available in the variable $row['0'].

I printed the arrays to the screen. Here's an example where the query turned up matching category_id's

Code: Select all

Array ( 
  ї0] => 1 
  їcategory_id] => 1 
  ї1] => Online Education 
  їcategory_name] => Online Education 
  ї2] => education programs for adults. 
  їcategory_description] =>  education programs for adults. 
  ї3] => 2 
  їcategory_order] => 2 
  ї4] => 2 
  їsubcat_id] => 2 
  ї5] => Enrollment 
  їsubcat_name] => Enrollment 
  ї6] => prices and costs associated with the university 
  їsubcat_description] => prices and costs associated with the university 
  ї7] => 1 
)
You can see the variable [category_id] has a value of 1 and so does [0].

But ['category_id'] below has no value but ['0'] does have a value.

Code: Select all

Array ( 
  ї0] => 4 
  їcategory_id] => 
  ї1] => About RWU 
  їcategory_name] => About RWU 
  ї2] => This category contains our mission statement and purpose statements. 
  їcategory_description] => This category contains our mission statement and purpose statements. 
  ї3] => 1 
  їcategory_order] => 1 
  ї4] => 
  їsubcat_id] => 
  ї5] => 
  їsubcat_name] => 
  ї6] => 
  їsubcat_description] => 
  ї7] => 
)
The question is why does [0] have a value but ['category_id] does not....

Why didn't I get the values I expected in the variables I expected?

Why is it in ['0']....

Sorry I didn't mean to be short in my description. It's hard sometimes to know when you've given too little information and when you've been to verbose.

Posted: Fri Oct 15, 2004 3:23 pm
by Weirdan
neophyte wrote: The question is why does [0] have a value but ['category_id] does not....
because in this case the value has been taken from second table where you have similary named field (which is null because of left join)
neophyte wrote: Sorry I didn't mean to be short in my description. It's hard sometimes to know when you've given too little information and when you've been to verbose.
It's ok, often I do not elaborate enough as well ;)

if you'd modify you query as follows:

Code: Select all

SELECT 
  c.category_id,
  c.category_name,
  c.category_description,
  c.category_order,
  s.subcat_id,
  s.subcat_name,
  s.subcat_descriptions
FROM 
  article_categories c
LEFT JOIN 
  article_subcat s
ON 
  c.category_id = s.category_id
  AND s.category_id = '$sec' 
ORDER BY 
  c.category_order  ASC
it should give you the results you expect.

as I said, it's always useful to name the fields explicitly, for clarity and to resolve such ambiguous cases.

Posted: Fri Oct 15, 2004 3:33 pm
by neophyte
Thanks for the reply weirdan.

I tested it out and it delivered the exact result set I was looking for!

Weirdan RULES!

I'm just wondering what the "c." and "s." do in the query?

Why did you re-label the table names in the FROM and LEFT JOIN statement with a and ending "c" and and ending "s"?

Posted: Fri Oct 15, 2004 3:57 pm
by Weirdan
neophyte wrote: I'm just wondering what the "c." and "s." do in the query?
they are aliases for the table names. I do not follow <table name>_<field_name> notation for fields, hence I used to use aliases.
neophyte wrote: Why did you re-label the table names in the FROM and LEFT JOIN statement with a and ending "c" and and ending "s"?
I just prefer it to be this way :)

Posted: Fri Oct 15, 2004 4:13 pm
by neophyte
Thanks for the explanation.
:wink: