Page 1 of 1

Accessing query on mutlitple tables in PHP

Posted: Fri Mar 10, 2006 3:38 am
by Borniet
I have this query:

Code: Select all

$listsql  =  "SELECT *";
$listsql  =  $listsql  . " FROM appartement AS a, app2socat AS a2s, category AS c, periode AS p";
$listsql  =  $listsql  . ", reservaties AS r, sport_ontsp AS so";
$listsql  =  $listsql  . " WHERE a2s.app_id = a.app_id" ; 
$listsql  =  $listsql  . " AND a2s.cat_id = c.cat_id";
$listsql  =  $listsql  . " AND r.res_app_id = a.app_id";
$listsql  =  $listsql  . " AND r.res_per_id = p.per_id";
$listsql  =  $listsql  . " AND so.so_category_id = c.cat_id";
$listsql  =  $listsql  . " AND p.maand = \"$maand\"";
Which I then try to retrieve via this code:

Code: Select all

$listresults  =  mysql_query ( $listsql )
	or die("Invalid query: " . mysql_error());
$listnum_rows = mysql_num_rows($listresults);
for($listi = 0; $listi < $listnum_rows; $listi++) {
    	$listrow = mysql_fetch_array($listresults, MYSQL_ASSOC);
	echo $listrow["straat"] . "<br>";
}
However, since 'straat' is a fieldname that occurs in 3 of the tables, I only get the last one, and I need all three. I tried accessing the value from the 'appartemen' table using 'a.straat', but this returns nothing at all.
Can someone help me out here?

Posted: Fri Mar 10, 2006 4:46 am
by newmember
try to prefix each field wit the same name that appears in multiple tables with appropriate table alias.

Posted: Fri Mar 10, 2006 6:14 am
by Borniet
That would mean actually naming EACH field that the query returns, and then differentiating those names that occur more then once, right? Or is there a way to use * together with some fieldnames?

Posted: Fri Mar 10, 2006 6:20 am
by newmember
yes, you have to list all the retrieved fields and that what you should do anyway...