#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Location
    Glendale AZ
    Posts
    230
    Rep Power
    96

    Some MySQL joining help


    I've got this SELECT that works properly:

    PHP Code:
    SELECT FROM events LEFT JOIN colors ON colors.color_no events.color ORDER BY start_date ASC 
    Now, I need the same SELECT statement except that I need to pull * from events WHERE events.event_no = ?

    I know this works in my prepared statement:

    PHP Code:
    SELECT event_notbdstart_dateend_datetitledescriptionperformancecolor FROM events WHERE event_no = ? 
    But I don't know how to get the join for the colors table. I've tried taking the first SELECT with LEFT JOIN and adding the WHERE clause both in front of and behind the join. I've also tried LEFT JOIN colors ON colors.color_no = events.color AND events.event_no =?. Obviously I'm just stabbing in the dark and neither have any success.

    I've tried:

    PHP Code:
    $select "SELECT events.event_no, events.tbd, events.start_date, events.end_date, events.title, events.description, events.performance, colors.color_no FROM events, colors WHERE events.event_no = ? AND colors.color_no = events.color";
    if (
    $stmt $conn->prepare($select)) {
        
    $stmt->bind_param('i'$event_number);
        if (!
    $stmt->execute()) {echo $stmt->error;}
        
    $stmt->bind_result($event_no$tbd$start_date$end_date$title$description$performance$color);
        
    $stmt->fetch();
        
    $stmt->close(); 
    I've done joins plenty but they've always been a single relationship in the WHEREs. That is

    WHERE table1.column = ?
    or
    WHERE table1.column = table2.column
    never
    WHERE table1.column = ? AND table1.column2 = table2.column3

    I'm sure it can be done in one select. I just haven't figure it out yet...

    Thanks,

    Mike
    Last edited by big0mike; August 22nd, 2010 at 03:31 PM. Reason: Found an error but fixing it didn't help
  2. #2
  3. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2008
    Location
    North Carolina
    Posts
    2,670
    Rep Power
    2677
    Do you really need to SELECT *? Unless you really need all the fields, its much better to only get the ones you need.

    Then, open up phpMyAdmin or whatever you use, and try this query, replacing ? with an actual id number. It ought to work without any problems. Then try putting it in to a PHP script and see if it still works. If it doesn't, then the issue lies in the PHP.

    sql Code:
    SELECT
    	events.*
    FROM
    	events
    INNER JOIN
    	colors
    		ON
    	colors.color_no = events.color
    WHERE
    	events.event_no = ?
    ORDER BY
    	start_date
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Location
    Glendale AZ
    Posts
    230
    Rep Power
    96
    Originally Posted by simshaun
    Do you really need to SELECT *? Unless you really need all the fields, its much better to only get the ones you need.
    Usually, I don't but in this case I do...

    Originally Posted by simshaun
    sql Code:
    SELECT
    	events.*
    FROM
    	events
    INNER JOIN
    	colors
    		ON
    	colors.color_no = events.color
    WHERE
    	events.event_no = ?
    ORDER BY
    	start_date
    That worked. Maybe the difference was in INNER JOIN? It seemed like what I was writing was very close to that statement but the difference JOINs can work so differently and I don't know may way around them so much...

    Thanks,

    Mike
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Location
    Glendale AZ
    Posts
    230
    Rep Power
    96
    Why does this work? The events tables has 8 fields exactly as listed in the bind_result line. But, I'm joining those 8 fields with colors.color_no so I need a ninth variable in bind_result but when I add it the script chokes.

    PHP Code:
    $select "SELECT events.* FROM events INNER JOIN colors ON colors.color_no = events.color WHERE events.event_no = ?";
    if (
    $stmt $conn->prepare($select)) {
        
    $stmt->bind_param('i'$event_number);
        if (!
    $stmt->execute()) {echo $stmt->error;}
        
    $stmt->bind_result($event_no$tbd$start_date$end_date$title$description$performance$color);
        
    $stmt->fetch();
        
    $stmt->close(); 
  8. #5
  9. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,435
    Rep Power
    9645
    Originally Posted by big0mike
    But, I'm joining those 8 fields with colors.color_no so I need a ninth variable in bind_result
    You'll only get back the fields mentioned in the SELECT list. Since the color is not in there you won't get it.

    Run the query yourself. See how many fields you actually get back.
  10. #6
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Location
    Glendale AZ
    Posts
    230
    Rep Power
    96
    I get 8. But, I'm not asking for 8, I'm asking for 9.

    SELECT events.* FROM events INNER JOIN colors ON colors.color_no = events.color WHERE events.event_no = ?

    You said it's only going to pull those columns in the select statement. That's everything from events (8) and one from colors for a total of 9. Is it not pulling events.color because it's used as a comparison and therefore assumed I only need colors.color_no and not events.color?

    Or is it pulling everything from what's between SELECT and FROM in which case it's only events.*. In which case the entire join is worthless because I was trying to get colors.color_name added to this query. $events holds the color (row number) of the color and $colors holds the color_no (row), color_name, color_value (hex code).
  12. #7
  13. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2006
    Location
    Hindustan
    Posts
    1,121
    Rep Power
    1312
    You need to add the columns you want to be displayed in the select clause
    PHP Code:
    SELECT events.*, colors.column1colors.column2,..etc 
    FROM events INNER JOIN colors ON colors
    .color_no events.color WHERE events.event_no = ? 
    Trimbak D. Bankar.
  14. #8
  15. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Location
    Glendale AZ
    Posts
    230
    Rep Power
    96
    Originally Posted by tbankar
    You need to add the columns you want to be displayed in the select clause
    PHP Code:
    SELECT events.*, colors.column1colors.column2,..etc 
    FROM events INNER JOIN colors ON colors
    .color_no events.color WHERE events.event_no = ? 
    Damn, you're right. I looked at so many different examples trying to figure it out and I would've sworn they didn't do that. I apparently was not paying enough attention to the examples, looking more at the JOIN ON part and not the SELECT part.

    Thanks,

    Mike

    EDIT: Now I see where my thought process was flawed (I think). In the sample given to me is SELECT * which is selecting EVERYTHING from BOTH tables which is why it was working. I thought it was selecting everything from events and then only adding the columns specified in the JOIN section. That's a very important distinction...

    Comments on this post

    • simshaun agrees : Rep for learning.
    Last edited by big0mike; August 24th, 2010 at 10:06 AM. Reason: Had an epiphany
  16. #9
  17. Blowing up trees with my mind.
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Feb 2005
    Location
    Neverland Ranch, minus the touching.
    Posts
    3,375
    Rep Power
    2570
    Originally Posted by big0mike
    I get 8. But, I'm not asking for 8, I'm asking for 9.

    SELECT events.* FROM events INNER JOIN colors ON colors.color_no = events.color WHERE events.event_no = ?

    You said it's only going to pull those columns in the select statement. That's everything from events (8) and one from colors for a total of 9. Is it not pulling events.color because it's used as a comparison and therefore assumed I only need colors.color_no and not events.color?

    Or is it pulling everything from what's between SELECT and FROM in which case it's only events.*. In which case the entire join is worthless because I was trying to get colors.color_name added to this query. $events holds the color (row number) of the color and $colors holds the color_no (row), color_name, color_value (hex code).
    Do you not know how a select works?
  18. #10
  19. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Location
    Glendale AZ
    Posts
    230
    Rep Power
    96
    Originally Posted by Veamon
    Do you not know how a select works?
    You obviously know the answer to that question.

IMN logo majestic logo threadwatch logo seochat tools logo