counter script only inserting ,not updating

Question

accra 1 Junior Poster in Training

12 Years Ago

Am trying to insert in to the same table two values from the same form. if counter equals to 1, do an update , else do an insert.

only the insert part is working, it looks like my counter script is not working,any help?

$counter=0;
	$sre_log_num = 0;
	$sre_log_result = mysql_QUERY("select count(*) As counter FROM sre_log WHERE vreg ='$vreg' AND id ='$id'");  
	$sre_log_num = mysql_numrows($sre_log_result);
	$i = 0;
		while($i < $sre_log_num) 
			{
				$counter = mysql_result($sre_log_result,$i,"counter");
	
					$i++;
			}

		 if ($counter ==0 )
		 
		 {
			 
		$sql = "insert INTO sre_log
		
		(com_name,loc,name_mech,vtype,vreg,last_startdate,due_end_date,mileage,cur_date) 
		
values 

('$com_name','$loc','$name_mech','$vtype','$vreg','$last_startdate','$due_end_date','$current_mileage','$cur_date')";
		 }else{
			 
 $sql = "UPDATE sre_log SET 

com_name='$com_name',loc='$loc',name_mech='$name_mech',vtype='$vtype',last_startdate='$last_startdate',due_end_date='$due_end_date',mileage='$current_mileage',cur_date='$cur_date' WHERE vreg='$vreg'";
		 }
							
		
		mysql_query($sql);

php

3 Contributors
10 Replies
136 Views
1 Week Discussion Span
Latest Post 12 Years Ago Latest Post by simplypixie

All 10 Replies

diafol

12 Years Ago

You did it again:

mysql_numrows

simplypixie 123 Posting Pro in Training

12 Years Ago

ardav means

mysql_numrows

is wrong, it doesn't do or mean anything as it should be

mysql_num_rows

What is the point of using ON DUPLICATE KEY UPDATE when all you have to do is use an update query?
You also have mysql_QUERY which should be mysql_query in line 4 and this code is pointless

$i = 0;
		while($i < $sre_log_num) 
			{
				$counter = mysql_result($sre_log_result,$i,"counter");
 
					$i++;
			}

All you need is

while($row = mysql_fetch_array($srelog_result)) 
			{
				$counter ++;
 
			}

Edited 12 Years Ago by simplypixie because: n/a

simplypixie 123 Posting Pro in Training

12 Years Ago

What you are saying you want to do will cause your above script not to work as it is more complicated than just being able to rely on a count.

Presumably each record has an id set as a primary key (I hope anyway) as you will need to use this and change your query. You will need to select * (rather than using a count) and get the last record so that you can just use that one to see if it can be updated or whether you need to create a new record. Obviously you will also need to check if a record exists at all still.

Something like (this is not tested but hopefully gives you an idea of what I mean):

$insert=0;
	
	$sre_log_result = mysql_query("select * FROM sre_log WHERE vreg ='$vreg' ORDER BY '$id' DESC LIMIT 1");  
	$sre_log_num = mysql_num_rows($sre_log_result);
	
	if ($sre_log_num > 0) {
		$record = mysql_fetch_array($sre_log_result);
		
		if (isset($record['current_mileage']) && $record['current_mileage'] != '') {
			$insert = 1;
		}
	} else {
		$insert = 1;
	}
	
	if ($insert == 1) {
		$sql = "insert INTO sre_log 		
		values ('$com_name','$loc','$name_mech','$vtype','$vreg','$last_startdate','$due_end_date','$current_mileage','$cur_date')";
	} else {
		$sql = "UPDATE sre_log SET com_name='$com_name',loc='$loc',name_mech='$name_mech',vtype='$vtype',last_startdate='$last_startdate',due_end_date='$due_end_date',mileage='$current_mileage',cur_date='$cur_date' WHERE vreg='$vreg'";
	}
		
		mysql_query($sql);

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

accra 1 Junior Poster in Training · Answer 1 · 2012-01-07T11:28:39+00:00

accra 1 Junior Poster in Training

12 Years Ago

Pls. explain

mysql_numrows

accra 1 Junior Poster in Training · Answer 2 · 2012-01-07T12:49:00+00:00

I did some reading and found this function 'ON DUPLICATE KEY UPDATE ' .

so i came up with this,but no insert or update is made.

any reason why?

$sql = "insert INTO sre_log
		
		(com_name,loc,name_mech,vtype,vreg,last_startdate,due_end_date,mileage,cur_date) 

		values 

('$com_name','$loc','$name_mech','$vtype','$vreg','$last_startdate','$due_end_date','$current_mileage','$cur_date')

ON DUPLICATE KEY UPDATE 


com_name='$com_name',loc='$loc',name_mech='$name_mech',vtype='$vtype',last_startdate='$last_startdate',due_end_date='$due_end_date',mileage='$current_mileage',cur_date='$cur_date' WHERE vreg='$vreg'";

accra 1 Junior Poster in Training · Answer 3 · 2012-01-08T12:01:12+00:00

Thanks for this, its short and to the point.

Now its inserting and updating.

Problem:

I have two fields in my db, mileage and current_mileage ,what i want to do is this:

1. when i insert a value into 'mileage' for the first time WHERE vreg='$vreg' then it should stay that way, but when i change the value in my from WHERE vreg='$vreg' then it should update 'current_mileage'.

2. But when i change the value for the third time, then it should insert it into 'mileage'. Its just a continues circle, over writing the value of 'mileage'.

what i get now, it just keep inserting into 'mileage' any time i submit the form.

$counter=0;
	$sre_log_num = 0;
	$sre_log_result = mysql_query("select count(*) As counter FROM sre_log WHERE vreg ='$vreg'");  
	$sre_log_num = mysql_numrows($sre_log_result);
	$i = 0;
		    while($row = mysql_fetch_array($sre_log_result))
			
				{
					
				$counter ++;
				 
				}

		 if ($counter ==0 )
		 
		 {
			 
		$sql = "insert INTO sre_log
		
		
		values ('$com_name','$loc','$name_mech','$vtype','$vreg','$last_startdate','$due_end_date','$current_mileage','$cur_date')";
		 }else{
			 
		
$sql = "UPDATE sre_log SET com_name='$com_name',loc='$loc',name_mech='$name_mech',vtype='$vtype',last_startdate='$last_startdate',due_end_date='$due_end_date',mileage='$current_mileage',cur_date='$cur_date' WHERE vreg='$vreg'";
		 }
							
		
		mysql_query($sql);

accra 1 Junior Poster in Training · Answer 4 · 2012-01-12T20:40:23+00:00

Thanks for the help,my internet connection was bad hence my delay in getting back.

While away, i managed to solve it.But now it is not inserting at all. This is what i get when i run the code in mysql :

MySQL said: Documentation
#1136 - Column count doesn't match value count at row 1

And this the code :

if($navtype == "serxbh")
	 
 {
	     
		 $com_name = $_POST['com_name'];
		 $loc = $_POST['loc'];
		 $name_mech = $_POST['name_mech'];
		 $vtype = $_POST['vtype'];
		 $s_veration = $_POST['s_veration'];
		 $remnder_ver = $_POST['remnder_ver'];
		 $vreg = $_POST['vreg'];
		 $last_startdate = $_POST['last_startdate'];		 	 
		 $mileage = $_POST['mileage'];	
		 $current_mileage = $_POST['current_mileage'];	
		 $cur_date = $_POST['cur_date'];

	   $counter=0;
	   $query = "select count(*) As counter FROM sre_log WHERE vreg = '$vreg'";  
		 $routes_result = mysql_query($query);
		 $routes_num = 0;
		 $routes_num = mysql_numrows($routes_result);
		 $i = 0;
		//$x = 1
	    while($i < $routes_num) 
			
			{
				$counter = mysql_result($routes_result,$i,"counter");
		
				$i++;
			}
			
		 if ($counter == 0 )
		 
		 {
			 
		
		$sql = "insert INTO sre_log
		values('$com_name','$loc','$name_mech','$vtype','$s_veration','$remnder_ver','$vreg','$last_startdate','$mileage','$current_mileage','$cur_date')";
		 
		 }else{
			 		
$sql = "UPDATE sre_log SET com_name='$com_name',loc='$loc',name_mech='$name_mech',vtype='$vtype',s_veration='$s_veration',remnder_ver='$remnder_ver',vreg='$vreg',last_startdate='$last_startdate',mileage='$mileage',current_mileage='$current_mileage',cur_date='$cur_date' WHERE vreg='$vreg'";
		 
		mysql_query($sql);
		 

		 }

simplypixie 123 Posting Pro in Training · Answer 5 · 2012-01-12T21:48:24+00:00

That is because you haven't specified the columns in your insert query

$sql = "insert INTO sre_log
		values('$com_name','$loc','$name_mech','$vtype','$s_veration','$remnder_ver','$vreg','$last_startdate','$mileage','$current_mileage','$cur_date')";

The above is wrong and should be

$sql = "insert INTO sre_log (com_name, loc, name_mech, vtype, s_veration, remnder_ver, vreg, last_startdate, mileage, current_mileage, cur_date) 		values('$com_name','$loc','$name_mech','$vtype','$s_veration','$remnder_ver','$vreg','$last_startdate','$mileage','$current_mileage','$cur_date')";

Also, are you sure you have all the columns that are actually in the table?

accra 1 Junior Poster in Training · Answer 6 · 2012-01-15T14:16:56+00:00

i have specified the columns in my insert query and still it is not inserting.

if($navtype == "serxbh")
	 
 {
	     
		 $com_name = $_POST['com_name'];
		 $loc = $_POST['loc'];
		 $name_mech = $_POST['name_mech'];
		 $vtype = $_POST['vtype'];
		 $s_veration = $_POST['s_veration'];
		 $remnder_ver = $_POST['remnder_ver'];
		 $vreg = $_POST['vreg'];
		 $last_startdate =$_POST['dy']."-".$_POST['dm']."-".$_POST['dd'];	
		 $mileage = $_POST['mileage'];	
		 $current_mileage = $_POST['current_mileage'];	
		 $cur_date = date("F j, Y, g:i a");
	
	   $counter=0;
	   $query = "select count(*) As counter FROM sre_log WHERE vreg = '$vreg'";  
		 $routes_result = mysql_query($query);
		 $routes_num = 0;
		 $routes_num = mysql_numrows($routes_result);
		 $i = 0;
		//$x = 1
	    while($i < $routes_num) 
			
			{
				$counter = mysql_result($routes_result,$i,"counter");
		
				$i++;
			}
			
		 if ($counter == 0 )
		 
		 {
			 
		
		$sql = "insert INTO sre_log (com_name, loc, name_mech, vtype, s_veration, remnder_ver, vreg, last_startdate, mileage, current_mileage, cur_date) 
		values('$com_name','$loc','$name_mech','$vtype','$s_veration','$remnder_ver','$vreg','$last_startdate','$mileage','$current_mileage','$cur_date')";
		 
		 }else{
			 		
$sql = "UPDATE sre_log SET com_name='$com_name',loc='$loc',name_mech='$name_mech',vtype='$vtype',s_veration='$s_veration',remnder_ver='$remnder_ver',vreg='$vreg',last_startdate='$last_startdate',mileage='$mileage',current_mileage='$current_mileage',cur_date='$cur_date' WHERE vreg='$vreg'";
		 
		mysql_query($sql);
		 
$sql = "UPDATE movlog SET mileage = '$mileage_in' WHERE vreg='$vreg'";
		
		mysql_query($sql);
		
		 }

simplypixie 123 Posting Pro in Training · Answer 7 · 2012-01-15T14:29:19+00:00

I am still not entirely sure what you are counting for as you don't seem to have more than one record per vreg and you are not updating in your while statement.
Plus I have changed your mysql_numrows (INCORRECT) to mysql_num_rows more than once now but you still have the incorrect code.
What is your reason for it? And why would you want to return a count from your db query which will only return one result and then use mysql_num_rows anyway?
Can you just explain a little more exactly what you are trying to do here?

counter script only inserting ,not updating

Recommended Answers Collapse Answers

All 10 Replies

Recommended Answers