[PHP-DB] Aliased mysql queries and mysql_fetch_array()

[Philip Thompson]

Hi all.
I am having a problem obtaining the specific information I need. I have 
a table of people that can have multiple rows - ie, Director, 
Manager, etc. However, in the projects table, each project has a 
director AND manager, but not necessarily the same person.

I have the query to pull the information - and I know it works for 
everything else. Now how do I reference the director/manager to store 
it in a variable without having to use numerical indexing?


	$query = select projects.*, locations.*, funding.*, people.* from 
people as people_1 RIGHT JOIN  .
(people RIGHT JOIN (funding RIGHT JOIN (locations RIGHT JOIN 
projects on locations.id =  .
projects.location_id) on funding.id = projects.funding_id) on 
people.id = projects.manager_id) on  .
people_1.id = projects.director_id $whereCondition order by 
$criteria;

$result = @mysql_query($query, $link);
$i = 0;
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
	// there are much more fields, but I have only included the
	// two important ones (manager and director)
$_SESSION[manager][$i] = $row[first_name] .   . 
$row[last_name];
$_SESSION[director][$i] = $row[people_1.first_name] .   . 
$row[people_1.last_name];

  $i++;
}

It does not work whenever I include the table within the $row[]. 
Otherwise, it shouldn't be a problem. Any suggestions?

Thanks in advance,
~Philip
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Aliased mysql queries and mysql_fetch_array()

[John Holmes]

Philip Thompson wrote:
I have the query to pull the information - and I know it works for 
everything else. Now how do I reference the director/manager to store it 
in a variable without having to use numerical indexing?


$query = select projects.*, locations.*, funding.*, people.* from 
people as people_1 RIGHT JOIN  .
[snip]
$_SESSION[director][$i] = $row[people_1.first_name] .   . 
$row[people_1.last_name];
Do you really need to select _every_ column from _all_ of those tables? 
To solve your problem, you need to select the columns with aliases...

$query = SELECT people_1.first_name as p1_first_name, people.first_name 
as p_first_name, ...;

Now you'll have $row['p1_first_name'] and $row['p_first_name'] available.
--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals  www.phparch.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php