PHP Mysql Updating positions and parents of an Id in a table

Question

Please I have a table with three columns :

id   (this is the id of my table)
parent  (this is the parent)
position  (and this is the position)

And I have this array $_arr['menu'] :

array (size=3)
  13 => 
    array (size=1)
      'parent' => string '0' (length=1)   
  14 => 
    array (size=1)  
      'parent' => string '13' (length=2) 
  25 => 
    array (size=1)  
      'parent' => string '0' (length=1)

I want to update may table with those values in the array $_arr['menu'].

I imagine to do something like but in one query (perhaps using case in) :

UPDATE table SET position = 1, parent = 0 WHERE id = 13;

UPDATE table SET position = 2, parent = 13 WHERE id = 14; 

UPDATE table SET position = 3, parent = 0 WHERE id = 25;

Please masters, how to get those values from that array and how to do the update !

Thanks a lot

post your array without explanation and your generated array — echo_Me, Dec 23 '12 at 20:59
@SamiElHilali very sorry to say, but the things needed to 'solve' your 'problem' are the basics of programming with php/mysql... — dbf, Dec 23 '12 at 21:08
@SamiElHilali you can use transaction. Here's a good example [transaction example](http://stackoverflow.com/questions/2708237/php-mysql-transactions-examples) I know, it's not in one statement but you can prepare all those query in a loop and then run together in one transaction. — JiboOne, Dec 23 '12 at 21:14
@dbf It's actually a very valid question, it seems you didn't see what basic *questions* really look like ;) — jadkik94, Dec 23 '12 at 21:20
@mariami thank you but I need also how to extract values from that array. Thank you. — Sami El Hilali, Dec 23 '12 at 21:21
@jadkik94 for this post being tagged with PHP, I'm surprised not to find "what have you tried" comments of any kind. I don't see anything "tried" in his question, so explain how you think this is in your opinion a valid question... — dbf, Dec 23 '12 at 21:37

score 3 · Accepted Answer · edited Dec 23 '12 at 22:01

3

I'm not sure I got your question, but I'll write my snippet. (I don't remember php syntax well and it's not tested, but I think it shows what I want to say)

try {
    $db->beginTransaction();
    $increment = 0;
    foreach ($arr as $id => $innerArray) {
            $db->query("UPDATE table SET position = ".(++$increment).", parent = ".$innerArray['parent']." WHERE id =".$id);
    }
    $db->commit();
} catch (Exception $e) {
    $db->rollback();
}

edited Dec 23 '12 at 22:01

Sami El Hilali

981
3
12
21

answered Dec 23 '12 at 21:39

JiboOne

1,438
4
22
55

1

+1 for using transactions, but how do you know he's using PDO? Second, using `$db->prepare("query")` and bind the values as parameters would prevent SQL injection. – dbf Dec 23 '12 at 21:44
2

I wanted to show only the idea. Sure, the code must be corrected and the parameters should be set and not concatenated – JiboOne Dec 23 '12 at 21:48
And I got your Idea, You are just awesome mariami ! – Sami El Hilali Dec 23 '12 at 21:58
1

@dbf I'm using MySQLi. and his Idea was helpful ! – Sami El Hilali Dec 23 '12 at 21:59

PHP Mysql Updating positions and parents of an Id in a table

1 Answers1