drakkarnoir
asked on
Multiple where clauses giving me trouble
What I'm having trouble with is if a user wants to specify that they want to see the same type of file, then they select "I want to see this type of file and this type of file". Right now I have my sql statement set up as the following:
$total_rows = mysql_num_rows(mysql_query
And...the code right above it:
global $HTTP_GET_VARS;
$id = $HTTP_GET_VARS['record'];
$def = $HTTP_GET_VARS['def'];
$category = $HTTP_GET_VARS['category']
$sortedby = $HTTP_GET_VARS['sortedby']
$orderway = $HTTP_GET_VARS['orderway']
// type
if($def=="")
{
$def = "type";
}
else
{
$def = $HTTP_GET_VARS['def'];
}
if($category=="")
{
$category = "Default";
}
else
{
$category = $HTTP_GET_VARS['category']
}
// sort
if($sortedby=="")
{
$sortedby = "id";
}
else
{
$sortedby = $HTTP_GET_VARS['sortedby']
}
// asc or desc
if($orderway=="")
{
$orderway = "desc";
}
else
{
if($orderway=="desc")
{
$orderway = "asc";
}
else if($orderway=="asc")
{
$orderway = "desc";
}
}
But what if I want to select both Type1 and Type2 files? How would I change my code to do that? I tried to do another if statement to say:
else if($category=="type1type2"
{
$category = "Type1file";
$category2 = "Tyle2file";
$def2 = "type";
$andstr = "and";
}
and made my SQL statement:
$total_rows = mysql_num_rows(mysql_query
But it will still only show the first category, I can't seem to find what is wrong.
Thanks in advance.
And actually this question is worth 1000 points, I will give the other 500 in another thread to the expert who can answer this :)
$total_rows = mysql_num_rows(mysql_query
And...the code right above it:
global $HTTP_GET_VARS;
$id = $HTTP_GET_VARS['record'];
$def = $HTTP_GET_VARS['def'];
$category = $HTTP_GET_VARS['category']
$sortedby = $HTTP_GET_VARS['sortedby']
$orderway = $HTTP_GET_VARS['orderway']
// type
if($def=="")
{
$def = "type";
}
else
{
$def = $HTTP_GET_VARS['def'];
}
if($category=="")
{
$category = "Default";
}
else
{
$category = $HTTP_GET_VARS['category']
}
// sort
if($sortedby=="")
{
$sortedby = "id";
}
else
{
$sortedby = $HTTP_GET_VARS['sortedby']
}
// asc or desc
if($orderway=="")
{
$orderway = "desc";
}
else
{
if($orderway=="desc")
{
$orderway = "asc";
}
else if($orderway=="asc")
{
$orderway = "desc";
}
}
But what if I want to select both Type1 and Type2 files? How would I change my code to do that? I tried to do another if statement to say:
else if($category=="type1type2"
{
$category = "Type1file";
$category2 = "Tyle2file";
$def2 = "type";
$andstr = "and";
}
and made my SQL statement:
$total_rows = mysql_num_rows(mysql_query
But it will still only show the first category, I can't seem to find what is wrong.
Thanks in advance.
And actually this question is worth 1000 points, I will give the other 500 in another thread to the expert who can answer this :)
well you have quotes around '$category' and not around '$category2', so depending on your error reporting it could be looking for the wrong thing in the table?
ASKER
No that was just a typo in writing that out, it still won't work even with the ' '
what does the statement actually say if you echo it to the screen. Can you get both categories to display when you type direct into a mysql prompt the query you are trying to generate? One of these things will track down your bug I hope!
Hi, drakkarnoir,
I took the liberty to clean up your code a bit to make it shorter, and also got rid of the HTTP_GET_VARS as it's outdated in newer php versions. I chose $_REQUEST as it deals with both post and get. And here's the script that should work for you (not tested, but probably works right away, hopefully anyway. :-) )
$id = $_REQUEST['record'];
$def = $_REQUEST['def'];
$category = $_REQUEST['category'];
$sortedby = $_REQUEST['sortedby'];
$orderway = $_REQUEST['orderway'];
// type
if($def=="") $def = "type";
if($category=="")
{
$category = "default"; # this statement can be omitted
$typestr = "type like 'default'";
}
else if(preg_match(',', $category))# assuming input use ',' to separate types (such as "type1, type2")
{
$types = preg_split('/\s*,\s*/', $category); # split types into array based on ',' and spaces
$newtypes = array();
foreach ($types as $type)
{
$newtypes[] = "type like '$type'";
}
$typestr = "(" . join(" or ", $newtypes) . ")"; # creates something like "(type like 'type1' or type like 'type2')"
}
// sort
if($sortedby=="") $sortedby = "id";
// asc or desc
if($orderway=="")
{
$orderway = "desc";
}
else if($orderway=="desc")
{
$orderway = "asc";
}
else if($orderway=="asc")
{
$orderway = "desc";
}
$total_rows = mysql_num_rows(mysql_query
I took the liberty to clean up your code a bit to make it shorter, and also got rid of the HTTP_GET_VARS as it's outdated in newer php versions. I chose $_REQUEST as it deals with both post and get. And here's the script that should work for you (not tested, but probably works right away, hopefully anyway. :-) )
$id = $_REQUEST['record'];
$def = $_REQUEST['def'];
$category = $_REQUEST['category'];
$sortedby = $_REQUEST['sortedby'];
$orderway = $_REQUEST['orderway'];
// type
if($def=="") $def = "type";
if($category=="")
{
$category = "default"; # this statement can be omitted
$typestr = "type like 'default'";
}
else if(preg_match(',', $category))# assuming input use ',' to separate types (such as "type1, type2")
{
$types = preg_split('/\s*,\s*/', $category); # split types into array based on ',' and spaces
$newtypes = array();
foreach ($types as $type)
{
$newtypes[] = "type like '$type'";
}
$typestr = "(" . join(" or ", $newtypes) . ")"; # creates something like "(type like 'type1' or type like 'type2')"
}
// sort
if($sortedby=="") $sortedby = "id";
// asc or desc
if($orderway=="")
{
$orderway = "desc";
}
else if($orderway=="desc")
{
$orderway = "asc";
}
else if($orderway=="asc")
{
$orderway = "desc";
}
$total_rows = mysql_num_rows(mysql_query
Your sql query is using the logical AND operator to merge categories - this is restricting the number of results returned to only file types that match *both* categories. If you use the OR operator your query will return the desired results, files that match *either* of the categories.
Looks like the previous poster has tried to implement this fix for you. Regardles whether it works or not, I'd recommend following the suggestion to use $_REQUEST over the depreciated $HTTP_GET_VARS.
Looks like the previous poster has tried to implement this fix for you. Regardles whether it works or not, I'd recommend following the suggestion to use $_REQUEST over the depreciated $HTTP_GET_VARS.
ASKER
On your code inq123, I got the following error:
No ending delimiter found on line 1075
In your code, that line is:
else if(preg_match(',', $category))# assuming input use ',' to separate types (such as "type1, type2")
No ending delimiter found on line 1075
In your code, that line is:
else if(preg_match(',', $category))# assuming input use ',' to separate types (such as "type1, type2")
drakkarnoir,
that's because for some reason I was using "else if" instead of "elseif". Please make all changes accordingly. I must've been thinking of some other language.
that's because for some reason I was using "else if" instead of "elseif". Please make all changes accordingly. I must've been thinking of some other language.
ASKER
Hrmm, else if is valid in PHP. Same error still.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
drakkarnoir,
After 2nd thought, I think you should get rid of the preg_match altogether, basically replace that line to
else
(just "else" is fine, because using that preg_match would rule out cases where only one type is specified).
After 2nd thought, I think you should get rid of the preg_match altogether, basically replace that line to
else
(just "else" is fine, because using that preg_match would rule out cases where only one type is specified).
ASKER
Ok I have a quick question as well, right now register_globals is ON.
But how would my code change if I were to turn them off? Like in the previous question you answered for me, we set $username as global, would that change? I heard register_globals is insecure, but when I turned it off, I had trouble passing form vars from one page to the next...any general thoughts?
Thanks again for help in this question :)
But how would my code change if I were to turn them off? Like in the previous question you answered for me, we set $username as global, would that change? I heard register_globals is insecure, but when I turned it off, I had trouble passing form vars from one page to the next...any general thoughts?
Thanks again for help in this question :)
I would turn it off. In previous script, it won't change. By declaring $username global, you don't need to change anything when you turn register_globals off.
What does need to change when you turn it off is the practice of use $username directly where one should be using $_POST['username'] for example. That's because register_globals, when on, would automatically produce variable $username for $_POST['username'] in current scope, and it of course also would produces a lot of other variables in the same scope for $_GET, $_FILES, $_SERVER, ... it's always a danger that you might get two same-named variables overwriting one or the other (or get one of your normal variables overwriting these auto-produced variables of the same name). So it's recommended that you turn register_globals off, and use $_POST, etc. instead. In your case, in last question I answered for you, the code is already $username = $_POST['username'], not using $username directly, so you don't need to change your code.
You also do not need to declare $_POST global even after turning the register_globals off, since these arrays are so-called superglobals, and they'll be global with or without register_globals.
BTW, I forgot to mention that if you don't want to use global $username; you can use $GLOBALS['username'] directly without declaration since $GLOBALS is superglobal. But I usually go the way of declaring global since you can declare a lot of variables this way on one line and do not have to type the cumbersome $GLOBALS['username'] every time you use $username.
What does need to change when you turn it off is the practice of use $username directly where one should be using $_POST['username'] for example. That's because register_globals, when on, would automatically produce variable $username for $_POST['username'] in current scope, and it of course also would produces a lot of other variables in the same scope for $_GET, $_FILES, $_SERVER, ... it's always a danger that you might get two same-named variables overwriting one or the other (or get one of your normal variables overwriting these auto-produced variables of the same name). So it's recommended that you turn register_globals off, and use $_POST, etc. instead. In your case, in last question I answered for you, the code is already $username = $_POST['username'], not using $username directly, so you don't need to change your code.
You also do not need to declare $_POST global even after turning the register_globals off, since these arrays are so-called superglobals, and they'll be global with or without register_globals.
BTW, I forgot to mention that if you don't want to use global $username; you can use $GLOBALS['username'] directly without declaration since $GLOBALS is superglobal. But I usually go the way of declaring global since you can declare a lot of variables this way on one line and do not have to type the cumbersome $GLOBALS['username'] every time you use $username.
ASKER
Ok so in my mysql_query() statements, where I use the forms names such as
INSERT INTO table (field) VALUES ($fieldname)
would have to be
INSET INTO table (field) VALUES ($_POST[fieldname])
?
Thanks in advance
INSERT INTO table (field) VALUES ($fieldname)
would have to be
INSET INTO table (field) VALUES ($_POST[fieldname])
?
Thanks in advance
ASKER
$filetype = $_POST['filetype'];
$_SESSION['search_filetype
$filetype = $_SESSION['search_filetype
Like there, I'm trying to set the $filetype variable they want to search for as a session variable so they can go back and forth between the forms, but it does not seem to register?
$_SESSION['search_filetype
$filetype = $_SESSION['search_filetype
Like there, I'm trying to set the $filetype variable they want to search for as a session variable so they can go back and forth between the forms, but it does not seem to register?
Yes, you should use $_POST['fieldname']. As to your 2nd question, I don't quite understand why you have 3 statements there. But anyway here's what'll happen:
After $filetype = $_POST['filetype'], in the same function or section of php code, $filetype will have the value of filetype. In other sections or functions you'd have to declare global $filetype before you can access the value of filetype.
After $_SESSION['search_filetype
In another php script, AFTER you had called session_start(); you can say $filetype = $_SESSION['search_filetype
As long as you understand these rules, you should be able to figure out why your access of the variable failed in another script. If you still have problem, please post the actual code, otherwise it's too hard to diagnose.
After $filetype = $_POST['filetype'], in the same function or section of php code, $filetype will have the value of filetype. In other sections or functions you'd have to declare global $filetype before you can access the value of filetype.
After $_SESSION['search_filetype
In another php script, AFTER you had called session_start(); you can say $filetype = $_SESSION['search_filetype
As long as you understand these rules, you should be able to figure out why your access of the variable failed in another script. If you still have problem, please post the actual code, otherwise it's too hard to diagnose.