Problems with MySQL query parameters MySQL
PROBLEM
Hey. I have the following complete code:
<?php
require("config.inc.php");
// initial query
$query = "SELECT * FROM `appdrgreenthumb_plant_species`";
// counter for the number of conditions filled by user
// useful for determining when to add "AND" keywords to the query
$numConditions = 0;
// check if any of the fields are filled
if (!isset($_POST['btnSubmit']))
{
?>
<!DOCTYPE html>
<html>
<head>
<title>Search Dr. Green Thumb Plant Infobook</title>
<style>
body {
font-family: "Courier New", sans-serif;
font-size: 1em;
}
input[type="submit"] {
font-family: "Courier New", sans-serif;
font-size: 1em;
width: 100%;
height: 1.5em;
}
.wrapper {
width: 30em;
padding: 0.5em;
margin: 0 auto;
}
legend {
font-size: 150%;
}
form {
}
.include_items {
width: 50%;
float: left;
margin: 0.5em 0 0.5em;
}
.form-group {
margin: 0.3em 0 0.3em;
}
.form-group label {
float: left;
width: 30%;
text-align: right;
margin-right: 0.1em;
}
.form-group input {
width: 68%;
}
</style>
</head>
<body>
<div class="wrapper">
<form method="post" action="dictionary_search.php">
<fieldset>
<legend>Search</legend>
<div class="form-group">
<label>Plant ID:</label>
<input type="text" name="id" value="" />
</div>
<div class="form-group">
<label>Plant Name:</label>
<input type="text" name="name" value="" />
</div>
<div class="form-group">
<label>Plant Family:</label>
<input type="text" name="family" value="" />
</div>
<div class="include_items">
<fieldset>
<input type="checkbox" name="cb_id" value="id">Plant ID<br />
<input type="checkbox" name="cb_name" value="name">Plant Name<br />
<input type="checkbox" name="cb_family" value="plant">Plant Family<br />
</fieldset>
</div>
<!-- submit button -->
<input type="submit" value="Search" name="btnSubmit" />
</fieldset>
</form>
</div>
</body>
</html>
<?php
}
else
{
if (!empty($_POST['id']) ||
!empty($_POST['name']) ||
!empty($_POST['family'])
)
{
$query = $query . " WHERE ";
// check if the ID field is filled
if (!empty($_POST['id']))
{
$numConditions++;
$query = $query . InfoBookUtilities::appendAND($numConditions) . "`id`" . " = " . ":id";
$query_params[':id'] = $_POST['id']; // this is to avoid SQL injection
}
// check if the NAME field is filled
if (!empty($_POST['name']))
{
$numConditions++;
# $query = $query . InfoBookUtilities::appendAND($numConditions) . "`name`" . " LIKE " . "'%:name%'";
# $query_params[':name'] = $_POST['name']; // this is to avoid SQL injection
}
// check if the FAMILY field is filled
if (!empty($_POST['family']))
{
$numConditions++;
# $query = $query . InfoBookUtilities::appendAND($numConditions) . "`family`" . " LIKE " . "'%:family%'";
# $query_params[':family'] = $_POST['family']; // this is to avoid SQL injection
}
# echo $query;
}
?>
<?php
// Run the query:
try
{
// These two statements run the query against your database table.
$stmt = $db->prepare($query);
$result = $stmt->execute($query_params);
}
catch (PDOException $ex)
{
$response["success"] = 0;
$response["message"] = "Database Error! <br />Exception: " . $ex->getMessage();
die(json_encode($response));
}
// finally, we can retrieve all of the found rows into an array using fetchAll
$rows = $stmt->fetchAll();
if ($rows)
{
$response["success"] = 1;
$response["message"] = "Records Available!";
$response["plants"] = array();
foreach ($rows as $row) {
$plant = array();
if (isset($_POST['cb_id']))
$plant["id"] = $row["id"];
if (isset($_POST['cb_name']))
$plant["name"] = $row["name"];
if (isset($_POST['cb_family']))
$plant["family"] = $row["family"];
if (isset($_POST['cb_price_floor']) || !empty($_POST['cb_price_ceiling']))
$plant["price"] = $row["price"];
if (isset($_POST['cb_introduction']))
$plant["introduction"] = $row["introduction"];
if (isset($_POST['cb_climate_and_soil']))
$plant["climate_and_soil"] = $row["climate_and_soil"];
if (isset($_POST['cb_temperature_min']))
$plant["temperature_min"] = $row["temperature_min"];
if (isset($_POST['cb_temperature_max']))
$plant["temperature_max"] = $row["temperature_max"];
if (isset($_POST['cb_soil_type']))
$plant["soil_type"] = $row["soil_type"];
if (isset($_POST['cb_soil_ph_min']))
$plant["soil_ph_min"] = $row["soil_ph_min"];
if (isset($_POST['cb_soil_ph_max']))
$plant["soil_ph_max"] = $row["soil_ph_max"];
if (isset($_POST['cb_recommended_varieties']))
$plant["recommended_varieties"] = $row["recommended_varieties"];
if (isset($_POST['cb_land_preparation']))
$plant["land_preparation"] = $row["land_preparation"];
if (isset($_POST['cb_crop_management']))
$plant["crop_management"] = $row["crop_management"];
if (isset($_POST['cb_care']))
$plant["care"] = $row["care"];
if (isset($_POST['cb_nutrition_management']))
$plant["nutrition_management"] = $row["nutrition_management"];
if (isset($_POST['cb_water_management']))
$plant["water_management"] = $row["water_management"];
if (isset($_POST['cb_harvest_management']))
$plant["harvest_management"] = $row["harvest_management"];
if (isset($_POST['cb_pests']))
$plant["pests"] = $row["pests"];
if (isset($_POST['cb_diseases']))
$plant["diseases"] = $row["diseases"];
if (isset($_POST['cb_date_sow_begin']))
$plant["date_sow_begin"] = $row["date_sow_begin"];
if (isset($_POST['cb_date_sow_end']))
$plant["date_sow_end"] = $row["date_sow_end"];
if (isset($_POST['cb_growth_min']))
$plant["growth_min"] = $row["growth_min"];
if (isset($_POST['cb_growth_max']))
$plant["growth_max"] = $row["growth_max"];
if (isset($_POST['cb_image_location']))
$plant["image_location"] = $row["image_location"];
// update our repsonse JSON data
array_push($response["plants"], $plant);
}
// echoing JSON response
echo json_encode($response);
}
else
{
$response["success"] = 0;
$response["message"] = "No Records Available!";
die(json_encode($response));
}
?>
<?php
}
?>
<?php
class InfoBookUtilities {
public static function appendAND ($numberOfConditions) {
if ($numberOfConditions == 1)
return "";
else
return " AND ";
}
}
?>
LONG STORY SHORT: I don't get any records even when I should when I filter the results with a textbox id
or name
.
Suppose I have this record in my table: 
And I enter the word "carrot" in the name text box, checking all my checkboxes. I guess I should get the tape. But I am getting this instead:
{"success":0,"message":"No Records Available!"}
When I receive this:
{"success":1,"message":"Records Available!","plants":[{"id":"1","name":"Carrot","family":"Umbelliferae"}]}
Another problem is that when I try to search with two text fields, I get the following error:
{"success":0,"message":"Database Error!
Exception: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens"}
I would like to think that this is really a problem with the way I added the parameters, because when I do my code like this (although this is bad practice):
// check if the NAME field is filled
if (!empty($_POST['name']))
{
$numConditions++;
# $query = $query . InfoBookUtilities::appendAND($numConditions) . "`name`" . " LIKE " . "'%:name%'";
# $query_params[':name'] = $_POST['name']; // this is to avoid SQL injection
$query = $query . InfoBookUtilities::appendAND($numConditions) . "`name`" . " LIKE " . "'%" . $_POST['name'] . "%'";
}
// check if the FAMILY field is filled
if (!empty($_POST['family']))
{
$numConditions++;
# $query = $query . InfoBookUtilities::appendAND($numConditions) . "`family`" . " LIKE " . "'%:family%'";
# $query_params[':family'] = $_POST['family']; // this is to avoid SQL injection
$query = $query . InfoBookUtilities::appendAND($numConditions) . "`family`" . " LIKE " . "'%" . $_POST['family'] . "%'";
}
I'm doing my job.
OTHER DETAILS:
I can filter the field id
just fine (using the code I pasted at the very top - yup, long).
I am customizing (setting WHERE clause conditions) my MySQL query via HTML form textbox fields. Checkboxes allow me to filter out what "attributes" I get to store on each line, which is then added up to my JSON array.
It looks like this: 
I've been looking at it for hours now and I can't figure out what happened.: /
+3
user4835507
source
to share
1 answer
By wrapping your placeholders in '%..%'
, your final value looks like '%'carrot'%'
. Instead, you want to add wildcards to your value $_POST
, i.e. "%".$_POST['name']."%"
and allow PDO to do quotes for you. So now your code will look like this:
// check if the NAME field is filled
if (!empty($_POST['name']))
{
$numConditions++;
$query = $query . InfoBookUtilities::appendAND($numConditions) . "`name`" . " LIKE " . ":name";
$query_params[':name'] = "%".$_POST['name']."%"; // this is to avoid SQL injection
}
// check if the FAMILY field is filled
if (!empty($_POST['family']))
{
$numConditions++;
$query = $query . InfoBookUtilities::appendAND($numConditions) . "`family`" . " LIKE " . ":family";
$query_params[':family'] = "%".$_POST['family']."%"; // this is to avoid SQL injection
}
0
source to share