SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Thread: mysql_query

  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql_query

    Hi,

    I Want to switch my query depending if a get variable exists. If it exists then the query checks for dates matching the get data.

    The query below doesnt seem to bring back any results or errors. The query also uses joins

    PHP Code:
        if(isset($_GET['sbd'])) {
        
    $eventdate ''.$_GET['yearnow'].'-'.$_GET['monthnow'].'-'.$_GET['day'].'';
        
    $where "WHERE e.eventdate = $eventdate";
        }
        elseif(isset(
    $_GET['userid'])) {
        
    $where "WHERE u.userid = ".$_GET['userid']."";
        }
        elseif((!
    $location == '') && (!$type =='')) {
        
    $where "WHERE u.location = '$location' && e.type = $type";
        }
        elseif((!
    $location == '')) {
        
    $where "WHERE u.location = '$location'";
        }
        elseif(!
    $type == '') {
        
    $where "WHERE e.type = $type";
        }
        elseif(
    $location == '') {
        
    $where "";
        }

        
    $sql mysql_query("SELECT e.eventid, e.type, e.heading, DATE_FORMAT(e.eventdate, '%Y-%m-%d'), e.description, e.imagename, u.userid, u.companyname
        FROM events as e 
        LEFT JOIN users as u ON e.userid = u.userid
        "
    .$where." && u.username != 'admin'
        ORDER BY e.eventdate ASC"
    ); 
    How should it be?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    if you use a LEFT OUTER JOIN and have specific conditions for the right table, those should probably go into the ON clause of the join, and not the WHERE clause

    could you please test your query outside of php? and then show us that version?

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

  3. #3
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    The probelm is with this part I thought?

    DATE_FORMAT(e.eventdate, '%Y-%m-%d')

    All the if statements work fine except

    PHP Code:
    if(isset($_GET['sbd'])) {     $eventdate ''.$_GET['yearnow'].'-'.$_GET['monthnow'].'-'.$_GET['day'].'';     $where "WHERE e.eventdate = $eventdate";     } 
    Thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    there doesn't seem to be any problem with that

    have you tested your query outside of php yet?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I tried a simple query in phpmyadmin

    Code:
    mysql_query("SELECT e.eventid, e.userid, e.eventdate, u.userid
    FROM events as e 
    LEFT JOIN users as u ON e.userid = u.userid
    WHERE e.eventdate = 2008-04-17
    ORDER BY u.userid ASC")
    and get error

    Code:
    MySQL said:  
    
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql_query("SELECT e.eventid, e.userid, e.eventdate, u.userid
    FROM events as e' at line 1
    Thanks

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    remove the doublequotes and everything outside of them

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

  7. #7
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    The following brings back zero results, but there is defently a record in the events table matching that date:

    Code:
    SELECT e.eventid, e.type, e.heading, DATE_FORMAT(e.eventdate, '%Y-%m-%d'), e.description, e.imagename, u.userid, u.companyname
    FROM events as e 
    LEFT OUTER JOIN users as u ON u.userid = e.userid
    WHERE e.eventdate = 2008-04-04
    ORDER BY e.eventdate ASC
    ?

    Thanks

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you forgot the quotes

    '2008-04-04' is a valid date constant

    2008-04-04 is an arithmetic expression, 2008 minus 4 minus 4, which equals 2000, which is not a valid date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    The simple things, its working great now.

    Thanks!

  10. #10
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql_query

    Hi,

    I have a bunch of if statements to populate a query depending on the get variables.

    So:

    userid, location, type depending if they hold data. But I also need to check on month and year if variables are set for them.

    I have:

    Code:
    	if($_GET['userid'] != '') {
    	$where = "WHERE u.userid = ".$_GET['userid']."";
    	}
    	elseif((!$location == '') && (!$type =='')) {
    	$where = "WHERE u.location = '$location' && e.type = $type";
    	}
    	elseif((!$location == '')) {
    	$where = "WHERE u.location = '$location'";
    	}
    	elseif(!$type == '') {
    	$where = "WHERE e.type = $type";
    	}
    	elseif($location == '') {
    	$where = "";
    	}
    	
    	$sql = mysql_query("SELECT e.eventid, e.type, e.heading, e.eventdate, e.description, e.imagename, u.userid, u.companyname
    	FROM events as e 
    	LEFT OUTER JOIN users as u ON u.userid = e.userid
    	".$where."
    	ORDER BY e.eventdate ASC");
    Whats a clean way to do this dependant on what get variables are captured, given I need to check the eventdate field for just month and year, but also need the full date to echo out with the results. The eventdate field is datetime.

    Thanks

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    clean way to do this: start the WHERE clause with WHERE 1=1

    then, depending on whether parameter values have been entered, you can append any number of additional conditions, each beginning with AND
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I guess like:

    Code:
    	$where = "WHERE e.eventdate = ".$year."";
    	
    	if($userid != '') {
    	$where = " && u.userid = ".$userid."";
    	}
    	elseif($month != ''){
    	$where = " && e.eventdate = ".$month."";
    	}
    	elseif((!$location == '') && (!$type =='')) {
    	$where = " && u.location = '$location' && e.type = $type";
    	}
    	elseif((!$location == '')) {
    	$where = " && u.location = '$location'";
    	}
    	elseif(!$type == '') {
    	$where = " && e.type = $type";
    	}
    	elseif($location == '') {
    	$where = "";
    	}
    I am more lost on DATE_FORMAT(e.eventdate, '%Y-%m-%d'), so I can query on the month or the year but also get the entire ymd to display with the results.

    Thanks

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    DATE_FORMAT is used to format a date into whatever format you want

    you wouldn't use it to query the rows, you'd use some other functions

    for example, to get all rows for 2008, you'd use WHERE YEAR(eventdate)=2008

    by the way, don't use && (it's non-standard) -- use AND instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I have it working using:

    PHP Code:
        $where "WHERE YEAR(e.eventdate)=".$year." AND MONTH(e.eventdate)=".$month."";
        
        if(
    $userid != '') {
        
    $where .= " AND u.userid = ".$userid."";
        }
        if((!
    $location == '') AND (!$type =='')) {
        
    $where .= " AND u.location = '$location' AND e.type = $type";
        }
        if((!
    $location == '')) {
        
    $where .= " AND u.location = '$location'";
        }
        elseif(!
    $type == '') {
        
    $where .= " AND e.type = $type";
        }
        
        
    $sql mysql_query("SELECT e.eventid, e.type, e.heading, e.eventdate, e.description, e.imagename, u.userid, u.companyname
        FROM events as e 
        LEFT OUTER JOIN users as u ON u.userid = e.userid
        "
    .$where."
        ORDER BY e.eventdate ASC"
    ); 

    Thanks for the && tip


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
  •