Pdo fatal error

Im getting this fatal error below with the code below that -

Fatal error: Uncaught exception ‘PDOException’ with message 'SQLSTATE[HY000]: General error: 2031 ’ in \CSFFILES11\WEBSITES\mysite.php:97 Stack trace: #0 \CSFFILES11\WEBSITES\mysite.php(97): PDOStatement->execute() #1 {main} thrown in \CSFFILES11\WEBSITES\mysite.php on line 97

if ($_POST["id3"]=="country") {
$area = $_POST["id3"];
$queryAll="select count(*) from tbl_hotels LEFT JOIN tbl_countries ON (tbl_countries.Id_Cntry=tbl_hotels.IdCntry_Hot) WHERE tbl_countries.Nom_Cntry like ? AND TourCheckActive=1 AND $order3 $order2";	
}

$stmt = $pdo->prepare($queryAll);
$stmt->execute(); 

The last line above is line 97

you forgot to bind your placeholder.

cf. https://dev.mysql.com/doc/refman/5.7/en/error-messages-client.html

Error: 2031 (CR_PARAMS_NOT_BOUND)

Message: No data supplied for parameters in prepared statement

Umm do you mean these, I have these set above the if statement

if($order2=="ORDER BY Id_Hot ASC"){
	$order3 = "(tbl_hotels.Id_Hot > ?)";
} else {
	$order3 = "(tbl_hotels.Id_Hot < ?)";
}

and since you don’t provide the statement with their respective values, it fails.

You are not binding placeholders, to do use bindParam() method.

And also on last part of WHERE clause you have missing operator.

this part AND $order3 $order2 - consider replacing variables with placeholder and bind them too

MySQL might also be complaining about the brackets around that

Morning guys,

I have taken it on, the fatal error message has changed, so will show what I got and then show the error.

if(!empty($_POST["id"]) && !empty($_POST["id2"]) && !empty($_POST["id3"])) {
if(!empty($_POST["id6"])){
	$order2 = $_POST["id6"];
} else {
	$order2 = "ORDER BY Id_Hot DESC";
}
if($order2=="ORDER BY Id_Hot ASC"){
	$order3 = "(tbl_hotels.Id_Hot > ?)";
} else {
	$order3 = "(tbl_hotels.Id_Hot < ?)";
}
if ($_POST["id3"]=="country") {
$area = $_POST["id3"];
$queryAll="select count(*) from tbl_hotels LEFT JOIN tbl_countries ON tbl_countries.Id_Cntry=tbl_hotels.IdCntry_Hot WHERE tbl_countries.Nom_Cntry like ? AND TourCheckActive=1 AND :order3 :order2";	
}
if ($_POST["id3"]=="region") {
$area = $_POST["id3"];
$queryAll="select count(*) from tbl_hotels LEFT JOIN tbl_resorts ON tbl_resorts.Id_Rsrt=tbl_hotels.IdRsrt_Hot WHERE tbl_resorts.Nom_Rsrt like ? AND TourCheckActive='1' AND :order3 :order2";
}
if($_POST["id3"]=="hotel") {
$area = $_POST["id3"];
$queryAll="select count(*) from tbl_hotels WHERE Nom_Hot like ? AND TourCheckActive=1 AND :order3 :order2";	
}
	$stmt = $pdo->prepare($queryAll);
	$stmt->bindParam(":order3", $order3);
	$stmt->bindParam(":order2", $order2);
	$stmt->execute();

The error is -
Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters’ in \CSFFILES11\WEBSITES\mysite.php:96 Stack trace: #0 \CSFFILES11\WEBSITES\mysite.php(96): PDO->prepare(‘select count(*)…’) #1 {main} thrown in \CSFFILES11\WEBSITES\mysite.php on line 96

And line 96 applies to

$stmt->bindParam(":order3", $order3);

obviously you can’t mix positional and named parameters. Makes sense if you ask me (and it’s also mentioned in the manual).

Can you use prepared statements and parameters in that way? I’m sure I’ve read that you cannot use bound parameters to specify column names as you do:

$queryAll="select count(*) from tbl_hotels LEFT JOIN tbl_resorts ON tbl_resorts.Id_Rsrt=tbl_hotels.IdRsrt_Hot WHERE tbl_resorts.Nom_Rsrt like ? AND TourCheckActive='1' AND :order3 :order2";

You can only use a parameter in certain places, and I think this is the bit of the doc that says so:

So I don’t think you can include column names, for example, like this

$order3 = "(tbl_hotels.Id_Hot < ?)";

Prepared statements aren’t just an alternative means of sticking a string inside a query and dealing with quotes, you can’t just stick any part of the query inside a parameter. In your example above, you’d need to do something like:

$order3 = "(tbl_hotels.Id_Hot < :something)";
...
// and then later
if ($order3 != "") { 
$queryAll .= $order3;
}
// and then later
$stmt->bindParam(":something", $hotelid);

with whatever values you want to use. On that, even if you could use bindParam() to add the entire conditional clause, and mix named and un-named placeholders, I don’t see where you provide the value to go in place of the ?.

2 Likes

No I think your right droopsnoot, I have scrapped it and gone back to the drawing board. I think I have over cooked it, and sort of lost my way a bit. So Ive gone back to the start and work my way back to it using your advice.

Thanks again for coming back

If you can afford the small overhead, a query builder (e.g. DBAL) helps a lot when creating dynamically composed queries (because you can add any query part independently).

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.