Little help with code!

watupdoe07

For some reason, my code is not saving records using the INSERT INTO statement. Below is my code:

<html>
<head>
<h1> YOUR SHOPPING CART </h1>
<h2> Products Summary </h2>
<body>

<?php

$dbc= mysql_connect('localhost', 'root', 'kellogg');


$db_selected = mysql_select_db('bosketda', $dbc);

if (!$db_selected)
   die('Could not connect: ' . mysql_error());


$sku = $_REQUEST["sku"];
$name = $_REQUEST["name"];
$summary = $_REQUEST["summary"];
$description = $_REQUEST["description"];
$price = $_REQUEST["price"];

$insert = "INSERT INTO products (sku,name,short_description,description,price) VALUES ($sku,$name,$summary,$description,$price)";


//these are the lines im having trouble with. the "ERROR" is displayed//
mysql_query($insert, $dbc);

if (!mysql_query($insert, $dbc));
   echo "ERROR";

mysql_close($dbc);

print $name."<br>";
print $summary."<br>";
print $description."<br>";
print $price."<br>";

print "<a href = product.html> Create new products </a>"."<br>";
print "<a href = view_product.php?sku=$sku> View Product </a>"."<br>";
?>




</body>
</html>

bradgrafelman

You've got a few problems:

First and foremost, user-supplied data should never be placed directly into a SQL query string, else your code will be vulnerable to SQL injection attacks and/or just plain SQL errors. Instead, you must first sanitize the data with a function such as [man]mysql_real_escape_string/man (for string data).
This:
```
mysql_query($insert, $dbc); 

if (!mysql_query($insert, $dbc)); 
   echo "ERROR"; 
```
is going to attempt to execute your query twice. In other words, even if you had written a valid INSERT query, you'd be inserting the same data twice.

See the PHP manual page for [man]mysql_query/man for more proper ways of handling the return value of this (and similar) functions.
When debugging a failing MySQL query, it's often helpful to: a) output the entire SQL query string as it was sent to MySQL (this way you can visually inspect it for problems), and b) output the MySQL error message (why try to diagnose a problem without first being told where/what the problem is?) using [man]mysql_error/man.
In the VALUES section of your SQL query, you don't surround any of the data with quotes (which are required for strings - just like they are in PHP). Since I'm assuming at least one of those pieces of data is a non-numeric quantity, this is likely the source (or one of the sources) of your problems.

EDIT: Also, welcome to PHPBuilder! When posting PHP code, please use the board's [noparse]

..

[/noparse] bbcode tags as they make your code much easier to read and analyze.

Bjom

brad beat me by a few seconds and got it all right.

watupdoe07

Thanks alot for your input!

So I made the changes. Now I am stuck on a couple other issues. Firstly, mysql_affected_rows returns a value of -1, meaning that the query failed. Also, I am trying to print the contents of my table, and I have no idea on how to do this.

Current code:

<html>
<head>
<h1> YOUR SHOPPING CART </h1>
<h2> Products Summary </h2>
<body>

<?php

$dbc = mysql_connect('localhost', 'user', 'password');

$db_selected = mysql_select_db('bosketda', $dbc) or die("Unable to select database");

$sku = mysql_real_escape_string($_REQUEST["sku"],$dbc);
$name = mysql_real_escape_string($_REQUEST["name"], $dbc);
$summary = mysql_real_escape_string($_REQUEST["summary"], $dbc);
$description = mysql_real_escape_string($_REQUEST["description"], $dbc);
$price = mysql_real_escape_string($_REQUEST["price"], $dbc);

$insert = "INSERT INTO products (sku,name,short_description,description,price) VALUES ('$sku','$name','$summary','$description',$price)"; 

$result1 = mysql_query($insert,$dbc);

if (!result1)
   die('Error: ' . mysql_error());
else
   echo "1 record added"."<br>";
   //returns a -1, meaning that my last query failed//
   echo mysql_affected_rows()."<br>";

$select = "SELECT*FROM products";

$result2 = mysql_query($select,$dbc);



   //printf("Name: %s Summary: %s Description %s Price %s", $row['name'], $row['summary'], $row['description'], $row[4]);//


print "<a href = product.html> Create new products </a>"."<br>";
print "<a href = view_product.php?sku=$sku> View Product </a>"."<br>";
?>

</body>
</html>

MOD EDIT: Possibly valid user credentials obfuscated; make sure you aren't posting valid login credentials in source code.

bradgrafelman

If the query is failing, then [man]mysql_query/man will return false. You forgot something here:

if (!result1) 
   die('Error: ' . mysql_error());

(note the missing $ on the variable name).

watupdoe07

The error I receive is this:

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 1

I take it that there is something wrong with my query, but I have no diea what.

bradgrafelman

When debugging failing SQL queries, it's often helpful to output the exact SQL query string as sent to the MySQL server along with the error message; that way you can visually inspect it for errors.

Can you output the SQL query and paste it for us to see? My guess is that $price might be empty, a condition you don't check for (in which case it would cause errors in the SQL query). Also note that you shouldn't be using [man]mysql_real_escape_string/man to sanitize that piece of data since it isn't a string value. Instead, use a function such as [man]is_numeric/man or simply cast it to a numeric type (e.g. double).

watupdoe07

Nothing is output. My code is as follows:

<html>
<html>
<meta http-equiv="content-type" content="text/html;charset=utf-8" /> 
<link rel="stylesheet" type="text/css" href="stylesheet.css" />
<head>
<h1> YOUR SHOPPING CART </h1>
<h2> Products Summary </h2>
<body>

<?php

$dbc = mysql_connect('localhost', 'user', 'pass');

$db_selected = mysql_select_db('database', $dbc) or die("Unable to select database");

$sku = mysql_real_escape_string($_REQUEST["sku"],$dbc);
$name = mysql_real_escape_string($_REQUEST["name"], $dbc);
$summary = mysql_real_escape_string($_REQUEST["summary"], $dbc);
$description = mysql_real_escape_string($_REQUEST["description"], $dbc);
$price = $_REQUEST["price"];

$insert = "INSERT INTO products (sku,name,short_description,description,price) VALUES ('$sku','$name','$summary','$description',$price)"; 

$result1 = mysql_query($insert,$dbc);

#returns nothing
echo $result1;

#if (!$result1)
#   die('Error: ' . mysql_error());
#else
#   echo "1 record added"."<br>";
#   //returns a -1, meaning that my last query failed//
#   echo mysql_affected_rows()."<br>";

$select = "SELECT*FROM products";

$result2 = mysql_query($select,$dbc);



   //printf("Name: %s Summary: %s Description %s Price %s", $row['name'], $row['summary'], $row['description'], $row[4]);//


print "<a href = product.html> Create new products </a>"."<br>";
print "<a href = view_product.php?sku=$sku> View Product </a>"."<br>";
?>

</body>
</html>

bradgrafelman

$result1 isn't the SQL query string; it's the value returned by the [man]mysql_query/man function, which is going to be boolean FALSE. The SQL query string in the code snippet above is called $insert; that is the variable you'd want to print out.

watupdoe07

Okay, I also realized that maybe I hadn't created the table yet. These are the changes that I've made to the code:



$table = "CREATE TABLE products
(
)";

$result1 = mysql_query($table);

#returns exactly what is in the string
echo $table;

$insert = mysql_query("INSERT INTO products (sku,name,short_description,description,price) VALUES ('$sku','$name','$summary','$description',$price')"); 

$result2 = mysql_query($insert,$dbc);

$select = "SELECT*FROM products";

#returns nothing
echo $insert;

watupdoe07

Okay so I removed the 'mysql_query' from my variable:


$result1 = mysql_query($table);

#returns -1
echo mysql_affected_rows();

#returns exactly what is in the string
echo $table;

$insert = "INSERT INTO products (sku,name,short_description,description,price) VALUES ('$sku','$name','$summary','$description',$price')"; 

$result2 = mysql_query($insert,$dbc);

returns -1
echo mysql_affected_rows();

$select = "SELECT*FROM products";

#returns NOW
echo $insert;

However, 'mysql_affected_rows' still returns -1...

Derokorian

echo the $insert variable to look at the query being run, we know thats where the problem is yet you keep trying to echo other things.

watupdoe07

I HAVE echoed the $insert variable as it shows in my code above.

It returns exactly what is in the string. Sorry if this was not made clear enough.

watupdoe07

It returns: INSERT INTO products (sku,name,short_description,description,price) VALUES ('$sku','$name','$summary','$description',$price')

Derokorian

but it should return the values of those variables not the names. Possibly a cause of the problem.