Hi guys I'm new here so I apologise if this is the wrong place to 'submit my query' pardon the pun.
I'm new to PHP, in fact I've been coding with it now for a grand amount of 1 week. I understand the basics and I've managed to produce a simple one-field working form that inputs into my PHP page and queries my database.

I did this with the following code (I've changed the names of my host, database and fields within the database:

//query.php
<?
// make connection to server.
    $dbServer=mysql_connect("host","database","pass");
// inserted error code to be echoed if failed to connect to server.
    if (!$dbServer) {echo "Failed to connect to MySQL"; exit; }

// select the database.
    mysql_select_db("database",$dbServer);

// query.
    $sql ="SELECT * FROM table";
    $sql.=" WHERE fieldone LIKE\"%".$_POST["fieldone"]."%\"";

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>";
      }
    }
 ?>

That worked, yey...but that's not all, the search form on my website does not just have one input field "fieldone"; it has 6, so for now if we just call them:
fieldone
fieldtwo
fieldthree
fieldfour
fieldfive
fieldsix

fieldone to fieldfive are text fields and I want them to be "LIKE" their database counterparts, but fieldsix is a year such as 1950. I want fieldsix to be equal (=\"".$_POST["fieldsix"]."\""; ) to its database counterpart.

Well, as you guessed I want the user of my website to go onto the search page and be able to use any number (at least 1 of course) of the fields as they want to search for the product they're looking for.

I've been working so much on this but I just can't crack it. My main problem is I can't see my PHP working because I can't access my database from home so I'm blindly coding here without much of a clue what its doing. I'd appreciate any help you can offer.

Just to give you a better idea of my code here's a few snippets I've been playing with trying to get to work:

// 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 (($_POST["fieldone"])!="") //and if fieldone !=""
{
$sql.=" WHERE fieldone LIKE \"%".$_POST["fieldone"]."%\" AND ";
}
else
{
$sql.=" WHERE ";
}
}
if (($_POST["fieldtwo"])!="")
{
$sql.=" fieldtwo LIKE \"%".$_POST["fieldtwo"]."%\" AND ";
}
if (($_POST["fieldthree"])!="")
{
$sql.=" fieldthree LIKE \"%".$_POST["fieldthree"]."%\"";
}
if (($_POST["fieldfour"])!="")
{
$sql.=" fieldfour LIKE \"%".$_POST["fieldfour"]."%\" AND ";
}
if (($_POST["fieldfive"])!="")
{
$sql.=" fieldfive LIKE \"%".$_POST["fieldfive"]."%\" AND ";
}
if (($_POST["fieldsix"])!="")
{
$sql.=" fieldsix=\"".$_POST["fieldsix"]."\"";
}
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>";
      }
    }
 ?>

Well, that's about it really, I think you know what I'm trying to do but it's just not producing what I want it to.
Bascially I just want:
The search criteria from searchpage.htm to be queried into the database and posted onto query.php.
I want empty fields from the search to be ignored and the used search fields somehow still need to be ANDed together if you know what I mean.

Thanks in advance for any help anybody can offer,

regards

Bigfatcat

    Bigfatcat,

    First you may want to look into SQL Injection, another link Here.

    Second of all, I think you are on the right track. I am unsure if you want the year there, if you want it so that it is taken into concideration only if they select it..

    <!-- this is a part of your html file -->
<select name="blah">
<option value="X">Select Year</option>
<option>1998</option>
<!-- etc etc -->
</select>

    
$fieldone = $_POST['field1'];
$fieldtwo = $_POST['field2'];
// etc...

$query = "SELECT * from table WHERE ";

if (strlen($fieldone) >= 1) {
   $query.="fieldone like '%$field1%' AND ";
}
if (strlen($fieldtwo) >= 1) {
   $query.="fieldtwo like '%$field2%' AND ";
}
// etc etc etc
if (strlen($fieldsix) >= 1 && $fieldsix != "X") {
   $query.="fieldsix = '$fieldsix' AND ";
}

$query = substr_replace($query,'',strlen($query), -4); // Takes off the last "AND "

echo $query; // just to test it...

    This way if they use the year or not, you always add the "AND " at the end; and take it out. Unless they are always using the year, I was unsure of this.

    Another recommendation, there are free web sites out there which offer PHP and MySQL Databases for you to do the testing. Most of them have error_reporting off; and are in safe mode, so it will come up blank if there is an error (at least the ones I have seen).

      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.

        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

          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.

            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).

              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);
}

                NogDog,

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

                Very Nice!

                  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

                    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.

                      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 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. 🙁

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

                            Write a Reply...