...

View Full Version : Problem inserting records...!!!



swap_ssj
11-03-2009, 10:43 AM
hi guyes...

i have 2 files, schedulemasternew.php and schedule_entry.php.
here is schedulemasternew.php


<?php
ob_start();
session_start();
?>
<?php
if($_REQUEST[month]=="January")
{
$maxday=31;
}
else if($_REQUEST[month]=="February")
{

$leap= $_REQUEST[year] % 4;

if($leap==0)
{
$maxday=29;
}
else
{
$maxday=28;
}
}
else if($_REQUEST[month]=="March")
{
$maxday=31;
}
else if($_REQUEST[month]=="April")
{
$maxday=30;
}
else if($_REQUEST[month]=="May")
{
$maxday=31;
}
else if($_REQUEST[month]=="June")
{
$maxday=30;
}
else if($_REQUEST[month]=="July")
{
$maxday=31;
}
else if($_REQUEST[month]=="August")
{
$maxday=31;
}
else if($_REQUEST[month]=="September")
{
$maxday=30;
}
else if($_REQUEST[month]=="October")
{
$maxday=31;
}
else if($_REQUEST[month]=="November")
{
$maxday=30;
}
else if($_REQUEST[month]=="December")
{
$maxday=31;
}
session_register("daycount");
$_SESSION["daycount"]=$maxday;

?>
<table width="757" align="center" cellpadding="0" cellspacing="1" border="0">
<form name="form" action="schedule_entry.php?action=new" method="post" onsubmit="return validateForms('form');">

<?php include_once("master_header.php");?>
<tr> <td align="center" width="100%">
<table width="80%" align="center" cellpadding="2" cellspacing="1" bgcolor="#333333">
<br /> <tr bgcolor="#FFFFFF">
<td colspan="5" align="left" class="bluelink_2"><strong>Water Management</strong></td>
</tr> <tr bgcolor="#FFFFFF"> <td width="29%" align="center" class="txtfld12">Date</td>
<td width="26%" align="center" class="txtfld12">Litres</td> <td width="26%" align="center" class="txtfld12">Hours</td>
</tr> <?php
for ($i=0;$i<$maxday;$i++)
{
?>
<tr bgcolor="#FFFFFF">
<td align="center" class="txtfld12" width="8%"><?php echo $i+1 ?></td>
<td align="center"><input type="text" name="litres<?php $i?>" id="litres" /><br><span id="litres" class="rederr"></span></td>
<td align="center"><input type="text" name="hours<?php $i?>" id="hours" /><br><span id="hours" class="rederr"></span></td>
</tr>
<?php
}
?>
<tr bgcolor="#FFFFFF"> <td colspan="3" align="center" class="txtfld12">&nbsp;</td>
</tr>
<tr> <td align="center" colspan="3"><input type="submit" class="button" name="Add Schedule" value="Add Schedule" /></td>
</tr></table></td>
</tr>
</table>
</form>




And, here is schedule_entry.php



<?php
ob_start();
session_start();

include_once("conn.php");

$count=$_SESSION["daycount"];

if($_GET['action']=="new")
{
if(mysql_query("INSERT INTO schedule(plotno,year,month) VALUES('$_POST[plotno]','$_POST[year]','$_POST[month]')"))
{

$sid = mysql_insert_id();

$litres =$_POST["litres"];
$hours =$_POST["hours"];
$record = explode('~',$litres);
$record1=explode('~',$hours);
for($i=0;$$i<$count;$i++)
{
$ScheduleDetail = explode("^",$record[$i]);
$ScheduleDetails = explode("^",$record1[$i]);
$sql ="INSERT INTO scheduledetail(scheduleid,date,litres,hours) values($sid,$i,'$ScheduleDetail[0]','$ScheduleDetails[0]')";
mysql_query($sql);
}

header("Location:index.php?page=schedulemaster");
}
else {
echo "<br>Query not executed";
}
}
?>


I am creating an application where user selects plot no, year and month. Then according to selected year and month, the no. of days for the selected month are calculated.
and then on next page i.e. schedulemasternew.php, user will have to enter date, litres and hours for no. of times depending upon the month and year selected (i.e. 31 times for January, 28 times for February).
and in schedule_entry.php, there is logic to insert the records.

but, i'm facing problem here while inserting records.
first of all, i'm not sure that, the array is properly created for the records to be inserted.
because, when i execute, it shows Fatal Error :Maximum execution time of 30 seconds exceeded in C:\wamp\www\agro\agro\schedule_entry.php on line 20

and, when i check database, only last entry is inserted in scheduledetail table.

can u please tell me, how should i solve this problem...???

Fumigator
11-03-2009, 05:30 PM
Make sure you are reporting query errors if your queries fail.

Fou-Lu
11-03-2009, 08:36 PM
for($i=0;$$i<$count;$i++)

The $$i is a variable variable which is undefined (variables cannot start with a number). Since null will be less than any number provided by $count, this will always equate to true. Remove one of the $ from the $$i to fix.

Also, use a switch to determine you're days:


switch($_REQUEST['month'])
{
case 'February':
$maxdays = (($_REQUEST['year'] % 4 == 0 &&
$_REQUEST['year'] % 100 != 0) || $_REQUEST['year'] % 400 == 0) ? 29 : 28;
break;
case 'April':
case 'June':
case 'September':
case 'November':
$maxdays = 30;
break;
default:
$maxdays = 31;
}

I think thats the right equation for leap year.

swap_ssj
11-04-2009, 05:43 AM
Thanks for the reply....

Ohh...
yes, that was a silly mistake in for loop. I have corrected it.
But still, i'm having a problem.

This loop is getting executed for a single time only, and also values in the entries are inserted to 0,0...

However i use any number,
e.g.
for($i=0;$i<30;$i++)
it is executing for the single time only.

Can u please help me...???

Fou-Lu
11-04-2009, 06:05 AM
The loop itself is unlikely the problem. If using $count only loops once, than $count is only 1.
Post you're create table for the scheduledetail table. My suspicion here is that scheduleid has been identified as a non-composite primary key rejecting any other attempts. This would fall in line with fumigator's response of displaying or logging you're mysql errors.


Also post the create table for schedule. If you're getting 0,0, one of those is probably the mysql_insert_id which would indicate that you are not using an auto_incrementing value.

swap_ssj
11-04-2009, 06:30 AM
Thanks for the reply...

Here is my scheduledetail table :


-- phpMyAdmin SQL Dump
-- version 2.9.0.3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 04, 2009 at 05:28 AM
-- Server version: 5.0.27
-- PHP Version: 5.2.0
--
-- Database: `agro`
--

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

--
-- Table structure for table `scheduledetail`
--

CREATE TABLE `scheduledetail` (
`scheduleid` int(15) NOT NULL,
`date` int(10) NOT NULL,
`litres` int(10) NOT NULL,
`hours` int(10) NOT NULL,
PRIMARY KEY (`scheduleid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `scheduledetail`
--

Fou-Lu
11-04-2009, 08:19 AM
And confirmed.
This query can never execute more than once:


$sql ="INSERT INTO scheduledetail(scheduleid,date,litres,hours) values($sid,$i,'$ScheduleDetail[0]','$ScheduleDetails[0]')";

$sid is bound to the insertion id (which appears to not be an auto-increment number since its value is 0) for every record. And since this table structure defines a non-composite key on scheduleid, you'll never be able to insert more than one record for that scheduleid. Perhaps you're looking to use a composite on the scheduleid and date? That may work. If you alter you're query to handle failures (I'll just die):


mysql_query($sql) or die(mysql_error());

it should whine about a duplicate key insertion failure.

Now, if mysql_insert_id() is returning 0, it will always return 0. This function requires that auto-increment is used in order to get the newest id. Even the first record inserted will be 1, never 0 with auto-increment.
Change you're schedule table so that the primary key (scheduleid maybe?) is an auto-increment number. That should fix the problem.

swap_ssj
11-04-2009, 11:37 AM
Thanks for the reply Fou-Lu...

I have done changes according to ur guidelines, but still it's executing once only and only one entry is getting inserted in database.

Here i'm giving my schedule table :

-- phpMyAdmin SQL Dump
-- version 2.9.0.3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 04, 2009 at 10:34 AM
-- Server version: 5.0.27
-- PHP Version: 5.2.0
--
-- Database: `agro`
--

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

--
-- Table structure for table `schedule`
--

CREATE TABLE `schedule` (
`scheduleid` int(15) NOT NULL auto_increment,
`plotno` int(5) NOT NULL,
`year` int(10) NOT NULL,
`month` varchar(15) NOT NULL,
PRIMARY KEY (`scheduleid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `schedule`
--

What i want is, all the entries of a particular month should be inserted under the same scheduleid.
Suppose, user selects June, so 30 entries should be inserted under the same scheduleid.

Am i going wrong somewhere in creating and processing arrays for litres & hours...???

Can u please guide me...???

Fou-Lu
11-04-2009, 07:59 PM
Yes, the form is incorrect, but that doesn't explain only one insertion; the current form should insert $count of the same record. Let me format it here...:


<table width="757" align="center" cellpadding="0" cellspacing="1"
border="0">
<form name="form" action="schedule_entry.php?action=new" method="post"
onsubmit="return validateForms('form');"><?php include_once("master_header.php");?>
<tr>
<td align="center" width="100%">
<table width="80%" align="center" cellpadding="2" cellspacing="1"
bgcolor="#333333">
<br />
<tr bgcolor="#FFFFFF">
<td colspan="5" align="left" class="bluelink_2"><strong>Water
Management</strong></td>
</tr>
<tr bgcolor="#FFFFFF">
<td width="29%" align="center" class="txtfld12">Date</td>
<td width="26%" align="center" class="txtfld12">Litres</td>
<td width="26%" align="center" class="txtfld12">Hours</td>
</tr>
<?php
for ($i=0;$i<$maxday;$i++)
{
?>
<tr bgcolor="#FFFFFF">
<td align="center" class="txtfld12" width="8%"><?php echo $i+1 ?></td>
<td align="center"><input type="text" name="litres<?php $i?>"
id="litres" /><br>
<span id="litres" class="rederr"></span></td>
<td align="center"><input type="text" name="hours<?php $i?>"
id="hours" /><br>
<span id="hours" class="rederr"></span></td>
</tr>
<?php
}
?>
<tr bgcolor="#FFFFFF">
<td colspan="3" align="center" class="txtfld12">&nbsp;</td>
</tr>
<tr>
<td align="center" colspan="3"><input type="submit" class="button"
name="Add Schedule" value="Add Schedule" /></td>
</tr>
</table>
</td>
</tr>

</table>
</form>

There thats better.
Now, replace the input types with an array, <?php $i;?> isn't correct since this won't print anything:


<tr bgcolor="#FFFFFF">
<td align="center" class="txtfld12" width="8%"><?php echo $i+1 ?></td>
<td align="center"><input type="text" name="litres[]"
id="litres" /><br>
<span id="litres" class="rederr"></span></td>
<td align="center"><input type="text" name="hours[]"
id="hours" /><br>
<span id="hours" class="rederr"></span></td>
</tr>
<?php
}
?>
<tr bgcolor="#FFFFFF">
<td colspan="3" align="center" class="txtfld12">&nbsp;</td>
</tr>
<tr>
<td align="center" colspan="3"><input type="submit" class="button"
name="Add Schedule" value="Add Schedule" /></td>
</tr>

Note the new input names have removed the <?php $i;?> and added [].
This will both prevent the data from being overwritten (which is a big part of the problem is), and create an array for easier handling in schedule_entry.php. This assumes that each entry is simply an integer value.


<?php
ob_start();
session_start();

include_once("conn.php");

$count = (int)$_SESSION["daycount"];

if (function_exists('get_magic_quotes_gpc') && get_magic_quotes_gpc())
{
$_POST['month'] = stripslashes($_POST['month']);
}
if (function_exists('set_magic_quotes_runtime'))
{
set_magic_quotes_runtime(0);
}

if($_GET['action']=="new")
{
$sql = sprintf("INSERT INTO schedule(plotno, year, month) VALUES (%d, %d, '%s')",
(int)$_POST['plotno'], (int)$_POST['year'], mysql_real_escape_string($_POST['month']));
if(mysql_query($sql))
{
$sid = mysql_insert_id();
for($i = 0; $i < $count; ++$i)
{
$litres = (int)$_POST['litres'][$i];
$hours = (int)$_POST['hours'][$i];

$sql = sprintf("INSERT INTO scheduledetail(scheduleid,date,litres,hours) VALUES(%d, %d, %d, %d)",
$sid, $i, $litres,$hours);
mysql_query($sql);
}
header("Location:index.php?page=schedulemaster");
}
else
{
echo "<br>Query not executed";
}
}
?>

Its important to use the proper datatypes with expected input. The above will handle the month as a string and all other values as integers (based on you're table structure provided).

If schedule detail still provides only one insertion record, ensure that its primary key is a composite of scheduleid and date, not just the scheduleid.

Also, if the table structure is correct, its possible you're session is not passing properly. For this, you can pass the $maxdays through a hidden form field if you like.

swap_ssj
11-05-2009, 06:18 AM
Thanks for the reply...

Your guidelines are very precious to a beginner in php, like me...!!!

I have made all the changes according to ur guidelines.
But, still it is inserting only one record.

Can u please tell me, how can i make primary key of scheduledetail table composite to scheduleid and date...???

Fou-Lu
11-05-2009, 06:27 AM
Yep, from the looks of it you're using PHPMyAdmin?
From the main table structure page, you should see near the middle on the left a listing of keys. Primary, unique, fulltext etc. Locate the one indicating you're current primary key (scheduleID) and click the edit button. From here, there will be a dropdown, one listing the current primary key and another listing ignore. Change the ignore to the column date. If its not available, go back to the main structure and flag the date field as index (I think it looks like a little lightning bolt). Save those changes, than it should show a split key on the main page.
From the CLI or query window, it should be (can't test this atm, sorry):


ALTER TABLE ScheduleDetail DROP PRIMARY KEY;
ALTER TABLE ScheduleDetail ADD PRIMARY KEY (scheduleID, date);



Hmm, thats interesting, I can't actually confirm ATM that its ADD PRIMARY KEY. The mysql site doesn't list this as an option (http://dev.mysql.com/doc/refman/5.1/en/alter-table.html), but I could swear thats what it is (I usually add this at creation time, but this should be for the modification).

swap_ssj
11-05-2009, 06:55 AM
Done....!!!!

You are GREAT.....!!!!!:thumbsup:
Everything is working fine now...!!!
Please, be there with me in future if i need some help...!!!;)

Thank-you very much...!!!

Fou-Lu
11-05-2009, 08:54 AM
Good to hear, wasn't sure if my instructions for the phpmyadmin were followable o.O
Been here for 7 years, probably be here for another as long as george keeps paying the bills ;)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum