Jump to content

Insert Not Working

himesh

By himesh
in SQL

Recommended Posts

himesh Newbie

himesh

Posted
Posted

HiI am trying to get a simple insert to work. When the user clicks add to basket, the manufacturer, model number, quantity and price are inserted into tblcart dependant on the productID . If there is already one of the item in the cart, it will add one to the quantity. The php runs my code fine but then doesnt insert the data into the table. Im thinking I have missed something simple out again. Heres my code. 

<?php require_once('Connections/conn_comm.php'); ?><?phpif (!function_exists("GetSQLValueString")) {function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") {  if (PHP_VERSION < 6) {	$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;  }  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);  switch ($theType) {	case "text":	  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";	  break;		case "long":	case "int":	  $theValue = ($theValue != "") ? intval($theValue) : "NULL";	  break;	case "double":	  $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";	  break;	case "date":	  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";	  break;	case "defined":	  $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;	  break;  }  return $theValue;}}$colname_addcart = "-1";  $colname_addcart = $_GET['productID'];mysql_select_db($database_conn_comm, $conn_comm);$query_addcart = sprintf("SELECT productID, manufacturer, modelnumber, price FROM tblproduct WHERE productID = %s", GetSQLValueString($colname_addcart, "int"));$addcart = mysql_query($query_addcart, $conn_comm) or die(mysql_error());$row_addcart = mysql_fetch_assoc($addcart);$totalRows_addcart = mysql_num_rows($addcart);$quantity = "SELECT quantity FROM tblcart WHERE productID = $colname_addcart";$manufacturer ="SELECT manufacturer FROM tblproduct WHERE productID = $colname_addcart";$modelnumber ="SELECT modelnumber FROM tblproduct WHERE productID = $colname_addcart";$price ="SELECT price FROM tblproduct WHERE productID = $colname_addcart";?><!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><?php if ( $quantity > 1 ){	$quantity+1;}		else {" INSERT INTO tblcart (manufacturer, modelnumber, quantity, price)		VALUES ('$manufacturer', '$modelnumber', '$quantity', '$price')";		}?><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>Untitled Document</title><link href="_css/main.css" rel="stylesheet" type="text/css" /></head><body></body></html><?phpmysql_free_result($addcart);?>

Link to comment
Share on other sites
justsomeguy w3schools Guru

justsomeguy

Posted
Posted

Your if statement doesn't do anything. This:

if ( $quantity > 1 ){	$quantity+1;}		else {" INSERT INTO tblcart (manufacturer, modelnumber, quantity, price)		VALUES ('$manufacturer', '$modelnumber', '$quantity', '$price')";		}

Will either run "$quantity+1", which doesn't do anything (you aren't saving the value anywhere), or in the else statement you just have a string there. You're not doing anything with the string though, you're not even saving it to a variable.You're also using queries for the values to insert. You define your variables here:$quantity = "SELECT quantity FROM tblcart WHERE productID = $colname_addcart";$manufacturer ="SELECT manufacturer FROM tblproduct WHERE productID = $colname_addcart";$modelnumber ="SELECT modelnumber FROM tblproduct WHERE productID = $colname_addcart";$price ="SELECT price FROM tblproduct WHERE productID = $colname_addcart";So when those values get substituted into this string:INSERT INTO tblcart (manufacturer, modelnumber, quantity, price) VALUES ('$manufacturer', '$modelnumber', '$quantity', '$price')you end up with this:INSERT INTO tblcart (manufacturer, modelnumber, quantity, price) VALUES ('SELECT manufacturer FROM tblproduct WHERE productID = $colname_addcart', 'SELECT modelnumber FROM tblproduct WHERE productID = $colname_addcart', 'SELECT quantity FROM tblcart WHERE productID = $colname_addcart', 'SELECT price FROM tblproduct WHERE productID = $colname_addcart')Up here you get all of those values from the database:$query_addcart = sprintf("SELECT productID, manufacturer, modelnumber, price FROM tblproduct WHERE productID = %s", GetSQLValueString($colname_addcart, "int"));So why not just use the values you got there instead of run more queries to get the same values?

Link to comment
Share on other sites
himesh Newbie

himesh

Posted
  • Author
Posted

I have changed the code to the following but still to no avail.

<?php require_once('Connections/conn_comm.php'); ?><?phpif (!isset($_SESSION['MM_Username'])) {  session_start();}if (!function_exists("GetSQLValueString")) {function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") {  if (PHP_VERSION < 6) {	$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;  }  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);  switch ($theType) {	case "text":	  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";	  break;		case "long":	case "int":	  $theValue = ($theValue != "") ? intval($theValue) : "NULL";	  break;	case "double":	  $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";	  break;	case "date":	  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";	  break;	case "defined":	  $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;	  break;  }  return $theValue;}}$colname_addcart = "-1";  $colname_addcart = $_GET['productID'];mysql_select_db($database_conn_comm, $conn_comm);$query_addcart = sprintf("SELECT productID, manufacturer, modelnumber, price FROM tblproduct WHERE productID = %s", GetSQLValueString($colname_addcart, "int"));$addcart = mysql_query($query_addcart, $conn_comm) or die(mysql_error());$row_addcart = mysql_fetch_assoc($addcart);$totalRows_addcart = mysql_num_rows($addcart);?><!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><?php if ( $row_addcart['quantity'] > 1 ){	$row_addcart['quantity'] = $row_addcart['quantity']+1;}		else { "INSERT INTO tblcart (manufacturer, modelnumber, quantity, price, username)		VALUES '$row_addcart[manufacturer]', '$row_addcart[modelnumber]', '$row_addcart[quantity]', '$row_addcart[price]', '$_SESSION[MM_Username]'}";	}?><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>Untitled Document</title><link href="_css/main.css" rel="stylesheet" type="text/css" /></head><body></body></html><?phpmysql_free_result($addcart);?>

Link to comment
Share on other sites
himesh Newbie

himesh

Posted
  • Author
Posted

I looked at another insert that dreamweaver done for me and I have tried to copy the style in which it is done but it has not worked. I have changed it to this.

<?php if ( $row_addcart['quantity'] > 1 ){	$row_addcart['quantity'] = $row_addcart['quantity']+1;}		else { $insertSQL = sprintf("INSERT INTO tblcart (manufacturer, modelnumber, quantity, price, username)		VALUES ('$row_addcart[manufacturer]', '$row_addcart[modelnumber]', '$row_addcart[quantity]', '$row_addcart[price]', '$_SESSION[MM_Username]')};"			 mysql_select_db($database_conn_comm, $conn_comm);  $Result1 = mysql_query($insertSQL, $conn_comm) or die(mysql_error());}?>

Link to comment
Share on other sites
  • 3 weeks later...
himesh Newbie

himesh

Posted
  • Author
Posted 
<?php if ( $row_addcart['quantity'] > 1 ){	$row_addcart['quantity'] = $row_addcart['quantity']+1;}		else { $insertSQL = sprintf("INSERT INTO tblcart (manufacturer, modelnumber, quantity, price, username),		VALUES ('$row_addcart[manufacturer]', '$row_addcart[modelnumber]', '$row_addcart[quantity]', '$row_addcart[price]', '$_SESSION[MM_Username]');"}		//mysql_select_db($database_conn_comm, $conn_comm);//$Result1 = mysql_query($insertSQL, $conn_comm) or die(mysql_error());?>

I have changed it to this now but it says that it is not expecting the } which is confusing me as i thought it needed that to close the else statement :) ive also commented and put the other bit out of the else as it gave me the unexpected t-string error. i have tried putting the ; out out the "" as well but that doesnt work

Link to comment
Share on other sites
himesh Newbie

himesh

Posted
  • Author
Posted 
<?php if ( $row_addcart['quantity'] > 1 ){	$row_addcart['quantity'] = $row_addcart['quantity']+1;}		else { $insertSQL = sprintf("INSERT INTO tblcart (manufacturer, modelnumber, quantity, price, username)";,		VALUES ('$row_addcart[manufacturer]', '$row_addcart[modelnumber]', '$row_addcart[quantity]', '$row_addcart[price]', '$_SESSION[MM_Username]');}		//mysql_select_db($database_conn_comm, $conn_comm);//$Result1 = mysql_query($insertSQL, $conn_comm) or die(mysql_error());?>

when i do that, it does not recognise the values, any more ideas?

Link to comment
Share on other sites
justsomeguy w3schools Guru

justsomeguy

Posted
Posted

Why did you close sprintf in the middle of the query? I mean, of all the possible places to close it, why right in the middle of the query? Why not at the end? Instead of hacking around the code, you need to understand what you're doing and why you're doing it.

Link to comment
Share on other sites
  • 2 weeks later...
himesh Newbie

himesh

Posted
  • Author
Posted

Ok I have changed it and it runs with no errors however nothing is being input into the database. I altered a line and i got an error saying cannot insert and the only value that came out was the username. Which then lead me to try and select all from the SQL but that did not change anything, still nothing is getting input. Im thinking there is a ; or " out of place somewhere?

<?php require_once('Connections/conn_comm.php'); ?><?phpif (!isset($_SESSION['MM_Username'])) {  session_start();}if (!function_exists("GetSQLValueString")) {function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") {  if (PHP_VERSION < 6) {	$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;  }  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);  switch ($theType) {	case "text":	  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";	  break;		case "long":	case "int":	  $theValue = ($theValue != "") ? intval($theValue) : "NULL";	  break;	case "double":	  $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";	  break;	case "date":	  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";	  break;	case "defined":	  $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;	  break;  }  return $theValue;}}$colname_addcart = "-1";if (isset($_GET['productID'])) {  $colname_addcart = $_GET['productID'];}mysql_select_db($database_conn_comm, $conn_comm);$query_addcart = sprintf("SELECT manufacturer, modelnumber, price FROM tblproduct WHERE productID = %s", GetSQLValueString($colname_addcart, "int"));$addcart = mysql_query($query_addcart, $conn_comm) or die(mysql_error());$row_addcart = mysql_fetch_assoc($addcart);$totalRows_addcart = mysql_num_rows($addcart);?><!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><?php if ( $row_addcart['quantity'] > 1 ){	$row_addcart['quantity'] = $row_addcart['quantity']+1;}		else { $insertSQL = sprintf("INSERT INTO tblcart (manufacturer, modelnumber, quantity, price, username);,		VALUES ('$row_addcart[manufacturer]', '$row_addcart[modelnumber]', '$row_addcart[quantity]', '$row_addcart[price]', '$_SESSION[MM_Username]'");	mysql_select_db($database_conn_comm, $conn_comm);$Result1 = mysql_query($insertSQL, $conn_comm) or die(mysql_error());}		?><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>Untitled Document</title><link href="_css/main.css" rel="stylesheet" type="text/css" /></head><body></body></html><?phpmysql_free_result($addcart);?>

Link to comment
Share on other sites
himesh Newbie

himesh

Posted
  • Author
Posted

Thanks! That worked but now I have a SQL syntax error, I have googled it but couldnt make sense of it, at first I thought it was the type of data in the structure, changed that to no avail. This is the error I am gettingYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' VALUES ('Nokia', 'N97 Mini', '1', '450', 'himesh')' at line 1

Link to comment
Share on other sites
himesh Newbie

himesh

Posted
  • Author
Posted

Altered code to below to print query

<?php if ( $row_addcart['quantity'] > 1 ){	$row_addcart['quantity'] = $row_addcart['quantity']+1;}		else { $insertSQL = sprintf("INSERT INTO tblcart (manufacturer, modelnumber, quantity, price, username), 							VALUES ('$row_addcart[manufacturer]', '$row_addcart[modelnumber]', '1', '$row_addcart[price]', '$_SESSION[MM_Username]')");	if ($insertSQL) {	$message  = 'Invalid query: ' . mysql_error() . "\n";	$message .= 'Whole query: ' . $insertSQL;	die($message);}		mysql_select_db($database_conn_comm, $conn_comm);$Result1 = mysql_query($insertSQL, $conn_comm) or die(mysql_error());}		echo $inserrSQL;?>

Getting this returned which seems ok to me :)=s Invalid query: Whole query: INSERT INTO tblcart (manufacturer, modelnumber, quantity, price, username), VALUES ('Apple', 'iPhone 3GS', '1', '400', 'himesh')

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...