[php|mysql] 1query update /meerdere rijen/meerdere waarden

[Verwijderd]

Hallo allen,

Ik heb een beetje het idee dat ik aan het zoeken ben naar iets wat niet kan. Aangezien ik veel antwoorden vind (google, GoT, andere fora), maar die zijn voor een andere vraag.

De antwoorden die ik vond werkten of met een loop om meer dan 1 query uit te voeren of werkten de meerdere rijen bij met 1 waarde. En ik wil dus meerdere rijen bijwerken met verschillende waarden in 1 query.

Voorbeeld:

Ik krijg deze array uit een $POST:
[order_nr] => Array
(
[1] => 0
[2] => 10
[3] => 20
[4] => 30
[5] => 40
[6] => 50
)

en ik wil in tabel `menu` de velden `menu_order` updaten van in dit geval 6 rijen. (de identifier is hier ook `menu_id`).

Kan dat dan met 1 query die hier op lijkt?:
(de query wordt dan d.m.v. een loop gegenereerd)

PHP:

$sql = 'UPDATE `menu` SET
          `menu_order` = 0,
          `menu_order` = 10,
          `menu_order` = 20,
          `menu_order` = 30,
          `menu_order` = 40,
          `menu_order` = 50         
           WHERE
           `menu`.`menu_id` = 1 AND
           `menu`.`menu_id` = 2 AND
           `menu`.`menu_id` = 3 AND
           `menu`.`menu_id` = 4 AND
           `menu`.`menu_id` = 5 AND
           `menu`.`menu_id` = 6';

De bovenstaande query is technisch wel in orde, maar doet niet wat ik wil. De eerste waarde onder SET moet corresponderen met de eerste waarde bij WHERE enz.

Kan dit?

Ik gebruik overigens PHP4.4.2 en MySQL4.1.14

[UltimateB]

waarschijnlijk wil je een waarde ertussen voegen. Kan je niets doen met

code:

update menu set menu_order = menuorder + 10 where menu_order > x

[Verwijderd]

UltimateB schreef op dinsdag 12 februari 2008 @ 18:49:
waarschijnlijk wil je een waarde ertussen voegen. Kan je niets doen met

code:

update menu set menu_order = menuorder + 10 where menu_order > x

Wat bedoel je precies met ertussen voegen. Ik wil rijen updaten, niet invoegen.

[robbert]

De enige manier die ik ken is de volgende:

SQL:

UPDATE tabel
SET kolom = CASE id
  WHEN 1 THEN 0
  WHEN 2 THEN 10
  WHEN 3 THEN 20
  WHEN 4 THEN 30
  WHEN 5 THEN 40
  ELSE kolom
END

zie: http://dev.mysql.com/doc/...ntrol-flow-functions.html

Of dit voor n items sneller is dan n queries uitvoeren weet ik niet. Zou je voor de grap eens moeten proberen. Dan natuurlijk wel voor een flink groot aantal items, anders zegt het nog niks.

[ Voor 42% gewijzigd door robbert op 12-02-2008 19:03 ]

[ripexx]

Je zal dit toch in een loop moeten doen. Je wil namelijk unieke record updaten. Verder zou je eens de volgende query moeten uitvoeren:

SQL:

SELECT *
FROM `menu`
WHERE
           `menu`.`menu_id` = 1 AND
           `menu`.`menu_id` = 2 AND
           `menu`.`menu_id` = 3 AND
           `menu`.`menu_id` = 4 AND
           `menu`.`menu_id` = 5 AND
           `menu`.`menu_id` = 6';

[ATS]

Volgens mij kan wat jij wil niet. Wat je zou kunnen overwegen is het maken van een transactie om alle updates te bundelen, maar ik weet zo niet waarom je dat nu persé zou willen. Wat is nu precies het probleem van het runnen van 6 update queries hier? Levert het een (meetbaar) performanceprobleem op?

Dat jouw eigen query niet werkt is natuurlijk logisch. Ten eerste zijn er geen rijen die voldoen aan al je WHERE voorwaarden, dus wordt er geen rij geselecteerd om te updaten, en ten tweede zouden de rijen die wél gevonden zouden worden (nogmaals: die zijn er niet) allemaal op dezelfde waarde gezet worden (afhankelijk van de volgorde van executie, maar ik gok 0 of 50).

[Verwijderd]

robbert schreef op dinsdag 12 februari 2008 @ 19:01:
De enige manier die ik ken is de volgende:

SQL:

UPDATE tabel
SET kolom = CASE id
  WHEN 1 THEN 0
  WHEN 2 THEN 10
  WHEN 3 THEN 20
  WHEN 4 THEN 30
  WHEN 5 THEN 40
  ELSE kolom
END

zie: http://dev.mysql.com/doc/...ntrol-flow-functions.html

Of dit voor n items sneller is dan n queries uitvoeren weet ik niet. Zou je voor de grap eens moeten proberen. Dan natuurlijk wel voor een flink groot aantal items, anders zegt het nog niks.

Dat dit de enige manier is die je weet geeft niet, hij werkt prima.

@ATS
Ik weet dat de query die ik stuurde niet werkt. Anders had ik de vraag niet gesteld.
Maar het ging mij even om de "structuur" van de query. Ik wilde namelijk niet het zoveelste antwoord lezen dat mij zij dat ik in een foreach o.i.d. elke update opnieuw moest uitvoeren.

De performance zal er in dit geval ook niet echt op vooruit gaan. Maar ik probeer te leren en efficient te werken. Dit kan ik de volgende keer weer gebruiken bij grotere aantallen.

Bedankt allen voor het meedenken!!

[Creepy]

Ik denk dat het qua efficientie niet veel uitmaakt. In dit geval een for(each) loop met daarin voor elk item een losse update leest qua code wat mij betreft veel prettiger dan een loop die een CASE statement samenstelt in je query. Qua onderhoudbaarheid denk ik toch wel een pluspunt Met efficient werken heeft dit in elk geval weinig meer te maken.

[Verwijderd]

Met de ELT of FIELD functie kun je wel iets, maar niet als er tussen elke 2 items 10 nummers moeten zitten denk ik. Maar stel je hebt rijen met id=1 t/m 10, dan kun je met UPDATE `tbl` SET `pos` = ELT(id, 10,9,8,7,6,5,4,3,2,1) de boel volgens mij wel omdraaien of anderszins opnieuw ordenen.

[MacWebber]

Wat ik me dan weer afvraag, waarom wil je dit zo graag in 1 statement oplossen? Het zal er in je code niet veel overzichtelijker op worden en performance-technisch gezien is dit alleen leuk als je een tabel met maximaal een handvol records update. Ik schat zo in dat geen van de beschreven werkwijzen nog een index gebruikt namelijk.

Als je dus een grote tabel hebt zal 'ie die helemaal langslopen om een paar records te updaten.

In dit geval zou ik zeggen, transactie starten, records updaten vanuit een loop, transactie committen.

[Verwijderd]

MacWebber schreef op dinsdag 12 februari 2008 @ 20:35:
Wat ik me dan weer afvraag, waarom wil je dit zo graag in 1 statement oplossen? Het zal er in je code niet veel overzichtelijker op worden en performance-technisch gezien is dit alleen leuk als je een tabel met maximaal een handvol records update. Ik schat zo in dat geen van de beschreven werkwijzen nog een index gebruikt namelijk.

Als je dus een grote tabel hebt zal 'ie die helemaal langslopen om een paar records te updaten.

In dit geval zou ik zeggen, transactie starten, records updaten vanuit een loop, transactie committen.

Wat de code betreft, dat gaat nog wel.

PHP:

$sql = 'UPDATE menu SET menu_order = CASE menu_id ';

foreach ($row as $key=>$value)  {
    $sql .=  "\n".'WHEN ' . $key . ' THEN '. $value ;
}
  
$sql .= ' ELSE menu_order';
$sql .= ' END';

$message = save( &$mydb, $sql );

Maar ik had het inderdaad nog niet zo bekeken dat hij alle records afgaat. De rede dat ik alles in 1 query wilde stoppen is, omdat ik dacht dat het minder belastend zou zijn. Omdat er dan steeds gewisseld moet worden tussen php en mysql. Dat had ik tenminste begrepen uit 1 van de post die ik gelezen had.

Verwijderd schreef op dinsdag 12 februari 2008 @ 20:10:
Met de ELT of FIELD functie kun je wel iets, maar niet als er tussen elke 2 items 10 nummers moeten zitten denk ik. Maar stel je hebt rijen met id=1 t/m 10, dan kun je met UPDATE `tbl` SET `pos` = ELT(id, 10,9,8,7,6,5,4,3,2,1) de boel volgens mij wel omdraaien of anderszins opnieuw ordenen.

In het veld waar er steeds 10 tussen zit is `menu_order` niet `menu_id`.

offtopic:
(Ik had een statische query in mijn testcode staan dus was het makkelijkst om via myadmin overal een 0 achter te zetten)

Met ELT zal ik ook nog even gaan vogelen.
http://dev.mysql.com/doc/...nctions.html#function_elt

Ik zie nog niet helemaal hoe hij werkt. En als ik denk dat ik weet hoe hij werkt is hij denk ik net zo efficiënt als het geen dat ik nu gebruik met CASE.

En anders toch maar een loop-je.

[ Voor 3% gewijzigd door Verwijderd op 13-02-2008 04:39 ]

[Voutloos]

Welicht ook wel handig om de reply van UltimateB nog een keer te lezen, want een dergelijke query kan bij een order veld wel degelijk nuttig zijn.

[Confusion]

Verwijderd schreef op woensdag 13 februari 2008 @ 04:35:
Maar ik had het inderdaad nog niet zo bekeken dat hij alle records afgaat. De rede dat ik alles in 1 query wilde stoppen is, omdat ik dacht dat het minder belastend zou zijn. Omdat er dan steeds gewisseld moet worden tussen php en mysql.

Als deze query alleen uitgevoerd wordt als iemand in een administratieve interface de menu volgorde op een website wijzigt, dan is de performance niet echt van belang en zou de onderhoudbaarheid van je applicatie de belangrijkste zorg moeten zijn. Die query die je dynamisch samenstelt is niet heel erg leesbaar. Ik zou dan toch gewoon 6 keer dezelfde query met andere parameters sturen. Iets minder performant, maar een stuk overzichtelijker.

[Verwijderd]

Ik heb ondertussen een mn database class uitgebreid me een algemene update functie:

PHP:

// Update rijen in de database en geeft het resultaat terug.
function update ( &$mydb, $table, $field, $rows ) {

    foreach ($rows as $key=>$value) {
        $sql = 'UPDATE `'.$table.'` SET `'.$field.'` = '. $value .' WHERE `'. $table.'_id` = '. $key .' LIMIT 1 ';
        $message .= save( &$mydb, $sql );       
    }   
    return $message;
}

Is iedereen het hier mee eens?

Wat de post van UltimateB betreft. Hier begrijp ik niets van. Mijn reply daarop slaat dan, achter af gezien, waarschijnlijk nergens op. Is er iemand die dit voor mij zou kunnen toelichten?

[Voutloos]

Die query van UltimateB is zo moeilijk nog niet? Wellicht is het doel van die query wat onduidelijk: Als je een menuitem verplaatst naar een andere plek, hoef je enkel dat menuitem het juist ordernummer te geven en de rows met ordernummer gelijk aan of groter door te schuiven. Je hoeft dan niet de totale tabel te updaten.

[Verwijderd]

Voutloos schreef op woensdag 13 februari 2008 @ 16:28:
Die query van UltimateB is zo moeilijk nog niet? Wellicht is het doel van die query wat onduidelijk: Als je een menuitem verplaatst naar een andere plek, hoef je enkel dat menuitem het juist ordernummer te geven en de rows met ordernummer gelijk aan of groter door te schuiven. Je hoeft dan niet de totale tabel te updaten.

Bedankt voor de uitleg.

Dat klinkt idd ook handig. Al zal het toch alle record doorlopen.

PHP:

$sql = 'update menu set menu_order = menuorder + 1 where menu_order > x';

Ik neem aan dat ik x dan moet vervangen door het laagste order_nr dat ik aangepast heb.

Ik weet niet of het handig is om te gebruiken (in dit geval). Aangezien er dan een controle bij moet om de array met de huidige volgorde vergeleken moet worden met de array van de nieuwe volgorde. Om te kijken welke er veranderd zijn en dan kijken wat de laagste is die veranderd is. Of denk ik nu te moeilijk?

[mithras]

Het gaat niet op bij een verwisseling van menu items. Als er ergens eentje tussen komt, kan je vanaf dat punt alles naar beneden verschuiven. Bij andere mutaties gaat het niet zomaar. Maar wat is er mis met zoiets:

PHP:

$db->start_transaction();
foreach( $menu as $item){
  $record = $db->query( 'UPDATE menu SET menu_order = ' . $item->order . ' WHERE id= ' . $item->id );
  if( !$record ){
    $error = true;
    break;
  }
}
if( !$error )
  $db->commit_transaction();
else
  $db->cancel_transaction()

Oftwel: transactions met een db roll-back systeem

[ Voor 6% gewijzigd door mithras op 13-02-2008 17:13 ]