PHP MYSQL Select postcode OR customerid OR anythingelse

Question

Hi guys (and sorry for my lazy english^^) ! I want a SELECT for a searchform to search (example) with zipcode 1234 for john doe. but when i search for john doe with 1234 the result is a customer with customerid like 1234, too. thats not good, but i am think, i am on the right way. How can i fix it ? Okay, so here is my beginners code and i hope you can laugh a little bit and you can help my to code this to a that what i am looking for. :D Thank you so much, Guys !!

<table class="table table-striped" > 
    <p>

        <tr>
            <th>Kundennummer</th>
            <th>Reklamationsnummer</th>
            <th>Firma</th>
            <th>Nachname</th>
            <th>Vorname</th>
            <th>Straße</th>
            <th>Nr</th>
            <th>Plz</th>
            <th>Ort</th>
            <th>Telefon</th>
            <th>Mail</th>
        <tr>





        <?php 
        error_reporting(-1);
        ini_set('display_errors', true);

        include "config.php";




        $kdnr = mysqli_real_escape_string($mysqli, $_GET['kdnr']);
        $reklamationsnummer = mysqli_real_escape_string($mysqli, $_GET['reklamationsnummer']);
        $firma = mysqli_real_escape_string($mysqli, $_GET['firma']);
        $nachname = mysqli_real_escape_string($mysqli, $_GET['nachname']);
        $vorname = mysqli_real_escape_string($mysqli, $_GET['vorname']);
        $street = mysqli_real_escape_string($mysqli, $_GET['street']);
        $hausnummer = mysqli_real_escape_string($mysqli, $_GET['hausnummer']);
        $postleitzahl = mysqli_real_escape_string($mysqli, $_GET['postleitzahl']);
        $ort = mysqli_real_escape_string($mysqli, $_GET['ort']);
        $telefon = mysqli_real_escape_string($mysqli, $_GET['telefon']);
        $mail = mysqli_real_escape_string($mysqli, $_GET['mail']);

        $result = $mysqli->query("SELECT kdnr, reklamationsnummer, firma, nachname, vorname, street, hausnummer, postleitzahl, ort, telefon , mail 
                                        FROM kundentest
                                        WHERE
                                        kdnr = '".$kdnr."' XOR
                                        reklamationsnummer = '".$reklamationsnummer."' XOR
                                        firma = '".$firma."' XOR
                                        nachname = '".$nachname."' XOR
                                        vorname = '".$vorname."' XOR
                                        street = '".$street."' XOR
                                        hausnummer = '".$hausnummer."' XOR
                                        postleitzahl = '".$postleitzahl."' XOR
                                        ort = '".$ort."' XOR
                                        telefon = '".$telefon."' XOR
                                        mail = '".$mail."' ");


        while ($row = $result->fetch_assoc()):
        ?>

        <tr>
            <td><?php echo $row['kdnr']; ?></td>
            <td><?php echo $row['reklamationsnummer']; ?></td>
            <td><?php echo $row['firma']; ?></td>
            <td><?php echo $row['nachname']; ?></td>
            <td><?php echo $row['vorname']; ?></td>
            <td><?php echo $row['street']; ?></td>
            <td><?php echo $row['hausnummer']; ?></td>
            <td><?php echo $row['postleitzahl']; ?></td>
            <td><?php echo $row['ort']; ?></td>
            <td><?php echo $row['telefon']; ?></td>
            <td><?php echo $row['mail']; ?></td>
        </tr>

        <?php
        endwhile;
        ?>

        </table>
        <table>
        <tr>
            <th><a class="btn btn-info" href="index.php" role="button">zurück zur Suche</a></th>
        </tr>
    </table>

Don't trust `mysqli_real_escape_string` to properly escape your input! [Use prepared statements instead!](http://stackoverflow.com/a/60496/477563) — Mr. Llama, Oct 08 '15 at 19:06
since your coluns aren't in english how are we supposed to know which one is the customerid column? can you translate the column names please? — I wrestled a bear once., Oct 08 '15 at 19:07
@Mr.Llama, trustworthy or not, using `real_escape` makes code messier and queries harder to write. @devando, I seriously recommend using `PDO`. The named placeholders allow for code that is SO MUCH cleaner and easier to modify and maintain than the `?` ones. — Reed, Oct 08 '15 at 19:09

Alex · Accepted Answer · 2015-10-09T19:34:39.477

1

I guess you need some kind of dynamic query string generation. Try to replace the fragment starts from include "config.php"; to :

include "config.php";
$query = "SELECT kdnr, reklamationsnummer, firma, nachname, vorname, street, hausnummer, postleitzahl, ort, telefon , mail 
          FROM kundentest ";
$search_fields = array('kdnr',
  'reklamationsnummer',
  'firma',
  'nachname',
  'vorname',
  'street',
  'hausnummer',
  'postleitzahl',
  'ort',
  'telefon',
  'mail'
);
$first = true;
$params = array();
foreach ($search_fields as $column) {
    if(isset($_GET[$column])) {
        if ($first) {
            $query.='WHERE ';
            $first = false;
        } else {
            $query.=' AND ';
        }
        $query.= $column.' = ? ';
        $params[]=$_GET[$column];
    }
}

if ($stmt = $mysqli->prepare($query)) {
    $i=0;
    foreach($params as $param) {
        ${'param'.++$i} = $param;
        $stmt->bind_param('s', ${'param'.$i});
    }

     $stmt->execute();

    /* bind result variables */

    $stmt->bind_result($kdnr, $reklamationsnummer, $firma, $nachname, $vorname, $street, $hausnummer, $postleitzahl, $ort, $telefon , $mail);

    while ($stmt->fetch()) { ?>
        <tr>
            <td><?= $kdnr ?></td>
            <td><?= $reklamationsnummer ?></td>
            <td><?= $firma ?></td>
            <td><?= $nachname ?></td>
            <td><?= $vorname ?></td>
            <td><?= $street ?></td>
            <td><?= $hausnummer ?></td>
            <td><?= $postleitzahl ?></td>
            <td><?= $ort ?></td>
            <td><?= $telefon ?></td>
            <td><?= $mail ?></td>
        </tr>
    <?php }
}

you are very welcome if any questions.

edited Oct 09 '15 at 19:34

answered Oct 08 '15 at 19:45

Alex

16,739
1
28
51

Nice work trying to tackle this in a programmatic way with an array rather than just smashing together a giant string. – tadman Oct 08 '15 at 19:50
Fatal error: Only variables can be passed by reference in C:\xampp\htdocs\searchengine.php on line 149 that is $stmt->bind_param('s',$param{$i}); – devando Oct 08 '15 at 21:19
Parse error: syntax error, unexpected '$stmt' (T_VARIABLE) in C:\xampp\htdocs\searchengine.php on line 146 – devando Oct 09 '15 at 19:32
Parse error: syntax error, unexpected 'endwhile' (T_ENDWHILE) in C:\xampp\htdocs\searchengine.php on line 173 - this is - meanwhile is think about a switch to PDO, if is is really better for a clean code an security - what doyou think, alex ? – devando Oct 10 '15 at 09:29
@devando frist: I have no `endwhile` in my code :-) Second: yes PDO is much better way imho – Alex Oct 10 '15 at 14:22
can anybody show me, how this code looks like in PDO ? – devando Oct 14 '15 at 20:24
@devando I would suggest you to take your current code and create new question, with 'How to convert this code to PDO' title. – Alex Oct 14 '15 at 20:34

score 0 · Answer 2 · answered Oct 08 '15 at 19:16

i think i dont understand your doubt, but maybe is better if you create an stored procedure to control this. If have any customer with zip code like 1234, return and dont serach for customerid

or,

change your query, to dont look for customerid.

Sorry about english too =)

PHP MYSQL Select postcode OR customerid OR anythingelse

2 Answers2