Help with PHP and SQL coding

mikebowyer3 · 08-19-2012, 08:37 PM

Hi All, hope someone can help me, I have a basic site which records returns from sales on amazon. I have the mysql table setup correctly, however the php and html code are not working. Here is the code:

HTML:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=windows-1250">
<meta name="generator" content="PSPad editor, www.pspad.com">
<title>Amazon Returns Logger</title>
<style type="text/css">
table tr > td { text-align: right; }
table tr > td + td { text-align: Left; }
</style>
</head>
<body>
<form action="amazonprocess.php" method="post">
<table>
<tr>
<td>
User:
</td>
<td>
<input type="text" id="User" name="User" />
</td>
</tr>
<tr>
<td>
Order Number:
</td>
<td>
<input type="text" id="OrderNumber" name="OrderNumber" />
</td>
</tr>
<tr>
<td>
Reason For Return:
</td>
<td>
<select id="ReasonForReturn" name="ReasonForReturn">
<option value="">- Choose -</option>
<option value="Change of mind">Change of mind</option>
<option value="Damaged in transit">Damaged in transit</option>
<option value="Lost in transit">Lost in transit</option>
<option value="Listing error - Missing parts">Listing error - Missing parts</option>
<option value="Listing error - Incorrect product">Listing error - Incorrect product</option>
<option value="Listing error - Returns note included">Listing error - Returns note included</option>
<option value="Listing error - Wrong condition">Listing error - Wrong condition</option>
<option value="Testing error - Faulty item">Testing error - Faulty item</option>
</select>
</td>
</tr>
<tr>
<td>
Outcome:
</td>
<td>
<select id="RefundReplacement" name="RefundReplacement">
<option value="">- Choose -</option>
<option value="Refunded">Refunded</option>
<option value="Replaced">Replaced</option>
</select>
</td>
</tr>
<tr>
<td>
If Refunded:
</td>
<td>
<select id="FullPartial" name="FullPartial">
<option value="">- Choose -</option>
<option value="Full">Full Refund</option>
<option value="Partial">Partial Refund</option>
</select>
</td>
</tr>
<tr>
<td>
Refund Amount:
</td>
<td>
<input type="text" id="RefundAmount" name="RefundAmount" />
</td>
</tr>
<tr>
<td>
Date Refunded (DDMMYYYY):
</td>
<td>
<input type="text" id="DateRefunded" name="DateRefunded" />
</td>
</tr>
<tr>
<td>
Was Product Returned?:
</td>
<td>
<select id="ProductReturned" name="ProductReturned">
<option value="">- Choose -</option>
<option value="Customer Disposed">Customer kept product</option>
<option value="Returned - Collection">Returned - Collection Arranged</option>
<option value="Returned - Customer Returned">Returned - Customer Returned</option>
</select>
</td>
</tr>
<tr>
<td colspan="2" style="text-align: center;">
<input type="submit" id="submit" value="Submit Return" />
<input type="reset" id="reset" value="Start Over!" />
</td>
</tr>
</table>
</form>
</body>
</html>

PHP:
<?php
$User = $_POST["User"];
$OrderNumber = $_POST["OrderNumber"];
$ReasonForReturn = $_POST["ReasonForReturn"];
$RefundReplacement = $_POST["RefundReplacement"];
$FullPartial = $_POST["FullPartial"];
$RefundAmount = $_POST["RefundAmount"];
$DateRefunded = $_POST["DateRefunded"];
$ProductReturned = $_POST["ProductReturned"];

$sql_connection = mysql_connect("localhost", "root", "") or die("connection failed");

mysql_select_db("customerservice", $sql_connection) or die("Select db failed");

$sql = "INSERT INTO amazon (
User,
OrderNumber,
ReasonForReturn,
RefundReplacement,
FullPartial,
RefundAmount,
DateRefunded,
ProductReturned
)
VALUES (
'$User',
'$OrderNumber',
'$ReasonForReturn',
'$RefundReplacement',
'$FullPartial',
'$RefundAmount',
'$DateRefunded',
'$ProductReturned',
NOW()
)";

mysql_query($sql, $sql_connection) or die("Query Failed");

mysql_close($sql_connection) or die ("Close Failed");
?>

When I run the code, I am getting the error Query Failed.
Any help will be greatly appreciated.

Mike

tangoforce · 08-19-2012, 08:48 PM

Take a look at my signature and the first line about [php] tags.

Yes, it really does apply to you too!

Secondly, promt the value of mysql_error() to find out what the SQL server said was wrong instead of using die()

mikebowyer3 · 08-19-2012, 09:01 PM

Sorry about that, first time in the forum and didn't read the posting guidelines as needed a quick answer. I changed the die to mysql_error() and no error pops up now, but it still doesn't add the data to the mysql database. Cheers

AndrewGSW · 08-19-2012, 09:10 PM

If you print out the sql statement just before you execute the query, then you should copy and run this sql statement in phpMyAdmin to check it.

Your sql statement, though, names eight fields but has nine values.

mikebowyer3 · 08-19-2012, 09:13 PM

Sorry my mistake, I changed the die statement for the error statement instead of adding it. The error is "Column count doesn't match value count at row 1". Here is a print screen of the table in phpmyadmin:

tangoforce · 08-19-2012, 09:29 PM

As Andrew says, you have specified 8 columnns but supplied 9 values. Thats what your error message is also telling you.

You need to look at your columns and values and work out what you've done wrong. As a temporary measure, delete the 'NOW()' bit from the SQL and see if it then works.

mikebowyer3 · 08-19-2012, 09:35 PM

Thanks Andrew/tangoforce. I deleted the NOW() function and it worked. I am not 100% sure why the now statement was stopping it. Could you possibly explain why it was wrong to use the now statement? Thanks

tangoforce · 08-19-2012, 09:53 PM

Quote:

Originally Posted by mikebowyer3

Thanks Andrew/tangoforce. I deleted the NOW() function and it worked. I am not 100% sure why the now statement was stopping it. Could you possibly explain why it was wrong to use the now statement? Thanks

We've already told you.

Your SQL statement was telling mysql to insert data into 8 columns yet you were supplying 9 pieces of data. The mysql NOW() function becomes a time value which counts as a 9th parameter.

Where is mysql supposed to store than when you have not specified a 9th column?

mikebowyer3 · 08-19-2012, 10:42 PM

Thanks for that, for some reason I had it in my mind that the NOW() statement meant it would insert the data Now... Lack of sleep. Thanks for the help anyway.

AndrewGSW · 08-19-2012, 11:31 PM

Quote:

the NOW() statement meant it would insert the data Now

Well, you made me smile

mikebowyer3 · 08-20-2012, 01:18 AM

Glad I made someone smile lol

tangoforce · 08-20-2012, 09:41 AM

Quote:

Originally Posted by mikebowyer3

Thanks for that, for some reason I had it in my mind that the NOW() statement meant it would insert the data Now... Lack of sleep. Thanks for the help anyway.

Sadly no, mysql simply thinks that you've supplied more data to be stored in a column. Because you've not told it what column it should go in it will give you an error instead