Get all results, pdo

[Pain]

Hello, i need some help with pdo. I'm struggling while trying to echo out all results.

class Test {

public $testName;
public $testEmail;
public $testLastname;

public function testGetAll() {
 
$query = $this->db->prepare('SELECT * FROM customers');
$query->execute();
 
$this->testEmail        = $data['email'];
$this->testName       = $data['name'];
$this->testLastname = $data['lastname'];
 
}
}

Edited February 16, 2014 by Pain

[.josh]

You need to use $query->fetch() or $query->fetchAll() or similar.

[ginerjm]

oops.

Edited February 16, 2014 by ginerjm

[Pain]

Thanks for the answer. 

 

When i retrieve those values from the db like this:

$i = 0;
foreach($profile->testGetAll() as $value[$i]) {
$value[$i];
$i++;
} 
 
 

 

i get a big bunch of rows. How can i assign a variable to each row? $name would be name, $lastname would be lastname etc

Edited February 17, 2014 by Pain

[ginerjm]

Just because you are now using PDO doesn't mean the basic principles don't still apply.

$qstmt = $conn->prepare('SELECT * FROM customers');
$qstmt->execute();
while ($row = $qstmt->fetch(PDO::FETCH_ASSOC))
{ 
   $email = $row['email'];
   $testname = $row['name'];
   $testlastname = $row['lastname'];
   echo "$testname has a last name of $testlastname and an email of $email<br>";
}

Note that you don't need to use prepare here since you don't have any arguments to your query. 

[Pain]

Thanks for the reply.

 

It works, thank you. However this code should be inside a class and i don't want my class to echo. I want to use these variables on another page. So if I have something like this in my class:

$i = 0;
while ($row = $qstmt->fetch(PDO::FETCH_ASSOC))
{
$email[$i] = $row['email'];
$testname[$i] = $row['name'];
$testlastname[$i] = $row['lastname'];
$i++;
}

How would i echo out $lastname[5] or any other random variable. 

 

So far i've got this :/

require_once('connect.php');
require_once('class/profile.php');
 
$profile = new Profile($db);
echo $profile->testGetAll();

Edited February 17, 2014 by Pain

[.josh]

you're making this harder than it needs to be.

 

First, you're using a loop with fetch to put columns into 3 separate arrays. This is terrible for 2 reasons. First is that now you have to pass around 3 separate arrays instead of just one multi-dim array. Second, now you will almost certainly never need to make use of just the names or just the lastnames or just the emails. Instead, you will almost certainly be using a loop to have the data grouped by it's original row. IOW you've flipped the rows and columns for no practical reason.

 

You should put it to an array that looks something like this:

 

array(
  0 => array('email'=>'email address','name'=>'some name','lastname'=>'last name here'),
  1 => array('email'=>'email address','name'=>'some name','lastname'=>'last name here'),
  2 => array('email'=>'email address','name'=>'some name','lastname'=>'last name here'),
  // etc..
)

That way you can easily reference a single set. Examples:

 

echo first row:

echo $array[0]['email'];

echo all emails:

foreach ($array as $row) {
  echo $row['email'];
}

Which brings me back to your original use of fetch() and the while loop. All you need to do is use

 

$data = $qstmt->fetchAll(PDO::FETCH_ASSOC);

And then $data will be structured exactly as above. No loop or assigning it to other vars or nothing. That is the exact point of fetchAll: when you want to grab all the returned results and pass it somewhere else.