Multiple MYSQL query structure

[vedub4us]

Hi all, I have been searching like crazy to figure out how do develop a filter menu bar that will query my photo database by the parameters Date, Time, Channel, and Keywords. I know how to do the actual queries but I need it to be more flexible. Right now when the user logs in it shows them the most currently uploaded photos ordered by date and thats how I want it at login. but I also want to give them the option to filter by any of the above mentioned parameters, every post that I have read only gives the basic query structure for all the parameters but I want the user to be abel only search only one, say all the photos on a particular date or a few like date and keyword. How do I set such a query and have it remain across several pages of results. How do you get the form to reset the original query at login? This might be a stupid question but do you ever set a query into cookies or do the parameters get set in the url? I have gotten one parameter to work but cant figure out how to insert or remove other parameters.

Thanks for your time

[phranque]

welcome to WebmasterWorld, vedub4us!

do some research on php session management.

you can use many technologies, sometimes in combination, to maintain session data including cookies, url/form parameters, user database, etc.

depending on your web technology there may be some form of session management included.

[Habtom]

This might be a stupid question but do you ever set a query into cookies or do the parameters get set in the url?

You wouldn't necessarily need to put the query into cookies.

If I understand you correctly, you want the filter parameters to remain active till the person who is logged in resets it to anything else. And you wish to reset the filter parameter when the person logs out and logs in to the system again.

You can store the filter parameter (the field name in the table - and not the whole query) in a SESSION value. And every query you have might look like the following:

$query = "SELECT field1, field2 FROM table1 WHERE ". $_SESSION['field3']." = ". $_REQUEST['txtbox1'];

Note:
You can use cookies instead of the SESSION.

Habtom

[edited by: Habtom at 6:29 am (utc) on Sep. 25, 2007]

[vedub4us]

Ok things are starting to become more clear. This is what I had

$query = "SELECT name FROM photos WHERE user='$username' ORDER BY date DESC, time DESC LIMIT $offset, $rowsPerPage";

That gets me all the photos with the most recent day's events showing first (exactly what I want at login. FYI both date and time are manually inputed). Now, do I have to have all the variables already set in the query at the beginning like this?

"SELECT name FROM photos WHERE (user, ch, date, time, keyword) = ('$username', '$ch', '$date', '$time', '$keyword') ORDER BY date DESC, time DESC LIMIT $offset, $rowsPerPage";

before the query I would use something like this calling the url or a session/cookie to set the one of the variables.

if(isset($_GET['date']))
{
$date = $_GET['date'];
}

What I cant figure out, is what if I only want to search one of the parameters, if I leave the other variables empty the query returns nothing. How do I set the other variables to query all results in their respective columns. Could the "if" statement look like this?

if(isset($_GET['date']))
{
$date = $_GET['date'];
}
else
{
$date = whatever will query ALL the results in the date column
}

Am I over thinking this? Thanks for all your help.

[Habtom]

Ok, this is not the complete code, but it can give you some idea.

if(isset($_GET['date']))
{
$date = $_GET['date'];
$WHERE = 'date = ". $date ."'
}
else
{
$date = whatever will query ALL the results in the date column
$WHERE = "";
}

Now play around your if conditions, so that you get the right SQL statement.

"SELECT name FROM photos WHERE ". $WHERE ." ORDER BY date DESC, time DESC LIMIT $offset, $rowsPerPage";

[vedub4us]

ok this is what I came up with, sorry if this is long but hopefully it will help someone else in the future. This is the file the form posts too

photoFilter.php:

<?php
if(isset($_POST['submit'])) //Get form info and pass to cookies
{
$hour = time() + 3600;
setcookie(photoQueryDate, $_POST['photoDate'], $hour);

if($_POST['photoDate']=='all') // if the form sends the value "all" it deletes the cookie so date parameter wont be run in the query
{
$past = time() - 100;
setcookie(photoQueryDate, gone, $past);
}
else
{
}

}

if(isset($_POST['submit'])) //Get form info and pass to cookies
{
$hour = time() + 3600;
setcookie(photoQueryCh, $_POST['photoCh'], $hour);

if($_POST['photoCh']=='all') // if the form sends the value "all" it deletes the cookie so ch parameter wont be run in the query
{
$past = time() - 100;
setcookie(photoQueryCh, gone, $past);
}
else
{
}

}

if(isset($_POST['submit'])) //Get form info and pass to cookies
{
$hour = time() + 3600;
setcookie(photoQueryTime, $_POST['photoTime'], $hour);

if($_POST['photoTime']=='all') // if the form sends the value "all" it deletes the cookie so time parameter wont be run in the query
{
$past = time() - 100;
setcookie(photoQueryTime, gone, $past);
}
else
{
}

}

header("Location: index.php"); // sends the browser it right back to the index.php so query will re run with new query values stored in the cookies.
?>

Here is the query section in the index.php.

if(isset($_COOKIE['photoQueryDate'])) //get date from cookie
{
$date = $_COOKIE['photoQueryDate'];
$dateWHERE = 'AND date = "'. $date .'"';
}
else
{
$dateWHERE = "";
}

if(isset($_COOKIE['photoQueryCh'])) //get channel from cookie
{
$channel = $_COOKIE['photoQueryCh'];
$channelWHERE = 'AND ch = "'. $channel .'"';
}
else
{
$channelWHERE = "";
}

if(isset($_COOKIE['photoQueryTime'])) //get time from cookie
{
$time = $_COOKIE['photoQueryTime'];
$timeWHERE = 'AND time = "'. $time .'"';
}
else
{
$timeWHERE = "";
}

$query = "SELECT name FROM photos WHERE user = '$username' ". $dateWHERE ." ". $channelWHERE ." ". $timeWHERE ." ORDER BY date DESC, time DESC LIMIT $offset, $rowsPerPage";

So far it is working fine, next I plan on sliming it down and adding some validation, does anyone see any drawbacks to this method? Once again thanks for all the help

[Habtom]

Glad you got it working.

Habtom