How to make search form where user has three columns to search.Using PHP AND SQL AND HTML

Question

I was wondering how to make a search form where user has 3 options to search with

Search By age (dropdown 18-25 & 26-40)
Search By gender (male or female)
Search By name

In my code, when I click "Submit" with blank fields, it's throwing all data which i don't it to:

<?php
$output = NULL;

if (isset ( $_POST ['submit'] )) {
    // Connect to database
    $mysqli = new Mysqli ( "localhost", "root", "2222", "matrimonialPortal" );

    $search = $mysqli->real_escape_string ( $_POST ['search'] );

    // Query the databse
    $resultSet = $mysqli->query ( "SELECT * FROM mp_user WHERE name LIKE '%$search%' OR email LIKE '%$search%' OR salutation LIKE '%$search%' OR id LIKE '%$search%'" );

    if ($resultSet->num_rows > 0) {
        while ( $rows = $resultSet->fetch_assoc () ) {
            $name = $rows ['name'];
            $email = $rows ['email'];


            $output .= "::<strong>The Details of your search</strong> ::<br /> Name: $name<br /> Email:$email<br /><br /> ";
        }
    } else {
        $output = "Oops No results Found!!";
    }
}
?>

<!-- The HTML PART -->
<form method="POST">
    <div>
        <p>
            Search By name: <input type="TEXT" name="search" /> <input
                type="SUBMIT" name="submit" value="Search >>" />
        </p>
    </div>

    <div>Search By Age :
        <select name="age">
            <option></option>
            <option value="18-20">18-20</option>
            <option value="20-25">20-25</option>
        </select><input type="SUBMIT" name="submit" value="Search >>" />
    </div>
    <br />
    <div>
        Search By Gender: 
        <select name="salutation">
            <option></option>
            <option value="0">--- Male ---</option>
            <option value="1">--- Female ---</option>
        </select> <input type="SUBMIT" name="submit" value="Search >>" />
    </div>
    <br> <br>
</form>
<?php echo $output; ?>

What do you mean by "throwing all data" can you please clarify your issue? — ajshort, Nov 13 '15 at 11:07
When you submit blank fields, the script returns all records because your MySQL query - all variables will be essentially blank spaces and therefore not filter on anything. You can either add a Limit to the rows returned in your query or add a condition that executes a different query or not at all. — Yavor, Nov 13 '15 at 11:37

Aamir Sarwar · Answer 1 · 2015-11-13T12:26:10.247

You can replace your code

if ($resultSet->num_rows > 0) {

with this

if ($resultSet->num_rows > 0 and trim($search) != "") {

so it will not show all results if your input box is empty hope this will help you

Edit

here is an example you can get idea

$qry = "SELECT * FROM test WHERE 1=1";

if($purpose!="")
  $qry .= " AND purpose='$purpose'";

if($location!="")
  $qry .= " AND location='$location'";

if($type!="")
  $qry .= " AND type='$type'";

and for age

if ($age!='') {
  $qry .= " AND age between ".str_replace('-',' and ',$age);
}

score 0 · Answer 2 · answered Nov 13 '15 at 12:17

Well i cant give you the whole code, but here are the few solutions..

Use 3 different forms with 3 different submit buttons.
Use radio buttons on html form, and make a check on PHP side and perform operations depending upon what or which radio is selected.
Use a button instead of submit, radio buttons, hidden fields, and pass data to different php page on form submit (this can be lengthy).

Well you have options.

score 0 · Answer 3 · edited May 23 '17 at 11:59

When you POST a blank variable and Query with %$search% and 'OR' other criteria, sql matches all records with space in column Name ! So you will have to use some variation of;

If(empty($POST['search']){ ['Query witbout Name parameter']} else{['Query with Name parameter']}

As for converting DOB to match age range. You will have to use

SELECT TIMESTAMPDIFF

answered here

calculate age based on date of birth

Laptop Medico · Accepted Answer · 2015-11-15T05:00:43.700

It seems like you are new to PHP. Here is a solution for you.

First HTML PART. Here use "action" which means that the page will locate the file and process data. For example action="search_process.php". But if you are processing the data from the same page use $_SERVER['PHP_SELF'];

       <!-- The HTML PART -->
        <form method="POST" action="$_SERVER['PHP_SELF']"> // here it will load the self page
            <div>
                <p>
    Search By name: <input type="text" name="search_name" />
    Search By age: <input type="text" name="search_age" />
    Search By gender: <input type="TEXT" name="search_gender" /> 
<input type="submit" name="submit_name" value="Search >>" />
                </p>
            </div>

Now the PHP part:

    <?php 

           if(isset($_POST['submit_name'])
           {
             //What happens after you submit? We will now take all the values you submit in variables

              $name = (!empty($_POST['search_name']))?mysql_real_escape_string($_POST['search_name']):null; //NOTE: DO NOT JUST USE $name = $_POST['search_name'] as it will give undefined index error (though your data will be processed) and will also be open to SQL injections. To avoid SQL injections user mysql_real_escape_string.
              $age = (!empty($_POST['search_age']))?mysql_real_escape_string($_POST['search_age']):null;
              $gender = (!empty($_POST['search_gender']))?mysql_real_escape_string($_POST['search_gender']):null;

           //Now we will match these values with the data in the database

        $abc = "SELECT * FROM table_name WHERE field_name LIKE '".$name."' or field_gender LIKE '".$gender."' or field_age LIKE '".$age."'"; // USE "or" IF YOU WANT TO GET SEARCH RESULT IF ANY OF THE THREE FIELD MATCHES. IF YOU WANT TO GET SEARCH RESULT ONLY WHEN ALL THE FIELD MATCHES THEN REPLACE "or" with "and"
        $def = mysql_query($abc) or die(mysql_error())// always use "or die(mysql_error())". This will return any error that your script may encounter

   //NOW THAT WE HAVE GOT THE VALUES AND SEARCHED THEM WE WILL NOW SHOW THE RESULT IN A TABLE
        ?>    
        <table cellspacing="0" cellpadding="0" border"0">
          <tr>
            <th>Name</th>
            <th>Age</th>
            <th>Gender</th>
          </tr>
        <?php while($row = mysql_fetch_array($def)) {  // I HAD MISSED OUT A WHILE LOOP HERE. SO I AM EDITING IT HERE. YOU NEED TO USE A WHILE LOOP TO DISPLAY THE DATA THAT YOU GOT AFTER SEARCHING.
          <tr>
            <td><?php echo $row[field_name]; ?></td>
            <td><?php echo $row[field_age]; ?></td>
            <td><?php echo $row[field_gender]; ?></td>
          </tr>
        <?php } ?>
        </table>
    <?php } ?>

A perfect solution for your query. All the best.

this solution is in mysql_ but you can convert it to mysqli_ if you want to. also that I have displayed the result in a table but you can display it any way you want. — Laptop Medico, Nov 13 '15 at 13:39
,You guessed it right i am pretty new to php and would like to thank you for the code it helped me alot to write a good code as well learned few things.I would be gratefull if you could help to add the code to calculate the age with the 'dob' column where the user's date of birth is stored.I tried couple code of codes i could'nt make it.All i want is to calculate the age and store it and when the user tries to search i.e(Male of age around 18 to 25 ).then it should display the appropriate registered users. — Syed Tanhel, Nov 13 '15 at 17:37
Hey first of all its good to hear that you learned something. Now 2nd, I have edited the table part in the answer as I had missed out a WHILE LOOP there that is necessary. — Laptop Medico, Nov 15 '15 at 05:02
I can help u calculate the age too but later.. I m in a hurry at this moment.. Also it will be good if you take
instead of
whenever you want to execute a search. — Laptop Medico, Nov 15 '15 at 05:10
also please accept the answer and give a plus 1 if it helped u :) — Laptop Medico, Nov 15 '15 at 05:11

How to make search form where user has three columns to search.Using PHP AND SQL AND HTML

4 Answers4