Re: UPDATE Problem

X-Received	by 10.55.71.79 with SMTP id u76mr9000773qka.2.1517524438119; Thu, 01 Feb 2018 14:33:58 -0800 (PST)
Path	csiph.com!weretis.net!feeder6.news.weretis.net!feeder.usenetexpress.com!feeder-in1.iad1.usenetexpress.com!border1.nntp.dca1.giganews.com!nntp.giganews.com!s47no5611258qta.0!news-out.google.com!g8ni1482qtk.0!nntp.google.com!peer02.iad!feed-me.highwinds-media.com!news.highwinds-media.com!post02.iad!fx27.iad.POSTED!not-for-mail
From	Call Me Tom <noemail@noemail.com>
Newsgroups	alt.php.sql
Subject	Re: UPDATE Problem
Message-ID	<va577dp4afi2b98icu0u8qgpki1k65beqf@4ax.com> (permalink)
References	<eps67dda1pu0dhqi8gfqshnf20k6rt3iji@4ax.com> <fdhdsmFighpU1@mid.individual.net>
User-Agent	ForteAgent/8.00.32.1272
MIME-Version	1.0
Lines	57
X-Complaints-To	http://abuse.usenetxs.com
NNTP-Posting-Date	Thu, 01 Feb 2018 22:33:57 UTC
Date	Thu, 01 Feb 2018 17:33:57 -0500
X-Received-Bytes	2520
X-Received-Body-CRC	715883364
Content-Type	text/plain; charset=us-ascii
Content-Transfer-Encoding	7bit
X-Original-Bytes	2458
Xref	csiph.com alt.php.sql:16

Show key headers only | View raw

On Thu, 1 Feb 2018 22:07:34 +0100, "J.O. Aho" <user@example.net>
wrote:

>On 02/01/18 21:07, Call Me Tom wrote:
>> In table test there is a text field. For all rows I want to change the
>> first 34 characters to something else. Here is my code.
>> 
>> <?php
>> 
>> require_once('./includes/mysql_connect.php');
>> 
>> $query = "SELECT report_id,fsacars_rep_url
>>            FROM test";
>> 
>> $result=$dbh->query($query);
>> $numin=$result->rowCount();
>> echo "$numin";
>> 
>> while($url_orig=$result->FETCH(PDO::FETCH_NUM)) {
>> 
>>         $report_id = $url_orig[0];
>>         $url_trim = substr($url_orig[1],34);
>>         $url_new = 'http://localhost/CAA' . $url_trim;
>> 
>>         $sql="UPDATE test
>>                 SET fsacars_rep_url = $url_new
>>                 WHERE report_id = $report_id";        
>>         $dbh->exec($sql);
>>         
>> } 
>> 
>> From tests I have shown that values are correct, However, the UPDATE
>> fails. A response in a PHP forum was:
>> 
>> "You are updating the table before recovering all the rows from a row
>> set.  The exec will invalidate the internal "cursor" that is used to
>> fetch the rows one by one."
>> 
>> So, my question here is how do I change the first 34 characters of a
>> field in every row of the table? 
>
>Rewrite your SQL-query so that you do it all on the SQL-server instead
>of doing it in the script.
>
>See the following links to SQL-function
>https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substr
>
>https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_trim
>
>https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_concat
>
>Keep in mind, you still need to take care of results/errors for your
>queries as Jerry already pointed out.

Thank you.  I forgot these functions existed in MySQL.  With your hint
the problem was quickly solved.

Back to alt.php.sql | Previous | Next — Previous in thread | Find similar

Thread

UPDATE Problem Call Me Tom <noemail@noemail.com> - 2018-02-01 15:07 -0500
  Re: UPDATE Problem "J.O. Aho" <user@example.net> - 2018-02-01 22:07 +0100
    Re: UPDATE Problem Call Me Tom <noemail@noemail.com> - 2018-02-01 17:33 -0500

csiph-web