Simple (but frustrating) PHP/PDO problem

gerryjuice · 26-03-2009 12:29PM #1

Hi im currently going through a fairly straight forward PHP tutorial. The tutorial consists of a simple insert into a table and a simple select from this table to view all records.
I am able to insert into the table easily, and can even select from the table when i use a selection which involes only selecting one of the columns of the table ie

SELECT name FROM attribute_values

But as soon as i try to select multiple columns, the program seems to time out, and i see the "Internet Explorer cannot ....etc" page. This seems a bit odd, i am using XAMPPLITE... are there any known issues surrounding doing selects from MYSQL databases within this package (im clutching at anything...) my the code is as follows, if anyone has any ideas....

<?php
/*** mysql hostname ***/
$hostname = 'localhost';

/*** mysql username ***/
$username = 'root';

/*** mysql password ***/
$password = '';

try {
    $dbh = new PDO("mysql:host=$hostname;dbname=shop", $username, $password);
    /*** echo a message saying we have connected ***/
    echo 'Connected to database<br />';

    /*** set the error reporting attribute ***/
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
    
    /*** INSERT data ***/
    $count = $dbh->exec("INSERT INTO attribute_values(name, value) VALUES ('color', 'yellow')");

    /*** echo the number of affected rows ***/
    echo $count;
    

    /*** The SQL SELECT statement ***/
    $sql = "SELECT * FROM attribute_values";
    foreach ($dbh->query($sql) as $row)
        {
        print $row['name'] .'<br />';
        }

    /*** close the database connection ***/
    $dbh = null;
}
catch(PDOException $e)
    {
    echo $e->getMessage();
    }
?>

FruitLover · 26-03-2009 01:04PM

gerryjuice wrote: »

    $sql = "SELECT * FROM attribute_values";
    foreach ($dbh->query($sql) as $row)
        {
        print $row['name'] .'<br />';
        }

My PHP is rusty, but this doesn't look right to me. Try something along the lines of:

    $sql = "SELECT * FROM attribute_values";
    $results = $dbh->query($sql);
    foreach ( $results->fetch(PDO::FETCH_ASSOC) as $row)
        {
        print $row['name'] .'<br />';
        }

gerryjuice · 26-03-2009 02:10PM

Hi Fruitlover,

Thanks for your surgestion, i made the change but unfortnatelly it does not seem to work. The web page seems to time out and i get the "Internet Explorer cannot...etc" page.

Im beginning to think that its not the code, but something to do with the database. But then again why can i to an insert and a select when looking for one column

(slowly going crazy)

ron_darrell · 26-03-2009 02:30PM

Silly question - I can almost see the answer as I write this but - do you need to use the PDO? For what you're trying to do I'd use the much simpler:

<?php
$con = mysql_connect('localhost', 'root', '');
if(!con) {
die('connection to DBMS could not be made');
}
else
{
echo "Connection to DBMS created ..<br />";
mysql_select_db('shop', $con);
/* I'm putting the below code in - in case you haven't already created a table called attribute_values - if you have leave the comment chars in - if not remove them
$strQ = "CREATE TABLE attribute_values(autonum integer not null auto_increment, name varchar(50) not null, value varchar(50) not null, primary key(autonum));
mysql_query($strQ);
*/
$strQ = "INSERT INTO attribute_values(name, value) VALUES ('color', 'yellow');";
mysql_query($strQ);
$strQ = "SELECT * FROM attribute_values;";
$result = mysql_query($strQ);
while($row = mysql_fetch_array($result)) {
echo $row["name"]."<br />";
}
mysql_close($con);
}

Hope this helps - if you still need the PDO come back to me - I'd advise running the above code anyways to make sure that the mySQL and Apache Servers are working correctly.

gerryjuice · 26-03-2009 02:52PM

thanks for the reply ron,

As you can probably see from my initial post im relatively new PHP. By using PDOs, i "think" i am trying to manage the database activities more effeciently than by not using them. My database activities i intend to use will prob be your run of the mill straight inserts/selects/updates. Is there a better way to manage database activity in PHP than PDOs.

I input your code it works fine.

pauldiv · 27-03-2009 12:43AM

gerryjuice wrote: »

Is there a better way to manage database activity in PHP than PDOs.

Gerry I use the same technique that Ron mentioned as it is pretty straightforward.

PDOs are earmarked to become the standard PHP method of database interaction in the future so dont rule them out yet.

With PDOs you dont need to write so much code and they are pretty efficient when you know how to use them.

In theory you can connect to many different databases using the same PDO code.

ron_darrell · 27-03-2009 01:20AM

I'd have to agree with PaulDiv agreeing with me here OP

For something as straight forward as selects and inserts (provided you're comfortable enough with SQL statements and manipulating arrays) the straight forward PHP code is probably the most efficient way of doing what you're trying to do.

PDO (from the little I know of the subject) seems to be trying to bring an OOD (Object Orientated Design) framework to how PHP interacts with databases. Nothing wrong with that but it's a little like building the Sistine Chapel when all you needed was a garden shed in a case like the one you've described.

Glad I could be of some help.

RD

Simple (but frustrating) PHP/PDO problem

Comments