PDO Connection then SELECT result or results

[djalam]

Hi, need some help getting column keys for a query, setting them as constant
and WHILE its getting rows it echo's each $row["$column1"] and ["$column2"]
I don't know how else to explain it so here is the script with and details in the comment section.

<?php
class PDO_connection {

	function do_query($getme) {
	
		//random do stuff connecting
		try {
		
				$this->dbh = new PDO("mysql:host=localhost;dbname=test_db", user, password);

				/*** set the error reporting attribute ***/
				$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
				$STH = $this->dbh->query($getme);
				$STH->setFetchMode(PDO::FETCH_ASSOC);

				//PAUSE - this is where we get results like echo $row['id']
				while($row = $STH->fetch()) {  
				//*
				The Problem here is that sometimes I want to query only one colum, or sometimes different column
				the $getme variable above only bring in a statement like 
				SELECT id FROM table1 ORDER BY updated_date DESC LIMIT 5
				after while $row = $STH->fetch(), i want to be able to get the array results and get keys so I can 
				do something like $row['id'] or if I'm getting 2 column results for a query then get those 2 keys
				and set them up like echo $row['id'] .'is for'. $row['firstsname']. this function will only be used to get
				different select statements.
				*//
				}
				
		}  //End of Try


		catch(PDOException $e) {  
		echo "Currently Having Problems, try again later."; 
		}  //End of Catch
		}

}

$query_one = 'SELECT id FROM table1 ORDER BY updated_date DESC LIMIT 5';
$query_two = 'SELECT id, firstname FROM table1 WHERE lastname=jordan ';
$test = new PDO_connection();
echo $test->caller($se);

?>

 

[dlukin]

Basic idea:

$row = $STH->fetch();
$column_names = array_keys( $row );
foreach( $column_names as $name ){
    $value = $row[ $name ] ;
    # you now have value and column name.
}

 

[misson]

The design looks potentially muddled. What is PDO_connection responsible for? What, exactly, do you want to do within the loop in do_query? Do you want to output anything within do_query? Make sure you keep DB access and display separate.

If that's a fair representation of the code, creating a new PDO object with each query is wasteful.

As for looping, you can iterate over both query results (since PDOStatement supports the Traversable and array keys & values (and object fields & values, while you're at it) using foreach.

// $things is a PDOStatement
foreach ($things as $item) {
  foreach ($item as $field => $value) {
    ...
  }
}

 

[djalam]

If that's a fair representation of the code, creating a new PDO object with each query is wasteful.

As for looping, you can iterate over both query results (since PDOStatement supports the Traversable and array keys & values (and object fields & values, while you're at it) using foreach.

// $things is a PDOStatement
foreach ($things as $item) {
  foreach ($item as $field => $value) {
    ...
  }
}

Yea what i wanted to do was create a seperate file with the class that does query and gives me the results, instead of building a new type of pdo object for differnt type of results. The Problem for me is I don't know how to make it universal
So if i threw a query at this class that says
Select count(*) from Table .... Which would throw out 13

Or I throw antoher query into this class like
Select firstname, lastname, addresss from Table ... which would throw out an array

i created an internal function to the class that would check to see if its an array

private function is_assoc_array($array) {
			return (is_array($array) && 0 !== count(array_diff_key($array, array_keys(array_keys($array)))));
		}

an if it was an array it would arrange the results each row.
by getting array keys then arranging the result according to the key value.

Thanks to dlukin and mission, i think i got the overview of what i should try to do next.

 

[misson]

Yea what i wanted to do was create a seperate file with the class that does query and gives me the results, instead of building a new type of pdo object for differnt type of results.

What does this class do that PDO and PDOStatement don't? What's PDO_connection's role in the overall design? It doesn't appear to offer enough to justify the added complexity.

So if i threw a query at this class that says
Select count(*) from Table .... Which would throw out 13

Or I throw antoher query into this class like
Select firstname, lastname, addresss from Table ... which would throw out an array

Do you mean that you want to return a scalar in the first instance and an array in the second? Functions that sometimes return arrays and other times return scalars usually result in messy code.

If you need to distinguish between single-value results, single-row results and multiple-row results, you should be using different classes or methods, assuming you need to use them at all. You could instead use PDOStatement::fetchColumn, a single PDOStatement::fetch and loop (foreach ($result as $row)) for multiple result rows. Either option should be used in the same layer where the statements are defined, in order to reduce coupling and increase cohesion.

 

[djalam]

Functions that sometimes return arrays and other times return scalars usually result in messy code.

That pretty much sums it up. The wiki articles were a good read (although it went over my head on the first read). I'm going to be correcting it soon, so thanx for taking the time to help.