I'm currently learning PHP, SQL/MySQL and HTML to develop a web application project (I have very little practical experience in all of these).
To get started, I spent far too long and many questions on Stack Overflow writing a web application which inserts records into my database for Game of Thrones characters. The web app uses a form, which POSTs to a PHP script, which uses PDO to interact with MySQL. I've done nothing to prevent SQL injection - although because this is a private project, so I'm not concerned about security in this instance.
I'd appreciate input on whether I'm using any bad practices, or if there are better/more efficient ways of doing what I'm doing. I plan to use a similar construct to get me started in my other web app project, so - apart from the SQL security weakness that I've acknowledged - if there are any other ways in which code like this would be unsuitable for a public domain, I'd like to know that as well.
asoiaf.php
<html>
<body>
<form action="addchar.php" method="post">
<select name="bookIntroduced">
<option value="1">A Game of Thrones</option>
<option value="2">A Clash of Kings</option>
<option value="3">A Storm of Swords</option>
<option value="4">A Feast for Crows</option>
<option value="5">A Dance with Dragons</option>
</select>
<p>
Page introduced:<br>
<input type="text" name="pageIntroduced" tabindex=1 autofocus>
<br>Title:<br>
<input type="text" name="title" tabindex=2>
<br>First name<br>
<input type="text" name="forename" tabindex=3>
<br>Surname<br>
<input type="text" name="surname" tabindex=4>
<br>Old surname<br>
<input type="text" name="oldSurname" tabindex=5>
<br>Alias or nickname<br>
<input type="text" name="alias" tabindex=6>
<br>Regnal number<br>
<input type="text" name="regnalNumber" tabindex=7>
<br>
<input type="submit" value="Add character" tabindex=8>
</p>
</form>
<?php
// Displays the message returned from the PHP script.
if ($_GET['msg']) {
echo "<br>".$_GET['msg'];
}
?>
</body>
</html>
addchar.php
<?php
if ($_POST) {
// Configuration.
$username = "root";
$password = "root";
$hostname = "localhost";
$dbname = "asoiaf";
$tablename = "charlist";
// Opens a connection to the database.
try {
$conn = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
// I don't know what this does; a
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
echo $e->getmessage();
}
// Gets the next available primary key from the table.
$qry = $conn->query("SELECT Auto_Increment FROM information_schema.tables WHERE table_name='$tablename'");
// Fetches the result of the query, stores it in $result.
$result = $qry->fetch();
// Puts the resulting primary key into $id.
$id = $result['Auto_Increment'];
// Fetches all the other information from the form.
$bookIntroduced = $_POST['bookIntroduced'];
$pageIntroduced = $_POST['pageIntroduced'];
$forename = $_POST['forename'];
$surname = $_POST['surname'];
$oldSurname = $_POST['oldSurname'];
$alias = $_POST['alias'];
$title = $_POST['title'];
$regnalNumber = $_POST['regnalNumber'];
// Queries the table to see if a record exists with the same forename and surname values.
$qry = $conn->query("SELECT forename, surname FROM charlist WHERE forename='$forename' AND surname='$surname'");
$result = $qry->fetch();
// If a record with the same forename/surname exists, the entry is a duplicate entry and should be disallowed.
if ($result[0]==$forename && $result[1]=$surname) {
// Return to the original page reporting a duplicate error.
header('Location: asoiaf.php?msg=duplicate error');
} else {
// Prepare the SQL statement.
$sql = "INSERT INTO $tablename (id, bookIntroduced, pageIntroduced, forename, surname, oldSurname, alias, title, regnalNumber)
VALUES (:id, :bookIntroduced, :pageIntroduced, :forename, :surname, :oldSurname, :alias, :title, :regnalNumber)";
$q = $conn -> prepare($sql);
// Executes the SQL.
$q -> execute(array(':id' => $id, ':bookIntroduced' => $bookIntroduced, ':pageIntroduced' => $pageIntroduced, ':forename' => $forename, ':surname' => $surname, ':oldSurname' => $oldSurname, ':alias' => $alias, ':title' => $title, ':regnalNumber' => $regnalNumber));
// Closes the PDO connection.
$conn = null;
// Returns to the original HTML page.
header('Location: asoiaf.php?msg=success');
}
}
?>
These are two separate screenshots of the web form spliced together: