Inserting into MySQL Newbie

skoobi · October 20, 2009

Hi ive got a slight problem where ive made a simple web form where the customer inserts the ammount of tickets and then enters their personal details... then this form does the post method and it then comes up with the confirmation page with all the calculations and how much it is going to cost including postage... All that works fine... When i press the send button to send it to the database and give a message 'order recieved' i get this error...

'Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order (qty_child,qty_adult,adult_cost,child_cost, postage,c_name, h_name, town, ' at line 1'

Right heres the code for the First page

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Buy your tickets</title>
</head>

<body>
<form action="includes/confirm.php" method="post"> 
Adult Ticket: 
<br />
Quantity:<input type="text" size="5" name="qty_adult" id="Adult Ticket" /> <br /><br />

<div>
Child Ticket: 
<br />
Quantity:<input type="text" size="5" name="qty_child" id="Child Ticket" /> <br /><br />
</div>

<div>
Customer Details: 
<br />
Name:<br /><input type="text" size="50" name="c_name" id="Customer Name" /> <br /><br />
House Name / Number:<br /><input type="text" size="50" name="h_name" id="House Name" /> <br /><br />
Town:<br /><input type="text" size="50" name="town" id="Town Name" /> <br /><br />
County:<br /><input type="text" size="50" name="county" id="County" /> <br /><br />
Post Code:<br /><input type="text" size="50" name="p_code" id="Post Code" /> <br /><br />
Email Address:<br /><input type="text" size="50" name="email" id="Email" /> <br /><br />
Phone Number:<br /><input type="text" size="50" name="p_num" id="Phone Number" /> <br /><br />
</div>

<input type="submit" />
</form>
</body>
</html>

Heres my confirmation page:

<title>Order Confirmation</title>
<form action="send.php" method="post"> 
<?php
include("helper.php");

/* DISPLAY THE OUTPUT
   ======= === ======*/

# Display Adult Order
if ($qty_adult > 0 )
{
echo "You ordered ". $qty_adult . " adult tickets.<br />";
echo "The cost of the tickets is £" .number_format ($calcItem,2) . "<br />";
echo "and the postage is £" .number_format ($calcPost,2) . "<br /><br />";

if ($qty_child == 0) 
{
	echo "Which gives you a total of £" .number_format ($calcTotal,2) . "<br /><br /> ";
}
}


# Display Child Order
if ($qty_child > 0 )
{
echo "You ordered ". $qty_child . " child tickets.<br />";
echo "The cost of the tickets is £".number_format ($calcChild,2) . "<br />";
echo "and the postage is £".number_format ($calcChildPost,2) ."<br /><br/>";

echo "Which gives you a total of £" .number_format ($calcAll,2) . "<br /> ";
}

# If nothing is ordered

if ($qty_child && $qty_adult = 0)
{
echo "Please choose the ammount of tickets you require before carrying on";
}

echo "<br/>";
echo "Name : " .$c_name ;
echo "<br/>";
echo "House name / Number: " .$h_name ;
echo "<br/>";
echo "Town: " .$town ;
echo "<br/>";
echo "County: " .$county ;
echo "<br/>";
echo "Post Code: " .$p_code ;
echo "<br/>";
echo "Email: " .$email ;
echo "<br/>";
echo "Phone Number: " .$p_num ;
echo "<br/>";
echo "<br/>";
?>
<input type="submit" />

</form>

Heres my helper file with all the calculations and what not:

<?php
# Calculation VARS
$qty_adult = $_POST['qty_adult'];
$qty_child = $_POST['qty_child'];
$a_ticket = $_POST['a_ticket'];
$c_ticket = $_POST['c_ticket'];
$a_price = 25;
$c_price = 0;
$p_price = 1.50;
$calcItem = totalItem($qty_adult, $a_price);
$calcPost = totalPost($qty_adult, $p_price);
$calcTotal = total($calcItem, $calcPost);
$calcChildPost = totalChildPost ($qty_child, $p_price);
$calcAll = totalAll ($calcItem,$calcChildPost,$calcPost);
$calcChild = totalChild ($qty_child, $c_price);

# Customer Detail Input VARS
$c_name = $_POST['c_name'];
$h_name = $_POST['h_name'];
$town = $_POST['town'];
$county = $_POST['county'];
$p_code = $_POST['p_code'];
$email = $_POST['email'];
$p_num = $_POST['p_num'];


/* FUNCTIONS
   =========*/

# Calculates the Item total for the adults
function totalItem($price, $qty)
{
    $totalItem = ($price * $qty);
    return $totalItem; 
}

# Calculates the Postage total for the adults
function totalPost($postage, $qty)
{
    $totalPost = ($postage * $qty);
    return $totalPost; 
}

# Calculates the Order Total for the adults
function total($item, $post)
{
    $total = ($item + $post);
    return $total; 
}

# Calculates the Order Total for childrens
function totalChildPost($childQty, $childPost)
{
    $totalChildPost = ($childQty * $childPost);
    return $totalChildPost; 
}

# Calculates the Order Total for childrens
function totalChild($childQty, $childCost)
{
    $totalChild = ($childQty * $childCost);
    return $totalChild; 
}

# Calculates the Order Total
function totalAll($adult, $child, $postA)
{
    $totalAll = ($adult + $child + $postA );
    return $totalAll; 
}
?>

And lastly and the most problematic is the sql :

<?php
include("helper.php");
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }


mysql_select_db("tickets", $con);

# Order
$sql=
"INSERT INTO order (qty_child,qty_adult,adult_cost,child_cost, postage,c_name, h_name, town, county, p_code, email, p_num) 
VALUES 
('$_POST[qty_child]','$_POST[qty_adult]','$_POST[calcItem]','$_POST[calcChild]','$_POST[calcAll]','$_POST[c_name]', '$_POST[h_name]', '$_POST[town]', '$_POST[county]', '$_POST[p_code]', '$_POST[email]', '$_POST[p_num])";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Order Recieved";

mysql_close($con)
?>

Any help would be greatfull... Or if someone could point me in the right direction that would be great....

Thanks in advance

Chris

Calver · October 20, 2009

Thdere's a single quote missing from near the end of the SQL - '$_POST[p_num] ...

skoobi · October 20, 2009

Well spotted... i think thats from me pottering around so much with different variations... Unfortunetly i still get an error whith that file...

'Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order (qty_child,qty_adult,adult_cost,child_cost, postage,c_name, h_name, town, ' at line 1'

Im stumped... I know im no php gurru but i cant see what ive done wrong and unfortunetly its probably going to be the most simple thing....

Thanks anyway...

redarrow · October 20, 2009

you need to learn, mysql_real_escape_string() added,

try this please...

<?php
//database connection.

mysql_select_db("tickets", $con);

# Order
$sql="INSERT INTO order
(qty_child,qty_adult,adult_cost,child_cost, postage,c_name,
h_name, town, county, p_code, email, p_num)VALUES(
".mysql_real_escape_string($_POST['qty_child']).",
".mysql_real_escape_string($_POST['qty_adult']).",
".mysql_real_escape_string($_POST['calcItem']).",
".mysql_real_escape_string($_POST['calcChild']).",
".mysql_real_escape_string($_POST['calcAll']).",
".mysql_real_escape_string($_POST['c_name']).",
  ".mysql_real_escape_string($_POST['h_name']).",
  ".mysql_real_escape_string($_POST['town']).",
   ".mysql_real_escape_string($_POST['county']).",
   ".mysql_real_escape_string($_POST['p_code']).",
   ".mysql_real_escape_string($_POST['email']).",
  ".mysql_real_escape_string($_POST['p_num'])." ";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Order Recieved";

mysql_close($con);

?>

skoobi · October 20, 2009

Nope still the same error... But thank you for pointing the mysql_real_escape_string out im going to look that up to find out a bit more about it...

I have just thaught tho...

In the database there is the order_id... do i need to do anythign with that

redarrow · October 20, 2009

make sure that all the variable names, are the same as what your

posting to the database please.

don't sound right?

error?

skoobi · October 20, 2009

Ye everythings right!!! :confused: ....

The error is

'Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order (qty_adult,qty_child,adult_cost,child_cost,postage,c_name,h_name,town,coun' at line 1'

Calver · October 20, 2009

I think you need a closing ')' for the VALUES section in redarrow's code.

PFMaBiSmAd · October 20, 2009

The leading part of the query that is printed in the error message is the point where mysql could not figure out what you mean. In this case 'order' is a reserved keyword and was encountered in your query where it could not normally exist -

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

You should rename your table to something else.

redarrow · October 20, 2009

see how redarrow got blamed lol,

and it wasn't me .

skoobi · October 20, 2009

Exellent im getting a little bit further now... that worked but im getting a new error now...

'Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' , , , , , , , , , ,' at line 2'

And the code is:

<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }


mysql_select_db("tickets", $con);


# Order
$sql="INSERT INTO cust_order (qty_adult,qty_child,adult_cost,child_cost,postage,c_name,h_name,town,county,p_code,email,p_num)VALUES(
".mysql_real_escape_string($_POST['qty_adult']).",
".mysql_real_escape_string($_POST['qty_child']).",
".mysql_real_escape_string($_POST['calcItem']).",
".mysql_real_escape_string($_POST['calcChild']).",
".mysql_real_escape_string($_POST['calcAll']).",
".mysql_real_escape_string($_POST['c_name']).",
".mysql_real_escape_string($_POST['h_name']).",
".mysql_real_escape_string($_POST['town']).",
".mysql_real_escape_string($_POST['county']).",
".mysql_real_escape_string($_POST['p_code']).",
".mysql_real_escape_string($_POST['email']).",
".mysql_real_escape_string($_POST['p_num']).")";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Order Recieved";

mysql_close($con);

?>

Thank you for everybodys help so far...

skoobi · October 20, 2009

Right ive got a little bit further now.....

Im only getting this error now...

'Parse error: syntax error, unexpected ';' in /home/skoobi/public_html/projects/cart/includes/send.php on line 27'

$sql="INSERT INTO cust_order (order_id,qty_adult,qty_child,adult_cost,child_cost,postage,c_name,h_name,town,county,p_code,email,p_num)
VALUES
(
".mysql_real_escape_string($_POST['qty_adult'].",
".mysql_real_escape_string($_POST['qty_child'].",
".mysql_real_escape_string($_POST['calcItem']).",
".mysql_real_escape_string($_POST['calcChild']).",
".mysql_real_escape_string($_POST['calcAll']).",
".mysql_real_escape_string($_POST['c_name']).",
".mysql_real_escape_string($_POST['h_name']).",
".mysql_real_escape_string($_POST['town']).",
".mysql_real_escape_string($_POST['county']).",
".mysql_real_escape_string($_POST['p_code']).",
".mysql_real_escape_string($_POST['email']).",
".mysql_real_escape_string($_POST['p_num']).")";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Order Recieved";

mysql_close($con);

Calver · October 20, 2009

Just a couple of closing parantheses missing, I think ...

".mysql_real_escape_string($_POST['qty_adult']).",
".mysql_real_escape_string($_POST['qty_child']).",

PFMaBiSmAd · October 20, 2009

Once you fix that php syntax error, you will be back at the code in reply #10, which is missing the single-quotes that go around string data values in a query. I recommend NOT using string concatenation (the dot .) as it results in a huge number of syntax errors because it is difficult to see exactly what syntax you have for the query string and what syntax you have as part of the php statements.

If you use sprintf, it will make it easy to see the syntax of your query and the syntax of your php statements -

$sql = "INSERT INTO cust_order (qty_adult,qty_child,adult_cost,child_cost,postage,c_name,h_name,town,county,p_code,email,p_num) 
VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')";

$query = sprintf($sql,
mysql_real_escape_string($_POST['qty_adult']),
mysql_real_escape_string($_POST['qty_child']),
mysql_real_escape_string($_POST['calcItem']),
mysql_real_escape_string($_POST['calcChild']),
mysql_real_escape_string($_POST['calcAll']),
mysql_real_escape_string($_POST['c_name']),
mysql_real_escape_string($_POST['h_name']),
mysql_real_escape_string($_POST['town']),
mysql_real_escape_string($_POST['county']),
mysql_real_escape_string($_POST['p_code']),
mysql_real_escape_string($_POST['email']),
mysql_real_escape_string($_POST['p_num']));

If you use the above, don't forget to use the final $query variable in your mysql_query() instead of what you have now.

Sign In

Inserting into MySQL Newbie

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information