SQL Fetch Array using Foreach Array

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
tmac25
Forum Newbie
Posts: 21
Joined: Fri Dec 28, 2012 5:24 pm

SQL Fetch Array using Foreach Array

Post by tmac25 »

SQL Server 2008.

I'm trying to create a function that I can use to dynamically grab values from a database, what I'm having trouble understanding is how to use an array to assist me with grabbing named items from a SQL server within a while loop.

So, let's say I have something simple like:

Code: Select all

CREATE TABLE [dbo].[test](
	[firstname] [varchar](50),
	[lastname] [varchar](50)
)
Typically you'd hard code it as such:

Code: Select all

<?php
include('config.inc.php'); // db connections details

$items = array (
    "1"  => "firstname",
    "2" => "lastname");

$sql = "SELECT * FROM dbo.test";
$result = "sqlsrv_fetch_array($db, $sql);

while ($row = sqlsrv_fetch_array($result)) {
echo $row['firstname'];
}

?>
But what I would like to do is use an array to grab those instead, this is how I would imagine that it would look, but this is not working for me:

Code: Select all

<?php
include('config.inc.php'); // db connections details

$items = array (
    "1"  => "firstname",
    "2" => "lastname");

$sql = "SELECT * FROM dbo.test";
$result = "sqlsrv_fetch_array($db, $sql);

    while ($row = sqlsrv_fetch_array($result)) {
        foreach ($items as $array => $value) {
        echo $row[$value];
        }
    }

?>
EDIT: Found errors in my initial code, I'm able to pull data now, but it won't iterate through the different columns in the array.

Is this closer?

Code: Select all

 <?php  
foreach ($items as $array => $value) {
        $i = 0;
        while ($i < $row = sqlsrv_fetch_array($result)) {
            echo "<tr><td>" . $row[$value] . "</td></tr>";?><?php
        }
    }
?>
Any help is appreciated!

Thanks!
Last edited by tmac25 on Sat Apr 13, 2013 3:39 am, edited 1 time in total.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: SQL Fetch Array using Foreach Array

Post by social_experiment »

tmac25 wrote:I'm able to pull data now
If you have the data inside an array, why query the database again?

What does value does $items hold?

Code: Select all

 <?php var_dump($items); ?>
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
tmac25
Forum Newbie
Posts: 21
Joined: Fri Dec 28, 2012 5:24 pm

Re: SQL Fetch Array using Foreach Array

Post by tmac25 »

social_experiment wrote:
tmac25 wrote:I'm able to pull data now
If you have the data inside an array, why query the database again?

What does value does $items hold?

Code: Select all

 <?php var_dump($items); ?>
Thank you for your reply!

I'm having a formatting issue now not really an issue of querying the data per se, I can't get my table to display all the data from my query, only the first column.

So I have all the column names, in the array would be the data (keys to the array, ex: $row['firstname'] - this would pull the firstname column.)

To give a breakdown of what I'm trying to accomplish:

I'm doing a select, the select items (firstname & lastname) are in an array, I want to echo the sqlsrv_fetch_array of the $result to iterate through the $items array, and echo the data table with those array items.

As for your $items question, it's in the first code quote:

Code: Select all

<php
$items = array (
    "1"  => "firstname",
    "2" => "lastname");
?>
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: SQL Fetch Array using Foreach Array

Post by social_experiment »

tmac25 wrote:So I have all the column names
i would use a normal array, not an associative one

Code: Select all

<?php
  $colNames = array('firstname', 'lastname');

  $sql = "SELECT * FROM dbo.test";
  $result = sqlsrv_fetch_array($db, $sql);

   while ($row = sqlsrv_fetch_array($result)) {
       // loop through $colNames
       foreach ($colNames as $colName) {
             echo $row[$colName]; 
       }
   }
?>
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: SQL Fetch Array using Foreach Array

Post by McInfo »

Aside from concerns of SQL injection, why not "implode" the column names into the query? Then fetch an associative array. It would spare the database having to return data destined to be discarded. Of course, if the column names come from an untrusted source, they should be validated or filtered.
tmac25
Forum Newbie
Posts: 21
Joined: Fri Dec 28, 2012 5:24 pm

Re: SQL Fetch Array using Foreach Array

Post by tmac25 »

social_experiment wrote:
tmac25 wrote:So I have all the column names
i would use a normal array, not an associative one

Code: Select all

<?php
  $colNames = array('firstname', 'lastname');

  $sql = "SELECT * FROM dbo.test";
  $result = sqlsrv_fetch_array($db, $sql);

   while ($row = sqlsrv_fetch_array($result)) {
       // loop through $colNames
       foreach ($colNames as $colName) {
             echo $row[$colName]; 
       }
   }
?>
Thank you Social! You actually helped me solved another problem I was having as well!
McInfo wrote:Aside from concerns of SQL injection, why not "implode" the column names into the query? Then fetch an associative array. It would spare the database having to return data destined to be discarded. Of course, if the column names come from an untrusted source, they should be validated or filtered.
Hey McInfo!

Interesting idea that I hadn't thought of, kinda stumped how would you format that to work with an HTML table with multiple columns. The below syntax I'm trying doesn't appear to work with more than one column name, if I take out the 'lastname' in the array, it works great!

Code: Select all

<?php
  $items = array('firstname', 'lastname');

  $sql = "SELECT * FROM dbo.test";
  $result = sqlsrv_query($db, $sql);

   while ($row = sqlsrv_fetch_array($result)) {
       echo "<tr><td>";
       echo $row[implode($items)];
       echo "</td></tr>";
       }
   }
?>
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: SQL Fetch Array using Foreach Array

Post by McInfo »

I meant actually put the column names into the query, like this:

Code: Select all

$columns = array('firstname', 'lastname');
$sql = "SELECT ".implode(', ', $columns)." FROM dbo.test";
// "SELECT firstname, lastname FROM dbo.test"
Then the data returned is only what you need instead of every column in the table.

One thing to be careful of is that, if you don't validate the column names, someone might then be able to do something like the following, which exposes data that should not be visible to them.

Code: Select all

$columns = array('username, password FROM dbo.users; --');
$sql = "SELECT ".implode(', ', $columns)." FROM dbo.test";
// "SELECT username, password FROM dbo.users; --FROM dbo.test"
How to correctly validate a column name depends on the database in question; however, column names are generally a mix of letters, digits, and underscore.

Another problem with this approach is that a database error will occur if any of the injected columns do not exist in the table.

When it comes to fetching results, configure the fetch function to return an associative array.

Code: Select all

$rows = array();
while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
    $rows[] = $row;
}
print_r($rows);
// Array(
//     0 => Array(
//         'firstname' => 'Amy',
//         'lastname' => 'Adams'
//     ),
//     1 => Array(
//         'firstname' => 'Betty',
//         'lastname' => 'Boop'
//     )
// )
Then, assuming the columns exist in the database table:

Code: Select all

print_r($columns);
// Array(
//     0 => 'firstname',
//     1 => 'lastname'
// )
echo '<table><tbody>';
foreach ($rows as $row) {
    echo '<tr>';
    foreach ($columns as $col) {
        echo '<td>', htmlspecialchars($row[$col], ENT_NOQUOTES), '</td>';
    }
    echo '</tr>';
}
echo '</tbody></table>';
// <table><tbody><tr><td>Amy</td><td>Adams</td></tr><tr><td>Betty</td><td>Boop</td></tr></tbody></table>
Post Reply