Hello!
this problem may just be that i don't understand how to use AS fully-
I have a table:
ARTICLE : id, title, author, editor
where author and editor are both integers and refer to the id column in the PEOPLE table:
PEOPLE: id, name
I can go:
SELECT article.*, author.name, editor.name
FROM article
left join people author on article.author = author.id
left join people editor on article.modifiedBy = editor.id
where article.id =1
If i got a row like this:
article.id, article.title, people.name, editor.name
article.id:1
article.title: My Article
people.name:Bob
editor.name:Mathew
then i could distinguish between the two name columns
people.name and editor.name
but what i get is a row like this
id, title, name, name
id: 1
title: My Article
name: Bob
name: Mathew
the last two columns have the same name! Theres no way to seperate the data from the last two rows without using the column order
So imagine i had an article, written by Bob and edited by Mathew
if in my php script i say:
$query = "SELECT article.*, author.name, edit.name
FROM article
left join people author on article.author = author.id
left join people editor on article.editor = editor.id
where article.id = 1";
$result = mysql_query($query );
if (($result) and ($row = mysql_fetch_array($result)))
{
echoArray( $row);
//my function to print out the contents of an array
}
i get a print out that says:
key: 0 value: 1
key: id value: 1
key: 1 value: First Article
key: title value: First Article
key: 2 value: 2
key: author value: 2
key: 3 value: 1
key: editor value: 1
key: 4 value: bob
key: name value: mathew <<<<<< heres my problem
key: 5 value: mathew
there is no named value to access the name of the author (bob)
perhaps it existed for an instant and was then overwritten with
name -> mathew
so i figure i want to use aliases on the column names as well as the table names,
but when i try to
i just don't get any data back at all,
or is there a way to force mysql to return the data row in the format:
article.id, article.title, people.name, editor.name?
thank you for taking the time to look at my problem
cheers,
mathew
