funbinod Posted January 30, 2015 Share Posted January 30, 2015 I encountered a unexpected problem while inserting row to MySQL table. the interesting point is that it was working one day earlier and it is still working on localhost. the following is the query I used.. INSERT INTO $temptbl(sn, vn, tr_type, date, ndate, aid, sid, description, catid, qty, rate, less, amt, ref, cid, uid) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) the following is the MySQL table.. CREATE TABLE IF NOT EXISTS `temptbl` (`list` int(10) NOT NULL AUTO_INCREMENT, `sn` int(10) NOT NULL, `ref` varchar(100) DEFAULT NULL, `date` date NOT NULL, `ndate` date NOT NULL, `vn` int(10) NOT NULL, `aid` int(10) NOT NULL, `sid` int(10) NOT NULL, `description` longtext NOT NULL, `catid` int(10) NOT NULL, `qty` int(100) NOT NULL, `rate` int(100) NOT NULL, `less` int(3) NOT NULL, `amt` int(100) NOT NULL, `tr_type` varchar(100) NOT NULL, `cid` tinyint(4) NOT NULL, `uid` tinyint(4) NOT NULL) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1; one thing I must mention is that, previously the AI column `list` was TINYINT(3) and its AI value reached 318 ( I don't know how it reached, or I might be confused, since I've read in web that its max value is 127). I thought it stopped inserting because the AI column reached maximum value. thus I changed it to INT(10) and truncated it to force it start from 1. but it is still not inserting anything. and what I wonder more is that it is working fine on localhost. please guide what could be the possible reason(s). Link to comment Share on other sites More sharing options...
justsomeguy Posted January 30, 2015 Share Posted January 30, 2015 If the query is failing then you need to get the error message from MySQL to figure out why. Link to comment Share on other sites More sharing options...
funbinod Posted January 31, 2015 Author Share Posted January 31, 2015 and another interesting thing. the query is not failing. rather it gives success message. it just doesn't insert into db. if ($stmt = $mysqli->prepare("INSERT INTO $temptbl(sn, vn, tr_type, date, ndate, aid, sid, description, catid, qty, rate, less, amt, ref, cid, uid) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")) { $stmt->bind_param('iisssiisiddddsii', $sn, $svn, $tr_type, $date, $dp, $aid, $sid, $desc, $catid, $qty, $rate, $less, $amt, $ref, $cid, $uid); $stmt->execute(); $_SESSION['vousub'] = $qty." ".$unit." '".$item."' added to basket!"; $mysqli->close(); die (header("location: invoice.php?m=$method"));} else { die('Insert Error: '.$mysqli->error);} Link to comment Share on other sites More sharing options...
Ingolme Posted January 31, 2015 Share Posted January 31, 2015 Remove the location header. If any error message is showing up, the location header is making the page redirect before you're able to see the message. if ($stmt = $mysqli->prepare("INSERT INTO $temptbl(sn, vn, tr_type, date, ndate, aid, sid, description, catid, qty, rate, less, amt, ref, cid, uid) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")) { $stmt->bind_param('iisssiisiddddsii', $sn, $svn, $tr_type, $date, $dp, $aid, $sid, $desc, $catid, $qty, $rate, $less, $amt, $ref, $cid, $uid); $stmt->execute(); if($stmt->errno) { echo $stmt->error; } exit; // $_SESSION['vousub'] = $qty." ".$unit." '".$item."' added to basket!"; //$mysqli->close(); // die (header("location: invoice.php?m=$method"));} else { die('Insert Error: '.$mysqli->error);} Link to comment Share on other sites More sharing options...
funbinod Posted January 31, 2015 Author Share Posted January 31, 2015 (edited) ok. I had no idea about this. I thought the else { } block will give error msg if it encountered any. and I thought if the insert succeeds then only the success msg will be generated. now it gave the error msg "Column 'date' cannot be null" this made me more confusing. first, as I mentioned previous, the script inserts everything on localhost. here I mention more of my script. there are two types of date. one international and other local. $date = $_POST['date'];if($srow->cal=='N') { // in setting table 'calander' is set to 'N'. this means the setting is for local date.. require('ecal.php'); // in it if($date=='2071-10-12'){$edate='2015-01-26';}elseif($date=='2071-10-13'){$edate='2015-01-27';} and more.. $dw = 'ndate, date'; $dp = $edate;} else { // and the same thing vice versa is here in this block... require('ncal.php'); $dw = 'date, ndate'; $dp = $ndate;}// testing if everything is ok or not...echo $srow->cal.'<br />'; // echos 'N'echo $date.'<br />'; // echos '2071-10-12' (this is local date)echo $date.'<br />'; // echos 'ndate, date'echo $edate; // echos nothing here, I think, the script is not including ecal.php in it.the motive of this script is to create the insert structure. that means, according to setting table, if $srow->cal is 'N' then the acquired date in 'ndate' and it has to grab 'edate'. so it has to be inserted in 'ndate' column and another in 'date' column. to define this, I created the $dw and $dp vars. in this current setting, date insert structure is ..,ndate, date,... and value for them-- ..,$date, $edate. if the setting is different, then the structure is ..,date, ndate,.. and value for them-- ..,$date,$ndate,.. here is the exact insert block $stmt = $mysqli->prepare("INSERT INTO $temptbl(sn, vn, tr_type, ".$dw.", aid, sid, description, catid, qty, rate, less, amt, ref, cid, uid) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");$stmt->bind_param('iisssiisiddddsii', $sn, $svn, $tr_type, $date, $dp, $aid, $sid, $desc, $catid, $qty, $rate, $less, $amt, $ref, $cid, $uid); the confusion here is, why isn't this including the 'ecal.php'. the ecal.php lies in the same directory of this insert script... (again reminding, the EVERYTHING is fine on localhost) Edited January 31, 2015 by funbinod Link to comment Share on other sites More sharing options...
Ingolme Posted January 31, 2015 Share Posted January 31, 2015 You need to do two tests. The first test to see if the statement was prepared and the second test to see if it was executed. You're only doing the first one. You should understand how to debug by now, put an echo inside your if and else statements to see which ones it's going through. Your logic is confusing; why do you need to switch the order of the date and ndate fields in the insert query? Link to comment Share on other sites More sharing options...
funbinod Posted January 31, 2015 Author Share Posted January 31, 2015 but, as i mentioned above, the IF block is executed, but the ecal.php is not included in it, as it should be if the IF block executes. after including ecal.php, it should give the value for $edate according to the value of $date. but while echoing the $edate, it gives nothing... Link to comment Share on other sites More sharing options...
Ingolme Posted January 31, 2015 Share Posted January 31, 2015 If no error message is showing up then it definitely was included. require() would halt the program if it failed. The problem you're encountering could only stem from the fact that whatever is inside the included file is not behaving the way you expected it to. Link to comment Share on other sites More sharing options...
funbinod Posted January 31, 2015 Author Share Posted January 31, 2015 (edited) here is a part of the included file if($date=='2071-10-09'){ $edate='2015-01-23';}elseif($date=='2071-10-10'){ $edate='2015-01-24';}elseif($date=='2071-10-11'){ $edate='2015-01-25';}elseif($date=='2071-10-12'){ $edate='2015-01-26';}elseif($date=='2071-10-13'){ $edate='2015-01-27';}elseif($date=='2071-10-14'){ $edate='2015-01-28';}elseif($date=='2071-10-15'){ $edate='2015-01-29';}elseif($date=='2071-10-16'){ $edate='2015-01-30';}elseif($date=='2071-10-17'){ $edate='2015-01-31';}elseif($date=='2071-10-18'){ $edate='2015-02-01';} and it must work. what could be the cause for not behaving the way i expect..????? Edited January 31, 2015 by funbinod Link to comment Share on other sites More sharing options...
funbinod Posted January 31, 2015 Author Share Posted January 31, 2015 the logic behind the switch between date and ndate is that, user may change the type of date anytime. if they change the type of date, then the date coming from $_POST['date'] would also be changed. then the insert query must be clear what type of date is it that is coming from 'date' field and it has to store both type of dates in the database. if 'N' date is coming then it should grab 'E' date and store in the same way. and opposite for another setting. Link to comment Share on other sites More sharing options...
Ingolme Posted January 31, 2015 Share Posted January 31, 2015 What value does the variable $edate have? Use var_dump() so you can determine the type as well. var_dump($edate); It's probably best to put it right inside ecal.php so that you can also be sure that the file is being included. If $edate doesn't have a value then perhaps none of the conditions are being met. If that's the case, check the value of $date. This looks like a good place for a swtich-case structure rather than if-elseif Link to comment Share on other sites More sharing options...
funbinod Posted January 31, 2015 Author Share Posted January 31, 2015 amaizing! the var_dump gave string(10) "2015-01-27" and suddenly it worked. then i removed this(var_dump) and it again worked. i think var_dump has nothing to do with inserting rows. I've not changed anything else and its working now. dunno what had happened. can u focus light on some possible reasons. Link to comment Share on other sites More sharing options...
Ingolme Posted January 31, 2015 Share Posted January 31, 2015 Perhaps you just happen to be sending the right data this time. var_dump() does nothing but show information about what's in a variable. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now