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

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

Problem With PDO? or While?

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

Contents

  Problem With PDO? or While? Call Me Tom <noemail@noemail.com> - 2018-01-31 23:20 -0500
    Re: Problem With PDO? or While? Ben Bacarisse <ben.usenet@bsb.me.uk> - 2018-02-01 11:11 +0000
      Re: Problem With PDO? or While? Call Me Tom <noemail@noemail.com> - 2018-02-01 15:54 -0500
        Re: Problem With PDO? or While? Ben Bacarisse <ben.usenet@bsb.me.uk> - 2018-02-01 23:27 +0000
          Re: Problem With PDO? or While? Call Me Tom <noemail@noemail.com> - 2018-02-01 23:57 -0500
    Re: Problem With PDO? or While? Jerry Stuckle <jstucklex@attglobal.net> - 2018-02-01 08:19 -0500

#17678 — Problem With PDO? or While?

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?

[toc] | [next] | [standalone]

#17681

Call Me Tom <noemail@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.

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

#17685

On Thu, 01 Feb 2018 11:11:18 +0000, Ben Bacarisse
<ben.usenet@bsb.me.uk> wrote:

>Call Me Tom <noemail@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?

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?

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

#17686

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

> On Thu, 01 Feb 2018 11:11:18 +0000, Ben Bacarisse
> <ben.usenet@bsb.me.uk> wrote:
>
>>Call Me Tom <noemail@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?
>
> 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?

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.

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

#17687

On Thu, 01 Feb 2018 23:27:54 +0000, Ben Bacarisse
<ben.usenet@bsb.me.uk> wrote:

>Call Me Tom <noemail@noemail.com> writes:
>
>> On Thu, 01 Feb 2018 11:11:18 +0000, Ben Bacarisse
>> <ben.usenet@bsb.me.uk> wrote:
>>
>>>Call Me Tom <noemail@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?
>>
>> 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?
>
>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).

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

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

#17682

On 1/31/2018 11:20 PM, Call Me Tom wrote:
> 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?
> 

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
jstucklex@attglobal.net
==================

[toc] | [prev] | [standalone]

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

csiph-web