SELECT Prepared statement

Question

I've tried everything that I found on this community and other sites. But I'm still failing to complete my objective.

What I want to achieve:

On one page I have a input box with a button. When I fill in de ID number I want to get all the information out of mysql which is linked with this ID number....For some reason it doesn't work. Any tips or hints?

<?php

$servername = "xxxxx";
$username = "xxxxx";
$password = "xxxxx";
$dbname = "xxxxxx";


try{
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


    $sql = "INSERT id, voornaam FROM sollicitatie_form WHERE id= "echo ($_POST['zoek'])"";
    // $stmt->bindParam(':id', $id,  PDO::PARAM_STR);
    $stmt->bindParam(':voornaam', $voornaam,  PDO::PARAM_STR);
    $stmt->exec($sql);

    $result = $stmt->fetchAll();

    foreach ($result as $row){
        echo "{$row['voornaam']}";    

    }
}
    // use exec() because no results are returned
    catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;
?>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<meta http-equiv="Content-Type" /></meta>

<head>
  <title>Sollicitatie Formulier</title>
  <link href="styletest.css" rel="stylesheet">
</head>
<body>

<div class="form">

  <div class="tab-content">
    <h1>Sollicitatie Formulier</h1>

    <form method="post" enctype="multipart/form-data" >


      <div class="top-row">
        <div class="field-wrap">

          <div class="field-wrap">
            <input type="text" name="zoek" value="">
            <input type="submit" name="submit" value="zoek">
          </div> <!-- /field-wrap-->

    </form> 

  </div><!-- /tab-content-->

</div> <!-- /form -->


</body>
</html>

Changes `"echo ($_POST['zoek'])"` to `id= $_POST['zoek']` but still not working — WouterS, Jun 18 '17 at 16:59
Yes, I noticed a few other issues, see answer below. (also that wasnt exactly what I meant) — chris85, Jun 18 '17 at 17:00

Nirav Joshi · Accepted Answer · 2017-06-18T19:21:30.063

    <?php

$servername = "xxx";
$username = "xxx";
$password = "xxx";
$dbname = "xxx";


if(isset($_POST['submit'])):
try{
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


    $stmt = $conn->prepare("SELECT id, voornaam FROM sollicitatie_form WHERE id= :voornaam and city = :city");
    // $stmt->bindParam(':id', $id,  PDO::PARAM_STR);
    //$stmt->bindParam(':voornaam', $voornaam,  PDO::PARAM_STR);
     $stmt->bindParam(':voornaam',$_POST['zoek'], PDO::PARAM_STR);
     $stmt->bindParam(':city',$_POST['city'], PDO::PARAM_STR);
     $stmt->execute();

    $result = $stmt->fetchAll();

    foreach ($result as $row){
        echo "{$row['voornaam']}";    

    }
}
    // use exec() because no results are returned
    catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;
endif;
?>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<meta http-equiv="Content-Type" /></meta>

<head>
  <title>Sollicitatie Formulier</title>
  <link href="styletest.css" rel="stylesheet">
</head>
<body>

<div class="form">

  <div class="tab-content">
    <h1>Sollicitatie Formulier</h1>

    <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>" enctype="multipart/form-data" >


      <div class="top-row">
        <div class="field-wrap">

          <div class="field-wrap">
            <input type="text" name="zoek" value="">
            <input type="text" name="city" value="">
            <input type="submit" name="submit" value="zoek">
          </div> <!-- /field-wrap-->
        </div>
        </div>
    </form> 

  </div><!-- /tab-content-->

</div> <!-- /form -->


</body>
</html>

If you want to fetch data you have to use SELECT statement not INSERT statement. Please check this answer hope it will helps you.

Edit

Please try again and check any error is showing or not.

FINAL EDIT

Now check it is running properly now, i just run it on my local server.

Updated with 2 parameters

@WouterS i doesn't run this script i just edit which i saw wrong in the script. — Nirav Joshi, Jun 18 '17 at 17:07
@WouterS Please set ini_display_errors and tell me what the error you got — Nirav Joshi, Jun 18 '17 at 17:08
@NiravJoshi It works!! Nice!! Also a big thank you to chris85 for his input. What about if I want to ad a second parameter. For example. If I want to seen all the results for a name and for example a city. what do I need to add to my script? — WouterS, Jun 18 '17 at 18:06

chris85 · Answer 2 · 2017-06-18T17:06:44.597

2

You need to prepare the statement first, use a select (not insert), use a placeholder in the query, and execute the prepared object. Something like:

$stmt = $conn->prepare("select id, voornaam FROM sollicitatie_form WHERE id= :voornaam";
// $stmt->bindParam(':id', $id,  PDO::PARAM_STR);
$stmt->bindParam(':voornaam', $voornaam,  PDO::PARAM_STR);
$stmt->execute();

should do it. An alternative syntax I usually use is:

$stmt = $conn->prepare("select id, voornaam FROM sollicitatie_form WHERE id= ?";
$stmt->execute(array($voornaam)); //or array(':voornaam' =>$voornaam) if you prefer named placeholders

Also not echo is for outputting. You can concatenate or place a variable in to double quotes and then your PHP string will have the value.

Another example:

 echo "{$row['voornaam']}"

can just be:

echo $row['voornaam'];

edited Jun 18 '17 at 17:06

answered Jun 18 '17 at 16:58

chris85

23,846
7
34
51

I'm trying to understand your 2nd syntax. Where does `:voornaam` and `$voornaam` come from? – WouterS Jun 18 '17 at 19:03
In the `bindParam` or in the alternative syntax (and if alternative, the commented line or standard)? – chris85 Jun 18 '17 at 19:10
So in your 2nd syntax you need to use `$stmt = $conn->prepare("select id, voornaam FROM sollicitatie_form WHERE id= ?";$stmt->bindParam(':voornaam', $voornaam, PDO::PARAM_STR);$stmt->execute(array($voornaam));` – WouterS Jun 18 '17 at 19:18
Nope, either `bindparm` or `execute` with an array. The `execute` will bind if given array. Dont use both. If you are using the anonymous placeholders you can't use names, `:name...`, they are just bound by the order they appear. Rough example `prepare where name = ? and city = ? and state = ?` then `execute(array(1,2,3))` – chris85 Jun 18 '17 at 19:21
I'm confused and I really want to understand this. Let's say that I add `city` and `telephone` as row line from mysql db. In my search box I want to search with `city` and/or `telephone`. What do I need to change than? `$voornaam`? – WouterS Jun 18 '17 at 19:26
You'd make the `where` clause of the query `where city = ? or telephone = ?` then the execute would be `execute(array($city, $phone))` and there'd be no `bindparam`. `$city` and `$phone` need to be defined before the `execute` call. – chris85 Jun 18 '17 at 19:28
so I need to do `$city = :city`? I mean to define them – WouterS Jun 18 '17 at 19:31
Assign it from where every that comes from, maybe a get parameter? `$city = $_GET['city'];` – chris85 Jun 18 '17 at 19:33
Maybe it's the heat here but I'm so confused. I have a search field which is called `zoek` (see my html). I want to be able to put in a firstname, city or telephone number and than I want to search in mysql database for the results and give all the results with matching data. I've tried with adding city and telephone to my prepare statement and than with `$city = $_GET['city'];` just befor I execute but that didn't work – WouterS Jun 18 '17 at 19:40
1

I figured it out!!! Thanks a million!!! But I really wanted to understand how it works :-) – WouterS Jun 18 '17 at 19:49
1

@WouterS Yes understanding how something works is always better than just getting it to work (now you can do it yourself in the future). Here's a good page you can reference in the future. http://php.net/manual/en/pdo.prepared-statements.php – chris85 Jun 18 '17 at 19:50
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/147009/discussion-between-wouters-and-chris85). – WouterS Jun 18 '17 at 20:15

SELECT Prepared statement

2 Answers2