using php to update a range of records in mysql

As cardid is not stored as integer value between will not work in such way
as per my understanding
You have all this cardid's stroed with you right means
$_POST[cardid] = '001'
$_POST[cardid1] = '002'
$_POST[cardid2] = '003'

Ten try this
$card1 = $_POST[cardid];
$card2 = $_POST[cardid1];
$card3 = $_POST[cardid2];

            mysql_query("UPDATE cardlist set status='0' WHERE cardid = '".$card1."' OR cardid = '".$card2."' OR cardid = '".$card3);

Replace code on line 15, 16 by this to see if it helps

                $card1 = $_POST['cardid'];
                $card2 = $_POST['cardid2'];

Select allOpen in new window

If it doesn't work then give us the schema of table cardlist and some sample data.
There maybe something wrong with datatype

pratima_mcs is right, if the cardids ARE integer, then try this query instead:
mysql_query("UPDATE cardlist set status='0' WHERE cardid between ".$card1." AND ".$card2);

Since you said you numbered them 001 through to 003 I assume the the fields are of varchar. In this case (I might be wrong) the sql command between won't work.

This is how I would do it.

// convert the string to a number
$card1 = (int)$_POST[cardid];
$card2 = (int)$_POST[cardid2];

// loop from the first number to the last number
for ($i = $card1; $i<=$card2; $i++) {
        // take a copy of $i
        $id = $i;
       // add 0's at the front until strlen is 3
        while (strlen($id)<3) {
            $id = '0'.$id;
        }
       // update database
        mysql_query('UPDATE `cardlist` SET `status`=0 WHERE `cardid`="'.$id.'" LIMIT 1');

}
		

Select allOpen in new window

All thanks for your solutions.

Problem lines  where the field is set to varchar. Reason for this is we had to put in dashes do

card id is 01-000  or 02-0123  etc

Stephangerlach your solution makes sence.. but it only updates one record in the table and not the entire range... which is the same problem i've been having from the start

I am sorry but that is not possible.

I have tested this code and it works!

I suggest you test and see that your post values are correct.

Also you said at first that your values are 001 and 003 now you say there is are 2 numbers and a dash in front of it. So what is the exact format of the values that you store in cardid??

@coolfiger: if cardid is entered in exactly format of the prefix of each cardid in DB, then you can use this query

                $card1 = $_POST['cardid'];
                $card2 = $_POST['cardid2'];
                mysql_query("UPDATE cardlist set status='0' WHERE SUBSTR(cardid, 1, 3) between '".$card1."' AND '".$card2."'");

Select allOpen in new window

Eg: this suppose your cardid prefix is a 3 char length string, with 001 and 003 as input, the query should be like this
UPDATE `cardlist` SET status=0 WHERE SUBSTR(cardid, 1, 3) BETWEEN '001' AND '003'

well for my test data i'm using the card id's below

06-1234
06-1235
06-1236
06-1237

the format would be like this but are not limited to 6 characters . it may increase to xx-xxxxxxxxxx  the first two numbers will always remain as 2 xx-

when you say test the post values are correct what do you mean ?

this is the latest code i'm working with ... looks correct here .... Let me know if im missing something

<?php
include 'dbc.php';
page_protect();
?>
<?php
include('dbconfig.php');
include('header.php');
$msg="";
if($_SERVER['REQUEST_METHOD']=="POST"){
	$cardresult=mysql_query("SELECT * FROM cardlist WHERE cardid='$_POST[cardid]' AND status=1");
	$cardcount = mysql_num_rows($cardresult);
	if($cardcount>0){
		$msg='<p style="color:#f00;">Sorry... this card was already issuded. Please select another</p>';
	}else{
		
$card1 = (int)$_POST[cardid];
$card2 = (int)$_POST[cardid2];

// loop from the first number to the last number
for ($i = $card1; $i<=$card2; $i++) {
        // take a copy of $i
        $id = $i;
       // add 0's at the front until strlen is 3
        while (strlen($id)<3) {
            $id = '0'.$id;
        }
       // update database
        mysql_query('UPDATE `cardlist` SET `status`=0 WHERE `cardid`="'.$id.'" LIMIT 50');

}
		$msg='<p style="color:#009900;">Success. Information was updated</p>';
	}
}
?>

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Temporary Relief Cards</title>
<link href="style.css" rel="stylesheet" type="text/css">
<script type="text/javascript">
function verify() {
	var ok = confirm("Are you sure you want to add this record? Please make sure all fields are filled in.");
	DestURL = 'index.php';
	if (ok) {location.href = DestURL;}
		return ok;
} 

$(function() {
		$("#date").datepicker( "option", "dateFormat", "yyyy-mm-dd" );
	});
</script>

</head>
<body id="main_body" >
<div id="form_container">

<h1>Temporary Relief Cards</h1><?=$msg?>
		<form id="signup" method="post" action="index.php"   onSubmit="return verify()">
					<table width="850" border="0" cellpadding="5" cellspacing="0" id="tblform">
					  <tr>
					    <td colspan="2" valign="top" bgcolor="#000000"><h2>Personal Details</h2></td>
					    <td colspan="2" valign="top" bgcolor="#000000"><h2>Relief Card Details</h2></td>
				      </tr>
					  <tr>
					    <td align="right" valign="top">&nbsp;</td>
					    <td valign="top">&nbsp;</td>
					    <td valign="top"><strong>Activate From:</strong></td>
					    <td valign="top"><input name="cardid" type="text" id="cardid" size="30" /></td>
				      </tr>
					  <tr>
					    <td valign="top">&nbsp;</td>
					    <td valign="top">&nbsp;</td>
					    <td width="16%" valign="top"><strong>Activate To:</strong></td>
					    <td width="25%" valign="top"><input name="cardid2" type="text" id="cardid2" size="30" /></td>
				      </tr>
					  <tr>
					    <td align="right" valign="top">&nbsp;</td>
					    <td valign="top">&nbsp;</td>
					    <td colspan="2" align="center" valign="top"><input id="submit"  type="submit" name="submit" value="Activate Batch" /></td>
				      </tr>
          </table>
</form>	
</div>
</body>
</html>

Select allOpen in new window

could it be the string length ?

Use this code, you don't need a look to do what you need.
Just run with between query is ok

<?php
include 'dbc.php';
page_protect();
?>
<?php
include('dbconfig.php');
include('header.php');
$msg="";
if($_SERVER['REQUEST_METHOD']=="POST"){
        $cardresult=mysql_query("SELECT * FROM cardlist WHERE cardid='$_POST[cardid]' AND status=1");
        $cardcount = mysql_num_rows($cardresult);
        if($cardcount>0){
                $msg='<p style="color:#f00;">Sorry... this card was already issuded. Please select another</p>';
        }else{
                
$card1 = (int)$_POST['cardid'];
$card2 = (int)$_POST['cardid2'];

		if ($card1 < 10) $card1 = '0'. $card1;
		if ($card2 < 10) $card2 = '0'. $card2;
		$sql = "UPDATE cardlist set status='0' WHERE SUBSTR(cardid, 1, 2) between '".$card1."' AND '".$card2."'";
		mysql_query($sql);
		$msg='<p style="color:#009900;">Success. Information was updated</p>';
        }
}
?>

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Temporary Relief Cards</title>
<link href="style.css" rel="stylesheet" type="text/css">
<script type="text/javascript">
function verify() {
        var ok = confirm("Are you sure you want to add this record? Please make sure all fields are filled in.");
        DestURL = 'index.php';
        if (ok) {location.href = DestURL;}
                return ok;
} 

$(function() {
                $("#date").datepicker( "option", "dateFormat", "yyyy-mm-dd" );
        });
</script>

</head>
<body id="main_body" >
<div id="form_container">

<h1>Temporary Relief Cards</h1><?=$msg?>
                <form id="signup" method="post" action="index.php"   onSubmit="return verify()">
                                        <table width="850" border="0" cellpadding="5" cellspacing="0" id="tblform">
                                          <tr>
                                            <td colspan="2" valign="top" bgcolor="#000000"><h2>Personal Details</h2></td>
                                            <td colspan="2" valign="top" bgcolor="#000000"><h2>Relief Card Details</h2></td>
                                      </tr>
                                          <tr>
                                            <td align="right" valign="top">&nbsp;</td>
                                            <td valign="top">&nbsp;</td>
                                            <td valign="top"><strong>Activate From:</strong></td>
                                            <td valign="top"><input name="cardid" type="text" id="cardid" size="30" /></td>
                                      </tr>
                                          <tr>
                                            <td valign="top">&nbsp;</td>
                                            <td valign="top">&nbsp;</td>
                                            <td width="16%" valign="top"><strong>Activate To:</strong></td>
                                            <td width="25%" valign="top"><input name="cardid2" type="text" id="cardid2" size="30" /></td>
                                      </tr>
                                          <tr>
                                            <td align="right" valign="top">&nbsp;</td>
                                            <td valign="top">&nbsp;</td>
                                            <td colspan="2" align="center" valign="top"><input id="submit"  type="submit" name="submit" value="Activate Batch" /></td>
                                      </tr>
          </table>
</form> 
</div>
</body>
</html>

Select allOpen in new window

@dsmile

thanks for your suggestion. I just tried this on the site. Same result only the first $card1 is updated and not the range. could it be bcasue the field is varchar ?

I'd suggest you should use a field where the values are integer. Add the column related to the varchar id if you want to manipulate the numbers. SQL's "between" works for integers. So if you want to have no problem updating, then instead of too complex query or string manipulation - add the column where the id IS integer.

thanks roads.

thing is the cardid has a - ( dash )  like 12-00000

so i cant make it a interger

Right if you have 06- in front of it then you need to do it the following way


<?php
include 'dbc.php';
page_protect();
?>
<?php
include('dbconfig.php');
include('header.php');
$msg="";
if($_SERVER['REQUEST_METHOD']=="POST"){
      $cardresult=mysql_query("SELECT * FROM cardlist WHERE cardid='$_POST[cardid]' AND status=1");
      $cardcount = mysql_num_rows($cardresult);
      if($cardcount>0){
            $msg='<p style="color:#f00;">Sorry... this card was already issuded. Please select another</p>';
      }else{
            
$card1 = (int)$_POST[cardid];
$card2 = (int)$_POST[cardid2];

// loop from the first number to the last number
for ($i = $card1; $i<=$card2; $i++) {
        // take a copy of $i
        $id = $i;
       // add 0's at the front until strlen is 3
        while (strlen($id)<3) {
            $id = '0'.$id;
        }
       // update database
        mysql_query('UPDATE `cardlist` SET `status`=0 WHERE `cardid`="06-'.$id.'" LIMIT 50');

}
            $msg='<p style="color:#009900;">Success. Information was updated</p>';
      }
}
?>

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Temporary Relief Cards</title>
<link href="style.css" rel="stylesheet" type="text/css">
<script type="text/javascript">
function verify() {
      var ok = confirm("Are you sure you want to add this record? Please make sure all fields are filled in.");
      DestURL = 'index.php';
      if (ok) {location.href = DestURL;}
            return ok;
}

$(function() {
            $("#date").datepicker( "option", "dateFormat", "yyyy-mm-dd" );
      });
</script>

</head>
<body id="main_body" >
<div id="form_container">

<h1>Temporary Relief Cards</h1><?=$msg?>
            <form id="signup" method="post" action="index.php"   onSubmit="return verify()">
                              <table width="850" border="0" cellpadding="5" cellspacing="0" id="tblform">
                                <tr>
                                  <td colspan="2" valign="top" bgcolor="#000000"><h2>Personal Details</h2></td>
                                  <td colspan="2" valign="top" bgcolor="#000000"><h2>Relief Card Details</h2></td>
                              </tr>
                                <tr>
                                  <td align="right" valign="top">&nbsp;</td>
                                  <td valign="top">&nbsp;</td>
                                  <td valign="top"><strong>Activate From:</strong></td>
                                  <td valign="top"><input name="cardid" type="text" id="cardid" size="30" /></td>
                              </tr>
                                <tr>
                                  <td valign="top">&nbsp;</td>
                                  <td valign="top">&nbsp;</td>
                                  <td width="16%" valign="top"><strong>Activate To:</strong></td>
                                  <td width="25%" valign="top"><input name="cardid2" type="text" id="cardid2" size="30" /></td>
                              </tr>
                                <tr>
                                  <td align="right" valign="top">&nbsp;</td>
                                  <td valign="top">&nbsp;</td>
                                  <td colspan="2" align="center" valign="top"><input id="submit"  type="submit" name="submit" value="Activate Batch" /></td>
                              </tr>
          </table>
</form>      
</div>
</body>
</html>

OK, this is a problem of a database design. Updating the records WILL be complex as the working code. You can add a column with a single query and then copy the values with dash sign to the new column, removing the dash. The numbers will stay ascending.

For example:
06-1234
06-1235
06-1236
06-1237

copied to the new column would be:

61234
61235
61236
61237

and then you will just do update where something between 61234 and 61237

Stephangerlach: thanks

i tried your code. It only updates one record ... so still stuck. Thanks for all your help

Roads: If i cant get it resolved i guess i would need to do this.

sorry to say again this code does work. I think it will be an issue with the POST data

run this code and post what it displays along with the cardid and cardid2 that you wanted to provide

if($_SERVER['REQUEST_METHOD']=="POST"){
     print_r($_POST);exit;
}

Select allOpen in new window

Stephangerlach: Thanks again :

where in the code do you want me to put this ? My php knowledge is very limited

Right hat the top of the page

it keeps throwing errors when i put that in ....

Just give me a sign :D

Just edited the code so that it take any integers as input, and your cardid in DB can be any string in format xxx-yyyyy (where numbers of x and y don't matter as long as they have a dash between them)


Test data:
cardid               status  
001-0001241   1
001-14124       2
01-124             3
02-1231           2
04-0001           2
3-000125         3

if you enter 1 (or 01, 001, ...) and 3 (or 03, 003, ...) at the form, you'll have this
cardid               status  
001-0001241   0
001-14124       0
01-124             0
02-1231           0
04-0001           2
3-000125         0

<?php
include 'dbc.php';
page_protect();
?>
<?php
include('dbconfig.php');
include('header.php');
$msg="";
if($_SERVER['REQUEST_METHOD']=="POST"){
        $cardresult=mysql_query("SELECT * FROM cardlist WHERE cardid='$_POST[cardid]' AND status=1");
        $cardcount = mysql_num_rows($cardresult);
        if($cardcount>0){
			$msg='<p style="color:#f00;">Sorry... this card was already issuded. Please select another</p>';
        }else{
                
			$card1 = (int)$_POST['cardid'];
			$card2 = (int)$_POST['cardid2'];

			echo $sql = "UPDATE `cardlist` SET `status`='0' WHERE CAST(SUBSTR(`cardid`, 1, LOCATE('-', `cardid`) - 1) AS SIGNED) BETWEEN '".$card1."' AND '".$card2."'";
			mysql_query($sql);
			$msg='<p style="color:#009900;">Success. Information was updated</p>';
        }
}
?>

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Temporary Relief Cards</title>
<link href="style.css" rel="stylesheet" type="text/css">
<script type="text/javascript">
function verify() {
        var ok = confirm("Are you sure you want to add this record? Please make sure all fields are filled in.");
        DestURL = 'index.php';
        if (ok) {location.href = DestURL;}
                return ok;
} 

$(function() {
                $("#date").datepicker( "option", "dateFormat", "yyyy-mm-dd" );
        });
</script>

</head>
<body id="main_body" >
<div id="form_container">

<h1>Temporary Relief Cards</h1><?=$msg?>
                <form id="signup" method="post" action="index.php"   onSubmit="return verify()">
                                        <table width="850" border="0" cellpadding="5" cellspacing="0" id="tblform">
                                          <tr>
                                            <td colspan="2" valign="top" bgcolor="#000000"><h2>Personal Details</h2></td>
                                            <td colspan="2" valign="top" bgcolor="#000000"><h2>Relief Card Details</h2></td>
                                      </tr>
                                          <tr>
                                            <td align="right" valign="top">&nbsp;</td>
                                            <td valign="top">&nbsp;</td>
                                            <td valign="top"><strong>Activate From:</strong></td>
                                            <td valign="top"><input name="cardid" type="text" id="cardid" size="30" /></td>
                                      </tr>
                                          <tr>
                                            <td valign="top">&nbsp;</td>
                                            <td valign="top">&nbsp;</td>
                                            <td width="16%" valign="top"><strong>Activate To:</strong></td>
                                            <td width="25%" valign="top"><input name="cardid2" type="text" id="cardid2" size="30" /></td>
                                      </tr>
                                          <tr>
                                            <td align="right" valign="top">&nbsp;</td>
                                            <td valign="top">&nbsp;</td>
                                            <td colspan="2" align="center" valign="top"><input id="submit"  type="submit" name="submit" value="Activate Batch" /></td>
                                      </tr>
          </table>
</form> 
</div>
</body>
</html>

Select allOpen in new window

thanks for yoru help. i will try it in a while. Please note i dont have cardid set as my primary key i jsut have a auto increment field which started at 1 as the primary

Test data:
cardid               status  
06-1234            3
06-1235             3
06-1337            3

i ran your script which produced the result below
Test data:
cardid               status  
06-1234            1
06-1235             3
06-1337            3

Give me the sql that the script printed

is this what you wanted to get?

Array ( [cardid] => 06-1235 [cardid2] => 06-1237 [submit] => Activate Batch ) UPDATE `cardlist` SET `status`='0' WHERE CAST(SUBSTR(`cardid`, 1, LOCATE('-', `cardid`) - 1) AS SIGNED) BETWEEN '6' AND '6'

Well, guess I misunderstood your requirement (thought that user only enters the first two digits of cardid)

Is this what you need??
1. user enter cardid1 and cardid2 in form of xx-yyyyy (where xx has 2 digits, yyyy has unlimited digits)
2. script needs to update all records in range of cardid1 and cardid2

What will happen in these cases?

case1:
cardid1 = 06-12345
cardid2 = 02-123

case2:
cardid1 = 02-12345
cardid2 = 06-123

hey dsmile. Thanks  I got it working.  I removed some of the validations i had on the script and my orignial worked with a few tweaks from yours. I will award points soon.

Thanks again