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 '
'.$row['vorname'].
           ' - '.$row['nachname'].

           ' - '.$row['strasse'].
           ' - '.$row['plz'].
           ' - '.$row['ort'].
           ' - '.$row['email'].
           ' - '.$row['telefon'].
           ' - '.$row['mobil'].'

';
           }    
}

HTML CODE:


    





    




     
    Anrede
        
    
    


  

    Vorname
    
      
    
  
    


    Nachname
    
      
    
  
    

    E-mail-Addresse
    
      
    
  
    

    Telefonnummer
    
      
    
  
  


    
      Geburtstag
    
    
       
   
  
 


    
      Plz
    
    
       
   



   
      Ort
    
    
       
   



  

 
    Straße
    
      
    
  

 


    
      Hausnummer
    

      

      
      Hausnummerzusatz

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

RiggsFolly · Accepted Answer

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:

was text7

was text2

was text8

was text3

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 '
'.$row['vorname'].
       ' - '.$row['nachname'].

       ' - '.$row['strasse'].
       ' - '.$row['plz'].
       ' - '.$row['ort'].
       ' - '.$row['email'].
       ' - '.$row['telefon'].
       ' - '.$row['mobil'].'

';
    } 
}

EDIT: RE Issues

I have a little test bed running, when I enter

$_POST = [  'submit' => 1, 
        'vorname' => 'Thomas', 
        'nachname' => 'Denly',
        'email' => 'xxxx@edfsd.com'
    ];

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] => xxxx@edfsd.com
)

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?

Multiple input field search in PHP and MySQL not working

Answers (1)

Related Questions