I have been trying to get my to select, sort and return query from mysql data base but it keeps returning the whole table of information as opposed to the selected info that I am trying to get. I have the HTML and PHP code. Can someone please help me? Whenever I include the " :" on items in the WHERE field I get a "not defined " error such as category not defined" etc... here is the code:
<?php
if(isset($_GET['search'])) {
try {
$dsn = 'mysql:host=localhost;dbname=bajan_glasses';
$db = new PDO($dsn, 'glasses_cms', '8019');
$sql = 'SELECT id, category, style, color, material, size, price, image,
position, caption, visible
FROM eyeglasses
WHERE id LIKE id AND category = category AND style = style AND
color = color
AND material = material AND size = size AND price = price AND
image = image AND position = position
AND caption = caption AND visible = visible
ORDER BY id';
// <!--------------------- when ":" is added to the above , the table
does not display------------->
$stmt = $db->prepare($sql);
$stmt->bindValue(':id', '%' . $_GET['id'] . '%');
// $stmt->bindParam(':id', $_GET['id'], PDO::PARAM_INT);
$stmt->bindParam(':category', $_GET['category'], PDO::PARAM_STR);
$stmt->bindParam(':style', $_GET['style'], PDO::PARAM_STR);
$stmt->bindParam(':color', $_GET['color'], PDO::PARAM_STR);
$stmt->bindParam(':material', $_GET['material'], PDO::PARAM_STR);
$stmt->bindParam(':size', $_GET['size'], PDO::PARAM_INT);
$stmt->bindParam(':price', $_GET['price'], PDO::PARAM_INT);
$stmt->bindParam(':image', $_GET['image'], PDO::PARAM_INT);
$stmt->bindParam(':position', $_GET['position'], PDO::PARAM_INT);
$stmt->bindParam(':caption', $_GET['caption'], PDO::PARAM_STR);
$stmt->bindParam(':visible', $_GET['visible'], PDO::PARAM_INT);
// $stmt->execute('bindParam'); // use://if still getting token error.
$stmt->execute();
if (isset($errorInfo[2])) {
$error = $errorInfo[2];
}
} catch (Exception $e) {
$error = $e->getMessage();
}
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>PDO: SELECT Loop</title>
<link href="../../styles/styles.css" rel="stylesheet" type="text/css">
</head>
<body>
<form method="get" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<fieldset>
<p>
<label for="id">id </label>
<select name="id" id="id">
<?php
//$material = array('woo' => 'wood');
//$material = array('ace' => 'acetate');
///$material = array('pla' => 'plastic');
//$material = array('ste' => 'steel');
for ($id =0; $id <=1 ; $id++) {
echo "<option>$id</option>";
};
?>
</select>
<label for="category ">category </label>
<select name="category " id="category ">
<option> </option>
<option>men</option>
<option>women</option>
<option>children</option>
</select>
<label for="style ">style </label>
<select name="style " id="style ">
<option selected value="style">style</option>
<option>aviator</option>
<option>rectangular</option>
<option>round</option>
<option>square</option>
<option>vintage</option>
<option>black</option>
</select>
<label for="color ">color </label>
<select name="color " id="color ">
<option></option>
<option>white</option>
<option>blue</option>
<option>green</option>
<option>yellow</option>
<option>brown</option>
<option>black</option>
<option>red</option>
<option>pink</option>
<option>pink</option>
</select>
<label for="material">Material </label>
<select name="material" id="material">
<?php
//$material = array('woo' => 'wood');
//$material = array('ace' => 'acetate');
///$material = array('pla' => 'plastic');
//$material = array('ste' => 'steel');
// for ($material = 'wood'+'steel'; $material <= 'plastic'; $material++)
{
// echo "<option>$material</option>";
// };
?>
<option> </option>
<option>wood</option>
<option>acetate</option>
<option>plastic </option>
<option>steel </option>
</select>
<label for="size">size </label>
<select name="size" id="size">
<?php
$size = array('sma' => 'small');
$size = array('med' => 'medium');
$size = array('lar' => 'large');
for ($size= 'small'; $size<= 'medium'; $size++) {
echo "<option>$size</option>";
};
?>
<option> </option>
<option>small</option>
<option>medium</option>
<option>large</option>
</select>
<label for="price ">price</label>
<select name="price " id="price ">
<option selected value="price"></option>
<option>$199</option>
<option>rectangular</option>
<option>$259</option>
<option>$129</option>
<option>$111</option>
<option>$111</option>
</select>
<label for="style ">image </label>
<select name="image " id="image ">
<option ></option>
</select>
<label for="style ">position</label>
<select name="style " id="style ">
<option selected value="position"></option>
<option>1</option>
<option>2</option>
<option>3</option>
</select>
<label for="style ">caption</label>
<select name="style " id="style ">
<option selected value="style"></option>
<option>Choose from Mens styles...</option>
<option>men glasses</option>
<option>women glasses</option>
<option> children glasses...</option>
</select>
<label for="visible ">visible </label>
<select name="visible " id="visible ">
<option></option>
<option>1</option>
<option>2</option>
<option>3</option>
</select>
<input type="submit" name="search" value="Search">
</p>
</fieldset>
</form>
<?php if (isset($_GET['search'])) {
$row = $stmt->fetch();
if ($row) {
?>
<table>
<tr>
<th>id</th>
<th>category</th>
<th>style</th>
<th>color</th>
<th>material</th>
<th>size</th>
<th>price</th>
<th>image</th>
<th>position</th>
<th>caption</th>
<th>visible</th>
</tr>
<?php /******** foreach ($db->query($sql) as $row) {*********/?>
<?php do { ?>
<tr>
<td><?php echo $row['id'];?></td>
<td><?php echo $row['category'];?></td>
<td><?php echo $row['style'];?></td>
<td><?php echo $row['color'];?></td>
<td><?php echo $row['material'];?></td>
<td><?php echo $row['size'];?></td>
<td><?php echo $row['price'];?></td>
<td><?php echo $row['image'];?></td>
<td><?php echo $row['position'];?></td>
<td><?php echo $row['caption'];?></td>
<td><?php echo $row['visible'];?></td>
</tr>
<?php } while ($row = $stmt->fetch()); ?>
</table>
<?php } else {
echo '<p>No results found.</p>';
}}
} ?>
</body>
</html>
Comparisons like this will always be true:
category = category
After all, when would anything ever not equal itself. So indeed every record in the table will always match those conditions.
For all of your conditions, you want to compare the column values with parameter values. Like this:
category = :category
Repeat this for all of your parameters.
It also looks like you might be binding the :id parameter a bit differently, and I wonder if that's resulting in the errors you alluded to in a previous attempt to correct this code. This other question may be helpful for binding wildcards in PDO parameters.