Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community!

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial


Aaron Walton
Aaron Walton
3,557 Points

PHP using PDO to query mysql and return results

Unfortunately there is no PHP/MySQL vids here on treehouse yet but I was hoping someone with some experience could help me out. Many of the examples I find online have the results of the query stored in an array and then the array iterated through with a loop to display the contents of the array. Isn't there a simpler method to use when you know that the query you are using will only have one result? Are arrays always necessary? It seems that the answer to this should be "of course", but every time I try to do this in code I get some kind of error.

If possible I would just like to set the value of a variable to the single result of the query and echo that out. Any code examples would be appreciated.

4 Answers

Aaron Walton
Aaron Walton
3,557 Points

Thanks for the responses. I worked through this and got where I needed to be. I was having a lot of trouble understanding how PDO returned the results and populated arrays, and how those arrays could be accessed. Ultimately my code looked something like this:

//connect to the db
$dbh = new PDO('mysql:host=localhost;dbname=mydb', dbuser, dbpw); 

//build the query
$query="SELECT field1, field2
FROM ubertable
WHERE field1 > 6969";

//execute the query
$data = $dbh->query($query);
//convert result resource to array
$result = $data->fetchAll(PDO::FETCH_ASSOC);

//view the entire array (for testing)

//display array elements
foreach($result as $output) {
echo output[field1] . " " . output[field1] . "<br />";
Jeremy Germenis
Jeremy Germenis
29,854 Points
$conn = mysql_connect('localhost', 'root', 'root'); 
$result = mysql_query('SELECT myColumn FROM myTable WHERE id="1"', $conn); 
echo $variable = mysql_result($result, 0); 

In the above example we have the table(myTable) with two columns (id) and (myColumn). If I know the value of id of a particular id in a row I can then select a specified column(myColumn) of that row and return a single result. I can then use mysql_result to assign the value to a variable.

Aaron Walton
Aaron Walton
3,557 Points

That code is deprecated. I was looking for usage of the PHP mysql PDO class. Thanks for the response though :)

Here's what I do (super simplified, I could put together a full example if you need Aaron):

$variables = our query;

so instead of foreach ($variables as $v)…

You can do $variables[0]['whatever'];

Since we know we are working with an array with one set the default is 0. Let me know if you would like a full example or if this was enough for you to understand.

Thank you,Thank you.Thank you.Thank you.Thank you !! That was exactly what I was searching for ! You're awesome dude, Have a nice day!

Glad you got it worked out. For next time you could just do

$result[0]['field1'] or $result[0]['field2']. That way you wouldn't have to go through the foreach loop for just one row.