PHP MYSQL INSERT error?

SpeedCrazy · April 25, 2011

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 @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@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 CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_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 April 25, 2011 by SpeedCrazy

ARandomOWL · April 25, 2011

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)

Locutus · April 25, 2011

I have no advice, just a bump. Also, here's some humor on the subject. http://xkcd.com/327/

d3bruts1d · April 25, 2011

Omit transactionID from your statement. It's an auto increment, MySQL will handle it.

SpeedCrazy · April 25, 2011

ah, thanks, i changed the table and forgot to change the code.

:thx:

krazypoloc · April 25, 2011

Also line 11 should read "field" not "filed"

SpeedCrazy · April 25, 2011

Lol, my spelling sucks, atleast it isn't in the code itself.

SpeedCrazy · April 26, 2011

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 April 26, 2011 by SpeedCrazy

viperk1 · April 26, 2011

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?

hierovision · April 26, 2011

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.

hierovision · April 26, 2011

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

SpeedCrazy · April 26, 2011

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) .

Sign In

PHP MYSQL INSERT error?

Recommended Posts

Share this post

Link to post

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Share this post

Link to post

Share on other sites

Please sign in to comment