PHP form dumps entire MySQL database unless neither input variable exists in the database

I have a form that searches a MySQL database using PHP. Currently, when a user inputs a search into one of two fields, the entire contents of the database are displayed. Also, if the user leaves both fields blank, again, the entire contents of the database will be displayed.

However, if the user inputs random information into both of the fields, then the results page will be blank.

The assumed usage of this form is that the user can search for an article based on the article's title, the article's author or organization, or the article's title and its author or organization by either filling out one or both of the fields.

What I'm trying to figure out is:

Why the results page keeps displaying all of the database contents.

and

How to ensure that the database is actually being queried rather than just being dumped by a coding error.

Code follows below:

search.php:

<div class="content">
    <form id="form1" name="form1" method="post" action="searchdb.php">
        <table width="100%" border="0" cellpadding="6">
            <tr>
              <td width="29%" align="right">Article Title:</td>
              <td width="71%" align="left"><input name="articletitle" type="text" id="articletitle" size="50" /></td>
            </tr>
            <tr>
              <td align="right">Author or Organization:</td>
              <td align="left"><input name="articleorganization" type="text" id="articleorganization" size="50" /></td>
            </tr>
        </table>


        <table width="100%" border="0" cellpadding="6">
          <tr>
            <td><input type="submit" name="submit" value="Submit" /></td>
          </tr>
        </table>
    </form>
</div>

searchdb.php

<?php

include('settings.php');
$query = "select * from articles";
$where = array();
if (!empty($_POST['articletitle'])) {
   $where[] = "articletitle LIKE '%".mysql_real_escape_string($_POST['articletitle'])."%'";
}
    if (!empty($_POST['articleorganization'])) {
       $where[] = "articleorganization LIKE  '%".mysql_real_escape_string($_POST['articleorganization'])."%'";
}
    if (!empty($where)) {
        $query .= " WHERE " . implode(" OR ", $where);
        $sql = mysql_query($query);
    } else {
        // No results
}
while ($row = mysql_fetch_array($sql)){
    echo '<br/> Article Title: '.$row['articletitle'];
    echo '<br/> Article Organization: '.$row['articleorganization'];
    echo '<td><a href="edit.php?id=' . $row['id'] . '">Edit</a></td>';
    echo '<td><a href="delete.php?id=' . $row['id'] . '">Delete</a></td>';
    echo '<td><a href="entry.php?id=' . $row['id'] . '">View Full Entry</a></td>';
    echo '<br/><br/>';
}

?>

Answers


Tested this, and it should do exactly what you want.

$query = "select * from articles";
$where = array();
if (!empty($_POST['articletitle'])) {
   $where[] = "articletitle LIKE '%".mysql_real_escape_string($_POST['articletitle'])."%'";
}
if (!empty($_POST['articleorganization'])) {
   $where[] = "articleorganization LIKE '%".mysql_real_escape_string($_POST['articleorganization'])."%'";
}
if (!empty($where)) {
    $query .= " WHERE " . implode(" OR ", $where);
    $sql = mysql_query($query);
} else {
    // No results
}

EDIT It appears your form is passing empty values, so instead of checking isset, check !empty. I have updated the code above.


When both are blank, your query states:

WHERE field LIKE '%%'

which matches everything.

The same happens when either one is blank, because you are using an OR to join the where clauses.

You can prevent this from happening, by checking the inputs aren't blank:

<?php
if (!((empty($_POST['field1']) || empty($_POST['field2']))) {
  //run your query
}

Following on the post by @sberry.

if (isset($_POST['articletitle']) && $_POST['articletitle'] != "")

The variable can be set, but still be an empty string.

The method used by @xbonez is simpler as

if (!empty($_POST['articletitle'])) is the same as the above example that requires two tests

Have you tried xbonez method?

To be complete, this checks that at least one of the fields has been filled in:

if (!empty($_POST['articletitle']) || !empty($_POST['articleorganization'])) {
   $query = "SELECT * from `articles` WHERE ";

   $query .= "`articletitle` LIKE '%" . mysql_real_escape_string($_POST['articletitle']) . "%' ";

   $query .= "OR `articleorganization` LIKE '%" . mysql_real_escape_string($_POST['articleorganization']) . "%'";

    $sql = mysql_query($query);

} else {
    // No results
}

Things that will only be used if one of the fields is filled in like:

$query = "SELECT * from `articles` WHERE ";

are placed inside the the if() statement, otherwise they are being parsed unneccesarily.

No need to create an array and then convert it into a string. ".=" will concatenate the string fragments into the final query string.

Matters of personal preference: MySql keywords written in full caps, I find it makes the statements easier to read. There are numerous discussions about it. Search for "sql uppercase keywords style"

Using backticks around table and fieldnames: Allows the use of reserved keywords for table or fieldnames (count, case, default, div, index, key, limit, option, order, etc...). Reduces work for the mysql parser, it doesn't need to check whether there is a reserved word conflict. Avoids problems if your table or field name becomes a reserved keyword in the future.

Again, numerous discussions. Search for "mysql backtick"

MySQLdocumentation: 9.3. Reserved Words

9.2. Schema Object Names Look for "quoted identifier" on this page. Also, if you might be migrating to a different database app in the future , you could use double quotes instead of backticks, look for "ANSI_QUOTES".

9.2.4. Function Name Parsing and Resolution Look for "quoted identifier" on this page.


Need Your Help

How to get twitter api access using http request from browser

node.js twitter-rest-api

Is there any way to get the data from twitter api for any location?

Add row in to a table if ID does not exist in other version of the same table

php sql

I searched the solution in google but I can't find (or I don't understand) the right solution.