help selecting only 100 rows

[bodycount]

I need help with a query.

Say I have a database with a 1000 serial numbers in it, and I want to get a batch of 100 serial numbers out, So i can update them with customer details. How would i do it. I tried the following but it got every one above 2007101164 and did not stop at 2007101264.

SELECT `SERIALNO` FROM products WHERE (`SERIALNO` >= "2007101164") ¦¦ (`SERIALNO` = "2007101264")ORDER BY `SERIALNO` ASC;

I think the problem is with the " ¦¦ (`SERIALNO` = "2007101264") " bit of the code.

[omoutop]

try the BETWEEN synttax

SELECT `SERIALNO` FROM products WHERE SERIALNO BETWEEN 2007101164 AND 2007101264 ORDER BY `SERIALNO` ASC;

[bodycount]

Thanks for your help, that did the trick.

[bodycount]

OK i have got the 100 serial numbers out of the database and i want to update some of the info that relates to these 100 serial numbers but when i use the following it only updates the last serial number with the info.

$sql = "UPDATE `products` SET `SERIALNO` = '$SERIALNO', `IMEI` = '$IMEI', `CANSERIAL` = '$CANSERIAL', `COMAPNY_id` = '$COMAPNY_id' WHERE `SERIALNO` = 'SERIALNO'";

How do i get it to do all 100 updates in one go?

[eelixduppy]

Try using this query:
$sql = "UPDATE products SET SERIALNO = '$SERIALNO', IMEI = '$IMEI', CANSERIAL = '$CANSERIAL', COMAPNY_id = '$COMAPNY_id' WHERE SERIALNO BETWEEN 2007101164 AND 2007101264";

Also, add some debugging to your mysql_query() function just in case any errors are thrown:

mysql_query($sql) or [url=http://us3.php.net/manual/en/function.die.php]die[/url]([url=http://us3.php.net/manual/en/function.mysql-error.php]mysql_error[/url]());

[bodycount]

That worked sort of.

It updated all the records, but it updated them with all the same data from the last record 2007101264 I have now the same data 100 times, thank god this is just a test database HEHE!

Any Ideas.

[omoutop]

// select loop
Select field_names FROM table WHERE criteria ORDER BY field_name
while _this_data_are_selected
{
execute update in selected id
}

[Vali]

To select them all:
SELECT id, SERIALNO FROM products WHERE SERIALNO >= "2007101164" LIMIT 100;

To update them, you can't do it in one go with different data for each record, so you would need a small for loop:

loop posted IDs
UPDATE products SET SERIALNO = '$POSTED_VALUE[$ID]' WHERE id = '$ID';

Hope that helps.

Vali

[bodycount]

I cant get it to work can you have a look at my code to see what i am doing wrong.

I have 3 pages the first one is a html page which you use to type the serial number range you want to get out of the database simple form.

The second page is as follows. the FROM and TO are from the first page.

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

<form action="pre_serial_update.php" method=post>

<?php
error_reporting(E_ALL);
include ("connection.php");

$FROM = (isset($_GET['FROM']))? $_GET['FROM'] : '';
$TO = (isset($_GET['TO']))? $_GET['TO'] : '';

$fromto = ("SELECT * FROM products WHERE SERIALNO BETWEEN '$FROM' AND '$TO' ORDER BY `SERIALNO` ASC");
$result = mysql_query($fromto);
if($num=mysql_numrows($result))
{

while ($record = mysql_fetch_array($result))
{

$SERIALNO = $record["SERIALNO"];
$IMEI = $record["IMEI"];
$CANSERIAL = $record["CANSERIAL"];
$CANTYPE = $record["CANTYPE"];
$COMPANY_id = $record["COMPANY_id"];
$BUILDTYPE = $record["BUILDTYPE"];
$MACADDRESS = $record["MACADDRESS"];
$SHIPDATE = $record["SHIPDATE"];
$DESCRIPTION = $record["DESCRIPTION"];
$product_id = $record["product_id"];

echo "<input type\"text\" name=\"product_id\" size=\"10\" maxlength=\"10\" value=\"$product_id\"><input type\"text\" name=\"SERIALNO\" size=\"10\" maxlength=\"10\" value=\"$SERIALNO\"> CUT OUT REST TO SORTEN POST";

}
}
echo "<input type\"hidden\" name=\"FROM\" size=\"10\" maxlength=\"10\" value=\"$FROM\">";
echo "<input type\"hidden\" name=\"TO\" size=\"10\" maxlength=\"10\" value=\"$TO\">";
echo "<input type =\"submit\" value=\"Amend the Record\">";
echo "</form>";
?>
-------------------------------------------------

third page is the update page

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

<?php
error_reporting(E_ALL);
include ("connection.php");

$FROM = (isset($_GET['FROM']))? $_GET['FROM'] : '';
$TO = (isset($_GET['TO']))? $_GET['TO'] : '';

$SERIALNO = (isset($_POST['SERIALNO']))? $_POST['SERIALNO'] : '';
$IMEI = (isset($_POST['IMEI']))? $_POST['IMEI'] : '';
$CANSERIAL = (isset($_POST['CANSERIAL']))? $_POST['CANSERIAL'] : '';
$CANTYPE = (isset($_POST['CANTYPE']))? $_POST['CANTYPE'] : '';
$COMPANY_id = (isset($_POST['COMPANY_id']))? $_POST['COMPANY_id'] : '';
$BUILDTYPE = (isset($_POST['BUILDTYPE']))? $_POST['BUILDTYPE'] : '';
$MACADDRESS = (isset($_POST['MACADDRESS']))? $_POST['MACADDRESS'] : '';
$SHIPDATE = (isset($_POST['SHIPDATE']))? $_POST['SHIPDATE'] : '';
$DESCRIPTION = (isset($_POST['DESCRIPTION']))? $_POST['DESCRIPTION'] : '';
$product_id = (isset($_POST['product_id']))? $_POST['product_id'] : '';
$FROM = (isset($_POST['FROM']))? $_POST['FROM'] : '';
$TO = (isset($_POST['TO']))? $_POST['TO'] : '';
$product_id = (isset($_POST['product_id']))? $_POST['product_id'] : '';

echo "<form>";
echo "<fieldset>";
echo "<legend>Amend Info</legend>";
echo "<input type\"text\" name=\"product_id\" size=\"10\" maxlength=\"10\" value=\"$product_id\"><input type\"text\" name=\"SERIALNO\" size=\"10\" maxlength=\"10\" value=\"$SERIALNO\"> CUT OUT REST TO SORTEN POST";

$sql = "UPDATE products SET `SERIALNO` = '$SERIALNO', `IMEI` = '$IMEI', `CANSERIAL` = '$CANSERIAL', `CANTYPE` = '$CANTYPE', `COMPANY_id`= '$COMPANY_id', `BUILDTYPE` = '$BUILDTYPE' , `MACADDRESS` = '$MACADDRESS', `SHIPDATE` = '$SHIPDATE', `DESCRIPTION` = '$DESCRIPTION' WHERE SERIALNO BETWEEN '$FROM' AND '$TO'";

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

//$result = mysql_query($sql) or die(mysql_error());

if (!mysql_query($sql))
{
echo "ERROR cannot Amend Record!";
} else
{
echo "<br>The Address for <b>$SERIALNO</b> has been amended";
echo "</form>";
echo "</fieldset>";
}
}
?>
--------------------------------------------------

Thanks

BC

[Vali]

echo "<input type\"text\" name=\"product_id\" size=\"10\" maxlength=\"10\" value=\"$product_id\"><input type\"text\" name=\"SERIALNO\" size=\"10\" maxlength=\"10\" value=\"$SERIALNO\"> CUT OUT REST TO SORTEN POST";

Check your html code generated by the script. Your just puting the same textbox all over. It needs to be an array or something.

[bodycount]

Can you point me in the right direction to a good tutorial on ARRAYS please.

[jatar_k]

you could look at this to start
[php.net...]

[bodycount]

Is this not an array?

$SERIALNO = (isset($_GET['SERIALNO']))? $_GET['SERIALNO'] : '';
$IMEI = (isset($_GET['IMEI']))? $_GET['IMEI'] : '';
$CANSERIAL = (isset($_GET['CANSERIAL']))? $_GET['CANSERIAL'] : '';
$CANTYPE = (isset($_GET['CANTYPE']))? $_GET['CANTYPE'] : '';
$COMPANY_id = (isset($_GET['COMPANY_id']))? $_GET['COMPANY_id'] : '';
$BUILDTYPE = (isset($_GET['BUILDTYPE']))? $_GET['BUILDTYPE'] : '';
$MACADDRESS = (isset($_GET['MACADDRESS']))? $_GET['MACADDRESS'] : '';
$SHIPDATE = (isset($_GET['SHIPDATE']))? $_GET['SHIPDATE'] : '';
$DESCRIPTION = (isset($_GET['DESCRIPTION']))? $_GET['DESCRIPTION'] : '';
$product_id = (isset($_GET['product_id']))? $_GET['product_id'] : '';
$num = (isset($_GET['num']))? $_GET['num'] : '';

I am sure it is because i did this in my code

print_r ($_GET);

and got this

Array ( [num] => 10 [product_id] => 39 [SERIALNO] => 39 [IMEI] => 010257-00-126118-3 [CANSERIAL] => S480015RV8
[CANTYPE] => GR48 [COMPANY_id] => Metric [BUILDTYPE] => Automated [MACADDRESS] => 12345 [SHIPDATE] => 26/05/2006 [DESCRIPTION] => FG000071 )

I know all the data is getting posted to the page which does the update because i can see it in the http bit in the browser

&num=10&product_id=30&SERIALNO=30&IMEI=010257-00-126076-3&CANSERIAL=S480015RU0&CANTYPE=GR48
&COMPANY_id=Metric&BUILDTYPE=Automated&MACADDRESS=&SHIPDATE=26%2F05%2F2006&DESCRIPTION=FG000071
&num=10&product_id=31&SERIALNO=31&IMEI=010257-00-124764-6&CANSERIAL=S480015Q76&CANTYPE=GR48
&COMPANY_id=Metric&BUILDTYPE=Automated&MACADDRESS=&SHIPDATE=26%2F05%2F2006&DESCRIPTION=FG000071

yet it still only inputs the very last one only. I am banging my head against the wall with this one i can't seem to find out what i am doing wrong.

[coopster]

Have you tried dumping your query to the browser yet to see if there are any issues with the expanded variables?

... 
exit($sql); 
mysql_query($sql) or die(mysql_error()); 
//$result = mysql_query($sql) or die(mysql_error()); 
if (!mysql_query($sql)) { 
...

I notice you are running your query twice, by the way -- see that here?

[bodycount]

I did notice that i was running it twice but thanks for that.

exit(sql); i get the following

UPDATE test_products SET product_id = '20',SERIALNO = '20', IMEI = '010257-00-126076-3', CANSERIAL = 'S480015RU0',
CANTYPE = 'GR48', COMPANY_id= 'Metric', BUILDTYPE = 'Automated' , MACADDRESS = '', SHIPDATE = '26/05/2006', DESCRIPTION = 'FG000071' WHERE product_id='20'

and thats all i get.

[coopster]

That doesn't match your query shown earlier at all:

$sql = "UPDATE products SET `SERIALNO` = '$SERIALNO', `IMEI` = '$IMEI', `CANSERIAL` = '$CANSERIAL', `CANTYPE` = '$CANTYPE', `COMPANY_id`= '$COMPANY_id', `BUILDTYPE` = '$BUILDTYPE' , `MACADDRESS` = '$MACADDRESS', `SHIPDATE` = '$SHIPDATE', `DESCRIPTION` = '$DESCRIPTION' WHERE SERIALNO BETWEEN '$FROM' AND '$TO'";

Where is the BETWEEN in your WHERE clause?

[bodycount]

I took them out because I was trying other things to get it to work. I will put them back in and try it again.

[bodycount]

I put the BETWEEN back in and it updates the records which are BETWEEN '$FROM' AND '$TO'.

but it is updating them all with the same data.

Is the way i am passing the data from page to page correct?

[coopster]

Of course it is going to update them all with the same data, you are telling it to do so. I think you need to explain in plain language exactly what you are trying to accomplish.

[bodycount]

What i want to do is a batch update.

For example I have 1000 PCB's in stock SERIALNO range from 2007101000 - 2007102000 When COMPANYx comes alone and wants 100 PCB's,I want to get a batch at a time of 100 out of the database and i want to update the following fields.

As CANTYPE is a high cost item we dont fit it to the PCB until someone buys the item. This is why i need to some how do batch updates.

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

IMEI.........EVERYONE IS DIFFERANT
CANSERIAL....EVERYONE IS DIFFERANT
CANTYPE......WILL BE THE SAME FOR THAT BATCH
COMPANY_id...WILL BE THE SAME FOR THAT BATCH

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

SERIALNO IMIE CANSERIAL CANTYPE COMPANY_id
2007101000 12345 54321 GM48 COMPANYx
2007101001 22346 64324 GM48 COMPANYx
2007101002 32347 74325 GM48 COMPANYx
2007101003 42348 84326 GM48 COMPANYx
2007101004 52349 94327 GM48 COMPANYx

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

I hope this helps. Sorry for not explianing this before.

[coopster]

No need for apologies, just needed clarification ;)

OK, so it looks to me like you are only making two of the columns the same for all the rows,

CANTYPE

and

COMPANY_id

. If this is indeed the case, then they should be the only two fields specified to be SET in your UPDATE statement, don't SET any of the others, leave them as they are.

[bodycount]

Thats right CANTYPE and COMPANY_id dont change for that batch, but IMEI and CANSERIAL change for every row. So how do I update them?.

[coopster]

they should be the only two fields specified to be SET in your UPDATE statement, don't SET any of the others, leave them as they are

[bodycount]

I think i am getting a bit confused about the UPDATE command.

So what you are saying coopster is my UPDATE statment should be like this below.

$sql1 = "UPDATE products SET CANTYPE = '$CANTYPE', COMPANY_id= '$COMPANY_id', WHERE SERIALNO BETWEEN '$FROM' AND '$TO'";

If so i dont know how the IMEI and CANSERIAL get updated.

Sorry for dragging this one for so long.

[coopster]

Don't worry about that, we just need clarification so we can help you the best we can ;)

If so i dont know how the IMEI and CANSERIAL get updated.

You stated before that they will be different for every row. I thought that meant that they would be different prior to any update, so they need to remain as is, updating only those columns that will be the same based on some user-supplied input. Perhaps you need to show a small set of data, a before and after snapshot to help us along here.

[bodycount]

Lets see

I want to get out of the database SERIALNO 1000 to 1005

This is how they are returned from the database

*******************************************************
* SERIALNO ¦ IMEI ¦ CANSERIAL ¦ CANTYPE ¦ COMPANY_id *
*******************************************************
* 1000 ¦ BLANK ¦ BLANK ¦ BLANK ¦ BLANK *
* 1001 ¦ BLANK ¦ BLANK ¦ BLANK ¦ BLANK *
* 1002 ¦ BLANK ¦ BLANK ¦ BLANK ¦ BLANK *
* 1003 ¦ BLANK ¦ BLANK ¦ BLANK ¦ BLANK *
* 1004 ¦ BLANK ¦ BLANK ¦ BLANK ¦ BLANK *
* 1005 ¦ BLANK ¦ BLANK ¦ BLANK ¦ BLANK *
*******************************************************

Then the following colums get filled in by the user IMEI, CANSERIAL, CANTYPE, COMPANY_id

*******************************************************
* SERIALNO ¦ IMEI ¦ CANSERIAL ¦ CANTYPE ¦ COMPANY_id *
*******************************************************
* 1000 ¦ 11111 ¦ 11111 ¦ GM47 ¦ COMPx *
* 1001 ¦ 22222 ¦ 22222 ¦ GM47 ¦ COMPx *
* 1002 ¦ 33333 ¦ 44444 ¦ GM47 ¦ COMPx *
* 1003 ¦ 99999 ¦ 99999 ¦ GM47 ¦ COMPx *
* 1004 ¦ 55555 ¦ 55555 ¦ GM47 ¦ COMPx *
* 1005 ¦ 77777 ¦ 77777 ¦ GM47 ¦ COMPx *
*******************************************************

Then UPDATED into the database all in one go as they are all in one form.

[coopster]

Then you will have to perform multiple UPDATE statements as the values for some of the columns are different. Since the information is being supplied by the user in a form, I would loop through the IMEI-supplied values and grab the associated CANSERIAL-supplied value for each (assuming they will be in the same array index). Also, you will want to use the "static" CANTYPE and COMPANY_id values to finish off your UPDATE. Something like this ...

$imei = $_POST['imei']; 
$canserial = $_POST['canserial']; 
foreach ($imei as $key => $value_imei) { 
 $value_canserial = $canserial[$key]; 
 $sql = "UPDATE products SET  
  `IMEI` = '" . mysql_real_escape_string($value_imei) . "', 
  `CANSERIAL` = '" . mysql_real_escape_string($value_canserial) . "', 
  `CANTYPE` = '$CANTYPE', 
  `COMPANY_id` = '$COMPANY_id' 
  WHERE SERIALNO BETWEEN '$FROM' AND '$TO'" 
 ; 
 mysql_query($sql); 
}

Don't forget to scrub the data your user is supplying in the $_POST variables first. And you will also want to monitor those UPDATE executions to make sure they ran properly.

[bodycount]

wow thanks for that I will give this a try, Funny enought I was looking at the foreach bit on the php web site today, but it was going striaght over my head.

But thanks again