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

Groups > comp.lang.php > #15617 > unrolled thread

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

Back to article view | Back to comp.lang.php

Contents

  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

#15617 — sql how capture on the fly the row's id when is updated

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?

..........................................................................

[toc] | [next] | [standalone]

#15618

On Sat, 01 Aug 2015 11:15:52 +0200, alex wrote:

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

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

First of all, forget php for a moment and launch your database 
interactive shell. I'm not sure which variant of sql you are using, it 
could be postgres, sqlite, mysql, mariadb or something else.

Now figure out how you obtain this information at the database level.

then google something like:

php <name of db software> <the command you use in the db to get this data>

If the database you're using doesn't provide access to this information 
in a way that you can see it from the interactive shell, it probably 
doesn't expose it in a way that php cab access it either.

p.s. at the moment this appears to be a sql question, not a php one.

-- 
Denis McMahon, denismfmcmahon@gmail.com

[toc] | [prev] | [next] | [standalone]

#15619

On Sat, 01 Aug 2015 11:15:52 +0200, alex wrote:
> 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?

Forget about SQL looping the rows; it might be doing that but you cannot
know that it is because it might also NOT be doing that. It's entirely
up to the SQL engine how to do something.

The correct way to do this is to do it using ONLY SQL, in several
queries, enveloped in a single transaction/unit of work. That should be
enough information to get you started on your own research, or re-ask
your question in a database newsgroup that's appropriate.

-- 
Windows gives you a nice view of clouds so you can't see any potentially
useful boot time messages.
              -- Bill Hay in the Monastery

[toc] | [prev] | [next] | [standalone]

#15620

alex wrote:

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

Yes.
 
-- 
PointedEars
Zend Certified PHP Engineer
Twitter: @PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.

[toc] | [prev] | [next] | [standalone]

#15621

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

[toc] | [prev] | [next] | [standalone]

#15622

Il 01/08/2015 21:44, Matthew Carter ha scritto:
> UPDATE  a AS a
>>  INNER JOIN c AS c  ON  a.id = c.id
>>  SET     a.total = 1
>>  WHERE b.x < c.y

if I do this sql (same but how select):

SELECT a.id
FROM a AS a
INNER JOIN c AS c  ON  a.id = c.id
WHERE b.x < c.y

I have solved?


and with this php code

$result = mysql_query("SELECT ....");
$results = array();
while($row = mysql_fetch_assoc($result))
{
    $results[] = $row;
}

[toc] | [prev] | [next] | [standalone]

#15623

alex <alex@nomailno.it> writes:

> Il 01/08/2015 21:44, Matthew Carter ha scritto:
>> UPDATE  a AS a
>>>  INNER JOIN c AS c  ON  a.id = c.id
>>>  SET     a.total = 1
>>>  WHERE b.x < c.y
>
> if I do this sql (same but how select):
>
> SELECT a.id
> FROM a AS a
> INNER JOIN c AS c  ON  a.id = c.id
> WHERE b.x < c.y
>
> I have solved?
>
>
> and with this php code
>
> $result = mysql_query("SELECT ....");
> $results = array();
> while($row = mysql_fetch_assoc($result))
> {
>    $results[] = $row;
> }

Apparently you do not understand what I wrote, and unfortunately I do
not understand what you wrote.

Sorry.

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

[toc] | [prev] | [next] | [standalone]

#15624

ok your solution is ok  thanks
array first
array after
difference

I must to test
I noticed that is used the 'use' function (php 5.3)
is there a solution for old php 5.2.x

[toc] | [prev] | [next] | [standalone]

#15625

alex <alex@nomailno.it> writes:

> ok your solution is ok  thanks
> array first
> array after
> difference
>
> I must to test
> I noticed that is used the 'use' function (php 5.3)
> is there a solution for old php 5.2.x
>
>

Instead of the array_filter, you can write a comparatively similar
implementation with a foreach loop.

...
foreach ($rows as $row)
{
  if ($row['id'] !== $original_rows[$c++]['id'])
  {
    $modified_rows[] = $row;
  }
}
...

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

[toc] | [prev] | [next] | [standalone]

#15626

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.  Such a column frequently comes in handy, so I prefer to 
create it with the table.

It is possible that the `total` column already can serve that purpose, in 
which case you only need to do

 UPDATE a SET a.total=0

beforehand, and then proceed with the now-third query as I described.

-- 
PointedEars
Zend Certified PHP Engineer
Twitter: @PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.

[toc] | [prev] | [next] | [standalone]

#15629

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?

[toc] | [prev] | [next] | [standalone]

#15630

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

[toc] | [prev] | [next] | [standalone]

#15634

> It is probably the easiest for a new PHP programmer to understand (

ok

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

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


B
> 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).
>


not understand the case A
I have mysql 5.1.x, can do an example ?

[toc] | [prev] | [next] | [standalone]

#15631

alex <alex@nomailno.it> wrote:
      ^^^^^^^^^^^^^^^^
> […]

<http://www.interhack.net/pubs/munging-harmful/>

Therefore, no cookies for you.

-- 
PointedEars
Zend Certified PHP Engineer
Twitter: @PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.

[toc] | [prev] | [next] | [standalone]

#15632

Il 04/08/2015 21:29, Thomas 'PointedEars' Lahn ha scritto:
> alex <alex@nomailno.it> wrote:
>        ^^^^^^^^^^^^^^^^
>> […]
>
> <http://www.interhack.net/pubs/munging-harmful/>
>
> Therefore, no cookies for you.


the email is an invalid mail antispam and is also write in the mail 
itself:  no mail no
if I write so antispammailantispam perhaps was more comprensible

is this a problem for you

[toc] | [prev] | [next] | [standalone]

#15633

> is this a problem for you


?    (isn't affirmation but a question)

[toc] | [prev] | [next] | [standalone]

#15635 — Address munging considered harmful (was: sql how capture on the fly the row's id when is updated)

alex wrote:

> Il 04/08/2015 21:29, Thomas 'PointedEars' Lahn ha scritto:
>> alex <alex@nomailno.it> wrote:
>>        ^^^^^^^^^^^^^^^^
>>> […]
>>
>> <http://www.interhack.net/pubs/munging-harmful/>
>>
>> Therefore, no cookies for you.
> 
> the email is an invalid mail antispam

First of all, antispam?  Nonsense.  You can get a spam filter for free.

Second, it is not an email (address) at all:

| $ chkmadd -v alex@nomailno.it
| chkmadd 0.1.6.2014122021 -- (C) 2003-2014  Thomas Lahn 
| <mehl@PointedEars.de>
| Distributed under the terms of the GNU General Public License (GPL).
| See COPYING file or <http://www.fsf.org/copyleft/gpl.html> for details.
| Report bugs to <chkmadd@PointedEars.de>.
| 
| Using chkmadd server pe.de
| 
| chkmadd 0.1.5.2006061200 -- (C) 2003-2006  Thomas Lahn 
| <mehl@PointedEars.de>
| Distributed under the terms of the GNU General Public License (GPL).
| See COPYING file or <http://www.fsf.org/copyleft/gpl.html> for details.
| Report bugs to <chkmadd@PointedEars.de>.
| 
| E-mail address(es) to check:
| alex@nomailno.it
| 
| Verifying <alex@nomailno.it>...
| Mail exchanger(s) for nomailno.it: none.
| `A' record for nomailno.it: 
| None, thus <alex@nomailno.it> is definitely not an e-mail address (no MX).
`----

This violates the Internet standard for Usenet, RFC 5536 (section “3.1.2.  
From”), …

> and is also write in the mail
> itself:  no mail no

… and it is antisocial behavior to burden others with the problems caused by 
one’s incompetence.  (Both have been pointed out in the document I referred 
you to.)

I will not support that.

> if I write so antispammailantispam perhaps was more comprensible

You are the one who does not comprehend.

> is this a problem for you[?]

I believe that is what I just said.

And it should be a problem for you, too.

-- 
PointedEars
Zend Certified PHP Engineer
Twitter: @PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.php

csiph-web