I have a simple app, the HTML is simple:
<ul>
<li><a href="create.php"><strong>Create</strong></a> - add a food by
name</li>
<li><a href="read.php"><strong>Read</strong></a> - find a food and the
nutrition values</li>
<li><a href="update.php"><strong>Create</strong></a> - update a foods
values</li>
<li><a href="delete.php"><strong>Read</strong></a> - delete an entry</li>
</ul>
I have one database named "test" and successfully created the table "foodnames", this is evident in the mySQL admin, so I won't post the sql init file.
When I try to add an item(food), i get the following error message:
INSERT INTO foodnames (foodName, calories, proteins, carbohydrates, fats) values (:foodName, :calories, :proteins, :carbohydrates, :fats) SQLSTATE[42S02]: Base table or view not found: 1146 Table 'food.foodnames' doesn't exist
Here is my create.php:
if (isset($_POST['submit']))
{
require "../config.php";
require "../common.php";
try
{
$connection = new PDO($dsn, $username, $password, $options);
$new_food = array(
"foodName" => $_POST['foodName'],
"calories" => $_POST['calories'],
"proteins" => $_POST['proteins'],
"carbohydrates" => $_POST['carbohydrates'],
"fats" => $_POST['fats']
);
$sql = sprintf(
"INSERT INTO %s (%s) values (%s)",
"foodnames",
implode(", ", array_keys($new_food)),
":" . implode(", :", array_keys($new_food))
);
$statement = $connection->prepare($sql);
$statement->execute($new_food);
}
catch(PDOException $error)
{
echo $sql . "<br>" . $error->getMessage();
}
}
?>
<?php require "templates/header.php"; ?>
<?php
if (isset($_POST['submit']) && $statement)
{ ?>
<blockquote><?php echo $_POST['foodName']; ?> successfully added.
</blockquote>
<?php
} ?>
<h2>Add a food</h2>
<form method="post">
<label for="foodName">Foods Name</label>
<input type="text" name="foodName" id="foodName">
<label for="calories">Calories per 100 grams</label>
<input type="text" name="calories" id="calories">
<label for="proteins">Proteins in %</label>
<input type="text" name="proteins" id="proteins">
<label for="carbohydrates">Carbohydrates in %</label>
<input type="text" name="carbohydrates" id="carbohydrates">
<label for="fats">Fats in %</label>
<input type="text" name="fats" id="fats">
<input type="submit" name="submit" value="Submit">
</form>
<a href="index.php">Back to home</a>
<?php require "templates/footer.php"; ?>
Why is this looking for food.foodnames? Instead of test.foodnames and what would be the proper syntax? As previously stated, there is only one test database(food db does not exist) and the table foodnames is there.
Further, when I try to read from the database, I get the following error
Notice: Undefined index: userInput in C:\xampp\htdocs\public\read.php on line 19 SELECT * FROM foodnames WHERE FoodName LIKE :user_input SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
Notice: Undefined variable: result in C:\xampp\htdocs\public\read.php on line 37 No results found for Notice: Undefined index: location in C:\xampp\htdocs\public\read.php on line 74
What I am trying there is to retrieve a database entry based on user input, even if he only inputs one character, here ist the read.php code:
<?php
/**
* Function to query information based on
* a parameter: in this case, food name.
*
*/
if (isset($_POST['submit']))
{
try
{
require "../config.php";
require "../common.php";
$connection = new PDO($dsn, $username, $password, $options);
$sql = "SELECT *
FROM foodnames
WHERE FoodName LIKE :user_input";
$userInput = $_POST['userInput'] . '%';
$statement = $connection->prepare($sql);
$statement->bindParam(':FoodName', $userInput, PDO::PARAM_STR);
$statement->execute();
$result = $statement->fetchAll();
}
catch(PDOException $error)
{
echo $sql . "<br>" . $error->getMessage();
}
}
?>
<?php require "templates/header.php"; ?>
<?php
if (isset($_POST['submit']))
{
if ($result && $statement->rowCount() > 0)
{ ?>
<h2>Results</h2>
<table>
<thead>
<tr>
<th>#</th>
<th>Food Name</th>
<th>Calories/100g</th>
<th>Proteins</th>
<th>Carbohydrates</th>
<th>Fats</th>
<th>Time to burn by running</th>
</tr>
</thead>
<tbody>
<?php
foreach ($result as $row)
{ ?>
<tr>
<td><?php echo escape($row["id"]); ?></td>
<td><?php echo escape($row["foodNAme"]); ?></td>
<td><?php echo escape($row["calories"]); ?></td>
<td><?php echo escape($row["proteins"]); ?></td>
<td><?php echo escape($row["carbohydrates"]); ?></td>
<td><?php echo escape($row["fats"]); ?></td>
<td><?php echo escape($row["ttb"]); ?> </td>
</tr>
<?php
} ?>
</tbody>
</table>
<?php
}
else
{ ?>
<blockquote>No results found for <?php echo escape($_POST['location']);
?>.</blockquote>
<?php
}
}?>
<h2>Find food by name</h2>
<form method="post">
<label for="food">Food</label>
<input type="text" id="food" name="food">
<input type="submit" name="submit" value="View Results">
</form>
<a href="index.php">Back to home</a>
<?php require "templates/footer.php"; ?>
Sorry for the very verbose code.
