MySQL Forums
Forum List  »  PHP

SQL error on php page
Posted by: Mark Hwang
Date: March 14, 2018 12:22PM

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>

Options: ReplyQuote


Subject
Written By
Posted
SQL error on php page
March 14, 2018 12:22PM
March 14, 2018 03:39PM
March 15, 2018 05:40PM
March 15, 2018 07:33PM
March 16, 2018 07:14PM
March 16, 2018 07:19PM
March 17, 2018 09:08AM
March 17, 2018 10:41AM
March 18, 2018 12:10PM
March 18, 2018 06:20PM
May 09, 2018 03:33AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.