Solved

Multiple where clauses giving me trouble

Posted on 2003-11-04
15
259 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses how to create an extensible mechanism for linked drop downs.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

740 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