table handler function, multi-dimentional 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
oliur
Forum Commoner
Posts: 29
Joined: Tue May 26, 2009 3:43 am

table handler function, multi-dimentional array?

Post by oliur »

A snippet of a function which retreives data from the database (sometimes more than one rows).

Code: Select all

 
$result = mysql_query($sql_query);
     if(!$result) {
            echo 'Could not run query: ' . mysql_error();
     }else{
            while($row = mysql_fetch_array($result)){
                //$data[] = $row;
                                                   $data[] = $row['name'];
            }
     }
    
     return $data;
 
Here, I am reading the data from the returned value ($userData = $data)

Code: Select all

foreach($userData as $key=>$value){
    echo $key."-----".$value."<br/>";
}
 
This $data[] = $row['name']; gives me only the keys and the names

this $data[] = $row; gives only the id number (first column in the table)

I am trying to figure out how to get all the columns for all the rows so i can display all the rows in the browser with all their values.

can anyone tell me if i should use multi dimentional array here?
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: table handler function, multi-dimentional array?

Post by Eric! »

What happens when you do this instead?

Code: Select all

while($row=mysql_fetch_array($result))
{
  foreach($row as $key=>$value){
    echo $key."-----".$value."<br/>";
  }
}
Isn't $data extracted as 'name' only and you're loosing other fields? Assuming I understand what you are trying to do....
oliur
Forum Commoner
Posts: 29
Joined: Tue May 26, 2009 3:43 am

Re: table handler function, multi-dimentional array?

Post by oliur »

Yes you got me right but when I am using $data[] = $row (like i mentioned in my post) I only get one column value and not all columns values.

How can i transfer all the values from $row to $data so I can later use it in my program?
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: table handler function, multi-dimentional array?

Post by Eric! »

How do you want to process this data? Row by row or do you need to deal with multiple rows at a time? You might be better off rethinking your database structure so php doesn't have to doo so much data manipulation.

If you just want to access multiple fields associatively:

Code: Select all

$i=0;
while ($row = mysql_fetch_assoc($result)) {
    $datafield1[$i] = $row["field1"]; // field1 is name in your snipet
    $datafield2[$i] = $row["field2"]; //field2 is whatever you called this in your db structure
    $datafield3[$i] = $row["field3"];//ditto for field3 and so on
    $i++;
}
This is not how I would do this in a program because it is ugly and unnecessary, but I think it illustrates the idea so that you can do what you need for you case. You could also leave the $row data associated and pass it around to your functions.
oliur
Forum Commoner
Posts: 29
Joined: Tue May 26, 2009 3:43 am

Re: table handler function, multi-dimentional array?

Post by oliur »

Okay The idea here is to do OO coding.

Code: Select all

 
<?php
 
class Model{
    
    function Model(){
        //echo 'Model class is initialising....';
    }   
 
    function connectDB(){
     $host = 'localhost';
     $user = 'root';
     $pass = '';
     $db = 'test';
     mysql_connect($host,$user,$pass) or print mysql_error();
     
     mysql_select_db($db) or print mysql_error(); 
    }
    
    function getData($table,$option,$whereClause){
     /**
     * DECLARE LOCAL VARIABLES
     **/
     $counter = 0;
     $data = array();
     /**
     * BUILD SQL QUERY
     **/
     
     $sql_query = "SELECT ";
     foreach($option as $value){
      
      if((count($option)-1)==$counter){
       $sql_query.=$value; 
      }else{
       $sql_query.=$value.','; 
      }
      $counter = $counter+1;
     }
     
     $sql_query.= " FROM ".$table;
     $sql_query.= " Where ".$whereClause;
     
    //echo $sql_query;
     /**
     * RUN SQL QUERY AND RETUN END RESULT
     **/
        
     $result = mysql_query($sql_query);
     if(!$result) {
            echo 'Could not run query: ' . mysql_error();
     }else{
            while($row = mysql_fetch_array($result)){
                $data[] = $row;
            }
     }
    
     return $data;
    }
 
}
?>
 
And a call to this class from the presentation layer

Code: Select all

 
 
require_once('Model.class.php');
 
$mInv = new Model();
 
$mInv->connectDB();
$userData = $mInv->getData("user",array('id','name'),"status = 'active'");
 
I think this is clean code which will save you time and hassle from using mysql database functions everytime you need to retrieve data from the database. So, I am trying to separate my database part from my presentation part (sort of like MVC concept - I am trying to build a mini framework for my small projects.)

So, now as you can see, the $data array is being returned to my presentation part but I am unable to find all the values inside $data. Or perhaps I am not even assigning all the values in the $data array in my function. This is where I am clueless.

P:S: I am learning codeigniter and I know they have cool stuff to solve this problem but I am trying to understand the inner bits.

Many Thanks
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: table handler function, multi-dimentional array?

Post by Eric! »

ok, I get it. This last snippet is different though. line 54 should return all the fields now, unlike the previous snippet. How are you extracting $data to know the fields aren't there?
oliur
Forum Commoner
Posts: 29
Joined: Tue May 26, 2009 3:43 am

Re: table handler function, multi-dimentional array?

Post by oliur »

I did a print_r() and I can see all the date in there

Code: Select all

 
echo '<pre>';
print_r($userData);
echo '</pre>';
 
I get this

Code: Select all

Array
(
    [0] => Array
        (
            [0] => 1
            [id] => 1
            [1] => Simon
            [name] => Simon
        )
 
    [1] => Array
        (
            [0] => 3
            [id] => 3
            [1] => Jamal
            [name] => Jamal
        )
 
    [2] => Array
        (
            [0] => 4
            [id] => 4
            [1] => Lucy
            [name] => Lucy
        )
 
)
 
 
It shows I have all the data now. (Altogether I have 3 active members in the db)

My understanding is $userData is an associative array now as it has both keys and values. So, I tried this

Code: Select all

 
foreach($userData as $key=>$user){
 
echo $key."----".$user.'</br>';
 
}
 
Not sure why does it print this and not actual values.

Code: Select all

0----Array
1----Array
2----Array
 
I guess I am missing something here. Can you shed some light?
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: table handler function, multi-dimentional array?

Post by Eric! »

Hmmm...is it because it is an array of an array and only the second inner array is associated? I usually only use foreach on one array element at a time. So I'm stabbing in the dark.

what about experimenting with a loop through userData:

Code: Select all

 
foreach($userData[$i] as $key=>$user){  
  echo $key."----".$user.'</br>'; 
}
Sorry, I'm not too good with these things....
oliur
Forum Commoner
Posts: 29
Joined: Tue May 26, 2009 3:43 am

Re: table handler function, multi-dimentional array?

Post by oliur »

This is just one array, and I am using foreach on one array only.
Thanks, I will try that and let you know.
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: table handler function, multi-dimentional array?

Post by Eric! »

Well, it is really one array of arrays, I've only done this with an array of elements. Most foreach examples extract data one row at a time(an array of elements), not by putting every row in an array and then extracting(an array of arrays). Sorry if that seems pretty anal but you said you wanted to learn about the guts so thats the difference. I'm sure some else here could explain this better. :?
oliur
Forum Commoner
Posts: 29
Joined: Tue May 26, 2009 3:43 am

Re: table handler function, multi-dimentional array?

Post by oliur »

I got your point now, lets see if someone with more advanced knowledge can help.

Many thanks for your input.
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: table handler function, multi-dimentional array?

Post by Eric! »

In liu of advanced knowledge, you have everything right there. You just need to toy with the operators to access the array.

I'm pretty sure looping $i through your array and then this line will extract it. If not try inserting a print_r for $userData[0] to compare to the full array to see if the operators are moving you into the array as expected.

foreach($userData[$i] as $key=>$user){
echo $key."----".$user.'</br>';
}
Post Reply