I'm new to PHP and MySQL, and I am trying to build a basic script that takes comments from users with an HTML form, inserts that data into a MySQL database using a PDO, and finally posts that data into an HTML page for users to view. My problem with my two scripts is that despite a successful database connection and passing of variables in a prepared statement, the script executes without an error but does not actually insert the data into the database. At first, I thought that the queries weren't taking the actual values of my variables and were just posting $name, etc, so I escaped all my variables within my prepared statements but unfortunately, I am still having the same issue. I'm not sure what exactly is wrong with the script at this point because from my limited knowledge it appears to be running correctly and the data is just getting lost in transit. I will provide the two scripts I am using below, the first script is to take user input, validate it, and insert it into the database. The second script is to take that data and put it on a page for users to view.
<?php
$title = $_POST['title'];
$name = $_POST['name'];
$comments = $_POST['comments'];
if (!preg_match ("/^[a-zA-Z0-9\s\.\!\&\;]*$/", $name)) {$Namepattern = "false";}
else {$Namepattern= "true";}
if (!preg_match ("/^[a-zA-Z0-9\s\.\!\&\;]*$/", $title)) {$Titlepattern = "false";}
else {$Titlepattern= "true";}
if (!preg_match ("/^[a-zA-Z0-9\s\.\!\&\;]*$/", $comments)) {$Commentpattern = "false";}
else {$Commentpattern= "true";}
if ($Namepattern == "false") {
die("Invalid entries! Please enter only alphanumeric characters and punctuation.");}
elseif ($Titlepattern == "false") {
die("Invalid entries! Please enter only alphanumeric characters and punctuation.");}
elseif ($Commentpattern == "false") {
die("Invalid entries! Please enter only alphanumeric characters and punctuation.");}
else {
try{
$db = new PDO("mysql:host=localhost;dbname=dbname", "username", "password");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "database connection successful. Your responses were recorded! <a href='Employees.php'>
Click here to return to the comments page!";
$db->getAttribute(constant("PDO::ATTR_CONNECTION_STATUS"));
}
catch(PDOException $error) {
die("ERROR: Connection unsuccessful." . $error->getMessage());
}
$query = "INSERT INTO COMMENTS (Name, Title, Comments, Commentdate)
VALUES('".$name."', '".$title."', '".$comments."', NOW())";
$prepared = $db->prepare($query);
$prepared->execute();
}
?>
Here is the second script
<?php
echo "<table style=’border: solid 1px black;’>";
echo "<center> <tr><th>Name</th><th>Title</th><th>Comments</th><th>Commentdate</th></tr>
</center>";class TableRows extends RecursiveIteratorIterator {
function __construct($it) {
parent::__construct($it, self::LEAVES_ONLY);
}
function current() {
return "<td style='width:150px;border:1px solid black;'>" . parent::current(). "</td>";
}
function begindata() {
echo "<tr>";
}
function enddata() {
echo "</tr>" . "\n";
}
}
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "dbname";
try {
$pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$prepared = $pdo->prepare("SELECT Name, Title, Comments, Commentdate FROM Comments");
$prepared->execute();
$result = $prepared->setFetchMode(PDO::FETCH_ASSOC);
foreach(new TableRows(new RecursiveArrayIterator($prepared->fetchAll())) as $k=>$v) {
echo $v;
}
}
catch(PDOException $e) {
echo "Data error: " . $e->getMessage();
}
$pdo = null;
echo "</table>";
?>