Accessing query on mutlitple tables in PHP

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Borniet
Forum Newbie
Posts: 2
Joined: Thu Mar 09, 2006 8:37 am
Location: Antwerpen

Accessing query on mutlitple tables in PHP

Post 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?
User avatar
newmember
Forum Contributor
Posts: 252
Joined: Fri Apr 02, 2004 12:36 pm

Post by newmember »

try to prefix each field wit the same name that appears in multiple tables with appropriate table alias.
Borniet
Forum Newbie
Posts: 2
Joined: Thu Mar 09, 2006 8:37 am
Location: Antwerpen

Post 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?
User avatar
newmember
Forum Contributor
Posts: 252
Joined: Fri Apr 02, 2004 12:36 pm

Post by newmember »

yes, you have to list all the retrieved fields and that what you should do anyway...
Post Reply