PHP prepared statement not working

Question

I am making a prepared statement in PHP and my code is fine until I add in 'id' and 'key' to my parameters. They are definitely in the table that I am requesting too. What is wrong? Thanks in advance!

ERROR: Call to a member function bind_param() on boolean

    if($_POST['userx']){
 echo '<div id="div2"><div id="font2">Dashboard</div>';
$queryA = "SELECT name,profo,password,id,key FROM collegestudents WHERE email = ?";
    $stmt = $connection->prepare($queryA);
          $stmt->bind_param('s',$_POST['userx']);
     $stmt->bind_result($name1,$profo,$password1,$key,$id);
  $stmt->execute();
   $stmt->fetch();
    $stmt->close();

Can you provide detailed information about the database table? — dimlucas, Jul 24 '16 at 10:15
Which field comes first, key or id ?!?!?? (Order in SELECT is different than bind_result) — user5329483, Jul 24 '16 at 10:22
@user5329483 that is true, but this code isn't making it past the `$stmt =` line. — Devon, Jul 24 '16 at 10:24
@garethpower, the table structure. Did you try checking the `$error` property to see what it says? Basic debugging and error checking is needed in your code. Copy the query directly into mysql `SELECT name,profo,password,id,key FROM collegestudents WHERE email = ''` and there is a good chance it fails... — Devon, Jul 24 '16 at 10:26
Devon, yes it was the 'key' name in the query , how do I escape it?? — gareth power, Jul 24 '16 at 10:36

Devon · Accepted Answer · 2016-07-24T11:17:22.283

2

Key is a reserved keyword in mysql.

It's a good habit to enclose field names and table names in backticks in queries but also to check for errors.

$queryA = "SELECT `name`,`profo`,`password`,`id`,`key` FROM `collegestudents` WHERE `email` = ?";
$stmt = $connection->prepare($queryA);
if ($stmt) {
    $stmt->bind_param('s',$_POST['userx']);
    ...
}
else {
    echo "MySQL ERROR: " . $connection->error;
}

edited Jul 24 '16 at 11:17

answered Jul 24 '16 at 11:06

Devon

32,773
9
61
91

Ahmad ghoneim · Answer 2 · 2016-07-24T10:34:00.347

0

$stmt = $connection->prepare($queryA);

returns boolean(false)

make sure your query is correct

you can do a simple check like this

$stmt = $connection->prepare($queryA);
if (!$stmt) {
 echo "failed to run";
} else {
$stmt->bind_param('s',$_POST['userx']);
$stmt->bind_result($name1,$profo,$password1,$key,$id);
$stmt->execute();
$stmt->fetch();
}

Edit: if you are using PDO you were doing it wrong it should be like this

$stmt = $conn->prepare("SELECT name,profo,password,id,key FROM 
collegestudents WHERE email = :email");
    $stmt->bindParam(':email', $email);

edited Jul 24 '16 at 10:34

answered Jul 24 '16 at 10:19

Ahmad ghoneim

676
6
13

I understand that, but I am asking why the query does not work?? – gareth power Jul 24 '16 at 10:30
Okay, but for the 'key' part, can I do mysqli escape string?? – gareth power Jul 24 '16 at 10:41
no you don't need to check this answer as well http://stackoverflow.com/questions/24716560/if-i-use-mysqli-prepared-statements-do-i-need-to-escape – Ahmad ghoneim Jul 24 '16 at 10:53

Guspan Tanadi · Answer 3 · 2016-07-24T12:46:34.820

-1

Change your database connection file with

<?php $con = new PDO('mysql:host=127.0.0.1;dbname=yourdatabasename;','username',''); ?>

Then change below line

$queryA = "SELECT name,profo,password,id,key FROM collegestudents WHERE email = ?";
 $stmt = $connection->prepare($queryA);
 $stmt->bind_param('s',$_POST['userx']);
 $stmt->bind_result($name1,$profo,$password1,$key,$id);
 $stmt->execute();

with

$queryA = "SELECT name,profo,password,id,key FROM collegestudents WHERE email = :v";
 $stmt = $connection->prepare($queryA);
 $stmt->execute( array('v' => $_POST['userx']) );

edited Jul 24 '16 at 12:46

answered Jul 24 '16 at 10:25

Guspan Tanadi

180
1
12

Not how mysqli prepare works. The first argument is the data type, `s` stands for string. – Devon Jul 24 '16 at 10:27
Where do you see that? The question is tagged `mysqli`. – Devon Jul 24 '16 at 11:03
Afraid not, this is mysqli code. Check the manual if you want to read up on the functions. There is no bind_param, bind_result in PDO. – Devon Jul 24 '16 at 12:07
You know it's better to use PDO than mysqli. – Guspan Tanadi Jul 24 '16 at 12:15

PHP prepared statement not working

3 Answers3