Solved

How to avoid localhost PDO query error?

Posted on 2013-05-14
26
373 Views
Last Modified: 2013-05-18
Hi Experts!

I'm facing a problem when trying to run a localhost query with PHP/ MySQL.

 //This line causes de browser abend
 $result = $conn->query($sql);

The code before this line,  the connection, is set with no errors.

But when reach this line the browser abends (I concluded it running with/ without this line).

This same code runs all fine remotelly.
Is it needed somewhere any extra configuration?

<?php

    echo "Pass 01" . "\n";

    $db_host = "localhost";
    $db_user = "root";
    $db_word = "espiriplug";
    $db_name = "sakila";

    echo "Pass 02" . "\n";
    
    try
    {
        // Connect and create the PDO object
        $conn = new PDO("mysql:host=$db_host; dbname=$db_name", $db_user, $db_word);
        $conn->exec("SET CHARACTER SET utf8"); // Sets encoding UTF-8

        // Define and perform the SQL SELECT query
        $sql = "SELECT * FROM `actor`";
        echo "Pass 03" . "\n";

        //This line causes de browser abend
        $result = $conn->query($sql);
         
    
        // If the SQL query is succesfully performed ($result not false)
        if ($result !== false)
        {
            $cols = $result->columnCount();  //Number of returned columns
            echo 'Number of returned columns: ' . $cols . '<br />';
             //Parse the result set
            foreach ($result as $row)
            {
                echo $row['actor_id'] . ' - ' . $row['first_name'] . ' - ' . $row['last_name'] . ' - ' . $row['last_update'] .
                    '<br />';
            }
        }

        echo "Pass 04" . "\n";
        $conn = null; // Disconnect
    }
    catch (PDOException $e)
    {
        echo $e->getMessage();
    }
?>

Open in new window


Thanks in advance!
0
Comment
Question by:Eduardo Fuerte
  • 13
  • 7
  • 6
26 Comments
 
LVL 33

Expert Comment

by:Slick812
ID: 39166266
Your query looks wrong to me

change this -
$sql = "SELECT * FROM `actor`";

to this -
$sql = "SELECT * FROM actor";
0
 

Author Comment

by:Eduardo Fuerte
ID: 39166295
I've tried, no success...

Since the PDO object is created, is amazing what causes error in the query. If I run a very similar code remotelly it goes fine....
0
 
LVL 33

Expert Comment

by:Slick812
ID: 39166798
You may just try and leave out this line -
//$conn->exec("SET CHARACTER SET utf8");

I am not sure you need that at all, but the "Language" setting in the Mysql and PDO are usually coordinated to have successful results, and you usually do not need to alter any of the language settings.

you have this line -
echo $e->getMessage();
Your ERROR message here should tell you the Exact reason that the Query failed, does it say that TABLE actor does not exist, or something about a SQL syntax error near  FROM , or what does it say? ?


You seem to think it has something to do with the db Host being localhost, , why is that?
I really have no other suggestions, On my PDO failures I get an error message that tells me what to fix.
0
 

Author Comment

by:Eduardo Fuerte
ID: 39167931
When running  remotely runs ok
img001
When running  at PHPDesigner debbugger runs ok
img003
The local host simply abends after 10-15 seconds working, without give any message error!
img002
(No connection error found)

The code:

<?php

    echo "Pass 01" . "\n";

    $db_host = "localhost";
    $db_user = "root";
    $db_word = "espiriplug";
    $db_name = "sakila";

    echo "Pass 02" . "\n";

    // Connect and create the PDO object
    $conn = new PDO("mysql:host=$db_host; dbname=$db_name", $db_user, $db_word);
   
    //Commented
   //$conn->exec("SET CHARACTER SET utf8"); // Sets encoding UTF-8

    $sql = "SELECT * FROM actor where first_name='PENELOPE'";
    echo "Pass 03" . "\n";

  
    try
    {
        // Line that causes ERROR!!!
        $result = $conn->query($sql);
    }    catch (PDOException $e)
    {
        echo $e->getMessage();
    }


    if ($result !== false)
    {
        $cols = $result->columnCount(); //Number of returned columns
        echo 'Number of returned columns: ' . $cols . '<br />';
        // Parse the result set
        foreach ($result as $row)
        {
            echo $row['actor_id'] . ' - ' . $row['first_name'] . ' - ' . $row['last_name'] .
                ' - ' . $row['last_update'] . '<br />';
        }
    }


    echo "Pass 04" . "\n";
    $conn = null; // Disconnect
    
?>

Open in new window


Any other suggestions?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39168067
Your script cannot accept that the connection occurred as an article of faith!  You might want to try adding error checking to the code.  See the PDO examples in this article.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

// WARNING: AN UNCAUGHT CONNECT ERROR WILL BARK OUT THE DB CREDENTIALS!
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$dsn = "mysql:host=$db_host;dbname=$db_name";
try
{
    $pdo = new PDO($dsn, $db_user, $db_word);
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error('NO PDO Connection', E_USER_ERROR);
}
// SHOW THE PDO CONNECTION OBJECT
var_dump($pdo);

Open in new window

0
 

Author Comment

by:Eduardo Fuerte
ID: 39168234
I've returned the try/ catch to envolve the PDO connection, similar the first code
It was retired since the error is in the line:
$result = $conn->query($sql);

So it was tested before and isn't na "Article of Faith"

<?php

     echo "Pass 01" . "\n";

    $db_host = "localhost";
    $db_user = "root";
    $db_word = "espiriplug";
    $db_name = "sakila";

    echo "Pass 02" . "\n";

    try
    {
        // Connect and create the PDO object
        $conn = new PDO("mysql:host=$db_host; dbname=$db_name", $db_user, $db_word);
       //$conn->exec("SET CHARACTER SET utf8"); // Sets encoding UTF-8
        echo "Pass 021" . "\n";
   
    }
    catch (PDOException $exc)
    {
        var_dump($exc);
        trigger_error('NO PDO Connection', E_USER_ERROR);
    }

    $sql = "SELECT * FROM actor where first_name='PENELOPE'";
    echo "Pass 03" . "\n";

  //  try
//    {
//This line produces the ERROR!
//        $result = $conn->query($sql);
//    }
//    catch (PDOException $e)
//    {
//        echo $e->getMessage();
//    }


//    if ($result !== false)
//    {
//        $cols = $result->columnCount(); //Number of returned columns
//        echo 'Number of returned columns: ' . $cols . '<br />';
//        // Parse the result set
//        foreach ($result as $row)
//        {
//            echo $row['actor_id'] . ' - ' . $row['first_name'] . ' - ' . $row['last_name'] .
//                ' - ' . $row['last_update'] . '<br />';
//        }
//    }
    echo "Pass 04" . "\n";
    $conn = null; // Disconnect
?>

Open in new window


The code above runs ok! so the connection is done!
Img 004
To confirm it's not a connection error.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39168264
Try using var_dump() to print out the contents of the PDO objects.  And in related news, don't remove the error checking code - you really want to know when errors occur!

This is how I might run the query.  Note that there are no single quote marks used here.
$fname    = 'PENELOPE';

// CREATE A QUERY FOR USE WITH BINDPARAM()
$sql = "SELECT * FROM actor WHERE first_name = :fname";

// CURSOR_SCROLL ALLOWS REPOSITIONING THE CURSOR - LIKE DATA_SEEK() - BUT SADLY NOT WITH MYSQL
$pdos = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

// BIND THE VARIABLE AND TRY THE QUERY
$pdos->bindParam(':fname', $fname, PDO::PARAM_STR);

try
{
    $pdos->execute();
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}

Open in new window

Try that and let us know what happens, thanks.
0
 

Author Comment

by:Eduardo Fuerte
ID: 39168799
Unfortunatelly the browser abends after some seconds, now when reach the line:

$pdos->execute();

<?php

    $db_host = "localhost";
    $db_user = "root";
    $db_word = "espiriplug";
    $db_name = "sakila";

    try
    {
        // Connect and create the PDO object
        $pdo = new PDO("mysql:host=$db_host; dbname=$db_name", $db_user, $db_word);
        echo "Pass 01" . "\n";
    }
    catch (PDOException $exc)
    {
        var_dump($exc);
        trigger_error('NO PDO Connection', E_USER_ERROR);
    }

    $fname = 'PENELOPE';

    // CREATE A QUERY FOR USE WITH BINDPARAM()
    $sql = "SELECT * FROM actor WHERE first_name = :fname";

    // CURSOR_SCROLL ALLOWS REPOSITIONING THE CURSOR - LIKE DATA_SEEK() - BUT SADLY NOT WITH MYSQL
    $pdos = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

    // BIND THE VARIABLE AND TRY THE QUERY
    $pdos->bindParam(':fname', $fname, PDO::PARAM_STR);

    try
    {
        // Causes the abend
        $pdos->execute();
    }
    catch (PDOException $exc)
    {
        var_dump($exc);
        trigger_error($exc->getMessage(), E_USER_ERROR);
    }

    $num = $pdos->rowCount();
    $num_fmt = number_format($num);

    //echo "$numfmt" . $num_fmt.  "\n";
    echo "Linhas....." . $num_fmt . "\n";

    echo "Pass 05" . "\n";
    $pdo = null; // Disconnect


?>

Open in new window


and the code to show the error:

var_dump($exc);
trigger_error($exc->getMessage(), E_USER_ERROR);

even is triggered to show something

I guess is a matter of browser incompatibility

The same code runs perfectly at PHPDesigner Debbuger and remotelly...
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39169927
This has nothing to do with the browser.  Please install this script exactly as shown here and run it.  Post the output back here using the code snippet feature to preserve the formatting and to provide line numbers.  I want to see exactly what the script does.  If you can put it on a public-facing server and post a link, that would be helpful, too.

<?php // RAY_EE_pdo_example.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// DATABASE CONNECTION AND SELECTION VARIABLES
$db_host = "localhost";
$db_user = "root";
$db_word = "espiriplug";
$db_name = "sakila";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$dsn = "mysql:host=$db_host;dbname=$db_name";
try
{
    $pdo = new PDO($dsn, $db_user, $db_word);
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}

// SHOW THE PDO CONNECTION OBJECT
var_dump($pdo);
echo PHP_EOL;


// SET PDO TO TELL US ABOUT WARNINGS OR TO THROW EXCEPTIONS
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );


// SOMETHING TO SEARCH FOR
$fname = 'PENELOPE';

// CREATE A QUERY FOR USE WITH BINDPARAM()
$sql  = "SELECT * FROM actor WHERE first_name = :fname";
$pdos = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

// BIND THE VARIABLE AND TRY THE QUERY
$pdos->bindParam(':fname', $fname, PDO::PARAM_STR);
var_dump($pdos);
echo PHP_EOL;

// TRY THE QUERY
try
{
    $pdos->execute();
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $pdos->rowCount();
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo "<br/>" . PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo "<br/>" . PHP_EOL;
}
echo PHP_EOL;


// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
echo "USING PDOStatement::FetchAll(PDO::FETCH_OBJ): ";
echo "<br/>" . PHP_EOL;
while ($row = $pdos->fetchAll(PDO::FETCH_OBJ))
{
    // ROW BY ROW PROCESSING IS DONE HERE
    foreach ($row as $key => $obj)
    {
        echo "$key: ";
        print_r($obj);
        echo PHP_EOL;
    }
}
echo PHP_EOL;

Open in new window

0
 

Author Comment

by:Eduardo Fuerte
ID: 39170013
Hello

Again, browser abends after about 10 sec when running localhost.

The code runs perfectly at PHPDesigner debbuger, so here the results:

R-Debbugger.txt

or
www.espiriplug.com.br/R_Debbugger.txt
0
 
LVL 33

Expert Comment

by:Slick812
ID: 39170016
OK, I can see by your image of img2-ee-150513.png that you do have an ADDRESS problem for connecting to your MySQL, as it says to check your connection address of http://localhost, Not always but it can be that your MySQL connection in the "running  remotely" has a different connection address,  Also the HPDesigner Debbuger may also use a different connection address or start location, I would think that Your PHP and it's settings for MySQL may not be set correctly OR you have not configured the alternate for a localhost, if your server MySQL is not a default configuration. But these are un-informed guesses. I know that on some host servers I have worked on the MySQL connection is a partial or full http address like "'mysql.thishost.com'"  If you use "localhost" with your "remote connection" then that will not work with this in the local PHP. . . . You might try to find out the MySQL connection address for your server PHP. Again I am just guessing but it does seem to fail. If I were you I would try and connect using just the Regular PHP MySQL connections, NOT PDO, and see if localhost works in that way.

$link = mysql_connect('localhost', 'root', 'espiriplug');
if ($link) {
    echo "Connection to MySQL SUCCESS";
    mysql_close($link);
    } else
    die('Could not connect: ' . mysql_error());


= = = = = = =
One thing I failed to notice, since I automatically add it to all of my PDO code is this line -
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
you need to have that in your code if you want it to exception out and give you error messages.
$db_host = "localhost";
$db_user = "root";
$db_word = "espiriplug";
$db_name = "sakila"
try {$conn = new PDO("mysql:host=$db_host; dbname=$db_name", $db_user, $db_word);
    echo 'Made PDO connection<br />';
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "SELECT * FROM actor where first_name='PENELOPE'";
    $result = $conn->query($sql);
    foreach ($result as $row)
        {
         echo $row['actor_id'] . ' - ' . $row['first_name'] . ' - ' . $row['last_name'] .
                ' - ' . $row['last_update'] . '<br />';
        }

    $conn = null;
    } // try
	catch(PDOException $e){echo $e->getMessage();}

Open in new window


 But if it is a connection address problem, then this will NOT give you any more information. . I hope you can find the correct connection address for your local PHP MySQL, but I can not see any way to really help you now, sorry. But you might can find MySQL connection info in your Host cPanel for the MySQL,
0
 

Author Comment

by:Eduardo Fuerte
ID: 39170040
I'm going to better understand what you've posted...

Just for curiosity, this time I have the option to trigger the debbuger and had this screen
AddBlock
Maybe this componente addblock is causing the instability (?!)

After uninstall AdBlock the problem remains....
0
 
LVL 33

Expert Comment

by:Slick812
ID: 39170075
I doubt that your browser (I.E.) is the cause of any PHP problems and a database connection. Maybe it's a PDO settings incorrect configure?
it may do you well to use the newer PHP improved mysqli  , for ALL of your database operations. I used to use PDO, but now I avoid it, It can seem mixed up (like you have here) and it DOES NOT offer you all of the methods and tools that improved MySQL has to offer.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Eduardo Fuerte
ID: 39170080
I've tryed the last code you give...
Abends at localhost too (Ok at PHPDesigner debbuger...)

Do you suggest I check any configuration at PHP.ini ?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39170098
Here is the output of the script.  It appears to have worked perfectly!
object(PDO)#1 (0) {
}

object(PDOStatement)#2 (1) {
  ["queryString"]=>
  string(45) "SELECT * FROM actor WHERE first_name = :fname"
}

QUERY: SELECT * FROM actor WHERE first_name = :fname FOUND 4 ROWS OF DATA 

USING PDOStatement::FetchAll(PDO::FETCH_OBJ): 
0: stdClass Object
(
    [actor_id] => 1
    [first_name] => PENELOPE
    [last_name] => GUINESS
    [last_update] => 2006-02-15 04:34:33
)

1: stdClass Object
(
    [actor_id] => 54
    [first_name] => PENELOPE
    [last_name] => PINKETT
    [last_update] => 2006-02-15 04:34:33
)

2: stdClass Object
(
    [actor_id] => 104
    [first_name] => PENELOPE
    [last_name] => CRONYN
    [last_update] => 2006-02-15 04:34:33
)

3: stdClass Object
(
    [actor_id] => 120
    [first_name] => PENELOPE
    [last_name] => MONROE
    [last_update] => 2006-02-15 04:34:33
)

Open in new window

So what is the issue at this point?
0
 

Author Comment

by:Eduardo Fuerte
ID: 39170122
Ray...

The problem is it doesn't run ok at browser...
It has runned ok in PHPDesigner debbuger that runs at its own PHP

Here the PHP.INI used by it - success in connect local database
php.ini

Apache localhost trigger this PHP.INI
php.ini
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39170128
doesn't run ok at browser...
Please post a clickable URL link that demonstrates the failure, thanks.
0
 

Author Comment

by:Eduardo Fuerte
ID: 39170169
How ?!

The error occurs at localhost....

http://localhost/...

localhost
0
 
LVL 33

Expert Comment

by:Slick812
ID: 39170292
EFuerte, I looked at your   php.ini and It looks like the default install, but I do not have time to look at all of it, it does have the
extention=mysql.dll

but it uses the defaults for many of the mysql settings. The PDO settings also seem to be the default, and it does seem to list a mysql in the PDO extention. . . However this php.ini does Not show how the MySQL server was set to use or not use localhost, , , Sorry but I am not an expert in PHP and MySQL configure settings on a windows box.

you might try using  a Web address connection through your browser, instead of http://localhost, maybe something like -
www.espiriplug.com.br/pdotest.php

but you really need to check and see if your normal (NOT PDO)  MySQL database connection is working from a web address at www.espiriplug.com.br  , as I tried to say the PDO is a "one size fits all databases" wrapper for MySQL, SQLite, and other DB.  You really should work only in mysql if you are setting up your server, and when the MySQL woks for you then test out PDO.
0
 

Author Comment

by:Eduardo Fuerte
ID: 39170890
As I told since the start :
The remote conection runs fine!

You can try at:
 www.espiriplug.com.br\testa_conexao2.php

The problem is just at localhost (my own PC/ Apache/PHP/MySQL)

Thanks.
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 39171176
Firefox can't find the server at www.espiriplug.com.br\testa_conexao2.php.
I'm sorry - I just don't know how I can help any more.  I've furnished a code snippet that works correctly in the "real world" and there is nothing I can do to make tests on your laptop.  

Best of luck with your project, ~Ray
0
 
LVL 33

Assisted Solution

by:Slick812
Slick812 earned 250 total points
ID: 39171818
Sorry, your description of your problem, and your comments, made me focus on the database PDO and it's connection, I simply did NOT understand that you had problems with your browser and using the http://localhost web address, because you said that it was a PDO problem. I also mis understood what you meant by your " remote conection" as a data base " remote conection" not a web browser  "remote conection" statement, As I now understand your problem I have no solutions to offer, It seems that even if you do a NON database web page or just plain page.html (no php) page from http://localhost, that they also will not work. All I will say is that you can change the way Apache recognizes the connections it accepts, but I have never ever tried to connect a browser to http://localhost, , but I have used a browser to connect to  network connections, by using the  IP address of the LAN connections, and these can be set to do Apache and PHP.  I can not see any purpose for you wanting to use the http://localhost , if you have no reason to do this, then don't do it. If you do have a necessary reason, then click the "request attention" under your top main question, and try to get someone in experts "Apache Zone" to tell you how to configure a window's box Apache to do http://localhost as if you were connecting to a Web connection on port 80. As far as I am concerned with server performance, if your Web connection for your domain name is working, then all is GOOD.
0
 

Author Comment

by:Eduardo Fuerte
ID: 39172424
Hello

Any page without the critical lines, like:
 $pdos->execute();
 $pdos->query();

runs fine at http://localhost/

including PDO object  code, like
 $pdo = new PDO($dsn, $db_user, $db_word);

(obs. What did you mean? www.espiriplug.com.br\testa_conexao2.php runs perfect on Firefox)
Firefox
Something very strange is happing... maybe some day I found a solution!

Thanks for all your attention and assistance !
0
 

Author Closing Comment

by:Eduardo Fuerte
ID: 39174416
They did what they could to help me. I guess it's almost an intractable issue, mainly that's at my desktop.
0
 

Author Comment

by:Eduardo Fuerte
ID: 39177591
Hello

I've installed PHP 5.3.24 and the problem is solved !!!

(I was using PHP 5.2.17)

PHP 5.3.24
There was an incompatibility between MySQL and PHP 5.2.17 at my desktop.
0
 
LVL 33

Expert Comment

by:Slick812
ID: 39178086
It's still strange, looks like there was a mix up between PHP and Apache for localhost in PDO after the PDO connection ? PHP 5.3 is suppose to have better underlying code, it's probably a good thing that you switched, I like 5.3 better than 5.2 , , , except that so many php mvc and addons are stuck with the old MySQL and can not be installed in it.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now