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

Groups > comp.lang.php > #15629

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

Show all headers | View raw

Il 02/08/2015 14:42, Thomas 'PointedEars' Lahn ha scritto:
> Matthew Carter wrote:
>
>> 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.
>
> Of all the possible approaches, this is probably the second to worst one.
>
> Of the ones that come to mind, the most compatible and efficient one appears
> to be to insert at least one new column (not necessarily in the same table),
> update that as well –
>
>    UPDATE
>      a INNER JOIN c ON a.id = c.id
>      SET a.total=1, a.updated=1
>      WHERE b.x < c.y
>
>    (I have removed pointless aliases)
>
> –, and then you perform a second query, querying only the IDs of the
> rows that have a “1” in that new column.
>
> In fact, you might not need to create a new column as there may already be a
> column that stores the date of the last update, or a counter, against which
> you can compare.  For example, in MySQL it could be a column with type
> TIMESTAMP, default value CURRENT_TIMESTAMP, and trigger ON UPDATE
> CURRENT_TIMESTAMP.

trigger ON UPDATE  CURRENT_TIMESTAMP
can explain beter with an example , thanks



>Such a column frequently comes in handy, so I prefer to
> create it with the table.



.....................................................
solutin with a field time is also ok
i think can to do so:
$datenow=time();

UPDATE
a INNER JOIN c ON a.id = c.id
SET a.total=1, a.updated=$datenow
WHERE b.x < c.y


and the select with same $date

is correct the solution?
...................................................


so solutions are more
1)
how suggest Carter the steps are this
- previous array
- the sql update
- the after array
- final array comparision


2) with a field date
- sql update with a field current time
- sql select (filtered by those time)
- final array by the sql select


in the 2) case
the date can to be insert so:
- a in the sql using the variable php time();
- b by sql using CURRENT_TIMESTAMP
- c by table creating a field set how CURRENT_TIMESTAMP

in the cases of b and c how write the code for extract the array seen 
that is necessary know the interval



which is more fast 1 or 2?

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