CODEWITHSUNDEEP
phpmysql
ZeroAccess
ZeroAccess

Reputation: 171

Invalid parameter number: number of bound variables does not match number of tokens'

I have a Query that populates $row["app_id"] and as I go through my div id="reveal" that app_id changes. What I'm trying to do is plug that app_id in the $revealstmt query. I keep receiving error.

 Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid     
 parameter number: number of bound variables does not match number of tokens

My code is as follows

    <?php   
    $revealid = $row["app_id"];
    while (isset($revealid)){
            $query3 = "";
            $revealstmt = $conn->prepare("SELECT logs.time_entry AS logs_entry, logs.description AS logs_description, substates.name AS reveal_substates_name FROM logs LEFT OUTER JOIN applications ON logs.fk_app_id = applications.pk_app_id LEFT OUTER JOIN substates ON logs.fk_substate_id = substates.pk_substate_id WHERE logs.fk_app_id = <?php echo $revealid ?> and time_entry >= curdate() - INTERVAL DAYOFWEEK(curdate()) + 14 DAY ORDER BY time_entry DESC;");
            $revealstmt->execute(array('query3' => $query3));
 ?>

 <div id="<?php echo $row["app_name"];?>" class='reveal-modal'>
    <div id="reveal"> 
            <h1><?php echo $row["app_name"]; echo $revealid; ?>
            <br />
            </h1>
            <div class="accordion">
                    <?php while ($revealrow = $revealstmt->fetch()){?>
                            <h3><a href=""><?php echo $revealrow["logs_entry"]?> -|- <? 
 php echo $revealrow["reveal_substates_name"]; ?></a></h3>
                            <div><?php echo $revealrow["logs_description"]; ?></div>
                    <?php  }  ?> <!-- Close Tag for $revealrow -->
    <?php } ?> <!-- Close tag for the while(isset) -->

I've tried using foreach but cannot as $row["app_id"] isn't an array it only returns 1 number at a time.

Hopefully I've given all the information needed, if not will of course add whatever I need to. Thanks again to the community for all of your help!

Upvotes: 1

Views: 14142

Answers (3)

Jeffery ThaGintoki
Jeffery ThaGintoki

Reputation: 457

$revealstmt->execute(array('query3' => $query3)); this is your problem, your query have no binding values but still you are sending an array of bindings on your execute statment.

Try this one:

$revealstmt = $conn->prepare("SELECT logs.time_entry AS logs_entry, logs.description AS   logs_description, substates.name AS reveal_substates_name FROM logs LEFT OUTER JOIN applications ON logs.fk_app_id = applications.pk_app_id LEFT OUTER JOIN substates ON logs.fk_substate_id = substates.pk_substate_id WHERE logs.fk_app_id = :query3 and time_entry >= curdate() - INTERVAL DAYOFWEEK(curdate()) + 14 DAY ORDER BY time_entry DESC;");

and then you execute yout query by passing the binding values like so:

$revealstmt->execute(array('query3' => $revealid));

OR :

$revealstmt->bindValue(':query3', $revealid);

Upvotes: 0

Francisco Presencia
Francisco Presencia

Reputation: 8841

You should use concatenation instead in your query. Besides, you don't want queries that long. In the same way you wouldn't write all your PHP in one line, the same goes for your MySQL: it's another language.

$revealstmt = $conn->prepare(
  "SELECT logs.time_entry AS logs_entry, " . 
    "logs.description AS logs_description, " .
    "substates.name AS reveal_substates_name " . 
  "FROM logs LEFT OUTER JOIN applications " .
  "ON logs.fk_app_id = applications.pk_app_id " . 
  "LEFT OUTER JOIN substates ON logs.fk_substate_id = substates.pk_substate_id " .
  "WHERE logs.fk_app_id = " . $revealid . " AND " .
  "time_entry >= curdate() - INTERVAL DAYOFWEEK(curdate()) + 14 DAY " .
  "ORDER BY time_entry DESC;");
$revealstmt->execute();

Another problem is that you were trying to assign query3 to something. There was no ? placeholder in your query, thus, no need to add anything in your execute(). However, the other working solution can be not secure depending on where your $revealid comes from. You can make it secure by doing this:

$revealstmt = $conn->prepare(
  "SELECT logs.time_entry AS logs_entry, " . 
    "logs.description AS logs_description, " .
    "substates.name AS reveal_substates_name " . 
  "FROM logs LEFT OUTER JOIN applications " .
  "ON logs.fk_app_id = applications.pk_app_id " . 
  "LEFT OUTER JOIN substates ON logs.fk_substate_id = substates.pk_substate_id " .
  "WHERE logs.fk_app_id = ? AND " .
  "time_entry >= curdate() - INTERVAL DAYOFWEEK(curdate()) + 14 DAY " .
  "ORDER BY time_entry DESC;");
$revealstmt->execute(array($revealid));

Upvotes: 1

Kjell
Kjell

Reputation: 832

I believe your query prepare statement has the wrong syntax. Try this:

$revealstmt = $conn->prepare("SELECT logs.time_entry AS logs_entry, logs.description AS   logs_description, substates.name AS reveal_substates_name FROM logs LEFT OUTER JOIN applications ON logs.fk_app_id = applications.pk_app_id LEFT OUTER JOIN substates ON logs.fk_substate_id = substates.pk_substate_id WHERE logs.fk_app_id = ".$revealid." and time_entry >= curdate() - INTERVAL DAYOFWEEK(curdate()) + 14 DAY ORDER BY time_entry DESC;");

If you echo the value it should go to stdout and not to the string/query you are preparing.

Upvotes: 2

Related Questions