PDO prepare statement doesn't substitute placeholders with data

Question

I'm making some basic login-register form using PDO but can not able insert data in database when i var_dump() the $query, i see no value will not execute in $query->execute statement .see below what found when i var_dump($query);

**

object(PDOStatement)#4 (1) { ["queryString"]=> string(92) "INSERT INTO users(username,password,name,email,website) VALUES (?,?,?,?,?)" } Registration successfull.Click here for login

**

i Experienced this problem and try almost everything i know . Please see the code help me find the problem .

Database format like database name : oopreg table name: users

id          int(11)       primary auto_increment
username    varchar(30)
password    varchar(30)
name        varchar(50) 
email       varchar(100)
website     varchar(100)

config.php

<?php
class databaseConnection{

    public function __construct(){
        GLOBAL $pdo;
        try{
    $pdo = new  PDO('mysql:host:localhost;dbname=oopreg','root','');
    echo "Connected";
        }catch(PDOException $e){

            echo "DataBase connection Error";

        } 
    }

}

functions.php

<?php

require 'config.php';

   class loginRegistration{

      function  __construct(){
        $database = new databaseConnection();
      } 
     public function registerUser($username,$password,$name,$email,$website){
        global $pdo;
        $query= $pdo->prepare("SELECT id FROM users WHERE username=? AND email=?");
        $query->execute(array($username,$email));
        $num= $query->rowCount();
        if($num==0){
          $query= $pdo->prepare("INSERT INTO users(username,password,name,email,website) VALUES
                 (?,?,?,?,?)");
          $query->execute(array($username,$password,$name,$email,$website));  
        var_dump($query);
         return true;

        }else{
             print "<span style='color:red'>Error...Username/Email alreay exists</span>";
        }
     }

   }


  ?>

register.php

<?php

require_once "functions.php";
$user= new loginRegistration();
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Registration Page</title>
    <link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>
<div class="wrapper">
    <div class="header">
        <h3>PHP OOP Login Register System</h3>
    </div><!--End header Section-->
    <div class="mainmenu">
        <ul>
            <li><a href="index.php">Home</a></li>
            <li><a href="profile.php">Show Profile</a></li>
            <li><a href="changePassword.php">Change Password</a></li>
            <li><a href="logout.php">Logout</a></li>
            <li><a href="login.php">Login</a></li>
            <li><a href="register.php">Register</a></li>
        </ul>
    </div><!--End Manin menu section-->

    <div class="content">
        <h2>Register</h2>

    <p class="msg">

       <!--PHP area start--> 
        <?php 
        if ($_SERVER['REQUEST_METHOD']=='POST') {
            $username=$_POST['username'];
            $password=$_POST['password'];
            $name=$_POST['name'];
            $email=$_POST['email'];
            $website=$_POST['website'];

           if(empty($username) or empty($password) or empty($name) or empty($email) or empty($website)) {

                echo "<span style='color:red;'> Field must not be empty</span>";
           }else{
                 $password=md5($password);
               $register=$user->registerUser($username,$password,$name,$email,$website);
               if($register){
                echo "<span style='color:green'>Registration successfull.<a href='login.php'>Click here</a> for login</span>";
               }else{
                echo "<span style='color:red'>UserName or email already exists</span>";
               }
           }

        }
         ?>
      </p>
    <div class="login_reg">
        <form action="" method="post"> 
            <table>
                <tr>
                    <td>Username:</td>
                    <td><input type="text" name="username" placeholder="Enter your username..."/></td>
                </tr>
                <tr>
                    <td>Password:</td>
                    <td><input type="password" name="password" placeholder="Enter your password..."/></td>
                </tr>
                <tr>
                    <td>Name:</td>
                    <td><input type="text" name="name" placeholder="Enter your name..."/></td>
                </tr>
                <tr>
                    <td>Email:</td>
                    <td><input type="email" name="email" placeholder="Enter your email..."/></td>
                </tr>
                <tr>
                    <td>Website:</td>
                    <td><input type="text" name="website" placeholder="Enter your website..."/></td>
                </tr>
                <tr>
                    <td colspan="2">
                        <input type="reset" name="" value="Reset"/>
                        <input type="submit" name="register" value="Register"/>

                    </td>
                </tr>
            </table>
        </form>
        <?php //var_dump($_POST); ?>
    </div><!--End Login_reg page-->
    <div class="back">
        <a href="">Back</a>

    </div>
    </div><!--End Content section-->
    <div class="footer">
        <h3>Training with live project</h3>
    </div>

</div><!--End Wrapper section-->
</body>
</html>

To debug PDO statements then there are useful suggestions: [How to debug PDO database queries?](http://stackoverflow.com/questions/2411182/how-to-debug-pdo-database-queries). For mysql this is useful: [How and When To Enable MySQL Logs](http://www.pontikis.net/blog/how-and-when-to-enable-mysql-logs). I use the 'enable logs at runtime' options. — Ryan Vincent, Aug 18 '16 at 08:34

Your Common Sense · Answer 1 · 2016-08-17T08:08:56.043

i see no value will not execute in $query->execute statement .see below what found when i var_dump($query);

That's all right, PDO works this way. It won't substitute question marks with the actual data in queryString property.

To see what is the real reason for your query not to work you have to configure PDO properly, as explained in my article, PDO tutorial. Connecting. DSN:

public function __construct(){
    $opt = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];
    $pdo = new  PDO('mysql:host:localhost;dbname=oopreg','root','', $opt);

After that, in case of error it will be reported to you.

score 0 · Answer 2 · answered Aug 07 '19 at 07:18

0

You haven't removed the single quotes from the values in the query. You probably used values(':aa',... instead of values(:a, ...

Hope this solves the problem. I had a similar challenge too

answered Aug 07 '19 at 07:18

Earl Samm

1

While this might be a useful tip for some people, I believe you have misunderstood this question. – Dharman Aug 09 '19 at 17:18

PDO prepare statement doesn't substitute placeholders with data

2 Answers2