Need to return/echo PHP PDO results from database

Question

Well I've did do my research and I just can't seem to figure this out. So long story short, I'm using something like this:

btw, "(WebsiteInfo)" is just to sensor out my website/database information.

$SQL = new PDO('mysql:dbname=(WebsiteInfo);host=(WebsiteInfo);charset=utf8', '(WebsiteInfo)', '(WebsiteInfo)');

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

$SQL -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Now, that's just currently just to start the database connect up. Which btw is mostly just a copy/paste code I found on this website too which is to prevent MySQL injection. Actually from this link How can I prevent SQL injection in PHP?. If there's anything wrong with it, I wouldn't mind advice or tips. As long as if it makes sense because I just started using databases probably not even a week ago so everything is new to me. Now there's this:

$Exicu = $SQL -> prepare("SELECT `tags` FROM `users` WHERE `user_id` = :a ") -> execute(array( ':a' => 16));

$Exicu is just there, because I have been trying to get the results from the query (if I said that right). Also the 16 is the users ID, but this will change often so that's why 16 isn't just tossed in the prepare statement. I've tried a lot of things but none of them worked. It either didn't work or made the PHP crash.

But anyway, things I already tried for $Exicu is $Exicu->rowCount(), $Exicu->bindParam, a while loop with $row = $Exicu->fetch(), $SQL->query($Exicu)->fetchAll();, a foreach loop with ($Exicu->fetch(PDO::FETCH_ASSOC) as $row), $Exicu->get_result(), echo PDO::query($Exicu);, echo mysql_result($Exicu), and just echo $Exicu. Yes I know, that looks pretty sloppy.

But none of these seemed to work to just show me the tags from the database of the specific user. So that's pretty much what I need help with. There's no problem when I use something like this echo mysql_result( mysql_query("SELECT (etc,etc)") ) but that doesn't have protection from MySQL injections.

aborted · Accepted Answer · 2013-02-14T07:03:07.303

5

I do my PDO queries like this:

$user_id = 16;    

$query = $SQL->prepare('SELECT tags FROM users WHERE user_id = :uid');
$query->bindValue(':uid', $user_id, PDO::PARAM_INT);
$query->execute();

while ($row = $query->fetch(PDO::FETCH_ASSOC))
{
    echo $row['tags'];
}

This will select data from the database, bind values in it safely and then we echo out the results.

The loop is needed to iterate through every result returned from the query. You can skip the looping part and create a variable like in the while statement, and use $row as an array with your results.

edited Feb 14 '13 at 07:03

answered Feb 13 '13 at 21:57

aborted

4,481
14
69
132

I think that's one of the codes that makes my php shows "500 Internal Server Error". I don't know why it does that though. – Hybrilynx Feb 13 '13 at 22:16
Personally, I never experienced a 500 error code from SQL statements via PDO. Are you sure code like this causes that error? – aborted Feb 13 '13 at 22:19
It does. I just copy/pasted my first 3 lines of code (except with my website database info) and then yours in a new php file and i get the same error. Well, though, my default php for my website is php5.2. Could that be why? Besides that, I can probably ask my costumer service since I did have issues with php before. Which was that sessions weren't working. – Hybrilynx Feb 13 '13 at 22:30
I'm not sure, I can't help you further, but the code is just fine and it should work. I guess contacting your host is a good idea. – aborted Feb 13 '13 at 22:38
I got it working now actually (with your code). I thought I did a code like yours already but I'm not sure what happened. However though, the problem currently with yours is that the while loop is missing the last ")". Thanks though. – Hybrilynx Feb 13 '13 at 23:49

Your Common Sense · Answer 2 · 2013-02-14T08:27:11.060

There is a thing called user defined function.

I am wondering why noone on this site ever using them.
For some reason everyone is ready to make a mile long single line of chained methods, instead of clean and concise function call:

$user_id = 16;    
$tags = getone('SELECT tags FROM users WHERE user_id = ?',array($user_id));

there are many ways to create such a function. A quick and dirty one

function getone($sql, $data) {
    global $SQL;
    $stmt = $SQL->prepare($sql);
    $stmt->execute($data);
    return reset($stmt->fetch());
}

but of course it would be better to make set of functions and put them in a class

Need to return/echo PHP PDO results from database

2 Answers2