SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query help with multiple table calls

    I'm creating a drink specials database. More of a fun side project than a good business model . I am listing all the bars, with the specials under them. I have accomplished a basic idea here:
    http://www.madtownparty.com/

    this has two tables bar, and bar_drinks.

    Right now it each bar, has either one or no record in the database, but I set it up that there can be more than one for a given day.

    So, first question, with the code below, how do I organize it to drop out the bar if it doesn't exist. I know this is php, however, I think my question might be better answered by combining my queries? I might be wrong here. I also want to add another field to flag happy hour specials.

    SO... I want to go from
    Code:
    BAR NAME
      special 1
      special 2
    BAR NAME
      no specials
    to
    Code:
    BAR NAME
      Happy Hour (add new filed to bar_drinks called bar_d_happyhour)
         special
      Nightly Special
        Special 1
        Special 2
    and drop the bar if there is no record in the bar_drinks table

    PHP Code:
    $SQL="SELECT bar.bar_name, bar.bar_id FROM bar ORDER BY bar_name";
    $result=mysql_query($SQL) or die ("Error in BAR NAME: $query. " mysql_error());
    while (
    $row=mysql_fetch_object($result)) {
        echo 
    "<h2><a href=\"bar_profile_$row->bar_id.php\">$row->bar_name</a></h2>";
        
    $today_dateDATE('Y-m-d');
        
    $SQL_special="SELECT * FROM bar_drinks where (bar_d_date = '$passed_date' and bar_d_repeat='once') or ((bar_d_date <= '$passed_date') and (WEEKDAY(bar_d_date) = WEEKDAY('$passed_date') and bar_d_repeat='weekly' and bar_d_date <= '$passed_date')) and (bar_id='$row->bar_id')";
        
    $result_special=mysql_query($SQL_special) or die ("Error in SPECIAL: $SQL_special. " mysql_error());
        
    $rowcount mysql_num_rows($result_special);
        if (
    $rowcount == 0) {
            echo 
    "<br /><div class=\"noresults\"> No Specials.</div><br />";
        } else {
            while (
    $row_special=mysql_fetch_object($result_special)) {
                if (
    $row_special->bar_d_name) { echo "<b><i>".$row_special->bar_d_name."</i></b><br />"; }
                echo 
    nl2br($row_special->bar_d_event)."<br /><br />";
            }
        }

    Table Design
    BAR
    Code:
    bar_id	int(20)	NO	PRI	NULL	auto_increment
    bar_name	varchar(255)	NO	 	 	 
    bar_c_id	int(20)	NO	 	0	 
    bar_address	varchar(255)	NO	 	 	 
    bar_city	varchar(255)	NO	 	 	 
    bar_state	char(2)	NO	 	 	 
    bar_zip	int(5)	NO	 	0	 
    bar_website	varchar(255)	NO	 	 	 
    bar_phone	varchar(255)	NO	 	 	 
    bar_description	text	NO	 	 	 
    bar_atm	char(3)	NO	 	 	 
    bar_games	varchar(255)	NO	 	 	 
    bar_created_date	datetime	NO	 	0000-00-00 00:00:00	 
    bar_last_update	datetime	NO	 	0000-00-00 00:00:00	 
    bar_count	int(20) unsigned	NO	 	0
    BAR_DRINKS
    Code:
    bar_d_id	int(20) unsigned	NO	PRI	NULL	auto_increment
    bar_id	int(20)	NO	 	0	 
    bar_d_name	varchar(255)	NO	 	 	 
    bar_d_event	text	NO	 	 	 
    bar_d_date	date	NO	 	0000-00-00	 
    bar_d_repeat	varchar(10)	NO
    * need to add bar_d_happyhour

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the solution is to run a join query which will also improve performance over doing a query in a loop

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I sort of figured out that, but how would I accomplish this? Is this a group by situation? I really have tried on my own for a few days now and am stumped.

  4. #4
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess the hard part here is this is the way I have always done things two sql statements, two while queries, with out being shown or taught how to do a join and query off that. Ie.. join the two tables (left or just simple?), then how do I cycle through the record result to get the format I'm looking for?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    important: run this query outside of php first, using an actual date instead of $passed_date

    Code:
    SELECT b.bar_name
         , b.bar_id 
         , bd.bar_d_name
         , bd.bar_d_event
         , bd.bar_d_date
         , bd.bar_d_repeat                         
      FROM bar AS b
    INNER
      JOIN bar_drinks AS bd
        ON bd.bar_id = b.bar_id
       AND (
            ( bd.bar_d_date = '$passed_date' 
          AND bd.bar_d_repeat = 'once'
            )
         OR 
            ( bd.bar_d_date <= '$passed_date'
          AND WEEKDAY(bd.bar_d_date) = WEEKDAY('$passed_date') 
          AND bd.bar_d_repeat = 'weekly' 
            )
           )
    ORDER  
        BY b.bar_name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok... that makes sense to me, but let's say I add that extra field (bar_p_happyhour), and I want to list things like this after I get those results?
    Code:
    BAR NAME
      Happy Hour
          special 1, special 2
      Nightly Specials
          special 1, special 2
    Can you make a while statement for individual object results? ie:

    while bar from the query
    echo bar
    while bar

    echo happy hour
    while happy hour
    echo specials

    echo nightly specials
    while nightly specials

    This is where my brain turns into a mush substance......
    echo nightly specials

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes, you can do that in your while loop, there should only be one while loop with embedded control breaks

    to use your example data, the query would return this:
    Code:
    bar_name bar_p_happyhour  bar_d_name
    BAR NAME Happy Hour       special 1
    BAR NAME Happy Hour       special 2
    BAR NAME Nightly Specials special 1
    BAR NAME Nightly Specials special 2
    that's why i said you should run the query outside of php first, so that you can get to know what the data will look like

    you will want to add bar_p_happyhour and bar_d_name to the ORDER BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok... so I would add "order by bar_name, bar_d_happyhour, bar_d_event". What is the control break structure, I think this is where I could use help the most....
    ie..
    PHP Code:
    $SQL="BIG *** SQL QUERY LISTED BELOW";
    $result=mysql_query($SQL) or die ("Error in BAR NAME: $query. " mysql_error());
    while (
    $row=mysql_fetch_object($result)) {
      do {
        echo 
    $row->bar_name;

    ???????????????????

      } while (
    $row->bar_name == $row->bar_name);

    Quote Originally Posted by r937 View Post
    yes, you can do that in your while loop, there should only be one while loop with embedded control breaks

    to use your example data, the query would return this:
    Code:
    bar_name bar_p_happyhour  bar_d_name
    BAR NAME Happy Hour       special 1
    BAR NAME Happy Hour       special 2
    BAR NAME Nightly Specials special 1
    BAR NAME Nightly Specials special 2
    that's why i said you should run the query outside of php first, so that you can get to know what the data will look like

    you will want to add bar_p_happyhour and bar_d_name to the ORDER BY

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    sorry, that's php, and i don't do php

    moving thread to php forum

    would you please display the first 10 or so rows from when you run the query outside of php
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Guy's as you can tell, I'm trying to output the following format

    Code:
    BAR NAME (bar_name)
     if (bar_d_happy) 
      <h2>Happy Hour</h2>
        Special (bar_d_event)
    end if
      <h2>Nightly Special</h2>
    if(bar_d_name)
        <b>special Name (bar_d_name)</b>
    end if
        Special 1 (bar_d_event)
       <b>special Name (bar_d_name)</b>
        Special 2 ...
    I use this query, but not sure how to output it properly cycling through the results.....
    Code:
    SELECT b.bar_name
         , b.bar_id 
         , bd.bar_d_name
         , bd.bar_d_event
         , bd.bar_d_date
         , bd.bar_d_repeat
         , bd.bar_d_happy
      FROM bar AS b
    INNER
      JOIN bar_drinks AS bd
        ON bd.bar_id = b.bar_id
       AND (
            ( bd.bar_d_date = '$passed_date' 
          AND bd.bar_d_repeat = 'once'
            )
         OR 
            ( bd.bar_d_date <= '$passed_date'
          AND WEEKDAY(bd.bar_d_date) = WEEKDAY('$passed_date') 
          AND bd.bar_d_repeat = 'weekly' 
            )
           )
    ORDER  
        BY b.bar_name, bar_d_happy

    Code:
    bar_name | bar_id | bar_d_name | bar_d_event | bar_d_date | bar_d_repeat | bar_d_happy
    
    Amy's Cafe | 1 | * | $3.00 Margarita/Daquiri | $3.25 Strawberry Margarit... | 2007-09-06 | weekly | 0
    Amy's Cafe | 1 | * | $3.00 Margarita/Daquiri | $2.50 tap beers | 2007-09-06 | weekly | 1
    Angelic Brewing Co | 27 | * | $4.00 Rail Jumbos | $4.00 Drop Shots | $2.50 Domesti... | 2007-10-04 | weekly | 0
    Argus Food & Spirit | 48 | * | $2.00 Rails, $4.00 Vodka Red Bull | 2007-10-11 | weekly | 0
    Azzalinos | 78 | * | $4.25 Speciality Drinks | $1.00 Domestic Taps (8pm-... | 2007-10-04 | weekly | 0
    Brink Lounge | 40 | Thirsty Thursday | 25% off all bottles of wine | 2007-10-04 | weekly | 0
    Brothers | 62 | * | $1 Long Islands | $2 You-Call-Its | $2.50 Vodka Redb... | 2007-10-11 | weekly | 0
    City Bar | 13 | * | 1/2 off Pints and Pitchers | $3.00 Kettle Mixers | $... | 2007-09-06 | weekly | 0

  11. #11
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Your requested logic was a bit ambiguous, so I took a best guess:

    PHP Code:
    $cur_bar '';
    while (
    $row mysql_fetch_object($result)) {

      
    //Display the bar name only if it's not the same as the last row we looked at
      
    if ($row->bar_name != $cur_bar) {

        
    $cur_bar $row->bar_name;
        
    $special_num 1;

        echo 
    '<h1>' $row->bar_name '</h1>';

        
    //Also only want to display specials heading the first time we see a row for that bar
        
    if ($row->bar_d_happy) {
          echo 
    '<h2>Happy Hour</h2>';
        } else {
          echo 
    '<h2>Nightly Specials</h2>';
        }

      }

      
    //For every row, display the specials
      
    if (!empty($row->bar_d_name)) {
        echo 
    '<b>' $row->bar_d_name '</b><br />';
        echo 
    'Special ' $special_num ': ' $row->bar_d_event '<br />';
        
    $special_num++;
      }




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
  •