mysql_query

[_matrix_]

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

    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

[r937]

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

[_matrix_]

Hi,

The probelm is with this part I thought?

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

All the if statements work fine except

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

Thanks

[r937]

there doesn't seem to be any problem with that

have you tested your query outside of php yet?

[_matrix_]

Hi,

I tried a simple query in phpmyadmin

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

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

[r937]

remove the doublequotes and everything outside of them

[_matrix_]

Hey,

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

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

[r937]

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

[_matrix_]

Hi,

The simple things, its working great now.

Thanks!

[_matrix_]

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:

	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

[r937]

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

[_matrix_]

Hi,

I guess like:

	$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

[r937]

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

[_matrix_]

Hi,

I have it working using:

    $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