-1

I am new to PDO and am attempting to convert my existing PHP/MYSQL code to meet PDO standards.

The problem i am having is I can connect to the database but no results are being shown and no errors are being displayed.

This is my database:

$db2 = new PDO('mysql:host=localhost;dbname=DATABASENAME;charset=utf8', 'USERNAME', 'PASSWORD');
$db2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db2->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

I am using

include 'db.php';

to include the above database details in my main PHP script.

My main script using the following as a select statement to display the rows which match the criteria:

<?

foreach($db2->query('SELECT view_invoice FROM user_info where username = "$timeapp_username"') as $inrow) {
$inrow['view_invoice']; //etc...

}

?>

On running this I get no errors but no results displayed. I cannot spot what I am doing wrong. Can anyone advise what I am doing wrong here?

halfer
  • 19,824
  • 17
  • 99
  • 186
Sandy
  • 312
  • 6
  • 20
  • When connecting, put your call into try...catch block:`try { $db2 = new PDO('mysql:host=localhost;dbname=DATABASENAME;charset=utf8', 'USERNAME', 'PASSWORD'); } catch (PDOException $e) { print "Error!: " . $e->getMessage() . "
    "; die(); }` let us know the results     – user4035 Nov 30 '13 at 20:25
  • Unfortunately, questions like "My code doesn't work" are offtopic. Stack Overflow is not online debugging facility. – Your Common Sense Nov 30 '13 at 20:55
  • 1
    Hi user4035 that didnt work. I can connect to the database and get confirmation of that, just not sure where im going wrong. – Sandy Nov 30 '13 at 21:24
  • **WHY** `foreach` ?? – david strachan Nov 30 '13 at 21:33
  • 1
    david strachan - I went with foreach based of an example as i am trying to learn PDO and eventually convert my current scripting to PDO. What do you recommend instead of foreach? – Sandy Nov 30 '13 at 21:38
  • I came across the example of how to use PDO on stackoverflow: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 – Sandy Nov 30 '13 at 21:44
  • `PDOStatement` is iterable, so foreach is correct here. – Maerlyn Dec 01 '13 at 08:07

1 Answers1

1

The query function is unsafe and should be used only for queries that will not return data, like UPDATE, DELETE, INSERT...

To make safe and working SELECT queries, prepare your query with the PDOStatement. See:

//Example querystring
$id = $_GET['id'];

try{
 //Instantiate PDO
 $pdo = new PDO('dsn', 'user', 'password');

 //Create the statement
 $statement = $pdo->prepare("SELECT * FROM `my_table` WHERE `id`=:id");

 //Now you can bind values to the statement. This will automatically escape the values

 //Defines the type of the value that you'll bind (optional)
 $data_type = (is_numeric($id)) ? PDO::PARAM_INT : PDO::PARAM_STR;

 //Replace the :id in the query by the value retrieved from the querystring
 $statement->bindValue(':id', $id, $data_type);

 //Now, let's execute our statement
 $statement->execute();

 //If the query has returned any rows, we can iterate over it
 if ($statement->rowCount() > 0)
 {
     foreach ($statement->fetchAll() as $result)
     {
         //Now you can retrieve the values using the defined fetch method.
         //Example with associative fetch mode:
         echo 'My name is '.$result['name']."!";
         echo '<br />';
     }
 }
 else
 {
     //No results found
 }

 } catch (PDOException $pe){
    die("An error has occurred: ".$pe->getMessage());
 }
Byte Bit
  • 513
  • 5
  • 12