Work with Query Returns as Array and not Looping through them?

[Marcus]

Can you work with the query returns as arrays? I only have luck with looping through the database returns. The arrays are a bit nested. This is an example from a textarea input where each line represents a new item and items can be added (or removed). The looping code is so much complicated, where a simple array_diff is much more elegant.

I would love to do this:

$editorItems = input[from Editor template]

$editorItemArray = implode("\n", $editorItems) // that works

$existingItems  = db->fetchAll()

$existingItemsArray = get_item_id_out_of_QueryReturn($existingItemsArray) // that does not work

$existingItemsInEditor = array_intersect($editorItemArray, $existingItemsArray)
$removeItems = array_diff ($existingItemsInEditor , $existingItemsArray) 
$addItems = array_diff($existingItemsInEditor , $editorItemArray)

foreach ($addItems as $item) 
{
    dw->write
}

foreach ($removeItems as $item) 
{
    dw->delete
}

Currently my code is much more complicated by looping through the results

$allItems  = db->fetchAll()
 
foreach ($allItems as $item)
{
  $existingItems[] = $item['item_id']}
}
 
$editorItems = input[from Editor template]
 
$editorItemArray = implode("\n", $editorItems) // that works
 
foreach ($editorItemArray as $item)
{
  if(!in_array($item, $allItems)
  {
    dw->write
    $addedItems[] = $item['item_id']
  }
}
 
$missingItems = array_diff( $existingItems, $addedItems )
 
foreach ($missingItems as $item)
{
    dw->delete
}

 

[Jake Bunce]

There are different fetch functions for the db object:

fetchAll = returns an array of rows
fetchRow = returns one row (which is an array)
fetchCol = returns an array of individual column values for multiple rows
fetchOne = fetches just one column for one row (not an array)

If your query returns only one row then use fetchRow. Then the result will be a straight array (no foreach required).

 

[Marcus]

I want to get access to the fetchAll or fetchKeyed array I get from mysql. Like this:

$pizza_ingredients = array(
  array('pizza_id'=>1,'ingredients_id'=>1),
  array('pizza_id'=>2,'ingredients_id'=>1),
  array('pizza_id'=>3,'ingredients_id'=>2)
);

How can I have direct access to the second row and the second item with PHP array functions and without looping? Thanks so much !

 

[Jake Bunce]

I you have the array index then you can index directly into that second array item without looping.

 

[Marcus]

I googled but did not find out how to do that $pizza_ingredients['pizza_id'=>2] will output the second row and the second item ?

When I enter my integer values, I implode("\n", $stringTextarea) and array_filter($stringTextarea , 'trim'). But do you know how to make integers out of them with array_filter? This does not work:

 array_filter($stringTextarea , 'intval')

PHP array(3) { [0]=> string(5) "0001" [1]=> string(5) "003" [2]=> string(5) "04323" }

 

[Marcus]

I you have the array index then you can index directly into that second array item without looping.

Can you give me a simple statement like $secondRowSecondCol= ..... ? That would be very great !!!

 

[Chris D]

I'm not sure I've understood fully as I've only really scanned the text -- short attention span

If you want to fetch an array of results from a MySQL query and then access the second row, second column you would fetch the results like this:

$results = $this->fetchAllKeyed = ('
	SELECT *
	FROM table
	WHERE something
', 'pizza_id');

The second row and second column (assuming from your example it is ingredients_id) would be:

$secondRowSecondCol = $results[2]['ingredients_id'];

Based on the $this->fetchAllKeyed query, the index of the array is now the pizza_id. If you wanted the 564th line second column it would be $results[564]['ingredients_id'].