retrieveing vars from mysql join ?

[= poster =]

Hi all ,

I have the following problem :

$some_query = mysql_query(
"SELECT table1.id, table1.category, table1.author, table1.title,
table2.title, table2.category FROM table1, table2
WHERE table1.category = table2.category ORDER by table1.id DESC");

while($record = mysql_fetch_array($some_query))
{
$mycategory = ($record['table1.category']);
$mytitle = ($record['table2.title']);

echo"$mycategory";
echo"$mytitle";
}

Does someone know what's wrong here ?
echo don't show the requested values ....
thanks !

[Erwin Moller]

= poster = wrote:

Hi all ,

I have the following problem :

$some_query = mysql_query(
"SELECT table1.id, table1.category, table1.author, table1.title,
table2.title, table2.category FROM table1, table2
WHERE table1.category = table2.category ORDER by table1.id DESC");

while($record = mysql_fetch_array($some_query))
{
$mycategory = ($record['table1.category']);
$mytitle = ($record['table2.title']);

echo"$mycategory";
echo"$mytitle";
}

Does someone know what's wrong here ?
echo don't show the requested values ....
thanks !

Hi,

Many databases return their results without table specification.
However, If you have 2 columns with the same name you MUST specify which one
you need in the query, or the database will start complaining about it.

The way around this problem is simple, use an alias in the query, and do not
give the tablenames in PHP.

In your case:
$some_query = mysql_query(
"SELECT table1.id,
table1.category AS cat1 ,
table1.author,
table1.title as title1,
table2.title as title2,
table2.category AS cat2
FROM table1, table2
WHERE table1.category = table2.category ORDER by table1.id DESC");

while($record = mysql_fetch_array($some_query))
{
$mycategory = ($record['cat2']);
$mytitle = ($record['title2']);

echo"$mycategory";
echo"$mytitle";
}

(Not tested)

Good luck and regards,
Erwin Moller

[Jerry Stuckle]

= poster = wrote:

Hi all ,

I have the following problem :

$some_query = mysql_query(
"SELECT table1.id, table1.category, table1.author, table1.title,
table2.title, table2.category FROM table1, table2
WHERE table1.category = table2.category ORDER by table1.id DESC");

while($record = mysql_fetch_array($some_query))
{
$mycategory = ($record['table1.category']);
$mytitle = ($record['table2.title']);

echo"$mycategory";
echo"$mytitle";
}

Does someone know what's wrong here ?
echo don't show the requested values ....
thanks !

A stupid question - but are you actually calling mysql_query() before
trying the fetch the results?

Then try the following - and look at your index values:

echo "<pre>\n";
print_r($record);
echo "</pre>\n";

And finally, you can make things easier on yourself with something like:

"SELECT ... table1.category AS tab1category,
... table2.category AS tab2category ...

Also, if a column has a unique name in the tables listed in your FROM
clause, just use the column name. You only need the table name when you
have duplicate column names, as in category.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[= poster =]

Thanks a lot for your help !

The problem was that I dind't know the 'AS' alias function.

Does the aliasing have an impact on the speed of the query ?

thanks a lot !

[Erwin Moller]

= poster = wrote:

Thanks a lot for your help !

The problem was that I dind't know the 'AS' alias function.

Does the aliasing have an impact on the speed of the query ?

Probably a weee little, but I wouldn't care about that effect too much.

Regards,
Erwin

>
thanks a lot !

[Jerry Stuckle]

= poster = wrote:

Thanks a lot for your help !

The problem was that I dind't know the 'AS' alias function.

Does the aliasing have an impact on the speed of the query ?

thanks a lot !

Nothing measurable, at any rate. There might be if you had a few
hundred aliases, but for a few I've never been able to measure a
difference the margin of error in the system.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================