Jump to content

PHP MYSQL INSERT error?


SpeedCrazy
 Share

Recommended Posts

Hey guys im having some trouble with a php mysql INSERT statement.

I cant think of how to explain this with out giving you all my code so bear with me, though incomplete(though it should function) this is 93 lines, i have comments explaining everything.

The setup is a method for saving sales info into a database. The products are listed as 'template' just because i am working on some stuff to sell on themeforest.

Erro explained after code.

PHP form(which calls from a basic html form)

<!DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtm11/DTD/xhtml11-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<title>confirmSale</title>
</head>
<body>
	<h1>Please confirm sale information.</h1>
	<?php 
		$templateNum = $_REQUEST["templateNum"];

		//load filed 1
		$template1S = $_REQUEST["template1S"];
		$template1To = $_REQUEST["template1To"];

		//load field 2
		$template2S = $_REQUEST["template2S"];
		$template2To = $_REQUEST["template2To"];

		//if field 1 is empty, field 2, these variable names are changed in a minute but i wanted verbose names to start with
		if($templateNum ==1) {
					$templateNums = "1";
					$templateS = "$template1S";
					$templateTo = "$template1To";
				} else{
					$templateNums = "2";
					$templateS = "$template2S";
					$templateTo = "$template2To";
		}//end if

		//create connection
		$conn = mysql_connect("localhost", "Design", "secure117") or die(mysql_error());
		mysql_select_db("sales");

		//gather vairiables, made shorter for ease of typing
		$tp= "$templateNums";//template number
		$num="$templateS";// number sold
		$to="$templateTo";// sold to who

		//pull template price from mysql table
		$sqlP=<<<HERE
		SELECT
			price
		FROM
			template
		WHERE
			templateID="$tp";
HERE;
		$result= mysql_query($sqlP, $conn);
		$inPrice=mysql_result($result, 0);
		$price = $inPrice * $num;// template price by number of templates for total price
		//end price pull

		//pull profit from table
		$sqlMM=<<<HERE
		SELECT
			max(totalProfit)
		FROM
			income
HERE;

		$result1= mysql_query($sqlMM, $conn);
		$mmS=mysql_result($result1, 0);
		$mm= $mmS + $price;//total proft, old plus current transaction
		//finish pull profit

		//insert: transactionID(=NULL), date(NOW()), template($tp), number sold($num), money made in transaction($price), total profit($mm), sold to($to)
		mysql_query ("INSERT INTO income (transactionID, date, templateID, numsold, moneymade, totalProfit, soldto) VALUES (NULL, NOW(), $tp, $num, $price, $mm, $to)");


		//insert into template info table the number sold
		//call current count
		$sqln=<<<HERE
		SELECT
			numsold
		FROM
			template
		WHERE
			templateID="$tp";
HERE;
		$resultn= mysql_query($sqln, $conn);
		$sqlN=mysql_result($resultn, 0);
		$numsold= $sqlN + $num;// add current transaction value to db value
		//end call

		// upadate template info table to new value where templateID = current transaction templateID
		mysql_query("UPDATE template SET numsold =$numsold WHERE templateID=$tp");

		//print what was sold and who to
		print "<h3>Sold: $templateS copies of template $templateNums, to: $templateTo for $inPrice each.</h3>";			
	?>
</body>
</html>

 

MYSQL tables it is calling:

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 24, 2011 at 11:06 PM
-- Server version: 5.1.41
-- PHP Version: 5.3.1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `sales`
--

-- --------------------------------------------------------

--
-- Table structure for table `income`
--

CREATE TABLE IF NOT EXISTS `income` (
 `transactionID` int(11) NOT NULL AUTO_INCREMENT,
 `date` date DEFAULT NULL,
 `templateID` varchar(45) DEFAULT NULL,
 `numsold` int(11) DEFAULT NULL,
 `moneymade` decimal(10,0) DEFAULT NULL,
 `totalProfit` decimal(10,0) DEFAULT NULL,
 `soldto` longtext,
 PRIMARY KEY (`transactionID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `income`
--

INSERT INTO `income` (`transactionID`, `date`, `templateID`, `numsold`, `moneymade`, `totalProfit`, `soldto`) VALUES
(0, '2011-04-23', 'template', 0, '0', '0', 'no-one');

-- --------------------------------------------------------

--
-- Table structure for table `template`
--

CREATE TABLE IF NOT EXISTS `template` (
 `templateID` int(11) NOT NULL,
 `name` varchar(45) DEFAULT NULL,
 `price` decimal(10,0) DEFAULT NULL,
 `numsold` int(11) DEFAULT NULL,
 PRIMARY KEY (`templateID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `template`
--

INSERT INTO `template` (`templateID`, `name`, `price`, `numsold`) VALUES
(1, 'Template 1', '10', 0),
(2, 'Template 2', '35', 0);

/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;

What happens is that data is written to table 'template' but 'income' is not touched. It doesn't throw any errors and i cant find any typos, so i am clueless as to what the problem is.

Any ideas?

Thanks,

Speed

Edited by SpeedCrazy

Share this post


Link to post
Share on other sites

  • Replies 24
  • Created
  • Last Reply

Top Posters In This Topic

I only had a quick look. But I noticed that you are trying to store "NULL" in "transactionID" which is not allowed by your table structure. As transactionID is AUTO_INCREMENT I think you should omit that column from the insert statement.

 

(Bare in mind it is a while since I worked with SQL so I could be wrong)

Share this post


Link to post
Share on other sites

line now reads:

mysql_query ("INSERT INTO income (numsold, moneymade, totalProfit, soldto) VALUES (NOW(), $tp, $num, $price, $mm, $to)");

but it still doesn't work......

hmm.

Edited by SpeedCrazy

Share this post


Link to post
Share on other sites

I'm not familiar with SQL, but for things like this I usually have the code output the text string query somehow, and then try running the sql statement manually. If that works, then at least you know the statement is valid and can move on to other problem determination.

 

BTW, is that SQL code being called from the client side? Isn't that a huge security no-no?

Share this post


Link to post
Share on other sites

Just an FYI, you should use GUIDs for IDs, not auto-incrementing integers. It won't hurt anything in the short-term, but GUIDs are far more unique and can be efficiently used as an index by themselves. It's easier to troubleshoot as well, since you can print GUIDs and know exactly what you're getting, whereas you might have multiple tables using auto-incrementing integers (and therefore multiple table PKs or IDs will have the same number).

 

I think this will take care of it for you: http://php.net/manual/en/function.com-create-guid.php

 

As far as the MySql side of things, store the guids in a char(36). I don't know if mysql supports uniqueidentifier fields yet.

Share this post


Link to post
Share on other sites

line now reads:

mysql_query ("INSERT INTO income (numsold, moneymade, totalProfit, soldto) VALUES (NOW(), $tp, $num, $price, $mm, $to)");

but it still doesn't work......

hmm.

 

You're specifying four fields and six values. They have to match up.

 

Should be:

INSERT INTO income (date, numsold, moneymade, totalProfit, soldto) VALUES (Now(), $num, $mm, $tp, $to)

 

Not sure why you're inserting the "$price" value into the table with no "price" field ;)

Share this post


Link to post
Share on other sites

You're specifying four fields and six values. They have to match up.

 

Should be:

INSERT INTO income (date, numsold, moneymade, totalProfit, soldto) VALUES (Now(), $num, $mm, $tp, $to)

Ooops, deleted too much.

 

Not sure why you're inserting the "$price" value into the table with no "price" field ;)

If you read my whole code the comments explain that. I mis named the variable and couldn't be bothered to work back threw again.

Btw, you lost me with all that GUIDs stuff, and client side SQL, this is my first mysql assignment(self assigned, wanted to explore what i had been learning) .

Share this post


Link to post
Share on other sites

Please sign in to comment

You will be able to leave a comment after signing in



Sign In Now
 Share


×
×
  • Create New...