PHP script problem

Discussion in 'Web Design and Development' started by nomade, Feb 5, 2010.

  1. nomade macrumors member

    nomade

    Joined:
    Dec 2, 2006
    #1
    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 :
    HTML:
    $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;
     
  2. Darth.Titan macrumors 68030

    Darth.Titan

    Joined:
    Oct 31, 2007
    Location:
    Austin, TX
    #2
    First of all, if you want to limit the result set to 1000, the easiest way is to do it in your query.
    Code:
    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:
    Code:
    $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.
     
  3. mufflon macrumors 6502

    Joined:
    Sep 15, 2006
    #3
    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 :)
     
  4. nomade thread starter macrumors member

    nomade

    Joined:
    Dec 2, 2006
    #4
    Thanks Dath-titan,

    I try your code
    HTML:
    $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:confused:
     
  5. nomade thread starter macrumors member

    nomade

    Joined:
    Dec 2, 2006
    #5
    Thanks Mufflon,

    You were absolutely right, the new script work perfectly fine :
    HTML:
    $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 :D
     

Share This Page