LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 03-08-2008, 10:15 PM   #1
win32sux
Guru
 
Registered: Jul 2003
Location: Los Angeles
Distribution: Ubuntu
Posts: 9,870

Rep: Reputation: 371Reputation: 371Reputation: 371Reputation: 371
Question PHP - How to properly handle GET method input in a MySQL query?


I've got a simple HTML page being generated showing all the rows in a MySQL table. I've added a drop-down menu (and a submit button) to the top of the page so that the user can filter the results being shown on the page. The drop-down is part of a form which uses the GET method. The database has two tables, Students, and Schools.

The Students table looks like:
Code:
+-----------+--------+---------+
| StudentID | School | Student |
+-----------+--------+---------+
| 1         | 2      | Seymour |
| 2         | 2      | Michael |
| 3         | 1      | Nikita  |
+-----------+--------+---------+
The Schools table looks like:
Code:
+----------+-----------+
| SchoolID | School    |
+----------+-----------+
| 1        | Harvard   |
| 2        | Princeton |
| 3        | Yale      |
+----------+-----------+
The query which I use to show all the results (no filtering) looks like:
Code:
$result = mysql_query("SELECT * FROM Students");
while($row = mysql_fetch_array($result))
  {
    // Miscellaneous stuff is done here.
  }
So to, for example, only show students which are in Harvard (only Nikita), I would do a:
Code:
$result = mysql_query("SELECT * FROM Students WHERE School = '1'");
while($row = mysql_fetch_array($result))
  {
    // Miscellaneous stuff is done here.
  }
So I managed to get the drop-down menu filter to work by doing something like:
Code:
$FilterSchool = $_GET['schoolid'];
if (empty($FilterSchool))
  {
    $FilterSchool = "School";
  }

$result = mysql_query("SELECT * FROM Students WHERE School = $FilterSchool");
while($row = mysql_fetch_array($result))
  {
    // Miscellaneous stuff is done here.
  }
So if a schoolid value was passed via GET by the drop-down menu form, it's plugged into the MySQL query. If one wasn't passed, then the query would have a "WHERE School = School", which shows all the rows. This seems to work fine, but I have a feeling it's the wrong way to do it (I'm a PHP/MySQL newbie). I would really appreciate it if someone could let me know whether my technique is completely insane or not, and what would be a more proper way. Thanks in advance!

Last edited by win32sux; 03-08-2008 at 10:23 PM.
 
Old 03-09-2008, 08:19 AM   #2
Mike_W
LQ Newbie
 
Registered: Dec 2007
Posts: 12

Rep: Reputation: 0
I'd probably do it something like this: (no guarantees that it's any better!)

Code:
$FilterSchool = $_GET['schoolid'];
$sql = "SELECT * FROM Students"
if (!empty($FilterSchool))
  {
    $sql .= ' WHERE `School`=\'' . $FilterSchool' . '\'';
  }

$result = mysql_query($sql);
while($row = mysql_fetch_array($result))
  {
    // Miscellaneous stuff is done here.
  }
Incidentally, I think you should be escaping any variables you're sticking into SQL commands. (See http://uk.php.net/mysql_real_escape_string, and beware of magic quotes!)
 
Old 03-09-2008, 11:28 AM   #3
j-ray
Senior Member
 
Registered: Jan 2002
Location: germany
Distribution: ubuntu
Posts: 1,419

Rep: Reputation: 99
mysql will not like being asked
where school='school'
if school should be an integer value. I guess you better asign an int value or write sth like
where school>0
if no corresponding get-value is found...
 
Old 03-10-2008, 09:56 AM   #4
Mike_W
LQ Newbie
 
Registered: Dec 2007
Posts: 12

Rep: Reputation: 0
Quote:
Originally Posted by j-ray View Post
mysql will not like being asked
where school='school'
if school should be an integer value. I guess you better asign an int value or write sth like
where school>0
if no corresponding get-value is found...
If I've understood the code correctly, the actual output would be

Code:
...where School = School
which (I assume) always evaluates to true. Of course, not having quotation marks leads to other problems.
 
Old 03-10-2008, 10:40 AM   #5
j-ray
Senior Member
 
Registered: Jan 2002
Location: germany
Distribution: ubuntu
Posts: 1,419

Rep: Reputation: 99
where School = School
will lead to a mysql error
where School = 'School'
will lead to an empty result set (if it doesnt die -> depending on the connecting function/class)
anyway this condition makes no sense
 
Old 03-10-2008, 02:24 PM   #6
Mike_W
LQ Newbie
 
Registered: Dec 2007
Posts: 12

Rep: Reputation: 0
Quote:
Originally Posted by j-ray View Post
where School = School
will lead to a mysql error
Just tried something similar on my machine, and it works just fine i.e. returns all rows. Presumably, it's actually checking on each row whether the School field is the same as... the School field. Not exactly useful SQL in this instance, but might be useful if you want to compare two different fields.
 
Old 03-11-2008, 02:13 AM   #7
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,261

Rep: Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028
Think that evaluates to 'true' eg like 1=1.
Best practice is to stick to a value type that matches the column type though.
 
Old 03-11-2008, 04:32 AM   #8
win32sux
Guru
 
Registered: Jul 2003
Location: Los Angeles
Distribution: Ubuntu
Posts: 9,870

Original Poster
Rep: Reputation: 371Reputation: 371Reputation: 371Reputation: 371
Thanks for the input guys. I'll be posting an update on my progress regarding this question soon. The reason I'm using "WHERE School = School" is because I couldn't find how to do it with a wildcard. So if I have, say, two drop-down menu filters, and the user makes a selection on only one of them, I'd need for the other to show all rows.
 
Old 03-13-2008, 01:18 PM   #9
win32sux
Guru
 
Registered: Jul 2003
Location: Los Angeles
Distribution: Ubuntu
Posts: 9,870

Original Poster
Rep: Reputation: 371Reputation: 371Reputation: 371Reputation: 371
Sorry about the delay, I ran into a brick wall on some other sections of my code - nothing unexpected for a newbie like myself - so it's all good. I'm now back to this section and made some progress. Okay, the "Student = Student" technique turned-out to be kind of a nightmare for me, especially when it came time to add more filters. The nightmare mainly revolved around the handling of possibly NULL values and stuff like that. After lots of experimenting, the best way I've found so far has been to have variables represent entire "AND" sections of the query, instead of just the lookup values.

Here's an example of what this part of my code currently looks like at the moment:


Students:
Code:
+-----------+---------+--------+-----+-------------+
| StudentID | Student | School | Sex | Nationality |
+-----------+---------+--------+-----+-------------+
| 1         | Seymour | 2      | 1   | 3           |
| 2         | Michael | 2      | 1   | 2           |
| 3         | Nikita  | 1      | 2   | 1           |
+-----------+---------+--------+-----+-------------+

Schools:
Code:
+----------+-----------+
| SchoolID | School    |
+----------+-----------+
| 1        | Harvard   |
| 2        | Princeton |
| 3        | Yale      |
+----------+-----------+

Sexes:
Code:
+-------+--------+
| SexID | Sex    |
+-------+--------+
| 1     | Male   |
| 2     | Female |
+-------+--------+

Nationalities:
Code:
+---------------+---------------+
| NationalityID | Nationality   |
+---------------+---------------+
| 1             | Australia     |
| 2             | France        |
| 3             | United States |
+---------------+---------------+

PHP Code:
if (!empty($_GET['schoolid']))
  {
    
$FilterSchool $_GET['schoolid'];
    
$FilterSchoolQuery "AND School =" $FilterSchool;
    
$FilterSet TRUE;
  }

if (!empty(
$_GET['sexid']))
  {
    
$FilterSex $_GET['sexid'];
    
$FilterSexQuery "AND Sex =" $FilterSex;
    
$FilterSet TRUE;
  }

if (!empty(
$_GET['nationalityid']))
  {
    
$FilterNationality $_GET['nationalityid'];
    
$FilterNationalityQuery "AND Nationality =" $FilterNationality;
    
$FilterSet TRUE;
  } 
PHP Code:
if ($FilterSet)
  {
    
$result mysql_query("SELECT * FROM Students
    WHERE StudentID > 0
    $FilterSchoolQuery
    $FilterSexQuery
    $FilterNationalityQuery
    ORDER BY Student"
);
  }
else
  {
    
$result mysql_query("SELECT * FROM Students ORDER BY Student");
  }
while(
$row mysql_fetch_array($result))
  {
    
// Miscellaneous stuff is done here.
  


The "WHERE StudentID > 0" line is just there so that I wouldn't have to code a way for the first variable to use WHERE instead of AND. Regarding the GET input, I will of course need to add some lines to make sure it's sane/safe. I don't think I'll need to use mysql_real_escape_string() because, as you can tell from above, the values sent by my drop-down menu options should be strictly numerical. So I'll probably use is_numeric() along with something else to make sure only numbers (and only valid ones) are accepted for use as part of the query. The example above is working great for me so far, in that any combination of the three drop-down menu filters works just as you would expect it to. As always, any feedback you could provide is greatly appreciated.

Last edited by win32sux; 03-14-2008 at 01:23 AM. Reason: Fixed a typo.
 
Old 03-13-2008, 06:43 PM   #10
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Lenny/Squeeze/Wheezy/Sid
Posts: 4,062

Rep: Reputation: 491Reputation: 491Reputation: 491Reputation: 491Reputation: 491
Very clean code and table setup.

If you have a drop down box in your HTML and a filter is NOT set (say FilterSchool is not set) what does the drop down box show you? Do you leave it empty?

If you create the drop down boxes in HTML, do you query the appropriate table to get the names to display and the associated numbers? I hope you do, because if you hard code them, your code becomes very rigid.

Where I run into these cases, I add an additional drop down box item with (say) value 0. So if the drop down box shows 'any' (which is what you want because you don't want to filter on schools) the GET will return 0 and not empty. It is obvious how to exclude the additional criteria.

If I use text input fields to search in, I do like you do: empty is no criteria, and that is what the user expects.

jlinkels
 
Old 03-14-2008, 01:17 AM   #11
win32sux
Guru
 
Registered: Jul 2003
Location: Los Angeles
Distribution: Ubuntu
Posts: 9,870

Original Poster
Rep: Reputation: 371Reputation: 371Reputation: 371Reputation: 371
Quote:
Originally Posted by jlinkels View Post
Very clean code and table setup.
Awesome, thanks!

Quote:
If you have a drop down box in your HTML and a filter is NOT set (say FilterSchool is not set) what does the drop down box show you? Do you leave it empty?
The filter drop-downs all have a default value with their name set. For example, the school filter displays "School Filter" by default. The defaults have a value of 0.

Quote:
If you create the drop down boxes in HTML, do you query the appropriate table to get the names to display and the associated numbers? I hope you do, because if you hard code them, your code becomes very rigid.
Yeah, definitely. The drop-downs are populated by a PHP function. It queries the non-Student tables and then uses the results to populate the menus. I didn't post that PHP code as I felt it was off-topic, though. But here's an example of what the generated HTML drop-down menus look like:
Code:
<select name="schoolid">
<option selected="selected" value="0">School Filter</option>
<option value="1">Harvard</option>
<option value="2">Princeton</option>
<option value="3">Yale</option>
</select>
Quote:
Where I run into these cases, I add an additional drop down box item with (say) value 0. So if the drop down box shows 'any' (which is what you want because you don't want to filter on schools) the GET will return 0 and not empty. It is obvious how to exclude the additional criteria.

If I use text input fields to search in, I do like you do: empty is no criteria, and that is what the user expects.
Yeah, sounds like we're on the same page. If nothing is selected from a filter, I want it to behave as though that filter shouldn't be filtering at all. And that's precisely how the above code has been performing on my tests so far. I do have a small (and sort of cosmetic) feature to implement into this, though: Right now after the submit button is hit, and the result page is loaded, the filters are all back to their default selection (the titles). I'm gonna change things so that they instead remain with their last search criteria as "selected", so that way the user can easily see what the results he's looking at were caused by, without having to look at the GETs in the URL.
 
Old 03-14-2008, 03:55 AM   #12
j-ray
Senior Member
 
Registered: Jan 2002
Location: germany
Distribution: ubuntu
Posts: 1,419

Rep: Reputation: 99
session_start();
if($_GET['schoolid']){
$_SESSION['schoolid']=$_GET['schoolid'];
}
$FilterSchool=$_SESSION['schoolid'];
if (!empty($FilterSchool))
{
$FilterSchoolQuery = "AND School =" . $FilterSchool;
$FilterSet = TRUE;
}

Last edited by j-ray; 03-14-2008 at 03:59 AM.
 
Old 03-14-2008, 04:08 AM   #13
win32sux
Guru
 
Registered: Jul 2003
Location: Los Angeles
Distribution: Ubuntu
Posts: 9,870

Original Poster
Rep: Reputation: 371Reputation: 371Reputation: 371Reputation: 371
Quote:
Originally Posted by win32sux View Post
I don't think I'll need to use mysql_real_escape_string() because, as you can tell from above, the values sent by my drop-down menu options should be strictly numerical. So I'll probably use is_numeric() along with something else to make sure only numbers (and only valid ones) are accepted for use as part of the query.
I went ahead and patched this part, since I don't want to forget about it.

It now looks like this:
PHP Code:
$FilterSchool       sanitize_filter_input($_GET['schoolid']);
$FilterSex          sanitize_filter_input($_GET['sexid']);
$FilterNationality  sanitize_filter_input($_GET['nationalityid']);

if (!empty(
$FilterSchool))
  {
    
$FilterSchoolQuery "AND School =" $FilterSchool;
    
$FilterSet TRUE;
  }

if (!empty(
$FilterSex))
  {
    
$FilterSexQuery "AND Sex =" $FilterSex;
    
$FilterSet TRUE;
  }

if (!empty(
$FilterNationality))
  {
    
$FilterNationalityQuery "AND Nationality =" $FilterNationality;
    
$FilterSet TRUE;
  } 
PHP Code:
function sanitize_filter_input($value)
  {
    if (!
is_numeric($value) || $value 99)
      {
        return 
0;
      }
    else
      {
        return 
$value;
      }
  } 
The "99" is hard coded for now, but I'm sure I'll modify it in the future so that it automatically becomes the highest primary key value for table corresponding to whatever filter is being used. Or better yet, I'll change the condition so that it only matches against existing primary keys. Anyhow, it's something I will definitely address at a later time. I think this extremely basic sanitization method should suffice for now. Would you agree? I'm wondering whether I should somehow pre-screen the GETs before running them through sanitize_filter_input(). Perhaps I should do the magic quotes checking thing to be safe - unless it's only necessary when the input will go directly to a database query. Hmmm. I've got some reading to do.

Last edited by win32sux; 03-14-2008 at 04:28 AM.
 
Old 03-14-2008, 04:15 AM   #14
win32sux
Guru
 
Registered: Jul 2003
Location: Los Angeles
Distribution: Ubuntu
Posts: 9,870

Original Poster
Rep: Reputation: 371Reputation: 371Reputation: 371Reputation: 371
Quote:
Originally Posted by j-ray View Post
Code:
session_start();
if($_GET['schoolid']){
 $_SESSION['schoolid']=$_GET['schoolid'];
}
$FilterSchool=$_SESSION['schoolid'];
if (!empty($FilterSchool))
  {
    $FilterSchoolQuery = "AND School =" . $FilterSchool;
    $FilterSet = TRUE;
  }
Thanks for the input. If you could elaborate a bit as to what the purpose of this code you've posted is it would be greatly appreciated. In the meantime, I shall Google about this session stuff to have an idea of what you are suggesting here.
 
Old 03-14-2008, 05:22 AM   #15
win32sux
Guru
 
Registered: Jul 2003
Location: Los Angeles
Distribution: Ubuntu
Posts: 9,870

Original Poster
Rep: Reputation: 371Reputation: 371Reputation: 371Reputation: 371
Quote:
Originally Posted by win32sux View Post
Perhaps I should do the magic quotes checking thing to be safe - unless it's only necessary when the input will go directly to a database query. Hmmm. I've got some reading to do.
Okay, everything I read says that addslashes() is done on all GETs by default (since magic_quotes_gpc is enabled by default). So I've opted for running it on the GETs only if my script is being run on a server without it enabled, just in case. I've patched the above to look like this:
PHP Code:
if (!get_magic_quotes_gpc())
  {
    
$FilterSchool      addslashes($_GET['schoolid']);
    
$FilterSex         addslashes($_GET['sexid']);
    
$FilterNationality addslashes($_GET['nationalityid']);
  }
else
  {
    
$FilterSchool      $_GET['schoolid'];
    
$FilterSex         $_GET['sexid'];
    
$FilterNationality $_GET['nationalityid'];
  }

$FilterSchool       sanitize_filter_input($FilterSchool);
$FilterSex          sanitize_filter_input($FilterSex);
$FilterNationality  sanitize_filter_input($FilterNationality);

if (!empty(
$FilterSchool))
  {
    
$FilterSchoolQuery "AND School =" $FilterSchool;
    
$FilterSet TRUE;
  }

if (!empty(
$FilterSex))
  {
    
$FilterSexQuery "AND Sex =" $FilterSex;
    
$FilterSet TRUE;
  }

if (!empty(
$FilterNationality))
  {
    
$FilterNationalityQuery "AND Nationality =" $FilterNationality;
    
$FilterSet TRUE;
  } 

Last edited by win32sux; 03-14-2008 at 05:23 AM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with comparing user input to result of a query php orfiyus Programming 3 07-10-2007 11:08 PM
query mysql using PHP for Persian mohtasham1983 Programming 2 07-08-2007 09:56 PM
php/,mysql problem: can't query JJX Linux - General 4 01-06-2005 05:10 PM
php: Why Can't I Query Mysql DB?? flamesrock Programming 7 11-16-2004 12:36 AM
PHP MySQL Query Question vi0lat0r Programming 1 07-15-2004 05:02 AM


All times are GMT -5. The time now is 07:08 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration