SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    UNION Query in the Same Table

    Code:
    //Query the Events
    $query  = "SELECT DATE_FORMAT(start_date, '%Y-%m-%d') AS a, DATE_FORMAT(end_date, '%Y-%m-%d'), title, state, country, type, channel, url, org, monster, DATE_FORMAT(added, '%Y-%m-%d') 
    			FROM eventstable
    				WHERE (end_date between '$today' AND '$year_out') AND (type = 'both' OR type='Live')
    			UNION
    			SELECT DATE_FORMAT(start_date, '%Y-%m-%d') AS a, DATE_FORMAT(end_date, '%Y-%m-%d'), title, state, country, type, channel, url, org, monster, DATE_FORMAT(added, '%Y-%m-%d') 
    			FROM eventstable
    				WHERE (end_date between '$today' AND '$year_out') AND (type = 'both' OR type='TV') AND air_date != '0000-00-00' AND air_time != '00:00:00'
        				ORDER BY a, state, city 
    						LIMIT $offset, $rowsPerPage";
    I am unable to get this UNION to work. Each Query Works Separately but not together.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what symptoms are you seeing? the union query crashed the server? it runs but takes forever? it runs but returns the wrong rows?

    pls define "get this UNION to work" in this context

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

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The Query dies. I am unable to echo a MYSQL Error out of it.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    please post the line that includes mysql_query(), too.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    //Query the Events
    $query  = "SELECT DATE_FORMAT(start_date, '%Y-%m-%d') AS a, DATE_FORMAT(end_date, '%Y-%m-%d'), title, state, country, type, channel, url, org, monster, DATE_FORMAT(added, '%Y-%m-%d') 
    			FROM eventstable
    				WHERE (end_date between '$today' AND '$year_out') AND (type = 'both' OR type='Live')
    			UNION
    			SELECT DATE_FORMAT(start_date, '%Y-%m-%d') AS a, DATE_FORMAT(end_date, '%Y-%m-%d'), title, state, country, type, channel, url, org, monster, DATE_FORMAT(added, '%Y-%m-%d') 
    			FROM eventstable
    				WHERE (end_date between '$today' AND '$year_out') AND (type = 'both' OR type='TV') AND air_date != '0000-00-00' AND air_time != '00:00:00'
        				ORDER BY a, state, city 
    						LIMIT $offset, $rowsPerPage";				
    				  				
    $result = mysql_query($query) or die('Error, Event Query Failed.  Report to Administrator.');
    
    while(list($start_date, $end_date, $title, $state, $country, $type, $channel, $url, $org, $monster, $added) = mysql_fetch_array($result))
    {
    //List in Table Here 
    }

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This:
    $result = mysql_query($query) or die('Error, Event Query Failed. Report to Administrator.');
    is meaningless without getting resulting mysql error

    Code:
    $result = mysql_query($query) 
    or die('Error, Event Query Failed.  Report to Administrator.') 
    . mysql_error();

  7. #7
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still the thing I get is:
    Error, Event Query Failed. Report to Administrator.

    I had mysql_error() in there before. Forgot that I took it out before I posted.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    as this is the mysql forum and not the php forum, i wonder if i could ask you to test your union query outside of php and tell us how it goes...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Unfortunately I am only familiar with MySQL via PhP.

    I have also tried the following without receiving an error from MySQL.
    Code:
    //Query the Events
    $query  = "( SELECT DATE_FORMAT(start_date, '%Y-%m-%d') AS a, DATE_FORMAT(end_date, '%Y-%m-%d'), title, state, country, type, channel, url, org, monster, DATE_FORMAT(added, '%Y-%m-%d') 
    			FROM eventstable
    				WHERE (end_date between '$today' AND '$year_out') AND (type = 'both' OR type='Live') )
    			UNION
    			( SELECT DATE_FORMAT(start_date, '%Y-%m-%d') AS a, DATE_FORMAT(end_date, '%Y-%m-%d'), title, state, country, type, channel, url, org, monster, DATE_FORMAT(added, '%Y-%m-%d') 
    			FROM eventstable
    				WHERE (end_date between '$today' AND '$year_out') AND (type = 'both' OR type='TV') AND air_date != '0000-00-00' AND air_time != '00:00:00' )
        				ORDER BY a, state, city 
    						LIMIT $offset, $rowsPerPage";				
    				  				
    $result = mysql_query($query) 
    or die('Error, Event Query Failed.  Report to Administrator.') 
    . mysql_error();
    
    while(list($start_date, $end_date, $title, $state, $country, $type, $channel, $url, $org, $monster, $added) = mysql_fetch_array($result))
    {
    //List in Table Here 
    }

  10. #10
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you have a small bug in your code that prevents you from seeing the error reported by mysql.

    change this:
    Code:
    or die('Error, Event Query Failed.  Report to Administrator.') 
    . mysql_error();
    to this:
    Code:
    or die('Error, Event Query Failed.  Report to Administrator.' 
    . mysql_error());
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  11. #11
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I got it working. Thanks for that. It didn't like the City in the Order By.

    However, the problem I am having now is that some of the events have a "live time" and a "televised time". Is it possible duplicate them in a list if they are televised and live?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mramonster View Post
    It didn't like the City in the Order By.
    aaargh, i should have caught that, it's so obvious now why

    Quote Originally Posted by mramonster View Post
    Is it possible duplicate them in a list if they are televised and live?
    yep

    add type to the SELECT clauses, and change UNION to UNION ALL

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

  13. #13
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I believe I can work with this now.

    Thanks a lot guys for the help.


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
  •