I have a simple php page to insert data using a stored procedure. The page will run the procedure and insert data alright, but after insertion a simple select statement will fail on the php page. The select statement queries a view to show the records inserted. It will run fine in MySQL but the same statement throws up an error in php. The code is copied below and the error is something like "SQL Statement Error: SELECT * FROM CustomerInterestsView WHERE CustomerLastName = 'Doe' AND CustomerFirstName = 'John'. A review of the customer table shows that John Doe was indeed inserted.
Any help is appreciated!
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Strict//EN" "
http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>NewCustomerAndInterests PHP Page</title>
<style type="text/css">
h1 {text-align: center; color: blue}
h2 {font-family: Ariel, sans-serif; text-align: left; color: blue}
p.footer {text-align: center}
table.output {font-family: Ariel, sans-serif}
</style>
</head>
<body>
<?php
// Get connection
$dsn = 'mysql:host=xxxxx;dbname=vrg';
$username = 'xxxxx';
$password = 'xxxxx';
$PDOconnection = new PDO($dsn, $username, $password);
// Test connection
if (!$PDOconnection)
{
exit ("ODBC Connection Failed: " . $PDOconnection);
}
// Create short variable names
$LastName = $_POST["LastName"];
$FirstName = $_POST["FirstName"];
$EmailAddress = $_POST["EmailAddress"];
$AreaCode = $_POST["AreaCode"];
$PhoneNumber = $_POST["PhoneNumber"];
$Nationality = $_POST["Nationality"];
// Create SQL statement to call the Stored Procedure
$SQLSP = "CALL InsertCustomerAndInterests (";
$SQLSP .= "'$LastName', '$FirstName', '$EmailAddress', ";
$SQLSP .= "'$AreaCode', '$PhoneNumber', ";
$SQLSP .= "'$Nationality')";
// Execute SQL statement
$Result = $PDOconnection->query($SQLSP);
// Test existence of $Result
if (!$Result)
{
exit ("SQL Statement Error: " . $SQLSP);
}
// Create SQL statement to retrieve additions to CUSTOMER_ARTIST_INT table
$SQL = "SELECT * FROM CustomerInterestsView ";
$SQL .= "WHERE CustomerLastName = '$LastName' ";
$SQL .= "AND CustomerFirstName = '$FirstName'";
// Execute SQL statement
$RecordSet = $PDOconnection->query($SQL);
// Test existence of $RecordSet
if (!$RecordSet)
{
exit ("SQL Statement Error: " . $SQL);
}
echo "<h1>
The View Ridge Gallery CUSTOMER Table
</h1>
<hr />";
echo "<h2>
New Customer and Artist Interests Added:
</h2>
<table>
<tr>";
echo "<td>Last Name:</td>";
echo "<td>" . $LastName . "</td>";
echo "</tr>";
echo "<tr>";
echo "<td>First Name:</td>";
echo "<td>" . $FirstName . "</td>";
echo "</tr>";
echo "<tr>";
echo "<td>Email Address:</td>";
echo "<td>" . $EmailAddress . "</td>";
echo "</tr>";
echo "<tr>";
echo "<td>Area Code:</td>";
echo "<td>" . $AreaCode . "</td>";
echo "</tr>";
echo "<tr>";
echo "<td>Phone Number:</td>";
echo "<td>" . $PhoneNumber . "</td>";
echo "</tr>";
echo "<tr>";
echo "<td>Artist Nationality:</td>";
echo "<td>" . $Nationality . "</td>";
echo "</tr>";
echo "</table><br /><hr />";
// Table headers
echo "<table class='output' border='1'>
<tr>
<th>CustomerLastName</th>
<th>CustomerFirstName</th>
<th>ArtistName</th>
</tr>";
// Table data
while($RecordSetRow = $RecordSet->fetch())
{
echo "<tr>";
echo "<td>" . $RecordSetRow['CustomerLastName'] . "</td>";
echo "<td>" . $RecordSetRow['CustomerFirstName'] . "</td>";
echo "<td>" . $RecordSetRow['ArtistName'] . "</td>";
echo "</tr>";
}
echo "</table>";
// Close connection
$PDOconnection = null;
?>
<br />
<hr />
<p class="footer">
<a href="../VRG/index.html">
Return to View Ridge Gallery Home Page
</a>
</p>
<hr />
</body>
</html>