HTML form values submit to PHP and query

Question

I'm wondering if anyone has a spare set of eyes for a moment or two. The problem I see lies with my issets. It is ignoring the first initial isset($lastName) and seems to always choose isset($title). Also I should mention the values are being posted from a HTML form to ssearch against a surname or title in a DB.

My question is, can anyone help with the logic problem or is there another way of doing so ? I know its prone to SQL injection too, however it's on a local DB and I'll be looking into this closely quite soon. If anyone can make suggestions I am definitely interested to hear them.

As per suggestion the HTML form.

<form name="lookup" method="post" action="searchEmployeeList.php" autocomplete="off">
<fieldset>
    <p>Conduct a search</p>
    <table width="600">
        <tr>
            <td width="150">Surname:</td>
            <td>
                <input type="text" name="lastName" value="" maxlength="25" placeholder="Employees surname">
            </td>
        </tr>
        <tr>
            <td width="150">Title:</td>
            <td>
                <input type="text" name="title" value="" maxlength="25" placeholder="Job role">
            </td>
        </tr>
        <tr>
            <td></td>
            <!--Blank row-->
            <td>
                <input type="submit" name="submit" value="Search now">

                <input type="submit" name="show_all" value="Show all">
            </td>
        </tr>
    </table>
</fieldset>
</form>

 if (isset($_POST['lastName'])) {

 include 'DBConDetails.php';

 if (isset($lastName)) {

     $sql = "SELECT * FROM employee_data Where last_name = '".$_POST['lastName'].
     "'";
 }
 if (isset($_POST['title'])) {

     $sql = "SELECT * FROM employee_data Where title = '".$_POST['title'].
     "'";
 }

 $result = mysqli_query($con, $sql);

 if ($result - > num_rows > 0) {

     echo "<table id = 'searchResults'> < tr >
         < td > ID < /td> < td > Name < /td> < td > Age < /td><td>Title</td > < td > Years of Service < /td> < td > Salary < /td> < /tr>";

     //multiple echos plainly for readability
     while ($row = $result - > fetch_assoc()) {
       echo '<tr>';
       echo '<td>' . $row["employee_id"] . '</td>';
       echo '<td>' . $row["first_name"] . ' ' . $row["last_name"] . '</td>';
       echo '<td>' . $row["age"] . '</td>';
       echo '<td>' . $row["title"] . '</td>';
       echo '<td>' . $row["yos"] . '</td>';
       echo '<td>' . $row["salary"] . '</td>';
       echo '</tr>';

     }
 } else {

     echo "I'm afraid we could not find any matches, try editing your criteria.";
 }
 echo "</table>";


}

If anyone should come across a similar problem in the future, the issue lay with both if statements being true, which lead to both executing and the latter overwriting the first if() statement. Should have noticed that !

Answer 1

I think you need to be checking:

isset($_POST['lastName']); 

instead of

isset($lastName);

As the latter would be set, but be equal to some undefined value (Maybe empty string?). The same applies to your title variable.

Answer 2

First, I think it is better to check on if ($_SERVER['REQUEST_METHOD'] == 'POST')

See: $_POST vs. $_SERVER['REQUEST_METHOD'] == 'POST'

After that, you can check the $_POST variables with isset and maybe it can be helpful to check the variables with strlen too. When you use strlen, with trim you can remove the unnecessary spaces.

Example:

<?php
if($_SERVER['REQUEST_METHOD'] == "POST") {

    if(isset($_POST['lastName']) && strlen(trim($_POST['lastName'])) > 0) {
        // Do some action
    }
}
?>

Answer 3

Firstly, you're overwriting your first query/conditional statement.

What you're telling it to do is, "if this is set, do this. If this is set, then do that".

What I feel you're probably looking to do is to check if both are set then passing those variables in your query in one go.

Such as and using !empty() instead of isset():

if (!empty($_POST['lastName']) && !empty($_POST['title']) ) {

    $lastName = $_POST['lastName'];
    $title = $_POST['title'];

    $sql = "SELECT * FROM employee_data 
            Where last_name = '" . $lastName . "' 
            AND title = '" . $title . "'";
}

If the goal here is to check for one OR the other (which looks to be the case), you can modify the above code to, and using the OR operator:

if (!empty($lastName) || !empty($title) ) {

    $lastName = $_POST['lastName'];
    $title = $_POST['title'];

    $sql = "SELECT * FROM employee_data 
            Where last_name = '" . $lastName . "' 
            OR title = '" . $title . "'";
}

and getting rid of:

if (isset($title)) {

    $sql = "SELECT * FROM employee_data Where title = '" . $title . "'";
}

Sidenote: The OR - || operator can also be used in a PHP conditional statement. && is AND; choose the one that is fitting for what you wish to achieve.

If my above throws you an error because of a character that MySQL is complaining about, such as apostrophes, then you will need to escape your data with mysqli_real_escape_string().

Sidenote: If you're going to use the above, make sure you're connected to your database first, and placing include 'DBConDetails.php'; as your first line above anything. It's usually good to be connected first.

Plus, your code is prone to an SQL injection. Use a prepared statement:

• https://en.wikipedia.org/wiki/Prepared_statement

Plus, there is no opening PHP tag before if (isset($_POST['lastName'])) { statement; if that is your actual code.

It should read as

<?php 
    if (isset($_POST['lastName'])) {
...

---

Footnotes:

DBConDetails.php is unknown as to which MySQL API you're using. You need to use the same API from connection to querying. Different MySQL APIs do not intermix.

• Consult Can I mix MySQL APIs in PHP?

Check for errors against your query also:

• http://php.net/manual/en/mysqli.error.php

---

If you want to seperate your submit buttons' actions

<input type="submit" name="submit" value="Search now">
<input type="submit" name="show_all" value="Show all">

You will need to use seperate conditional statements and using the related name attribute.

I.e.:

if (isset($_POST['submit'])) {
// do something, as in include other conditionals, code etc.
}

and

if (isset($_POST['show_all'])) {
// do something, as in include other conditionals, code etc.
}