UPDATE Problem

[Call Me Tom]

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?

[J.O. Aho]

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

[Call Me Tom]

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

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