Array in combination with join statement (PHP + MYsql)

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
gino
Forum Newbie
Posts: 2
Joined: Sat Feb 16, 2008 4:00 am

Array in combination with join statement (PHP + MYsql)

Post by gino »

Hi everyone,
I have a problem that I can't sort out. I have several tables, two of them I joined together
(see below example)

Code: Select all

$query="select * from tbl_login login, tbl_settings  settings WHERE login.login = '$loginUsername'";
  $result=mysql_query($query) or die("Fout: ". mysql_error());
  while($row = mysql_fetch_array($result))
{
    $gebruiker=$row['login'];
    $toegang=$row['level'];
    $refresh=$row['refresh'];
    }
  $_SESSION['level'] = $toegang;
  $_SESSION['refresh']= $refresh;
the fields login and level are derived from the first table (tbl_login). The second table (tbl_settings) contains settings for refresh rates, display of columns etc. I however don't know on how to retrieve the info. Apparently $row['refresh'] doesn't pick up the value from the query. I thought the info could be retrieved by doing $refresh=$row['settings.refresh'] but that doesn't seem to work. In short, I'd like to know how the info can be accessed when I run a query based on joined tables.
It seems to me as if this would be a common practise ??

Can someone shed some light on this problem?

Tks for the assistance.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Array in combination with join statement (PHP + MYsql)

Post by califdon »

Are you sure that your column names are correct? That's one (just one) reason not to use Select *. Try

Code: Select all

SELECT login, level, refresh FROM ...
Then, if your column names are incorrect, you'll receive an error from MySQL and immediately know what's wrong.

Also, you could echo the values of the variables as soon as you think you have generated them, until it is running correctly. Results are sometimes surprising.

Finally, I prefer to use mysql_fetch_assoc() instead of mysql_fetch_array(), because it will move all row contents into named variables with a single command, extract($row). You might consider whether that would be helpful for you.

Code: Select all

while($row = mysql_fetch_array($result)) {
   extract($row);
   $gebruiker=$login;
   $toegang=$level;
   . . .
}
gino
Forum Newbie
Posts: 2
Joined: Sat Feb 16, 2008 4:00 am

Re: Array in combination with join statement (PHP + MYsql)

Post by gino »

Thanks for your reply. Yes column names are correct. After having done some further investigation, I found I had to include another filter to join or link the two tables (see below example). This works without any problems as it appears.

$query="select * from tbl_login login, tbl_settings settings WHERE login.login = '$loginUsername' and login.login=settings.login";


mysql_fetch_assoc .. hmm seems to be quite useful. It for sure seems to keep the code simple. I'll give it a try. Thanks for the suggestion

Cheers
Gino
Post Reply