Disclaimer: this is for an assignment. I am not asking that people give me explicit code, only for enough help so that I may see my error, correct it, and prevent similar mistakes in the future.
I'm working on an assignment that asks me to create a table in a database, populate it with a few entries, then create a form and allow users to update the database through the form (this is very basic stuff.) In addition to the updating, users should see their entries after submitting their changes.
One of the requirements is to use an auto-incrementing primary key. As this key has no real importance to the user, I see no need to make it something they'd include in their submission (and so there's no field for it.) Additionally, since it's supposed to auto-increment, giving the option of a manually-added key doesn't really make sense.
So I've put together a small set of fields and MySQL statements, all of which I believe should work. When I have a field to add a key (in this case, id_no) and I manually input a value there, the submissions are successfully stored to the database. When I remove that option, however, nothing is submitted to the database.
I should note that my primary key field id_no is marked for auto-increment, so that's not the problem. Per my understanding and what I've seen by manually inserting entities, auto-incrementing values should be updated automatically without needing to include them in any insert statements.
Here's what I've got so far (this is my first MySQL/PHP assignment, so please be gentle):
<!DOCTYPE html>
<html>
<head>
<title>Actor Database</title>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script src="//ajax.aspnetcdn.com/ajax/jquery.validate/1.11.0/jquery.validate.js"></script>
<script>
$(document).ready(function() {
$("form").validate();
});
</script>
</head>
<body>
<form method="post">
First Name: <input name="first_name" type="text" id="first" class="required" minlength="3">
<br>
Last Name: <input name="last_name" type="text" id="last" class="required" minlength="3">
<br>
<!--ID Number: <input name="id_no" type="text" id="id_num" class="required digits" maxlength="5">
<br>-->
Photo: <input name="photo" type="file" id="photo" class="required">
<br>
<input type="submit" id="submit">
</form>
<?php
$dbhost = '*******************';
$dbname = '*******************';
$dbuser = '*******************';
$dbpass = '*******************';
$mysql_handle = mysql_connect($dbhost, $dbuser, $dbpass)
or die("Error connecting to database server");
mysql_select_db($dbname, $mysql_handle)
or die("Error selecting database: $dbname");
//mysql_query("INSERT INTO Actors(first_name, last_name) VALUES('Anne', 'Hathaway')");
$id_no = array_key_exists("id_no", $_REQUEST) ? $_REQUEST["id_no"] : 0;
$first_name = array_key_exists("first_name", $_REQUEST) ? $_REQUEST["first_name"] : '';
$last_name = array_key_exists("last_name", $_REQUEST) ? $_REQUEST["last_name"] : '';
$photo = array_key_exists("photo", $_REQUEST) ? $_REQUEST["photo"] : NULL;
if ($id_no <= 0) {
echo "";
} else if ($id_no > 0) {
$rs = mysql_query("SELECT id_no FROM Actors WHERE id_no = ".$id_no);
if (mysql_numrows($rs) == 0) {
mysql_query("INSERT INTO Actors(id_no, last_name, first_name) VALUES("
. $id_no
. ",'" . mysql_real_escape_string($last_name) . "'"
. ",'" . mysql_real_escape_string($first_name) . "')"
);
} else {
mysql_query("UPDATE Actors
SET last_name = '".mysql_real_escape_string($last_name). "',
SET first_name = '".mysql_real_escape_string($first_name)."'
WHERE id_no ".$id_no
);
}
}
$results = mysql_query('SELECT id_no, last_name, first_name, photo FROM Actors');
$nrows = mysql_numrows($results);
echo "<table>";
for ($i = 0; $i < $nrows; $i++) {
echo "<tr>";
echo "<td>".htmlspecialchars(mysql_result($results, $i, "first_name")). " " .htmlspecialchars(mysql_result($results, $i, "last_name"))."</td>";
echo "</tr>";
}
echo "</table>";
mysql_close($mysql_handle);
?>
</body>
</html>
