sorting and sql query

[dazi_behappy]

Hi all,

I have results of a mysql query that will give me ID's of rows (from a different database) that should be displayed on my website.

What would be the best way of me sorting these rows?

i.e. at the moment i am doing the following:

1. Get ID values from database1:

$sql = "SELECT f.id FROM favourites f WHERE f.username='" .$bewiseusername. "'";

2. Save these IDs into a php array

3. Get all row values one at a time from database2:

$sql = "SELECT f.id FROM casinos s WHERE s.id =" .$favourites[$y];

However, doing it this way, I am unableto sort the rows taken from database2. any ideas on how i could create one sql query?

Cheers

[r937]

yes, you can, with a JOIN

which columns would you like to return? which columns would you like to sort by?

[dazi_behappy]

Ah right. I will be requiring to output all columns (*) and the sorting field will be decided by a combo box but say the field 'name' for the mement.


Have you any idea how I would og about creating this JOIN using php?
I know how I am able to access 1 database at a time but have no idea how I can open two at the same time.

$username = "username";
$password = "password";
$database = "database";

	#CONNECT TO MYSQL
	$dbcnx = @mysql_connect('mysql5.streamline.net',$username,$password) or die("Unable to connect to MySQL");

	#CONNECT TO DATABASE
	@mysql_select_db($database) or die( "Unable to select database");

             $result = mysql_query($sql);

	while ( $row = mysql_fetch_array($result) ) {

            }

sql for db1:
$sql = "SELECT f.id FROM pokerfavourites f WHERE f.username='" .$bewiseusername. "'";

sql for db2:
SELECT * FROM url u INNER JOIN rating r USING(id) INNER JOIN name n USING(id) INNER JOIN games g USING(id) WHERE n.id = [php array]

How would I go about opening the two databases at the same time (e.g. database1 and database2) and how would I join these two sql queryies by using JOIN?

[r937]

okay, i didn't understand about the separate databases

your first query builds the array

your next query should look like this:

SELECT * 
  FROM url u 
INNER 
  JOIN rating r 
    ON r.id = u.id
INNER 
  JOIN name n 
    ON n.id = r.id
INNER 
  JOIN games g 
    ON g.id = n.id 
 WHERE n.id in
       ( list the ids in the array here )
ORDER
    BY x.name

don't use USING, use ON clauses -- and check to make sure you're joining the correct columns

always qualify every column in a join -- you didn't say which table "name" comes from

[dazi_behappy]

don't use USING, use ON clauses

Just wondering why not ON, because I use this quite a lot within my site.

Cheers

[r937]

because USING can get confused about which columns to join with which other columns

for example, go back to your original query --

... FROM url u INNER JOIN rating r USING(id) ...

according to this, you're joining url and rating by two columns called id, right?

so url.id would be the id of the url, right? so what's rating.id? it had better be the id of the url too, or else you are joining the wrong columns!

that's why you shouldn't use USING!!