retrieve two table in combobox

[CrOuZ]

I retrieve data from two database table for combobox.

<form>
<select>
<?PHP $query=mysql_query("select option.*,user.* from user,option where user.data='$id' and option.userid != user.id"); 
while($row= mysql_fetch_assoc($query)){ ?>
<option value=<?PHP echo $row["id"];?>><?php echo $row["name"];?></option>
<?PHP } ?>
</select></form>

Now this is retrieves :

Show two result for each id !!! what's my problem ?

[NogDog]

Are you sure you don't want and option.userid = user.id (instead of the != comparison)?

[CrOuZ]

Yea ! User.id different userid. really id in table user = userid in table option. Now i want to dont show any user in option table. please see image .
alireze (line 1) and dfdf ( line 3 ) singular because really existence in option table. i dont know why repeat my list ! this is my problem.

[NogDog]

It's probably repeating because you will get one result row for each combination of rows between the two tables where those two column do not equal each other, as long as the first table's row matches that first condition. You could do a SELECT DISTINCT, but then your column listing would have to be only that one table.column name that you actually want to use (not table.* from both tables).

[CrOuZ]

Whats Your Idea For fix this ? ( coding )

[NogDog]

SELECT DISTINCT user.id FROM . . .

[CrOuZ]

SELECT DISTINCT user.id FROM . . .

How to work with two table ? 😕 can you rewrtie my code with your idea ?:o Thanks

[NogDog]

Just change the opening part of your SQL string with what I supplied above, from "SELECT" through "FROM", except it looks like you also need the "name" column from whichever table you get that:

SELECT DISTINCT user.id, user.name FROM . . .

...where "user.name" might instead be "option.name" if that is where the name value comes from.

[CrOuZ]

I change Code To This But Not worked : ( both result )

<form> 
<select> 
<?PHP $query=mysql_query("SELECT DISTINCT option.userid,user.id,user.name from user,option where user.data='$id' and option.userid != user.id"); 
while($row= mysql_fetch_assoc($query)){ ?> 
<option value=<?PHP echo $row["id"];?>><?php echo $row["name"];?></option> 
<?PHP } ?> 
</select></form> 

[niroshan]

Hi CrOuZ,
If I got your point correctly I think you got 2 problems here,

1. In your query, your trying to get results which is not in the 'option' table but still in your select part your referring to option.userid. How can you get the userid data from option table, because the record in the user table doesn`t have matched data in the option table because your using <>.

2. instead of using != try to use NOT IN

*By the way if option.userid is equal to user.id then why do you need option.userid in the select statement. instead you can use user.id right?

below is the changed query,

<form>
<select>
<?php
$sql = "SELECT DISTINCT(a.id), a.name from user a, `option` b WHERE a.data = '$id' AND a.id NOT IN (SELECT userid FROM `option`)";
$query=mysql_query($sql);
while($row= mysql_fetch_assoc($query)){ ?>
<option value=<?PHP echo $row["id"];?>><?php echo $row["name"];?></option>
<?PHP } ?>
</select></form>

Hope this helps..

Regards,
Niroshan

[CrOuZ]

Hi niroshan. Thanks for help . your idea really fix my problem . thanks again .
NogDog thanks For your help .

Best regards Both.