I’ve been going through this book and I’ve been trying to apply the lessons to a mock project just to try to solidify the concepts in my mind. I basically finished the lessons in CH4 of the book and I’m applying what I learned to a new database. This chapter is about displaying, adding and deleting content to a database using PDO’s to connect to the db and submit queries.
I can get the book lessons to work but I can’t seem to get my own project to work. The only part that doesn’t seem to work is the INSERT query. What works is I’m able to display the contents of the database, however when I submit new content it is not added to the database.
Here is the link I’m working with just so you can see what I mean:
http://www.lostdoggrafix.com/tuts/php/php-nov2ninja/games/
(its just an unformatted page, I just want to get the functionality going first and foremost)
I’m stuck and would appreciate a second set of more experienced eyes to look over what I have and maybe point me in the right direction. I’m sure its some stupid mistake but I’m still a novice at PHP so I’m having trouble locating it.
Thanks in advance for any help :]
Below is my code:
My database table:
CREATE TABLE IF NOT EXISTS `PCgames` (
`game_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'stores the primary key',
`title` varchar(75) COLLATE utf8_unicode_ci NOT NULL COMMENT 'stores the game title',
`type` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'stores the game type',
PRIMARY KEY (`game_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;
This is the controller script: index.php
<?php
//disable magic quotes at runtime
if (get_magic_quotes_gpc())
{
$process = array(&$_GET, &$_POST, &$_COOKIE, &$_REQUEST);
while (list($key, $val) = each($process))
{
foreach ($val as $k => $v)
{
unset($process[$key][$k]);
if (is_array($v))
{
$process[$key][stripslashes($k)] = $v;
$process[] = &$process[$key][stripslashes($k)];
}
else
{
$process[$key][stripslashes($k)] = stripslashes($v);
}
}
}
unset($process);
}
////////////////////////////////////////////////////////////////////////
/* If the '?addjoke' link is clicked (from the 'games.html.php' template)
then display the 'addgame.html.php' template. */
////////////////////////////////////////////////////////////////////////
if (isset($_GET['addgame'])) {
include 'addgame.html.php';
exit();
}
//connect to database
try
{
$pdo = new PDO('mysql:host=******;dbname=*****', '*****', '******');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('SET NAMES "utf8"');
}
catch (PDOException $e) //catch any error messages
{
$error = 'unable to connect to database' . $e->getMessage();
include ('error.html.php');
exit();
}
/*$output = "Connection successfully attained!";
include ('connected.html.php');*/
////////////////////////////////////////////////////////
/*Check to see if the addjoke form has been submitted*/
///////////////////////////////////////////////////////
if (isset($_GET['title']) && isset($_GET['type'])) {
try
{
//create SQL Prepared Statemnt to insert form data to the database
//this is done to protect against SQL injection attacks
$sql = 'INSERT INTO PCgames SET
title = :title,
type = :type';
$s = $pdo->prepare($sql);
$s->bindValue(':title', $_POST['title']);
$s->bindValue(':type', $_POST['type']);
$s->execute();
}
catch(PDOException $e)
{
$error = 'Error adding game to database ' . $e->getMessage();
include 'error.html.php';
}
header('location: .');
exit();
}
//create query to select the contents of the database
try
{
$sql = 'SELECT * FROM PCgames';
$results = $pdo->query($sql);
}
catch (PDOException $e)
{
$error = 'Unable to fetch data: ' . $e->getMessage();
include 'error.html.php';
exit();
}
//fetch the results of the database
while($row = $results->fetch()) {
$games[] = array( 'title'=> $row['title'], 'type'=> $row['type']);
}
include 'games.html.php';
?>
games.html.php template:
<body>
<h1>My Games</h1>
<p><a href="?addgame">Add a new Game</a></p>
<?php
foreach($games as $game) { ?>
<p>
<?php
echo htmlspecialchars($game['game_id'], ENT_QUOTES, 'UTF-8');
echo htmlspecialchars($game['title'], ENT_QUOTES, 'UTF-8');
echo htmlspecialchars($game['type'], ENT_QUOTES, 'UTF-8');
?>
</p>
<?php
}
?>
</body>
addgame.html.php
<body>
<form action="?" method="post">
<p>Add Game Title:<br />
<input type="text" id="title" name="title" /><br />
Add Game Type:<br />
<input type="text" id="type" name="type" /></p>
<p><input type="submit" id="submit" name="submit" value="Add a new Game!" /></p>
</form>
</body>