simple thing: my code is just not working. Neither INSERT nor SELECT is working in my PDO. Probably I have something wrong, but I'm not a code master, so I need your help.
if (isset($_POST['submit']))
{
try
{
$connection = new PDO('sqlite:../tracker.db');
$name = $_POST['name'];
$unitsize = $_POST['unitsize'];
$line = $_POST['line'];
$mmr = $_POST['mmr'];
$lifespan = $_POST['lifespan'];
$connection->exec("INSERT INTO unit (name, unitsize, line, mmr, lifespan)
VALUES ('$name', '$unitsize', '$line', '$mmr', '$lifespan')");
$new_unit = "SELECT unit_id
FROM unit
ORDER BY unit_id DESC
LIMIT 1";
foreach ($connection->query($new_unit) as $row) {
$id = $row['unit_id'];
};
}
catch(PDOException $error)
{
echo $error->getMessage();
}
}
Of course I'm aware that SELECT without records can't work... But my begginer's intuition says, that it could also hava a mistake.
PS: I know, that the code may be a bit messy... sorry for your eyes bleeding :(
EDIT: WHAT I WANT TO ACHIEVE
form.$id(unit_id is AUTOINCREMENT and PRIMARY KEY)Well, from looking into your code, I could say that the error (at least one) lie in the following parts:
PDO::exec() or/and PDO::query(). You are using both functions in a proper way regarding their definitions, but maybe they returned FALSE on failure. Situation which you didn't handled at all and which is stated in the "Returned Values" parts of their corresponding docus on php.net.So, because the problem with your code was that you didn't know why it didn't work, e.g. you didn't received any error or sign of it, I thought to show you a complete way to use error reporting + prepared statements + validations + exception handling. Please note that all these four elements are mandatory if you want to code a secure and solid PDO solution. More of it, when you are applying them in a proper manner, you'll always know where a problem (or more) lies. And you'll have a lot more efficiency in code writing, because you'll not loose any more time (sometimes hours!) for finding errors.
Also, how you structure your code is up to you. I presented you here a procedural form, in which you can follow the steps with ease. A better form would be one implemented in an object oriented way.
Recommendations:
PDO::prepare() + PDOStatement::execute() instead of PDO::exec (read the "Description" from PDO::exec on php.net).PDO::prepare():If the database server cannot successfully prepare the statement, PDO::prepare() returns FALSE or emits PDOException (depending on error handling).
Feel free to ask anything if you have unclarities.
Good luck.
<?php
/*
* Try to include files using statements
* only on the top of the page.
*/
require "../config.php";
require "../common.php";
/*
* Set error reporting level and display errors on screen.
*
* =============================================================
* Put these two lines in a file to be included when you need to
* activate error reporting, e.g the display of potential errors
* on screen.
* =============================================================
* Use it ONLY ON A DEVELOPMENT SYSTEM, NEVER ON PRODUCTION !!!
* If you activate it on a live system, then the users will see
* all the errors of your system. And you don't want this !!!
* =============================================================
*/
error_reporting(E_ALL);
ini_set('display_errors', 1);
/*
* ===================================================
* Two functions used for automatically binding of the
* input parameters. They are of course not mandatory,
* e.g. you can also bind your input parameters one
* by one without using these functions. But then
* you'd have to validate the binding of each input
* parameter one by one as well.
*
* Put these two functions in a file to be included,
* if you wish.
* ===================================================
*/
/**
* Get the name of an input parameter by its key in the bindings array.
*
* @param int|string $key The key of the input parameter in the bindings array.
* @return int|string The name of the input parameter.
*/
function getInputParameterName($key) {
return is_int($key) ? ($key + 1) : (':' . ltrim($key, ':'));
}
/**
* Get the PDO::PARAM_* constant, e.g the data type of an input parameter, by its value.
*
* @param mixed $value Value of the input parameter.
* @return int The PDO::PARAM_* constant.
*/
function getInputParameterDataType($value) {
if (is_int($value)) {
$dataType = PDO::PARAM_INT;
} elseif (is_bool($value)) {
$dataType = PDO::PARAM_BOOL;
} else {
$dataType = PDO::PARAM_STR;
}
return $dataType;
}
/*
* ======================
* Hier begins your code.
* ======================
*/
try {
// Read from HTTP POST.
$name = $_POST['name'];
$unitsize = $_POST['unitsize'];
$line = $_POST['line'];
$mmr = $_POST['mmr'];
$lifespan = $_POST['lifespan'];
// Create a PDO instance as db connection to sqlite.
$connection = new PDO('sqlite:../tracker.db');
// The sql statement - it will be prepared.
$sql = 'INSERT INTO unit (
name,
unitsize,
line,
mmr,
lifespan
) VALUES (
:name,
:unitsize,
:line,
:mmr,
:lifespan
)';
// The input parameters list for the prepared sql statement.
$bindings = array(
':name' => $name,
':unitsize' => $unitsize,
':line' => $line,
':mmr' => $mmr,
':lifespan' => $lifespan,
);
// Prepare the sql statement.
$statement = $connection->prepare($sql);
// Validate the preparing of the sql statement.
if (!$statement) {
throw new UnexpectedValueException('The sql statement could not be prepared!');
}
/*
* Bind the input parameters to the prepared statement
* and validate the binding of the input parameters.
*
* =================================================================
* This part calls the two small functions from the top of the page:
* - getInputParameterName()
* - getInputParameterDataType()
* =================================================================
*/
foreach ($bindings as $key => $value) {
// Read the name of the input parameter.
$inputParameterName = getInputParameterName($key);
// Read the data type of the input parameter.
$inputParameterDataType = getInputParameterDataType($value);
// Bind the input parameter to the prepared statement.
$bound = $statement->bindValue($inputParameterName, $value, $inputParameterDataType);
// Validate the binding.
if (!$bound) {
throw new UnexpectedValueException('An input parameter could not be bound!');
}
}
// Execute the prepared statement.
$executed = $statement->execute();
// Validate the prepared statement execution.
if (!$executed) {
throw new UnexpectedValueException('The prepared statement could not be executed!');
}
/*
* Get the id of the last inserted row.
* You don't need to call a SELECT statement for it.
*/
$lastInsertId = $connection->lastInsertId();
/*
* Display results. Use it like this, instead of a simple "echo".
* In this form you can also print result arrays in an elegant
* manner (like a fetched records list).
*
* Theoretically, this statement, e.g. the presentation of results
* on screen, should happen outside this try-catch block (maybe in
* a HTML part). That way you achieve a relative "separation of
* concerns": separation of the fetching of results from the
* presentation of them on screen.
*/
echo '<pre>' . print_r($lastInsertId, TRUE) . '</pre>';
// Close the db connecion.
$connection = NULL;
} catch (PDOException $exc) {
echo '<pre>' . print_r($exc, TRUE) . '</pre>';
// echo $exc->getMessage();
// $logger->log($exc);
exit();
} catch (Exception $exc) {
echo '<pre>' . print_r($exc, TRUE) . '</pre>';
// echo $exc->getMessage();
// $logger->log($exc);
exit();
}
?>