Page 1 of 1

Array in combination with join statement (PHP + MYsql)

Posted: Sat Feb 16, 2008 4:08 am
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.

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

Posted: Sat Feb 16, 2008 11:03 am
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;
   . . .
}

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

Posted: Sat Feb 16, 2008 11:22 pm
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