SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2005
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sorting and sql query

    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:
    Code:
    $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:
    Code:
    $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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yes, you can, with a JOIN

    which columns would you like to return? which columns would you like to sort by?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2005
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    $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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, i didn't understand about the separate databases

    your first query builds the array

    your next query should look like this:
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Nov 2005
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    don't use USING, use ON clauses
    Just wondering why not ON, because I use this quite a lot within my site.

    Cheers

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •