Problem With PDO? or While?

[Call Me Tom]

I chose this forum because my sql statements appear to be working.

The logged user has all privileges on the database.
The $numin echo prints 2555 which is the number of rows in the table.
The table test is unchanged after running the file.
Here's the 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);
}

What am I missing?

[Ben Bacarisse]

Call Me Tom <noe...@noemail.com> writes:

> I chose this forum because my sql statements appear to be working.

But I don't think you have a PHP problem.

> The logged user has all privileges on the database.
> The $numin echo prints 2555 which is the number of rows in the table.
> The table test is unchanged after running the file.
> Here's the code.
>
> <?php
>
> require_once('./includes/mysql_connect.php');
>
> $query = "SELECT report_id,fsacars_rep_url
> FROM test";
>
> $result=$dbh->query($query);
> $numin=$result->rowCount();

rowCount is only guaranteed to tell you the number of rows affected by
queries that modify a table. It will work like this for some databases
but it's not guaranteed.

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

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.

> }
>
> What am I missing?

--
Ben.

[Jerry Stuckle]

In addition to Ben's comments, you aren't checking return codes from
your SQL statements - something you should always do.

--
==================
Remove the "x" from my email address
Jerry Stuckle
jstu...@attglobal.net
==================

[Call Me Tom]

Do you have a suggestion as to how I can accomplish my goal of
changing the first 34 characters of the field for every row on the
table?

[Ben Bacarisse]

This is not a PHP question but an SQL one. You could do part of the
work in PHP but you could also do all of it in SQL. I don't have a
simple way to test any examples, so use this with care, but you want
something like:

update test
set fsacars_rep_url =
concat('http://localhost/CAA/', substring(fsacars_rep_url, 35));

(you need to adjust for the different notions of character counting used
by PHP and SQL).

--
Ben.

[Call Me Tom]

On Thu, 01 Feb 2018 23:27:54 +0000, Ben Bacarisse

Thanks for your help. The SQL forum reminded me that these string
functions exist in SQL. With that, the solution was pretty easy.