Solved

Multiple where clauses giving me trouble

Posted on 2003-11-04
15
247 Views
Last Modified: 2008-03-03
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 :)
0
Comment
Question by:drakkarnoir
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 5

Expert Comment

by:LornaJane
ID: 9679735
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
 

Author Comment

by:drakkarnoir
ID: 9679740
No that was just a typo in writing that out, it still won't work even with the ' '
0
 
LVL 5

Expert Comment

by:LornaJane
ID: 9679768
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
 
LVL 8

Expert Comment

by:inq123
ID: 9680365
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
 
LVL 7

Expert Comment

by:minnirok
ID: 9680493
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
 

Author Comment

by:drakkarnoir
ID: 9681958
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
 
LVL 8

Expert Comment

by:inq123
ID: 9683271
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:drakkarnoir
ID: 9683320
Hrmm, else if is valid in PHP. Same error still.
0
 
LVL 8

Accepted Solution

by:
inq123 earned 500 total points
ID: 9683325
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
 
LVL 8

Expert Comment

by:inq123
ID: 9683670
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
 

Author Comment

by:drakkarnoir
ID: 9683716
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
 
LVL 8

Expert Comment

by:inq123
ID: 9685371
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
 

Author Comment

by:drakkarnoir
ID: 9687768
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
 

Author Comment

by:drakkarnoir
ID: 9687882
$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
 
LVL 8

Expert Comment

by:inq123
ID: 9691160
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
This article discusses four methods for overlaying images in a container on a web page
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now