#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0

    Several inserts and delets, always one failing


    Hi,
    I have this script to save old properties in another table then I delete from other tables, the strange is, there is always one that fails, before one of the deletes failed, I changed the way of the delete and then the last insert failed. I just dont get it.

    This is what I have at this moment and the insert that does not work is the last one, being the column and tablename correct:

    Code:
      mysql_query($query);
    $query = "INSERT INTO buscador_old SELECT * FROM buscador WHERE `id_propiedad` = '$id_propiedad'";
    
      mysql_query($query);
    $query = "INSERT INTO availability_old (id_propiedad, tipo, zona, duermen, link_ingles, link_esp, link_sve, conditions, condiciones, villkor)
     SELECT id_propiedad, tipo, zona, duermen, link_ingles, link_esp, link_sve, conditions, 
     condiciones, villkor  FROM casa WHERE `id_propiedad` = '$id_propiedad'";
     
       mysql_query($query);
    $query = "INSERT INTO Casas_alquilar_old SELECT * FROM Casas_alquilar WHERE `nombre` = '$id_propiedad'";
    
      mysql_query($query); 
       $query = "INSERT INTO casa_old (id_propiedad)" .
    "VALUES ('$id_propiedad')";
    
    $sql = mysql_query ("DELETE FROM `casa` WHERE `id_propiedad` = '$id_propiedad'");
     
    $sql = mysql_query ("DELETE FROM `buscador` WHERE `id_propiedad` = '$id_propiedad'");
    
    $sql = mysql_query("DELETE FROM `Casas_alquilar` WHERE `nombre` = '$id_propiedad'");
    Any help please, as if I do changes so the failing works, then another one fails....
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    i'm sorry, i have never seen the error message "does not work" before

    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    >i'm sorry, i have never seen the error message "does not work" before

    That's weird, because that error message shows up in this very forum alarmingly often!

    ;-)
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0
    Thanks
    The thing is I have not done in phpmysql as the inserts and deletes works perfectly separately, but as I said when doing them all together there is always one that is not inserted or deleted, if I change the php to make the failing one to work then another one does not work anymore, I need to do the inserts and deletes all at the same time so must be done with php, if I do them one by one I dont have any problem at all. Therefore I dont get it.

    The problem is not doing them separately, its when done all together. Dont know how to do all at the same time to test in phpmyadmin.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    contrary to popular belief, phpmyadmin does not run multiple sql statements

    it runs them one at a time, just like you have to do, except it does them one after the other so that it only looks like it's doing multiple statements

    and you still did not explain what "does not work" means

    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0
    Originally Posted by r937
    contrary to popular belief, phpmyadmin does not run multiple sql statements

    it runs them one at a time, just like you have to do, except it does them one after the other so that it only looks like it's doing multiple statements

    and you still did not explain what "does not work" means

    Does not work, means, it does not do what itīs supposed to do, ie insert or update, I done changes many time in this code, and I always ends up with one not working, inserting or deleting depending, the way the statements are done above itīs the last insert that does not work, the one before the 3 deletes.
    Thanks
    So I suppose the problem is in the php handling the sql.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    okay, i'm gonna move this thread to the php forum, as php seems to be where you are having the problem
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0
    Originally Posted by r937
    okay, i'm gonna move this thread to the php forum, as php seems to be where you are having the problem
    Thanks did not thought about that.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    33
    Rep Power
    2
    This should work

    PHP Code:
    $query "INSERT INTO buscador_old SELECT * FROM buscador WHERE `id_propiedad` = '$id_propiedad'";
    mysql_query($query);

    $query "INSERT INTO availability_old (id_propiedad, tipo, zona, duermen, link_ingles, link_esp, link_sve, conditions, condiciones, villkor)
     SELECT id_propiedad, tipo, zona, duermen, link_ingles, link_esp, link_sve, conditions, 
     condiciones, villkor  FROM casa WHERE `id_propiedad` = '
    $id_propiedad'";
    mysql_query($query);

    $query "INSERT INTO Casas_alquilar_old SELECT * FROM Casas_alquilar WHERE `nombre` = '$id_propiedad'";
    mysql_query($query); 

    $query "INSERT INTO casa_old (id_propiedad)" .
    "VALUES ('$id_propiedad')";
    mysql_query($query); 


    $sql "DELETE FROM `casa` WHERE `id_propiedad` = '$id_propiedad'";
    mysql_query($sql);


    $sql "DELETE FROM `buscador` WHERE `id_propiedad` = '$id_propiedad'";
    mysql_query($sql);


    $sql mysql_query("DELETE FROM `Casas_alquilar` WHERE `nombre` = '$id_propiedad'";
    mysql_query($sql); 
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0
    Originally Posted by jpmul
    This should work

    PHP Code:
    $query "INSERT INTO buscador_old SELECT * FROM buscador WHERE `id_propiedad` = '$id_propiedad'";
    mysql_query($query);

    $query "INSERT INTO availability_old (id_propiedad, tipo, zona, duermen, link_ingles, link_esp, link_sve, conditions, condiciones, villkor)
     SELECT id_propiedad, tipo, zona, duermen, link_ingles, link_esp, link_sve, conditions, 
     condiciones, villkor  FROM casa WHERE `id_propiedad` = '
    $id_propiedad'";
    mysql_query($query);

    $query "INSERT INTO Casas_alquilar_old SELECT * FROM Casas_alquilar WHERE `nombre` = '$id_propiedad'";
    mysql_query($query); 

    $query "INSERT INTO casa_old (id_propiedad)" .
    "VALUES ('$id_propiedad')";
    mysql_query($query); 


    $sql "DELETE FROM `casa` WHERE `id_propiedad` = '$id_propiedad'";
    mysql_query($sql);


    $sql "DELETE FROM `buscador` WHERE `id_propiedad` = '$id_propiedad'";
    mysql_query($sql);


    $sql mysql_query("DELETE FROM `Casas_alquilar` WHERE `nombre` = '$id_propiedad'";
    mysql_query($sql); 
    Puf, that worked, thousands of thanks, had many irritations with that script.
  20. #11
  21. No Profile Picture
    I haz teh codez!
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2003
    Posts
    2,540
    Rep Power
    2337
    Uh, you might want to look into transactions.
    I ♥ ManiacDan & requinix

    This is a sig, and not necessarily a comment on the OP:
    Please don't be a help vampire!
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Location
    Marbella, Spain
    Posts
    381
    Rep Power
    0
    Originally Posted by ptr2void
    Uh, you might want to look into transactions.
    Yes, maybe, but that script is rarely used and only by me, but its good to know about it, thanks
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    33
    Rep Power
    2
    glad i could help

IMN logo majestic logo threadwatch logo seochat tools logo