3

Apologies for opening what should be considered a very basic question. Please note I have coded using the mysql_query method 1000's of times thus, it is really difficult for me to make the switch but I'm finally doing it.

My 1st Problem

My 1st problem comes in simply returning a list of select statements

Example:I am trying to execute:

$sql="Select *From members";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
  $name = $row['name'];
}

I have read the following SO questions, and tried to implement the answers without success,

PDO equivalent of mysql_fetch_array

translation mysql_fetch_array to PDO::FETCH_NUM

I am now working with a PHP book supposed to be "one of the best for 2015/16", the book says the following regarding the above problem

enter image description here

Again I have tried the recommendation from the book without success

<?php
$dsn ='mysql:localhost;dbname:myDB;';
$uname = 'root';
$pword = '';
$db = new PDO($dsn, $uname,$pword);
if($db){
  echo'Success';    
} else {
  echo'error';  
} 

$sql="SELECT * from members";
$results = $db->query($sql);
foreach($results as $result) {
  echo $result['name'];
}

The above code gives me the message, "success" thus, I am successfully connecting to my DB but the query returns the following error:

Invalid argument supplied for foreach()

I am finding it enormously frustrating that I have to go through such lengths just to return the results from a simple select statement and am seriously considering going back to the old way, where I would have completed my assignment by now, I am turning to the SO community for help in a last attempt to get it right. Any help greatly appreciated.

Community
  • 1
  • 1
Timothy Coetzee
  • 5,626
  • 9
  • 34
  • 97
  • `PDO::query` returns a [PDOStatement object](http://php.net/manual/en/class.pdostatement.php) which you can use to `fetch` results. – apokryfos Apr 12 '16 at 08:17
  • @apokryfos OK but why does my $50 book give me a different example...just asking? – Timothy Coetzee Apr 12 '16 at 08:21
  • 1
    `PDOStatement implements Traversable` which means you can iterate it via a foreach, provided the query worked. – apokryfos Apr 12 '16 at 08:23
  • 1
    Are you sure your query is actually returning successfully? Put a `print_r($db->errorInfo());` before the `foreach` and after the `query` to make sure. – Matthew Herbst Apr 12 '16 at 08:27
  • If you're told that your argument to `foreach` is incorrect, then **inspect what argument you're actually giving to `foreach`.** – deceze Apr 12 '16 at 08:29
  • 1
    @TimothyCoetzee Because it is a book and books are evil. – Tom Apr 12 '16 at 09:38

1 Answers1

2

I am very ashamed of posting this, but I have found the problem. Possibly it might help someone in the future.

The code with querying the DB is fine.

The Problem is with the following in line 1:

$dsn ='mysql:localhost;dbname:myDB;';

It should read

$dsn ='mysql:host=localhost;dbname=myDB;';

The If statment will always return echo success even if you are not connected thus, use try/catch

lesson learned: Always inspect your code from line 1

Timothy Coetzee
  • 5,626
  • 9
  • 34
  • 97