SpeedCrazy Posted April 25, 2011 Posted April 25, 2011 (edited) 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 Share this post Link to post Share on other sites More sharing options...
ARandomOWL Posted April 25, 2011 Posted 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) Share this post Link to post Share on other sites More sharing options...
Locutus Posted April 25, 2011 Posted April 25, 2011 I have no advice, just a bump. Also, here's some humor on the subject. http://xkcd.com/327/ Share this post Link to post Share on other sites More sharing options...
d3bruts1d Posted April 25, 2011 Posted April 25, 2011 Omit transactionID from your statement. It's an auto increment, MySQL will handle it. Share this post Link to post Share on other sites More sharing options...
SpeedCrazy Posted April 25, 2011 Posted April 25, 2011 ah, thanks, i changed the table and forgot to change the code. Share this post Link to post Share on other sites More sharing options...
krazypoloc Posted April 25, 2011 Posted April 25, 2011 Also line 11 should read "field" not "filed" Share this post Link to post Share on other sites More sharing options...
SpeedCrazy Posted April 25, 2011 Posted April 25, 2011 Lol, my spelling sucks, atleast it isn't in the code itself. Share this post Link to post Share on other sites More sharing options...
SpeedCrazy Posted April 26, 2011 Posted April 26, 2011 (edited) 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 Share this post Link to post Share on other sites More sharing options...
viperk1 Posted April 26, 2011 Posted 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? Share this post Link to post Share on other sites More sharing options...
hierovision Posted April 26, 2011 Posted 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. Share this post Link to post Share on other sites More sharing options...
hierovision Posted April 26, 2011 Posted 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 Share this post Link to post Share on other sites More sharing options...
SpeedCrazy Posted April 26, 2011 Posted 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) . Share this post Link to post Share on other sites More sharing options...
Recommended Posts
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now