Multiple input field search in PHP and MySQL not working

Question

I have to enter multiple inputs (in pair) in one search: for example:

1 Vorname and Strasse
2 Nachname and Strasse
3 Vorname and Email
4 Nachname and Email
5 Only Vorname
6 Only nickname
7 Only email
8 Vorname, Nachname, and Strasse
9 Vorname, Nachname, and Email
10 like that all separately and together

I did this below code and it's NOT WORKING for the above conditions. Somebody help me out with PHP/MySQL here. It would be really appreciated

PHP CODE:

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

    if(isset($_POST['text1'], $_POST['text7'])){
        // we got both vorname and ort 

        $statement= $myconnection->prepare(
                "SELECT * FROM OL_trans WHERE vorname = ? AND ort = ? OR nachname = ? OR strasse = ? OR email = ?");

        $statement->execute( [$_POST['text1'], $_POST['text7'], $_POST['text2'],$_POST['text8'], $_POST['text3']] ); 

    }
    else{
        if(isset($_POST['text1'])){
            // vorname
            $statement= $myconnection->prepare(
                "SELECT * FROM OL_trans WHERE vorname = ?");

            $statement->execute( [$_POST['text1']] ); 
        }
        if ( isset($_POST['text7'] )) {
            // ort 
            $statement= $myconnection->prepare(
                "SELECT * FROM OL_trans WHERE ort = ?");

            $statement->execute( [$_POST['text7']] ); 
        }
    }
    // generate the output
    $rows = $statement->fetchAll(); 

    foreach($rows as $row){ 
            echo '<br/>'.$row['vorname'].
           ' - '.$row['nachname'].

           ' - '.$row['strasse'].
           ' - '.$row['plz'].
           ' - '.$row['ort'].
           ' - '.$row['email'].
           ' - '.$row['telefon'].
           ' - '.$row['mobil'].'<br/><br/>';
           }    
}

HTML CODE:

<form method="post" action="sample.php">
    <br><br>
<br><br>
    <!--Beginn des Anrede-->
<div class="form1"><br><br>
<div class="names">
    <div class="anrede"> 
    <label for="Anrede">Anrede
        <select id="select" name="anrede" >
        <option value="">--Please choose an option--</option>
        <option value="herr">Herr</option>
        <option value="frau">Frau</option>
        <option value="andere">Andere</option>
        </select>
    </label></div>
    <br><br>
  <div><br>
    <label class="desc" id="title1" for="text1">Vorname</label>
    <div>
      <input id="text1" name="text1" type="text" class="field text fn" value="" size="8" tabindex="1" placeholder="vorname">
    </div>
  </div>
    <div><br><br>
    <label class="desc" id="title1" for="text2">Nachname</label>
    <div>
      <input id="text2" name="text2" type="text" class="field text fn" value="" size="8" tabindex="1" placeholder="Ihr Nachname">
    </div>
  </div>
    <div><br>
    <label class="desc" id="title1" for="text3">E-mail-Addresse</label>
    <div>
      <input id="text3" name="text3" type="text" class="field text fn" value="" size="8" tabindex="1" placeholder="Deine Email">
    </div>
  </div>
    <div><br>
    <label class="desc" id="title1" for="text4">Telefonnummer</label>
    <div>
      <input id="text4" name="text4" type="number" class="field text fn" value="" size="8" tabindex="1" placeholder="Deine Telefonnummer">
    </div>
  </div>
  <div><br><br>
    <label class="desc" id="title3" for="text5">
      Geburtstag
    </label>
    <div>
      <input id="text5" name="text5" type="date" spellcheck="false" value="" maxlength="255" tabindex="3"> 
   </div>
  </div>
 <div><br><br>
    <label class="desc" id="title3" for="text6">
      Plz
    </label>
    <div>
      <input id="text6" name="text6" type="number" spellcheck="false" value="" maxlength="255" tabindex="3" placeholder="Ihre Postleitzahl"> 
   </div><br><br>
   <label class="desc" id="title3" for="text7">
      Ort
    </label>
    <div>
      <input id="text7" name="text7" type="text" spellcheck="false" value="" maxlength="255" tabindex="3" placeholder="Dein Platz"> 
   </div><br><br>
  </div>

 <div>
    <label class="desc" id="title1" for="text8">Stra&szlig;e</label>
    <div>
      <input id="text8" name="text8" type="text" class="field text fn" value="" size="8" tabindex="1" placeholder="Deine Stra&szlig;e">
    </div>
  </div>

 <div><br><br>
    <label class="desc" id="title3" for="text9">
      Hausnummer
    </label>

      <input id="text9" name="text9" type="number" spellcheck="false" value="" maxlength="255" tabindex="3" placeholder="Ihre Hausnummer">

      <label class="desc" id="title3" for="text9">
      Hausnummerzusatz
    </label>
      <input id="text10" name="text10" type="number" spellcheck="false" value="" maxlength="255" tabindex="3" placeholder="Zus&#228;tzliche Adresse">
      <div><br><br>
      <div class="sub">
      <input type="submit" name="submit" value="Suche">
      <input type="reset" name="reset" value="Reset">
      </div>
    <br><br><br></div>
    </div>
  </div>
</form>

None of the pair searches is working. I tried changing between 'AND' & 'OR' and it just not working for me.

And what is not working? You have to be a bit more detailed. — LLJ97
– LLJ97, Commented May 14, 2019 at 8:16
No pairs of inputs are working, I tried changing "AND" & "OR" and still no changes. Kindly, help me to get single and pair search function working. — Uscokk
– Uscokk, Commented May 14, 2019 at 8:22
Please... get rid of naming your variables like text1 text2 ...text7 — B001ᛦ
– B001ᛦ, Commented May 14, 2019 at 8:26
How should I rewrite it then? I tried calling it the same and just not working too — Uscokk
– Uscokk, Commented May 14, 2019 at 8:29

RiggsFolly · Accepted Answer · 2019-05-14 10:35:06Z

1

First of all give your form fields sensible names so you know what you are dealing with when the fields get to PHP. For example at least the ones we are dealing with in the script:

was text1:

<input id="vorname" name="vorname" type="text" class="field text fn" value="" size="8" tabindex="1" placeholder="vorname">

was text7

<input id="ort" name="ort" type="text" spellcheck="false" value="" maxlength="255" tabindex="3" placeholder="Dein Platz">

was text2

<input id="nachname" name="nachname" type="text" class="field text fn" value="" size="8" tabindex="1" placeholder="Ihr Nachname">

was text8

<input id="strasse" name="strasse" type="text" class="field text fn" value="" size="8" tabindex="1" placeholder="Deine Stra&szlig;e">

was text3

<input id="email" name="email" type="text" class="field text fn" value="" size="8" tabindex="1" placeholder="Deine Email">

The PHP

// while developing and debugging add these
// just in case you are developing on a PHP configured for a LIVE env
ini_set('display_errors', 1); 
ini_set('log_errors',1); 
error_reporting(E_ALL);

// this should really go in the db connect section
// will cause PDO to throw exceptions if errors happen in the PDO Code
// so you can actually see what the problems are

$myconnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

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

    $sql = 'SELECT * FROM OL_trans WHERE ';

    if ( !empty($_POST['vorname']) ) {
        $sql .= ' vorname = ? AND ';
        $params[] =  $_POST['vorname'];
    }   

    if ( !empty($_POST['nachname']) ) {
        $sql .= ' nachname = ? AND ';
        $params[] =  $_POST['nachname'];
    }

    if ( !empty($_POST['email']) ) {
        $sql .= ' email = ? AND ';
        $params[] =  $_POST['email'];
    }

    if ( !empty($_POST['strasse']) ) {
        $sql .= ' strasse = ? AND ';
        $params[] =  $_POST['strasse'];
    }

    if ( !empty($_POST['ort']) ) {
        $sql .= ' ort= ? AND ';
        $params[] =  $_POST['ort'];
    }

    // remove trailing AND

    $sql = rtrim($sql, 'AND ');
    $stmt = $myconnection->prepare($sql);
    $stmt->execute($params);

    $rows = $stmt->fetchAll();

    foreach($rows as $row){ 
        echo '<br/>'.$row['vorname'].
       ' - '.$row['nachname'].

       ' - '.$row['strasse'].
       ' - '.$row['plz'].
       ' - '.$row['ort'].
       ' - '.$row['email'].
       ' - '.$row['telefon'].
       ' - '.$row['mobil'].'<br/><br/>';
    } 
}

EDIT: RE Issues

I have a little test bed running, when I enter

$_POST = [  'submit' => 1, 
        'vorname' => 'Thomas', 
        'nachname' => 'Denly',
        'email' => '[email protected]'
    ];

The query generated is

SELECT * FROM OL_trans WHERE  vorname = ? AND  nachname = ? AND  email = ?

And the $params array is

Array
(
    [0] => Thomas
    [1] => Denly
    [2] => [email protected]
)

If i enter

$_POST = [  'submit' => 1, 
        'vorname' => 'Thomas', 
        ];

The query is

SELECT * FROM OL_trans WHERE  vorname = ?

And parameters are

Array
(
    [0] => Thomas
)

So what is wrong with that query and parameter values?

edited May 14, 2019 at 10:35

answered May 14, 2019 at 9:38

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Uscokk Over a year ago

Yeah, Because of the 'AND' i have to enter all coded fields mandatory. but i need a solution, when i enter only vorname or nachname (or any one field) it should also work

RiggsFolly Over a year ago

This should work for any 1 or any 2 or any 3 or any 4 or any 5 or these fields being entered? Can you echo the $sql from what does not work please.

Uscokk Over a year ago

Right now it's working for 5 fields. It's not working for any 1, any 2, any 3 and any 4 fields. I entered 'vorname', 'nachname', 'email', 'Strasse' and 'ort' and it works! When I remove any one or two or three or four field, its not working. The output is a blank page

RiggsFolly Over a year ago

PLEASE show me the ECHO $sql; print_r($params); for one situation where it does not work

Uscokk Over a year ago

No!, See i entered -> vorname:Thomas nachname: Denly email: [email protected] strasse: 34 nordstr Ort:Berlin. It prints the output successfully. NOW! I also need it to work when i enter -> vorname:Thomas Nachname:Denly email:[email protected]. Similarly, any 2 or 3 fields i request it should work. Thanks a lot for your effort though

|

Collectives™ on Stack Overflow

Multiple input field search in PHP and MySQL not working

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related