penpen penpen - 4 months ago 35
SQL Question

Adding a column/variable to PDO FetchAll row

I'm new to using PDO and have got a little bit stuck- is there a way I can add a row to the fetchAll array returned?

I want to use a value in the row and create a new value from it. I hope my example is clear enough-

class.php
--------------------
class connect
{
//all PDO database connection functions here
}

class Display
{
function cars($colour)
{
$crud = new crud();
$crud->conn();
$sql = "SELECT * FROM cars WHERE colour=:colour";
$stmt = $crud->db->prepare($sql);
$stmt->bindParam(':colour', $colour);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

****WHAT I WANT TO DO (see edit)*****
foreach ($rows as $row)
{
ADD A ROW CALLED 'newprice' that equals ($row['oldprice'] + 300 / 2)
}


$this->results = $rows;
}
}


index.php
--------------------
//other html code...

$D = new Display;
$D->cars('1');
foreach($D->results as $row)
{
?>

<div class="car">
<h1>Car Name:</h2>
<?=($row['model']);?>
Old Price:
<?=$row['price'];?>
New Price:
<?=($row['newprice']);?> <- outputting the new row I created
</div>
}?>


Can this be done or is there a different more efficient way of doing this?

Sorry, I guess I wasn't very clear with my example! I want to do a lot more than just mathematically alter the value for the new column- I want to preg_split/run it through another class etc!

Best solution I've got so far is to create a new multidimensional array from the PDO provided array and then add/edit that array, like this:

$new=array();
$key_loop = 0;
$row_loop = 0;

foreach ( $rows as $val )
{
$keys = array_keys($val);
foreach ($keys as $key)
{
$new[$row_loop][$keys[$key_loop]] = $val[$keys[$key_loop]];
$new[$row_loop]['new_entry'] = 'this works';
$key_loop++;
}
$row_loop++;
$key_loop = 0;
}

Answer

What I've ended up doing is cycling through the rows and sticking them in a new multidimensional array with the extra values I need- might not be the most elegant way but it works for me:

$new  = array();
$array_loop = 0;

$sql = "select old_price, item_id from car";

...

$stmt->execute();
$rows =  $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($rows as $val)
{
    $new[$array_loop]['item_id']   = $val['item_id'];
    $new[$array_loop]['old_price'] = $val['old_price'];

    $newprice = $val['old_price'] + 300 / 2;
    $new[$array_loop]['new_price'] = $new_price;
    $array_loop++;
}
Comments