Returning a array from a database connect

Codewalkers Forums Sponsor:

August 11th, 2009, 04:44 PM

LLX

Contributing User

Join Date: Apr 2007

Location: Glendale, CA, USA

Posts: 1,270

Time spent in forums: 2 Days 10 h 6 m 51 sec
Reputation Power: 4

MySQL 5 - Returning a array from a database connect

So I'm looking through my code and it occurs to me that i chiefly use the same 4 lines of code over and over again to connect to different tables in the same database and i got to thinking "is their a way to turn this into a PHP custom function and only call that?"

PHP Code:


		
			
 $querystring2='SELECT * FROM Issues';

    $IssueCheck=mysql_query($querystring2);

    $IssueArray = mysql_fetch_array($IssueCheck, MYSQL_ASSOC);

echo 'Issue: '.$IssueArray['Name'].'<BR>';

And basically all i really would change is the table name and the related variable names but if i made a custom function i could simply that. But would i be able to return a workable array though a function.

Something like

PHP Code:


		
			
function DBaseArray($querry){

    $querystring2=$querry;

    $DBCheck=mysql_query($querystring2);

    $DBArray = mysql_fetch_array($DBCheck, MYSQL_ASSOC);

return $DBArray;

}

then then i could just run

PHP Code:


		
			
 $IssueArray = DBaseArray('SELECT * FROM Issues')

echo 'Issue: '.$IssueArray['Name'].'<BR>';

echo 'Issue: '.$IssueArray['Category'].'<BR>';

echo 'Issue: '.$IssueArray['Foo'].'<BR>';

What's more would this custom function produce something i could loop though a while or for loop to get more then one entry?

__________________
29 years of creative writing
13 years of HTML
10 years of Photoshop
6 years of PHP/MySQL
And I never knew Photoshop could do HTML until 2004!
You learn something new every day.

August 11th, 2009, 05:44 PM

IAmALlama

Join Date: Apr 2007

Location: Seattle, WA

Posts: 1,937

Time spent in forums: 1 Week 5 Days 1 h 54 m 18 sec
Reputation Power: 4

there is nothing wrong with returning an array from a function. One of the query functions from my custom database class looks like this:

PHP Code:


		
			
public function querya($query, $key=null){

        $this->query($query);

        $this->out = array();

        if(empty($key)){

            while($row=mysql_fetch_assoc($this->result)){

                $this->out[] = $row;

            }

        } else {

            while($row=mysql_fetch_assoc($this->result)){

                if(isset($this->out[$row[$key]])){

                    foreach($row as $k=>$v){

                        if(is_array($this->out[$row[$key]][$k])){

                            if(!in_array($v, $this->out[$row[$key]][$k])){

                                $this->out[$row[$key]][$k][] = $v;

                            }

                        } elseif($this->out[$row[$key]][$k] != $v){

                            $this->out[$row[$key]][$k] = array($this->out[$row[$key]][$k],$v);

                        }

                    }

                } else {

                    $this->out[$row[$key]] = $row;

                }

            }

        }

        return($this->out);

    }

it will actually process the query and get a multi-dimensional array of the result and return that. then I only have to work with the array. It also can take a second argument that would be a column name and then makes that the key value for the 1st dimension if needed. I also have a query1 function that will just return the first row from a result and that figures out how many columns and if only 1, it return that value, if more than 1 then it return an array of that row. pretty handy. using this I could query for all users for example:

$users = $DB->querya("SELECT * FROM Users", 'username');

taking advantage of the 2nd argument I could then access each user like:

echo $users['someonesUserName']['username'];

a sloppy example, but it works.

also note that this is part of a complete class. so by itself this function won't work because of all the $this calls and self calls (the connection is static).

August 11th, 2009, 05:50 PM

LLX

Contributing User

Join Date: Apr 2007

Location: Glendale, CA, USA

Posts: 1,270

Time spent in forums: 2 Days 10 h 6 m 51 sec
Reputation Power: 4

well looks promising, a bit complicated but i'll mull over your sample i think it could streamline my code quite a bit once i muddle though all the specifics

my only concern is wouldi need to query the database multiple times to get data from the same table ie

$users = $DB->querya("SELECT * FROM Users where xxxx", 'username');
$users = $DB->querya("SELECT * FROM Users where xxxx", 'password');
$users = $DB->querya("SELECT * FROM Users where xxxx", 'address');

August 11th, 2009, 06:09 PM

IAmALlama

Join Date: Apr 2007

Location: Seattle, WA

Posts: 1,937

Time spent in forums: 1 Week 5 Days 1 h 54 m 18 sec
Reputation Power: 4

nope. if you leave off the second argument it would normally just be an array of each row. so if you had a table like:

Code:

table: users

id | username | firstName | lastName

1  | test        | test        | person
2  | user        | john        | smith
3  | name       | susan     | jones
4  | admin      | brian       | jackson

if I query for:

$users = $DB->querya("SELECT * FROM USERS");

my array ($users) would look like:

Code:

Array
(
    [0] => Array
        (
            [id] => 1
            [username] => test
            [firstName] => test
            [lastName] => person
        )
    [1] => Array
        (
            [id] => 1
            [username] => user
            [firstName] => john
            [lastName] => smith
        )
    [2] => Array
        (
            [id] => 1
            [username] => name
            [firstName] => susan
            [lastName] => jones
        )
    [3] => Array
        (
            [id] => 1
            [username] => admin
            [firstName] => brian
            [lastName] => jackson
        )
)

if I queried like so:

$users = $DB->querya("SELECT * FROM USERS", 'username');

then $users would look like:

Code:

Array
(
    [test] => Array
        (
            [id] => 1
            [username] => test
            [firstName] => test
            [lastName] => person
        )
    [user] => Array
        (
            [id] => 1
            [username] => user
            [firstName] => john
            [lastName] => smith
        )
    [name] => Array
        (
            [id] => 1
            [username] => name
            [firstName] => susan
            [lastName] => jones
        )
    [admin] => Array
        (
            [id] => 1
            [username] => admin
            [firstName] => brian
            [lastName] => jackson
        )
)

the difference is in how I can access that data after that. the first user in the first example can be accessed by $users[0], in the second one I have just switched the numeric array with the username as the key. so I can get a specific user like the one named admin by using $users['admin']. probably a bad example, but if there are not a lot of users (like 10) then I can just query for all the users and do something like if(isset($users['admin']) echo "user admin found.";

Last edited by IAmALlama : August 11th, 2009 at 06:13 PM.

August 11th, 2009, 06:58 PM

LLX

Contributing User

Join Date: Apr 2007

Location: Glendale, CA, USA

Posts: 1,270

Time spent in forums: 2 Days 10 h 6 m 51 sec
Reputation Power: 4

Quote:

Originally Posted by IAmALlama

nope. if you leave off the second argument it would normally just be an array of each row. so if you had a table like:

Code:

table: users

id | username | firstName | lastName

1  | test        | test        | person
2  | user        | john        | smith
3  | name       | susan     | jones
4  | admin      | brian       | jackson

if I query for:

$users = $DB->querya("SELECT * FROM USERS");

my array ($users) would look like:

Code:

Array
(
    [0] => Array
        (
            [id] => 1
            [username] => test
            [firstName] => test
            [lastName] => person
        )
    [1] => Array
        (
            [id] => 1
            [username] => user
            [firstName] => john
            [lastName] => smith
        )
    [2] => Array
        (
            [id] => 1
            [username] => name
            [firstName] => susan
            [lastName] => jones
        )
    [3] => Array
        (
            [id] => 1
            [username] => admin
            [firstName] => brian
            [lastName] => jackson
        )
)

if I queried like so:

$users = $DB->querya("SELECT * FROM USERS", 'username');

then $users would look like:

Code:

Array
(
    [test] => Array
        (
            [id] => 1
            [username] => test
            [firstName] => test
            [lastName] => person
        )
    [user] => Array
        (
            [id] => 1
            [username] => user
            [firstName] => john
            [lastName] => smith
        )
    [name] => Array
        (
            [id] => 1
            [username] => name
            [firstName] => susan
            [lastName] => jones
        )
    [admin] => Array
        (
            [id] => 1
            [username] => admin
            [firstName] => brian
            [lastName] => jackson
        )
)

i see, and thats a lot more functional then what i was doing. thanks that looks very promising

Edit: so looking at the function (i have very limited experiences with classes) im guess all the $this-> have to be changed. But honestly i'm not sure how shouldi be trying to take this int oa stand along function or just figure out classes instead?

PHP Code:


		
			
function querya($query, $key=null){

    $querystring2=$querry;

    $DBCheck=mysql_query($querystring2);

        if(empty($key)){

            while($row=mysql_fetch_assoc($DBCheck)){

                $newarr = $row;

            }

        } else {

            while($row=mysql_fetch_assoc($DBCheck)){

                if(isset($newarr[$row[$key]])){

                    foreach($row as $k=>$v){

                        if(is_array($newarr[$row[$key]][$k])){

                            if(!in_array($v, $newarr[$row[$key]][$k])){

                                $newarr[$row[$key]][$k][] = $v;

                            }

                        } elseif($newarr[$row[$key]][$k] != $v){

                            $newarr[$row[$key]][$k] = array($newarr[$row[$key]][$k],$v);

                        }

                    }

                } else {

                    $newarr[$row[$key]] = $row;

                }

            }

        }

        return($newarr);

    }

My horibly butched atempt to make it a stand alone function

Last edited by LLX : August 11th, 2009 at 07:22 PM.

August 11th, 2009, 07:59 PM

IAmALlama

Join Date: Apr 2007

Location: Seattle, WA

Posts: 1,937

Time spent in forums: 1 Week 5 Days 1 h 54 m 18 sec
Reputation Power: 4

for the most part you got it. all the calls to $this->somevar just means that it is referencing a function/variable outside of the current function but still inside the class container. I didn't test this, but this is what you should use. I also commented everything.

PHP Code:


		
			
<?php

function querya($query, $key=null){

    //perform the query.

    $result = mysql_query($query) OR DIE(mysql_error());

    //initialize our out variable.

    $out = array();

    

    //check if the key was passed or is empty.

    if(empty($key)){

        //loop through the result

        while($row=mysql_fetch_assoc($result)){

            //add each row to the output array.

            $out[] = $row;

        }

    } else {

        //loop through each row

        while($row=mysql_fetch_assoc($result)){

            //check if this key already exists in the output

            if(isset($out[$row[$key]])){

                //if it does exists the next few lines make that

                //into a multi dimensional array and just appends

                //each column. this allows you to specify a key

                //with non-unique values and it just makes that

                //another array. confusing, but if you try it you

                //will see.

                foreach($row as $k=>$v){

                    if(is_array($out[$row[$key]][$k])){

                        if(!in_array($v, $out[$row[$key]][$k])){

                            $out[$row[$key]][$k][] = $v;

                        }

                    } elseif($out[$row[$key]][$k] != $v){

                        $out[$row[$key]][$k] = array($out[$row[$key]][$k],$v);

                    }

                }

            } else {

                //if this key doesn't exist then add it.

                $out[$row[$key]] = $row;

            }

        }

    }

    //return our $out var.

    return($out);

}

?>

August 11th, 2009, 08:04 PM

LLX

Contributing User

Join Date: Apr 2007

Location: Glendale, CA, USA

Posts: 1,270

Time spent in forums: 2 Days 10 h 6 m 51 sec
Reputation Power: 4

thanks Llama, i'll toss in a coment credit to you somewhere even if only i can see it. This is a nice productivity booster

August 11th, 2009, 08:28 PM

LLX

Contributing User

Join Date: Apr 2007

Location: Glendale, CA, USA

Posts: 1,270

Time spent in forums: 2 Days 10 h 6 m 51 sec
Reputation Power: 4

a thought occured to me
So now i have my multi dimensional array but how to i dynamlly post data from it.

Is it as simple as

for ($x = 0; $users = $DB->querya("SELECT * FROM USERS", 'username'); $x++){$user[$x]['username'];}

?

August 11th, 2009, 08:44 PM

IAmALlama

Join Date: Apr 2007

Location: Seattle, WA

Posts: 1,937

Time spent in forums: 1 Week 5 Days 1 h 54 m 18 sec
Reputation Power: 4

I don't understand what you mean by "post data from it" but if you want to just loop through the data, use a foreach loop.

PHP Code:


		
			
<?php

$users = querya("SELECT * FROM USERS");

foreach($users as $user){

    echo $user['firstName']." ".$user['lastName']."<br />";

}

?>

also just know that the column names and keys from the resulting array are case sensitive. so in the second argument where you specify a key value, it has to be the same case as the column name in the database. or if you specify the columns in your query (select col1,col2...etc), the case has to match the column in the select.

#10

August 11th, 2009, 08:47 PM

LLX

Contributing User

Join Date: Apr 2007

Location: Glendale, CA, USA

Posts: 1,270

Time spent in forums: 2 Days 10 h 6 m 51 sec
Reputation Power: 4

yup that what i wanted, since they were be cases when i want a single roy pull and a entire table pulled from the querry. Thanks again.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: