Page 1 of 1

table handler function, multi-dimentional array?

Posted: Wed Jun 17, 2009 6:52 am
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?

Re: table handler function, multi-dimentional array?

Posted: Wed Jun 17, 2009 7:56 am
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....

Re: table handler function, multi-dimentional array?

Posted: Wed Jun 17, 2009 8:03 am
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?

Re: table handler function, multi-dimentional array?

Posted: Wed Jun 17, 2009 8:50 am
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.

Re: table handler function, multi-dimentional array?

Posted: Wed Jun 17, 2009 9:01 am
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

Re: table handler function, multi-dimentional array?

Posted: Wed Jun 17, 2009 9:28 am
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?

Re: table handler function, multi-dimentional array?

Posted: Wed Jun 17, 2009 9:38 am
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?

Re: table handler function, multi-dimentional array?

Posted: Wed Jun 17, 2009 10:29 am
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....

Re: table handler function, multi-dimentional array?

Posted: Wed Jun 17, 2009 11:05 am
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.

Re: table handler function, multi-dimentional array?

Posted: Wed Jun 17, 2009 11:20 am
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. :?

Re: table handler function, multi-dimentional array?

Posted: Wed Jun 17, 2009 11:47 am
by oliur
I got your point now, lets see if someone with more advanced knowledge can help.

Many thanks for your input.

Re: table handler function, multi-dimentional array?

Posted: Wed Jun 17, 2009 12:27 pm
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>';
}