two tables one field

myraleen

Sorry guys, this is rather simple, but for some reason i'm not thinking right.

I have two tables of user names.
I have this SQL:

$sql_query = "SELECT accounts.username, other_accounts.username FROM accounts, other_accounts";

$username_result = mysql_query($sql_query) or die(mysql_error());

The query it self is working fine, or seems to be so far.

I want all of the usernames to then be printed, preferabbly saying which table it came from as well. But i'm not sure what to use, mysql_num_rows, mysql_fetch_rows, mysql_fetch_array, the while(list) the foreach... argh!!!
Too many choices and none seem to be working the way i want. Right now i'm using this:

while(list($username) = mysql_fetch_row($username_result)) {
     echo "$username<br>";
}

But i'm only getting the results from the first table, and they are repeating over and over.

Any help or explanation would be greatly appreaciated.

Thanks

LordShryku

This would just be so much less of a headache for you if you just did two seperate queries. Then youd get all the names, and have no trouble knowing which table they came from.

myraleen

Yes i did think of that, but it's for a search query, eventually i'll want it as one big one.

But i'll work with it as two for now i guess. :S

LordShryku

Try this

$tables = array("accounts","other_accounts");
foreach($tables as $table) {
   $sql = "SELECT username FROM ".$table;
   $res = mysql_query($sql) or die(mysql_error());
   while($row = mysql_fetch_array($res)) {
      echo $row['username']."<br />";
   }
}

myraleen

I think that is getting close.

It doesn't like the
echo $row['username']."<br />";

Is giving me silly parse errors.

Another option, is to do the two table method, but somehow make one variable out of both results.

WOuld that be similar to ocmbining the two tables?

Lars_Berg

$sql_query = 
"SELECT 'Accounts', accounts.username
from accounts
union all
select 'otherAccounts', other_accounts.username 
FROM  other_accounts";

myraleen

i got it.
This is what i did:


$query = "SELECT username FROM accounts";
$result = mysql_query($query);
while ($object = mysql_fetch_object($result)) {
     $array[$object->username] = true;
}
$query2 = "SELECT username FROM other_accounts";
$result = mysql_query($query);
while($object = mysql_fetch_object($result)) {
      $array[$object->username] = true;
}
foreach ($array as $key => $value ) {
     echo "$key";
}

Thanks!!