Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #15621

Re: sql how capture on the fly the row's id when is updated

Show key headers only | View raw

alex <alex@nomailno.it> writes:

> UPDATE  a AS a
>  INNER JOIN c AS c  ON  a.id = c.id
>  SET     a.total = 1
>  WHERE b.x < c.y
>
>
>
> this sql, in the table a total's field, insert the value 1 under
> certain conditions; in some rows the value already exists and will
> only insert in other lines;
> is possible put in an array php only the id of the rows updated?
>
> es nel caso sotto su 1  6 ho già il valore; con sql vado a d inserire
> solo nella riga 3
>
> example
> table a
> id     total
> 1       1 already there is
> 2	0
> 3	0 only here the sql will insert the new value
> 4	0
> 5	0
> 6	1 already there is
> 7	0
> ...
> 1000	0
>
>
> I think is necessary, while sql loop the rows, when a row is updated,
> capture the id and insert in the php array, but how?
>
> ..........................................................................

It's not the best choice to do it as phrased, but to do so you would
(memory permitting) query all the rows/relevant fields from the
database, then perform your update query, then re-query all the
rows/relevant fields into a new array.

Loop through the two arrays and when the field you're checking is
different, do something with it.

Real simple example (assume $db holds a PDO instance) and excuse any
syntax errors (if any), as I didn't set up a test database to do this
snippet with:

<?php

// Get original data set
$stmt = $db->prepare ('SELECT id,total FROM table ORDER BY id');
$stmt->execute ([]);
$original_rows = $stmt->fetchAll ();

// Assume your update clause is in $update_sql
$stmt = $db->prepare ($update_sql);
$stmt->execute ([]);

// Now get the new result set
$stmt = $db->prepare ('SELECT id,total FROM table ORDER BY id');
$stmt->execute ([]);
$current_rows = $stmt->fetchAll ();

// Now just get the rows where 'total' was updated
// by filtering out matches of total value
$c = 0;
$modified_rows = array_filter ($current_rows,
  function ($row) use ($original_rows, $c)
  {
    return $row['total'] !== $original_rows[$c++]['total'];
  });

// Now $modified_rows only contains the rows where total changed
print_r ($modified_rows);

-- 
Matthew Carter (m@ahungry.com)
http://ahungry.com

Back to comp.lang.php | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

sql how capture on the fly the row's id when is updated alex <alex@nomailno.it> - 2015-08-01 11:15 +0200
  Re: sql how capture on the fly the row's id when is updated Denis McMahon <denismfmcmahon@gmail.com> - 2015-08-01 14:51 +0000
  Re: sql how capture on the fly the row's id when is updated "Peter H. Coffin" <hellsop@ninehells.com> - 2015-08-01 10:03 -0500
  Re: sql how capture on the fly the row's id when is updated Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-08-01 20:33 +0200
  Re: sql how capture on the fly the row's id when is updated Matthew Carter <m@ahungry.com> - 2015-08-01 15:44 -0400
    Re: sql how capture on the fly the row's id when is updated alex <alex@nomailno.it> - 2015-08-01 22:01 +0200
      Re: sql how capture on the fly the row's id when is updated Matthew Carter <m@ahungry.com> - 2015-08-01 16:13 -0400
        Re: sql how capture on the fly the row's id when is updated alex <alex@nomailno.it> - 2015-08-02 01:51 +0200
          Re: sql how capture on the fly the row's id when is updated Matthew Carter <m@ahungry.com> - 2015-08-01 21:38 -0400
    Re: sql how capture on the fly the row's id when is updated Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-08-02 14:42 +0200
      Re: sql how capture on the fly the row's id when is updated alex <alex@nomailno.it> - 2015-08-04 19:24 +0200
        Re: sql how capture on the fly the row's id when is updated Matthew Carter <m@ahungry.com> - 2015-08-04 14:55 -0400
          Re: sql how capture on the fly the row's id when is updated alex <alex@nomailno.it> - 2015-08-05 01:13 +0200
        Re: sql how capture on the fly the row's id when is updated Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-08-04 21:29 +0200
          Re: sql how capture on the fly the row's id when is updated alex <alex@nomailno.it> - 2015-08-05 01:01 +0200
            Re: sql how capture on the fly the row's id when is updated alex <alex@nomailno.it> - 2015-08-05 01:03 +0200
            Address munging considered harmful (was: sql how capture on the fly the row's id when is updated) Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-08-05 10:08 +0200

csiph-web