Searching a db using several input fields

[Bigfatcat]

thanks for the reply big.nerd.

About the year input...It's just a text field like the other inputs:

Here's the form in my html page:

<FORM action="query.php" method="post">
<P align="right">
fieldone: <INPUT type="text" name="titleofcomic"><BR>
fieldtwo: <INPUT type="text" name="publisher"><BR>
fieldthree: <INPUT type="text" name="genre"><BR>
fieldfour: <INPUT type="text" name="author"><BR>
fieldfive: <INPUT type="text" name="artist"><BR>
Year: <INPUT type="text" maxlength=4 size=5 name="yearpublished">
</P>
<P align="right">
<INPUT class="button" type="submit" value="Search">
<INPUT class="button" type="reset" value="Reset">
</P>
</FORM>

The 'searcher' simply types the year in, however the max length is restricted to 4.
The first five fields are like:

fname
surname
team
etc...

All the fields, including the 'year' field are text fields, the only difference is the user is expected to type text in the first five fields such as names, whereas in the 'year' field the user is prompted to type a 4 digit number (the year).

Hope that makes more sense.

My problem isn't related to the 'year' part specifically. My problem is I can't get the query to ignore all the empty fields (i.e. "") but use the fields that have been set together.

I've tried so much but I just can't seem to fix it.

[big_nerd]

I did a quick edit, there may be some issues with it.. but its just an idea..

<?php
// connection to server. 
    $dbServer=mysql_connect("host","database","pass"); 
// error code if failed to connect. 
    if (!$dbServer) {echo "Failed to connect to MySQL"; exit; } 
// select the database. 
    mysql_select_db("database",$dbServer); 
// query. 
    $sql ="SELECT * FROM table"; 
if (isset ($_POST["fieldone"])) //if fieldone is set 
{ 
if ((strlen($_POST["fieldone"]))>=1) //and if fieldone >=1 
{ 
$sql.=" WHERE fieldone LIKE \"%".mysql_real_escape_string($_POST["fieldone"])."%\" AND "; 
} 
else 
{ 
$sql.=" WHERE "; 
} 
} 
if ((strlen($_POST["fieldtwo"]))>=1) 
{ 
$sql.=" fieldtwo LIKE \"%".mysql_real_escape_string($_POST["fieldtwo"])."%\" AND "; 
} 
if ((strlen($_POST["fieldthree"]))>=1) 
{ 
$sql.=" fieldthree LIKE \"%".mysql_real_escape_string($_POST["fieldthree"])."%\""; 
} 
if ((strlen($_POST["fieldfour"]))>=1) 
{ 
$sql.=" fieldfour LIKE \"%".mysql_real_escape_string($_POST["fieldfour"])."%\" AND "; 
} 
if ((strlen($_POST["fieldfive"]))>=1) 
{ 
$sql.=" fieldfive LIKE \"%".mysql_real_escape_string($_POST["fieldfive"])."%\" AND "; 
} 
if ((strlen($_POST["fieldsix"]))>=1) 
{ 
$sql.=" fieldsix=\"".$_POST["fieldsix"])."\" AND "; 
} 
$sql = substr_replace($sql,'',strlen($sql), -4); // Takes off the last "AND " 

echo $sql; 

$queryResult=mysql_query($sql); 
// error message in case of error during query. 
    if (mysql_error()) 
    { 
      echo "Problem with Query<BR>"; 
      echo "The following error message was returned from MySQL:<BR>"; 
      echo mysql_error(); 
      exit; 
    } 
// response to query 
    if (mysql_num_rows($queryResult)==0) 
    { 
      echo "your search returned no results."; 
    } 
    else 
    { 
      while ($dbRecord=mysql_fetch_array($queryResult)) 
      { 
        echo "<BR>found: <BR>".$dbRecord["colone"]).", ".$dbRecord["coltwo"]).", ".$dbRecord["colthree"]).", ".$dbRecord["colfour"]).", ".$dbRecord["colfive"]).", ".$dbRecord["colsix"]).", ".$dbRecord["colsev"])."<BR>"; 
      } 
    } 
?> 

I have also added mysql_real_escape_string(); You should read up on that and SQL Injection.

Good Luck

[Bigfatcat]

Thanks again mate, I'll give this a good run over when I get access to the server tomorrow.
Thanks for the link too!

I can see how sometimes, purely by accident searches can cause query errors due to 'sql injection'...do you imagine this accidental sql injection is causing half of my problems?

Anyway, I'll have a go at this and let you know how it goes.

[big_nerd]

Bigfatcat,

Depending on your search queries, it is entirely possible, anyting with ' or " is a a pretty good chance.

Plus instead of asking if its empty, I ran an if based on if its longer than or equal to 1 char, it seems to work better (for me anyways).

[NogDog]

I might approach it this way:

<?php
$searchFields = (   // form_field_name => db_column_name
   'field1' => 'fieldone',
   'field2' => 'fieldtwo',
   'field3' => 'fieldthree' // etc. for each applicable field
);
$where = array();
foreach($searchFields as $formID => $colID)
{
   if(trim($_POST[$formID]) !== '')
   {
      $where[] = sprintf(
         "%s LIKE '%%%s%%'",
         $colID, 
         mysql_real_escape_string(trim($_POST[$formID]))
      );
   }
}
$sql = "SELECT * from table";
if(count($where))
{
   $sql .= " WHERE " . implode(' AND ', $where);
}

[big_nerd]

NogDog,

Excellent approach to the situation, if you don't mind, I actually have a use for that (well something like that)

Very Nice!

[Bigfatcat]

Thanks guys,
NogDog that looks very neat, not sure I understand it 🙂 but it does look nifty and small.
I'll mull over it to see what I can make out.

EDIT: OK I've had a read and so far this is what I can make of NogDog's work 🙂
I've commented into the code what I understand...

<?php
$searchFields = (   // creating array $searchFields
   'field1' => 'fieldone',  //but why use '=>' symbols?
   'field2' => 'fieldtwo',
   'field3' => 'fieldthree'
);
$where = array(); // referring to $searchFields array?
foreach($searchFields as $formID => $colID)  /*or each search field when $formID is the same as $colID */ 
{
   if(trim($_POST[$formID]) !== '') // if $formID does not equal "" after trimming
   {
      $where[] = sprintf(
         "%s LIKE '%%%s%%'",
         $colID,
         mysql_real_escape_string(trim($_POST[$formID]))
      ); //make variable $where to = string: "$colID, %trimmed posted $formID5"
   }
}
$sql = "SELECT * from table";
if(count($where)) //I can't make out what this is doing though
{
   $sql .= " WHERE " . implode(' AND ', $where);
}

Okay, so my idea of whats happening here, and please correct me if I'm wrong because I'm not quite sure...

Created array
for each variable in the array that is the same as their colID counterparts:
loop them into:
if trimmed inputs do not equal "" when trimmed
...
but then I don't quite understand what the purpose of the output of the if statement is and what if(count($where)) does?

Thanks in advance for any sort of enlightenment!

bfc

[NogDog]

The if(count($where)) is being used to only create a where clause if at least one search parameter was received from the form (and therefore added to the $where array). If there was at least one entry, then we're adding a " WHERE " to the $sql, followed by each comparison statement saved in the $where array. The implode sticks an " AND " between each comparison. (Just change it to " OR " if that's the desired logic.) If there is only one entry in the $where array, there will be no " AND ", and you never have to worry about any excess trailing "AND" that has to be deleted.

[Bigfatcat]

NogDog wrote:

The if(count($where)) is being used to only create a where clause if at least one search parameter was received from the form (and therefore added to the $where array). If there was at least one entry, then we're adding a " WHERE " to the $sql, followed by each comparison statement saved in the $where array. The implode sticks an " AND " between each comparison. (Just change it to " OR " if that's the desired logic.) If there is only one entry in the $where array, there will be no " AND ", and you never have to worry about any excess trailing "AND" that has to be deleted.

Ah thats great! Definately makes more sense than my own error-ridden solution.

Thanks mate 🆒

[bradgrafelman]

Don't forget to mark this thread resolved (if it is).

[NogDog]

bradgrafelman wrote:

Don't forget to mark this thread resolved (if it is).

Arrgghh! I hate opening up a thread flagged as having unread replies, skimming through to refresh my memory as to what it was about, only to find one of these posts at the end being the reason it was flagged. 🙁

[bradgrafelman]

Which is precisely why I am constantly educating posters on how to mark their threads resolved, despite those that are ungrateful for my policy.