PHP script problem

[nomade]

I want to slice a large database in smaller group. I made a script to do that by slice of 1000. The script work but affect the entire database instead of the 1000 first one. Here's the script :

$i=1;
$sql_tous=mysql_query("SELECT * FROM listeenvoi");
while($ligne_tous=mysql_fetch_array($sql_tous)){
if(($ligne_tous['groupe']=='abonne1') && ($i < 1000)){
$ud = mysql_query("UPDATE listeenvoi SET groupe=\"abonne\" ");
$i++;
}
}

I tried many variations without success )) o;

 

[Darth.Titan]

First of all, if you want to limit the result set to 1000, the easiest way is to do it in your query.

SELECT * FROM listeenvoi LIMIT 0,999

That will give you the first 1000, LIMIT 1000,1999 would give you the next, etc...

Secondly in the code you gave, $i is only incremented when the conditions of the if statement are met. Therefore you're affecting more rows than you intend. Try:

$i=1;
$sql_tous=mysql_query("SELECT * FROM listeenvoi");
while($ligne_tous=mysql_fetch_array($sql_tous)){
if(($ligne_tous['groupe']=='abonne1') && ($i < 1000)){
$ud = mysql_query("UPDATE listeenvoi SET groupe=\"abonne\" ");
}
$i++;
}

That should make it work the way you want.

 

[mufflon]

and your second mySQL query

UPDATE listeenvoi SET groupe="abonne"


changes every listeenvoi row to have the variable "abonne" in the column groupe. Seems unintended - is there a single element with the value "abonne1" then every variable will be "abonne".


what you should do is

UPDATE listeenvoi SET groupe="abonne" where UNIQUEVARIABLE=UNIQUEKEY

where UNIQUEVARIABLE/UNIQUEKEY pair needs to be your primary key (or a combination of variables needed to recognise the particular element).
This should only change the element you want to be changed

 

[nomade]

Thanks Dath-titan,

I try your code

$i=1;
$sql_tous=mysql_query("SELECT * FROM listeenvoi LIMIT 0,999");
while($ligne_tous=mysql_fetch_array($sql_tous)){
if(($ligne_tous['groupe']=='abonne') && ($i < 1000)){
$ud = mysql_query("UPDATE listeenvoi SET groupe=\"abonne1\" ");
}
$i++;
}

But still all the entries are affected without the 0-999 limitation

 

[nomade]

Thanks Mufflon,

You were absolutely right, the new script work perfectly fine :

$i=1;
$sql_tous=mysql_query("SELECT * FROM listeenvoi LIMIT 0,999");
while($ligne_tous=mysql_fetch_array($sql_tous)){
if(($ligne_tous['groupe']=='abonne') && ($i < 1000)){
$ud = mysql_query("UPDATE listeenvoi SET groupe=\"abonne1\" WHERE id_listeenvoi = ".$ligne_tous['id_listeenvoi']."");
}
$i++;
}

Thanks again