Getting usernames into option fields

[Gibbs]

Hello,

I'm following a tutorial and trying to get the usernames from my database to appear in a drop-down list by using <option>.

TBL_USERS is defined (as i'm already using it on the admin page successfully.

The original code from the tutorial was:
[code]$sql = mysql_query("SELECT * FROM users WHERE id != '$_SESSION[username]' ORDER BY username");[/code]

I decided to change it to this:
[code]$sql = mysql_query("SELECT * FROM ". TBL_USERS. " WHERE id != $session->username ORDER BY username");[/code]

This is the code being used (underneath the above) to fill in the option fields:
[code]if (mysql_num_rows($sql) > 0)
{
  while ($x = mysql_fetch_assoc($sql)) echo "<option value=\"$x[id]\">$x[username]</option>\n";
} [/code]

My admin page has this function which works (and might be able to help):
[code]function displayUsers(){
  global $database;
  $q = "SELECT username,userlevel,email,timestamp "
      ."FROM ".TBL_USERS." ORDER BY userlevel DESC,username";
  $result = $database->query($q);[/code]

I'm sorry if that's confusing. If I could get any help I would really appreciate it!

Thanks

[Cep]

I dont think you can do this,

$session->username

within an SQL statement.

[Gibbs]

[quote author=Cep link=topic=122629.msg505947#msg505947 date=1168949081]
I dont think you can do this,

$session->username

within an SQL statement.
[/quote]

That is my problem (thanks for pointing it out!).

This now works:
[code]$sql = mysql_query("SELECT * FROM ". TBL_USERS. " ORDER BY username");[/code]

However I need to exclude the logged in users name. This is the part thats not working:

[code]WHERE id != ". $session->username ."[/code]

When I echo $session->username I get the username. Why won't MySQL read this and exclude it?

Thanks for the help so far

[HuggieBear]

Just change it to this then...

[code]$username = $session->username;
$sql = mysql_query("SELECT * FROM " .TBL_USERS. " WHERE id != $username ORDER BY username");[/code]

Regards
Huggie

[HuggieBear]

The feature to edit has disappeared so I've had to post this below...

If it's a varchar rather than an int then don't forget the single quotes around the value e.g.

[code]$sql = mysql_query("SELECT * FROM " .TBL_USERS. " WHERE id != '$username' ORDER BY username");[/code]

Regards
Huggie

[Gibbs]

[quote author=HuggieBear link=topic=122629.msg505960#msg505960 date=1168950326]
Just change it to this then...

[code]$username = $session->username;
$sql = mysql_query("SELECT * FROM " .TBL_USERS. " WHERE id != $username ORDER BY username");[/code]

Regards
Huggie
[/quote]

To kill two birds with one stone I have  "$from = $session->username;"

It doesn't work when using this line:
[quote]$sql = mysql_query("SELECT * FROM ". TBL_USERS. " WHERE id != $from ORDER BY username");[/quote]

http://vortex-inc.net/pm.php

[Gibbs]

[quote author=HuggieBear link=topic=122629.msg505961#msg505961 date=1168950415]
The feature to edit has disappeared so I've had to post this below...

If it's a varchar rather than an int then don't forget the single quotes around the value e.g.

[code]$sql = mysql_query("SELECT * FROM " .TBL_USERS. " WHERE id != '$username' ORDER BY username");[/code]

Regards
Huggie
[/quote]

Even with the '' it doesn't work...

[paul2463]

the reason it wont work is that you need single speech marks around a php variable used in an SQL Statement
[code]
$sql = mysql_query("SELECT * FROM ". TBL_USERS. " WHERE id != '$from' ORDER BY username");
[/code]
Just Like HuggieBear mentioned in his last post

[paul2463]

it could also be the gaps in your FROM part

".TBL_USERS."

no gaps

[Gibbs]

[quote author=paul2463 link=topic=122629.msg505968#msg505968 date=1168950954]
the reason it wont work is that you need single speech marks around a php variable used in an SQL Statement
[code]
$sql = mysql_query("SELECT * FROM ". TBL_USERS. " WHERE id != '$from' ORDER BY username");
[/code]
Just Like HuggieBear mentioned in his last post
[/quote]

I am. This is what I have currently:
[code]$sql = mysql_query("SELECT * FROM ". TBL_USERS. " WHERE id != '$from' ORDER BY username");[/code]

When I echo $from I get the username so I don't think that's the problem... If I take out this section it works, but obviously keeps the username there...
[code]"WHERE id != '$from'[/code]

[HuggieBear]

OK, report the error then...

[code]<?php
$sql = "SELECT * FROM " .TBL_USERS. " WHERE id != $from ORDER BY username";
$result = mysql_query($sql) or die("Can't execute $sql: " .mysql_error());
$x = mysql_fetch_array($result, MYSQL_ASSOC);
print_r($x);
?>[/code]

This will report an error on failure of the query and then dump the contents of the first row in the result set to make sure it has data in it.

Regards
Huggie

[Gibbs]

Removing the spaces doesn't make a difference...

[Gibbs]

[quote author=HuggieBear link=topic=122629.msg505975#msg505975 date=1168951193]
OK, report the error then...

[code]<?php
$sql = "SELECT * FROM " .TBL_USERS. " WHERE id != $from ORDER BY username";
$result = mysql_query($sql) or die("Can't execute $sql: " .mysql_error());
$x = mysql_fetch_array($result, MYSQL_ASSOC);
print_r($x);
?>[/code]

This will report an error on failure of the query and then dump the contents of the first row in the result set to make sure it has data in it.

Regards
Huggie

[/quote]

No errors...

[Gibbs]

I think it's a problem with the variable. Even if I add it just over the SQL code it doesn't help. Screw it, if people are sad enough to send messages to themselves i'll let them :P.

Thanks for your help :)

[Cep]

Try,

[code=php:0]
$sql = "SELECT * FROM " .TBL_USERS. " WHERE id NOT LIKE '$from' ORDER BY username";
[/code]

[Cep]

Actually I just realised your using id as the field in the SQL statement when surely you should be using username?

$sql = "SELECT * FROM " .TBL_USERS. " WHERE username != '$from' ORDER BY username";

[Gibbs]

[quote author=Cep link=topic=122629.msg505990#msg505990 date=1168952262]
Actually I just realised your using id as the field in the SQL statement when surely you should be using username?

$sql = "SELECT * FROM " .TBL_USERS. " WHERE username != '$from' ORDER BY username";
[/quote]

It works!

Thanks for everyones help!