using php to update a range of records in mysql

I'm trying to write a small script to update a range of records.

the code i attached is only updating one record which is the first value i specify

its not updating any others withing the range

eg
 in the db there are 3 cards
001
002
003

The status of all of these are currently set to 3

my script is supposed to select all between 001 and 003 if i specify the numbers and change the status to 0

What its doing is settign the status on 001 as 0 and all others 002 and 003 remain the same status of 3

Any help and suggestions will be welcomed ! I'm no php guru .. still learning
<?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 = $_POST[cardid];
		$card2 = $_POST[cardid2];
		mysql_query("UPDATE cardlist set status='0' WHERE cardid between '".$card1."' AND '".$card2."'");
		$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>

Open in new window

LVL 8
coolfigerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pratima PharandeCommented:
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);
0
dsmileCommented:
Replace code on line 15, 16 by this to see if it helps

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

Open 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
0
Lukasz ChmielewskiCommented:
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);
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

StephangerlachCommented:
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');

}
		

Open in new window

0
coolfigerAuthor Commented:
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
0
StephangerlachCommented:
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??
0
dsmileCommented:
@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."'");

Open 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'
0
coolfigerAuthor Commented:
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 ?
0
coolfigerAuthor Commented:
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>

Open in new window

0
coolfigerAuthor Commented:
could it be the string length ?
0
dsmileCommented:
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>

Open in new window

0
coolfigerAuthor Commented:
@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 ?
0
Lukasz ChmielewskiCommented:
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.
0
coolfigerAuthor Commented:
thanks roads.

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

so i cant make it a interger
0
StephangerlachCommented:
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>
0
Lukasz ChmielewskiCommented:
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.
0
Lukasz ChmielewskiCommented:
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
0
coolfigerAuthor Commented:
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.

0
StephangerlachCommented:
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;
}

Open in new window

0
coolfigerAuthor Commented:
Stephangerlach: Thanks again :

where in the code do you want me to put this ? My php knowledge is very limited
0
StephangerlachCommented:
Right hat the top of the page
0
coolfigerAuthor Commented:
it keeps throwing errors when i put that in ....
0
Lukasz ChmielewskiCommented:
Just give me a sign :D
0
dsmileCommented:
@coolfiger:  there's nothing wrong with a varchar field and a between ...and.. query since I tried it on my own DB.


Here's what I used to simulate your case

CREATE TABLE cardlist (
  cardid varchar(11) NOT NULL DEFAULT '',
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (cardid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Dumping data for table `cardlist`
#

INSERT INTO cardlist VALUES ('01-000', 3);
INSERT INTO cardlist VALUES ('02-123', 3);
INSERT INTO cardlist VALUES ('03-000', 3);


If you enter 01 and 03 on the form, then all three records' status will be updated to 0

----
Run my code again, this time copy & paste the result here
<?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;
		echo $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>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dsmileCommented:
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>

Open in new window

0
coolfigerAuthor Commented:
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
0
coolfigerAuthor Commented:
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

0
dsmileCommented:
Give me the sql that the script printed
0
coolfigerAuthor Commented:
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'


0
dsmileCommented:
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
0
dsmileCommented:
What will happen in these cases?

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

case2:
cardid1 = 02-12345
cardid2 = 06-123
0
coolfigerAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.