return mysql result set in Foreach for my getAll function

Question

might be a basic one but i wrote a function that selects all from a dbtable in backend. But in front end, i want to use it with foreach to display results wherever i want.

Array(
[0] => Array
    (
        [stockCatID] => 1
        [stockCatName] => Copper
        [stockParentCat] => 0
    )

[1] => Array
    (
        [stockCatID] => 2
        [stockCatName] => Zinc
        [stockParentCat] => 0
    )
)

When I send resultset to my front-end page, this array shows up. So I can't basically play it with:

<?php
$r = getAll("stockcategories");
foreach($r as $k=>$v) {
    echo ("<p><strong>$k</strong>: $v</p>");   
}

With above result array iu outputs:

0 = Array1 = Array

Added: I don't want to echo from backend function.

So finally it is my function:

<?php
function getAll ($tableName,$orderBy="", $limit="") {
        $orderBy = $orderBy == "" ? $orderBy : (" ORDER BY =\"".$orderBy."\" "); 
        $limit = $limit == "" ? $limit : (" LIMIT =\"".$limit."\" ");
        $q  = mysql_query("SELECT * FROM $tableName $orderBy $limit");
        if (!$q) { die('Could not connect: ' . mysql_error());} else { $num=mysql_numrows($q); 
        if ($num != 0 ) { 
            while($r = mysql_fetch_assoc($q)) { 
                $rArray[] = $r;
            }
            mysql_free_result($q); 
            return $rArray; 
            } else { echo '<span class="notification n-error">No Record Found</span>'; return false; }        
    }
?>

Thanks for any help.

Dynamic SQL is a very bad idea for a security point of view. And you're also not sanitizing your output, so if there's anything user supplied in there you're wide open to XSS. — Johan, Nov 05 '11 at 13:20
Well to understand you better @Johan, while being inserted in mysql table, i **secure** inputs `function secure($string_to_clear) { $string_to_clear = @trim($string_to_clear); $string_to_clear = strip_tags($string_to_clear); if(get_magic_quotes_gpc()) { $string_to_clear = stripslashes($string_to_clear); } $string_to_clear = mysql_real_escape_string($string_to_clear); return $string_to_clear; }` with this. Still open to XSS? — , Nov 05 '11 at 13:30
All that escaping works against one another defeating the purpose. See: http://stackoverflow.com/questions/71328/what-are-the-best-practices-for-avoiding-xss-attacks-in-a-php-site — Johan, Nov 05 '11 at 14:30

score 0 · Answer 1 · answered Nov 05 '11 at 13:20

0

As you yourself point out, the array contains arrays as values. $v is an array with the keys stockCatID, stockCatName and stockParentCat. Try something like:

$r = getAll("stockcategories");
foreach($r as $record) {
    foreach($record as $k=>$v) {
        echo("<p><strong>$k</strong>: $v</p>");
    }
    echo("<hr/>");
}

answered Nov 05 '11 at 13:20

rid

61,078
31
152
193

thank you. It works in that way. But is there any way to leave only one for each in front end? – Nov 05 '11 at 13:22
@yahyaE, it depends on what you want to do. If you want to loop through all records, you need a `foreach`. Then if you want to loop through all the values of a record, you need a `foreach`. If you already know the names of the keys you're interested in, you can just `echo $v['stockCatID']` for example, instead of using a `foreach` to loop through all the keys. – rid Nov 05 '11 at 13:25

Michael Berkowski · Accepted Answer · 2011-11-05T13:29:03.520

0

$rArray is a 2 dimensional array but you are accessing it in only one dimension. You need nested foreach loops. The outer loop iterates over each row returned, and the inner iterates over the columns in each row:

foreach ($r as $row) {
  foreach($row as $k=>$v) {
    echo ("<p><strong>$k</strong>: " . htmlspecialchars($v) . "</p>");   
  }
}

Update a function to iterate over both dimensions:

function printResults($resultArray) {
    foreach ($resultArray as $row) {
      foreach($row as $k=>$v) {

        // Note sanitizing against XSS if this was user-input...
        echo ("<p><strong>$k</strong>: " . htmlspecialchars($v) . "</p>");   
      }
    } 

}

Call it as:

$r = getAll("stockcategories");
printResults($r);

edited Nov 05 '11 at 13:29

answered Nov 05 '11 at 13:20

Michael Berkowski

267,341
46
444
390

thank you. It works in that way. But is there any way to leave only one for each in front end? – Nov 05 '11 at 13:26
@yahyaE You will always need two loops to iterate over two dimensions. You can wrap it in a function to do one function call in the front end. I'm adding that above.. – Michael Berkowski Nov 05 '11 at 13:28
Thank you for reply, will follow this way. – Nov 05 '11 at 13:35

score 0 · Answer 3 · answered Nov 05 '11 at 13:22

0

<?php
$outer_array = getAll("stockcategories");
foreach($outer_array as $inner_array) {
    foreach($inner_array as $k => $v {
      echo "<p><strong>$k</strong>: $v</p>";
    }
}

you have an array of arrays, and your loop only traverses the outer array.

answered Nov 05 '11 at 13:22

Quasdunk

14,944
3
36
45

thank you. It works in that way. But is there any way to leave only one for each in front end? – Nov 05 '11 at 13:25

score 0 · Answer 4 · answered Nov 05 '11 at 13:26

0

Or you can do it in one loop like this:

foreach($r as $row) {
    echo ("<p><strong>$row['stockCatName']</strong>: $row['whatever']</p>");   
}

Depends what output you need, really.

answered Nov 05 '11 at 13:26

Shomz

37,421
4
57
85

return mysql result set in Foreach for my getAll function

4 Answers4