Cant get data from table using PHP/PDO

Question

im trying to retrieve data from my table only for some reason I receive a syntax error...

Can anybody see where i may be going wrong?

    $cid = $_GET['id']; 

    $username = 'liam';
    $password ='';  

try {
$conn = new PDO('mysql:host=localhost;dbname=', $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$data = $conn->query('SELECT * FROM directory WHERE ID = :cid');
$data->bindValue(":cid", $cid);

foreach($data as $row) {


    print_r($row). '<br />';
}

Error

 ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':cid' at line 1

It would be helpful if you would post the exact error message you get. — PeeHaa, Jan 06 '13 at 17:55
I think you should read this http://stackoverflow.com/questions/4978481/pdoexec-or-pdoquery — cjds, Jan 06 '13 at 18:07
@Liam, like I said, make sure WHERE ID = :cid' is not suppose to be WHERE id = :cid' — Rocks, Jan 06 '13 at 18:11
You also do not seem to naming a database in your new PDO stmt — Brad, Jan 07 '13 at 21:35

score 1 · Accepted Answer · answered Jan 06 '13 at 17:58

1

$data = $conn->query('SELECT * FROM directory WHERE ID = :cid');
$data->bindValue(":cid", $cid);

You have to bind variables before querying DB...

Take a look at the example on the manual http://php.net/manual/en/pdostatement.bindparam.php

answered Jan 06 '13 at 17:58

dev-null-dweller

29,274
3
65
85

score 1 · Answer 2 · edited May 23 '17 at 11:56

You are running the query instead of preparing it.

Change

$conn->query

to

$conn->prepare

query() simply runs a given query as is. prepare() sends the prepared statement to your database after which it can be run with bound parameters.

I also see you are using the MySQL driver with PDO. When working with MySQL you have to disable emulated prepared statements:

$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

For more information see: How can I prevent SQL injection in PHP?

Oliver Spryn · Answer 3 · 2013-01-06T18:01:50.780

1

Try using this:

$data = $conn->prepare('SELECT * FROM directory WHERE ID = :cid');
$data->bindValue(":cid", $cid);

Note the use of prepare() rather than query().

edited Jan 06 '13 at 18:01

answered Jan 06 '13 at 17:59

Oliver Spryn

16,871
33
101
195

Rocks · Answer 4 · 2013-01-06T18:12:00.923

1

define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_CHARSET', 'UTF-8');
define('DB_DATABASE', 'database');
define('PDO_DSN', 'mysql:host=' . DB_SERVER . ';dbname=' . DB_DATABASE . ';charset=' .
DB_CHARSET);

try {

$db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD,array(PDO::MYSQL_ATTR_INIT_COMMAND 
=> "SET NAMES UTF8"));
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/*** echo a message saying we have connected ***/

}
catch(PDOException $e)
{
echo $e->getMessage();
}

Make sure ID is capital letter in your table!!

$data = $db->prepare('SELECT * FROM directory WHERE ID = :cid');

Maybe here I would use bindParam instead of bindValue()

$data->bindValue(':cid', $cid);
$data->execute();
$result = $data->fetchAll();

foreach($result as $row) {


print_r($row). '<br />';
}

edited Jan 06 '13 at 18:12

answered Jan 06 '13 at 18:04

Rocks

511
1
6
19

And what if the field in his table is not capital ID? Just wondering why it had to be in capital .. – dbf Jan 06 '13 at 18:22
1

@dbf, there is a difference between WHERE ID = 1 and WHERE id = 1 – Rocks Jan 06 '13 at 18:31
Yeah the difference is one is capitalized and the other is not, my question was why does this matter .. – dbf Jan 06 '13 at 19:27

score 0 · Answer 5 · answered Jan 06 '13 at 18:00

0

I'm not seeing a database name in your connect line, nor am I seeing a use clause in your script.

answered Jan 06 '13 at 18:00

hd1

33,938
5
80
91

Cant get data from table using PHP/PDO

5 Answers5