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

Groups > comp.lang.php > #15630

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

Show all headers | View raw

alex <alex@nomailno.it> writes:

> 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?
>
>

My solution (doing it via multiple array queries in PHP) is going to be
the slowest and most unusual real world use case.

It is probably the easiest for a new PHP programmer to understand (as it
doesn't involve adding more logic to the database structure, which you
may or may not have had access to change), and it answered your question
as stated (how to do it in PHP).

For a real world use case, go with what Thomas posted (keep track of
last updates via database).

If you're using MySQL (and probably most other database engines these
days) you can put this logic in the query itself using the ON UPDATE clause.

If your database does not support that, you can just add a new datetime
or timestamp column type and insert or update that value whenever the
column is modified (then to get the changed rows, query based on that
time).

-- 
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