homepage Welcome to WebmasterWorld Guest from 54.163.84.199
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
get data from join statement
nil111




msg:3825110
 7:57 am on Jan 13, 2009 (gmt 0)

hi!
say I'v two tables a and b. Both tables hv a column named 'id'.
after joining des two how can I retrieve the id of table a for example?

like-

while($row = mysql_fetch_assoc($result))
{
$a_id = $row['#*$!']; // here wat should '#*$!' be?
}

thanks in advance..!

 

nil111




msg:3825111
 7:58 am on Jan 13, 2009 (gmt 0)

SELECT * FROM a as aa join bb as at on aa.val1 = bb.val2 where c = 'somethin'

is my join statement

janharders




msg:3825118
 8:04 am on Jan 13, 2009 (gmt 0)

select aa.id AS aid, at.id AS bid ....

nil111




msg:3825119
 8:12 am on Jan 13, 2009 (gmt 0)

Im sorry its not very clear. I want help in tryin to get the data searched by the query.

coopster




msg:3826265
 5:03 pm on Jan 14, 2009 (gmt 0)

janharders demonstrated that the column name (or alias if an alias is used) is one way to use the associative index in a mysql fetch. So, using mysql_fetch_assoc() the following example shows how you use the associative index name from your query. First, some simple tables for our example ...
TableA 
id
column1
column2

TableB 
id
column2
column3

SELECT 
TableA.id AS aid,
column1,
TableA.column2 AS acolumn2,
B.column2 AS bcolumn2,
column3
FROM TableA
INNER JOIN TableB AS B ON (TableA.id = TableB.id)

In your PHP while loop, you can refer to them like so ...
while($row = mysql_fetch_assoc($result)) 
{
$a_id = $row['aid']; // TableA.id
$column1 = $row['column1']; // TableA.column1
$acolumn2 = $row['acolumn2']; // TableA.column2
$bcolumn2 = $row['bcolumn2']; // TableB.column2
$column3 = $row['column3']; // TableB.column3
}

A number of techniques are demonstrated here. First, notice how both tables have some identical column names? We can use an alias on the column names so as to differentiate between the two. The columns are called ambiguous and by using an alias we can remove the ambiguity. We can also use an alias on a table name. You see that here when I use an alias for TableB. Now, whatever the column name, or alias name if used, is the index we use in our fetching control structure.

nil111




msg:3840135
 8:33 am on Feb 2, 2009 (gmt 0)

thanks a lot. I didnt go ahead with this method at that time. but will have a look n learn now. thanks again

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved