Jump to content

insert problem


funbinod

Recommended Posts

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

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

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

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 by funbinod
Link to comment
Share on other sites

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

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

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

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 by funbinod
Link to comment
Share on other sites

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

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

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. :facepalm: dunno what had happened. can u focus light on some possible reasons.

Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...