Re: UPDATE Problem

Path	csiph.com!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From	"J.O. Aho" <user@example.net>
Newsgroups	alt.php.sql
Subject	Re: UPDATE Problem
Date	Thu, 1 Feb 2018 22:07:34 +0100
Lines	55
Message-ID	<fdhdsmFighpU1@mid.individual.net> (permalink)
References	<eps67dda1pu0dhqi8gfqshnf20k6rt3iji@4ax.com>
Mime-Version	1.0
Content-Type	text/plain; charset=utf-8
Content-Transfer-Encoding	7bit
X-Trace	individual.net Lxy70AJyQr/Utlfhxb4RSwxzPOQSXcIc6fixpe4cnezXLv86lj
Cancel-Lock	sha1:umGcA1Z+57nePrwG83HN6/rsqgQ=
User-Agent	Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.4.0
In-Reply-To	<eps67dda1pu0dhqi8gfqshnf20k6rt3iji@4ax.com>
Content-Language	en-GB
Xref	csiph.com alt.php.sql:15

Show key headers only | View raw

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.


-- 

 //Aho

Back to alt.php.sql | Previous | Next — Previous in thread | Next 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