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("SELECT * FROM files where $def='$category' and approved='1'"));

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("SELECT * FROM files where $def='$category' $andstr $def2=$category2 and approved='1'"));

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 :)
drakkarnoirAsked:
Who is Participating?
 
inq123Connect With a Mentor Commented:
just found it's not the else if problem.  Please change that line to

elseif(preg_match('/,/', $category))

I just verified that after this change the $typestr was produced correctly using 'type1,type2' as a test string.  BTW both "else if" and "elseif" work.
0
 
LornaJaneCommented:
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?
0
 
drakkarnoirAuthor Commented:
No that was just a typo in writing that out, it still won't work even with the ' '
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LornaJaneCommented:
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!
0
 
inq123Commented:
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("SELECT * FROM files where $typestr and approved='1'"));
0
 
minnirokCommented:
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.
0
 
drakkarnoirAuthor Commented:
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")

0
 
inq123Commented:
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.
0
 
drakkarnoirAuthor Commented:
Hrmm, else if is valid in PHP. Same error still.
0
 
inq123Commented:
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).
0
 
drakkarnoirAuthor Commented:
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 :)
0
 
inq123Commented:
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.
0
 
drakkarnoirAuthor Commented:
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
0
 
drakkarnoirAuthor Commented:
$filetype = $_POST['filetype'];
      $_SESSION['search_filetype'] = $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?
0
 
inq123Commented:
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'] = $filetype; in the whole script, $_SESSION['search_filetype'] will have value of filetype.  You don't have to declare global anywhere to use $_SESSION['search_filetype'] and get value of filetype.

In another php script, AFTER you had called session_start(); you can say $filetype = $_SESSION['search_filetype']; and thus retrieve value of filetype from the session variable and store into $filetype.  But again, in other section of code or functions you'd have to declare global $filetype before you use it.

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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.