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.
