fecthing from a join select

mkoohian · October 22nd, 2003, 05:24 PM

How do you fetch data from a mySql select statment that joins to tables:
select a.fld1, a,fld2, b.fld from table1 a, table2 b where a.fld3=b.fld3;

Can you run this statment and then fecth the results using statements similar to the following:
$res1 = quesry_date['a.fld1'];
$res2 = query_data['b.fld];

I actually was able to get results without the alias and by just using
$res1 = quesry_date['fld1']; // this resulted in the correct value

But it doesn't make sense. What if you have fields with the same name in the two tables.

ANy ideas?

Thanks
Merdaad

richard.york · October 22nd, 2003, 05:56 PM

Hi Merdaad,

No there is no need to call on the joined field in that way, the reason being is the data there is already expected to be the same between the two tables. So it would be acessible only in the $data['user_id'] format and not in $data['user_ids.user_id'] format. the latter not even a possible representation of the data.

Code:

<?php

    $link = mysql_pconnect("localhost");
 
    mysql_select_db("quesadilla");
 
    $result = mysql_query("SELECT `user_ids`.`user_id`, `user_name`, `user_profiles`.`user_id`, `first_name` FROM `user_ids`, `user_profiles` WHERE `user_ids`.`user_id` = `user_profiles`.`user_id`");
 
    if (empty($result))
    {
        echo mysql_error().": ";
         echo mysql_errno()."<br /><br />\n";
    }
 
    while($data = mysql_fetch_array($result))
    {

        echo $data["user_id"].": ".$data["user_name"]." (".$data["first_name"].")<br />\n";

    }

Further more, if you are interested in seeing exactly how mysql returns the results and creates indices you could use a snip like this:

Code:

$data = mysql_fetch_array($result);

foreach($data as $key => $value)
{
    echo "\$data[\"$key\"] = $value;<br />\n";
}            

?>

hth,
: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::

richard.york · October 22nd, 2003, 06:34 PM

Hello again,
I don't think I explained what I did well enough so here's another go at it.

Quote:

quote:
What if you have fields with the same name in the two tables.

As a general rule of thumb whenever possible and within your control avoid using the same field names in different tables unless that data is expected to be related, as is the case with the 'user_id' field in my example. It hadn't crossed my mind that you might be using fields with the same name and the data *not* be related. But again, the foreach there will help you to determine how associative indices are named. Otherwise if the second argument of in this case, the mysql_fetch_array function can be specified to use numeric, associative or both types of indices. Meaning you may access the data by the string representation of the field, or the numeric order with which it appears in the query.

http://www.php.net/mysql_fetch_array

So in my example I called on user_ids.user_id, user_name (from the user_id table) user_profiles.user_id (Expected to contain related data), and first_name (from the user_profiles table). As long as each field is unique there is no need to specifiy the table before it.

Here is another test script to see how the query handles data if the fields are unrelated. In this example I changed the name of the first_name field to user_name.

Code:

$result = mysql_query("SELECT `user_ids`.`user_id`, `user_ids`.`user_name`, `user_profiles`.`user_id`, `user_profiles`.`user_name` FROM `user_ids`, `user_profiles` WHERE `user_ids`.`user_id` = `user_profiles`.`user_id`");
 
    if (empty($result))
    {
        echo mysql_error().": ";
         echo mysql_errno()."<br /><br />\n";
    }
 
    echo "<br />";

    $data = mysql_fetch_array($result);

    foreach($data as $key => $value)
    {
        echo "\$data[\"$key\"] = $value;<br />\n";
    }

Outputs:
$data["0"] = user_ids.user_id;
$data["user_id"] = user_ids.user_id;
$data["1"] = user_ids.user_name;
$data["user_name"] = user_ids.user_name;
$data["2"] = user_profiles.user_id;
$data["3"] = user_profiles.user_name;

: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::

nikolai · October 22nd, 2003, 09:31 PM

This is simpler than doing a foreach() on your result rows:

$row = mysql_fetch_array($result, MYSQL_ASSOC);
print_r($row);

Take care,

Nik
http://www.bigaction.org/