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! While you're at it, check out some resources Treehouse students have shared here.

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

PHP

Posted by nicholas maddren
nicholas maddren
12,793 Points

PHP MySQL PDO Display Data

Hey guys so I am watching Randy's videos about PHP integration with MySQL, just so I don't have to go and watch all of the Simple PHP Application videos how would I echo one row of one column?

Here is the code I currently have:

<?php

try {
    $db = new PDO("mysql:host=localhost;dbname=database","driven","password");
    $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    $db->exec("SET NAMES 'utf8'");
} catch (Exception $e) {
    echo "Could not connect to the database.";
    exit;
}

try {
    $results = $db->query("SELECT Make, Model FROM import ORDER BY Make ASC");
    echo "Successful.";
} catch (Exception $e) {
    echo "Error.";
    exit;
}
echo "<pre>";
var_dump($results->fetchAll(PDO::FETCH_ASSOC));

?>

So if I wanted to echo the 'Make' column of the SQL table how would I go by doing this?

Andrew Shook
Andrew Shook
31,709 Points

Well I see that you are returning the rows as an Associative array, so you should be able to select one or the other. Could you paste the var_dump in so I can see how the array is structured?

nicholas maddren
nicholas maddren
12,793 Points

Okay here is the dump:

Successful.
array(10) {
  [0]=>
  array(2) {
    ["Make"]=>
    string(3) "BMW"
    ["Model"]=>
    string(4) "520D"
  }
  [1]=>
  array(2) {
    ["Make"]=>
    string(3) "BMW"
    ["Model"]=>
    string(2) "M5"
  }
  [2]=>
  array(2) {
    ["Make"]=>
    string(11) "Lamborghini"
    ["Model"]=>
    string(9) "Aventador"
  }
  [3]=>
  array(2) {
    ["Make"]=>
    string(8) "Mercedes"
    ["Model"]=>
    string(7) "C Class"
  }
  [4]=>
  array(2) {
    ["Make"]=>
    string(6) "Pagani"
    ["Model"]=>
    string(5) "Zonda"
  }
  [5]=>
  array(2) {
    ["Make"]=>
    string(4) "Saab"
    ["Model"]=>
    string(2) "93"
  }
  [6]=>
  array(2) {
    ["Make"]=>
    string(6) "Subaru"
    ["Model"]=>
    string(3) "STI"
  }
  [7]=>
  array(2) {
    ["Make"]=>
    string(6) "Subaru"
    ["Model"]=>
    string(6) "Legacy"
  }
  [8]=>
  array(2) {
    ["Make"]=>
    string(6) "Toyota"
    ["Model"]=>
    string(5) "Supra"
  }
  [9]=>
  array(2) {
    ["Make"]=>
    string(8) "Vauxhall"
    ["Model"]=>
    string(6) "Vectra"
  }
}

2 Answers

Andrew Shook
Andrew Shook
31,709 Points
Andrew Shook
Andrew Shook
31,709 Points 
<?php 
    foreach( $results as $result ){
        echo $result['Make'];
    }
 ?>
Andrew Shook
Andrew Shook
31,709 Points

Sorry you should put this before the foreach statement:

$results = $results->fetchAll(PDO::FETCH_ASSOC);

 foreach( $results as $result ){
        echo $result['Make'];
    }
thomascawthorn
thomascawthorn
22,986 Points
thomascawthorn
thomascawthorn
22,986 Points

In your PDO statement, you're using $results->fetchAll(PDO::FETCH_ASSOC), which is bringing back ALL of the resullts ;)

Are you trying to return a single result and print that to the screen? Or from your total array, echo out each value in turn?

If you're looking to only echo and return a specific result from the database, or the first result etc.. you can try some other PDO functions.

You don't have to always find your answers via Treehouse - the PHP documentation would be far more efficient! Try this out. You can see all the different functions in the menu on the right hand side (including fetchAll). If you don't want to fetch ALL the results, how about using just plain old fetch instead, which should return just one row. You should find what you're looking for in example#1 if you scroll down the page.

Fetch column is also available.

If you're looking simply to roll over your array and grab all the data, the method Andrew mentioned is probably most efficient!