Get value range from MYSQL using PHP

Question

I'm trying to get all the ages (let's say 18-21) after searching ages 18-21 from the form, but the form is displaying all the data from the database. The username search works though. Any help appreciated.

Here's the form:

<?php
return'
<form id="searchForm" action="views/searchResults.php" method="GET">
    <input id="searchBox" placeholder="Search" type="text" name="username" />


<select id="age" name="age">
  <option value="0"> - </option>
  <option value="18-20">18-20</option>
  <option value="20-23">20-23</option>
  <option value="20">20</option>
  <option value="21">21</option>
  <option value="22">22</option>
</select>


<select id="gender" name="gender">
  <option name="nothing" value="0"> - </option>  
  <option name="female" value="female">Female</option>
  <option name="male" value="male">Male</option>
</select>
    <input id="searchButton" type="submit" value="Search" />

</form>';

?>

Here's the script for search:

<?php
    mysql_connect("127.0.0.1", "root", "") or die("Error connecting to database: ".mysql_error());
    /*
        localhost - it's location of the mysql server, usually localhost
        root - your username
        third is your password

        if connection fails it will stop loading the page and display an error
    */

    mysql_select_db("modul8b") or die(mysql_error());
    /* tutorial_search is the name of database we've created */


$username = $_GET['username']; 
$age = $_GET['age']; 
$male = $_GET['gender']; 
$female = $_GET['gender']; 
    // gets value sent over search form

    $min_length = 0;
    // you can set minimum length of the query if you want

    if(strlen($username) >= $min_length){ // if query length is more or equal minimum length then

        $username = htmlspecialchars($username); 
        // changes characters used in html to their equivalents, for example: < to &gt;

        $username = mysql_real_escape_string($username);
        // makes sure nobody uses SQL injection

// Username query does not need a like, you know what the username will be precisely
$raw_results = mysql_query("SELECT * FROM user WHERE (`username` = '".$username."')");

// You need to filter out an age range
$ages = "";
switch($age)
{
    case 0:
    case 20:
    case 21:
    case 22:
        $ages = $age;
        break;
    case "18-20":
        $ages = "18,19,20";
        break;
    case "20-23":
        $ages = "20,21,22,23";
        break;
}
// combine with ages to get all relevant results
//$raw_results = mysql_query("SELECT * FROM user WHERE (`age` in (" . $ages . ") AND gender = '" . $gender . "')");
        if(mysql_num_rows($raw_results) > 0){ // if one or more rows are returned do following



            while($results = mysql_fetch_array($raw_results)){
            // $results = mysql_fetch_array($raw_results) puts data from database into array, while it's valid it does the loop

               echo "<table border='1'>
                <tr>
                <th>username</th>
                <th>gender</th>
                <th>age</th>
                </tr>";
                    {
                    echo "<tr>";
                    echo "<td>" . $results['username'] . "</td>";
                    echo "<td>" . $results['gender'] . "</td>";
                    echo "<td>" . $results['age'] . "</td>";
                    echo "</tr>";
                    }
                  echo "</table>";


            }

        }
        else{ // if there is no matching rows do following
            echo "No results";
        }

    }
    else{ // if query length is less than minimum
        echo "Minimum length is ".$min_length;
    }

?>

And here's the table:

CREATE TABLE IF NOT EXISTS `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL,
  `email` varchar(64) NOT NULL,
  `password` varchar(32) NOT NULL,
  `age` int(11) NOT NULL,
  `gender` varchar(7) NOT NULL,
  `image` blob NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=24 ;

Any help appreciated.Thanks

Answer 1

Firstly, you are using the deprecated mysql_ functions, use PDO or mysqli. Also, your code as it stands is potentially open to SQL injection.

That aside, you are getting all the results due to the incorrect usage of mysql queries.

For example, a user might enter the following information.

No Username
18-20
Male

When submitted this will result in

// Username not set
@$age = '18-20'; 
@$male = 'male'; 
// Female not set

Which will equate to your final query looking something like

SELECT * FROM user WHERE (`gender` LIKE '%male%' OR `gender` LIKE '%%')

As the % is a wild card, this will match all records in database.

You need to validate your input and ensure you are running the correct queries.

Try something like

// Username query does not need a like, you know what the username will be precisely
$raw_results = mysql_query("SELECT * FROM user WHERE (`username` = '".$username."')");

// You need to filter out an age range
$ages = "";
switch($age)
{
    case 0:
    case 20:
    case 21:
    case 22:
        $ages = $age;
        break;
    case "18-20":
        $ages = "18,19,20";
        break;
    case "20-23":
        $ages = "20,21,22,23";
        break;
}
// combine with ages to get all relevant results
$raw_results = mysql_query("SELECT * FROM user WHERE (`age` in (" . $ages . ") AND gender = '" . $gender . "')");

Complete Code - Not Tested

<?php
    mysql_connect("127.0.0.1", "root", "") or die("Error connecting to database: ".mysql_error());
    /*
        localhost - it's location of the mysql server, usually localhost
        root - your username
        third is your password

        if connection fails it will stop loading the page and display an error
    */

    mysql_select_db("modul8b") or die(mysql_error());
    /* tutorial_search is the name of database we've created */


$username = $_GET['username']; 
$age = $_GET['age']; 
$male = $_GET['gender']; 
$female = $_GET['gender'];  
    // gets value sent over search form

    $min_length = 0;
    // you can set minimum length of the query if you want

    if(strlen($username) >= $min_length) { // if query length is more or equal minimum length then

        $username = htmlspecialchars($username); 
        // changes characters used in html to their equivalents, for example: < to &gt;

        $username = mysql_real_escape_string($username); 
        // makes sure nobody uses SQL injection

    // Username query does not need a like, you know what the username will be precisely
    $raw_results = mysql_query("SELECT * FROM user WHERE (`username` = '".$username."')");
    } else if (strlen($age) > 0 && (strlen($male) > 0 || strlen($female) > 0)) {

    // You need to filter out an age range
    $ages = "";
    switch($age)
    { 
        case 0:
        case 20:
        case 21:
        case 22:
            $ages = $age;
            break;
        case "18-20":
            $ages = "18,19,20";
            break;
        case "20-23":
        $ages = "20,21,22,23";
            break;
    }
    // combine with ages to get all relevant results
    //$raw_results = mysql_query("SELECT * FROM user WHERE (`age` in (" . $ages . ") AND gender = '" . $gender . "')");
    } else{ // if query length is less than minimum
        echo "Minimum length is ".$min_length;
    }

     if(isset($raw_results)) {
        if (mysql_num_rows($raw_results) > 0){ // if one or more rows are returned do following



            while($results = mysql_fetch_array($raw_results)){ 
            // $results = mysql_fetch_array($raw_results) puts data from database into array, while it's valid it does the loop

               echo "<table border='1'>
                <tr>
                <th>username</th>
                <th>gender</th>
                <th>age</th>
                </tr>";
                    {
                    echo "<tr>";
                    echo "<td>" . $results['username'] . "</td>";
                    echo "<td>" . $results['gender'] . "</td>";
                    echo "<td>" . $results['age'] . "</td>";
                    echo "</tr>";
                    }
                  echo "</table>";


            }

        }
        else{ // if there is no matching rows do following
            echo "No results";
        }
    }

?>