custom function for mysqli queries

Question

I'm trying my hand at custom functions in PHP in order to streamline a lot of stuff I'm otherwise doing manually. I'm damn new to custom functions so I'm not sure the limitations. Right now I'm trying to get data with MySQLi using custom functions Here's the code, and then I'll explain the issue:

function connect_db($db = 'db_username') {
    iconv_set_encoding("internal_encoding", "UTF-8");
    mb_language('uni');
    mb_internal_encoding('UTF-8');
    @ $mysqli = new mysqli('host',$db,'password',$db);
    if(mysqli_connect_errno())
    {
        die('connection error');
    }

}

This one seems to be working fine. It's the next function I'm having more trouble with.

edit: Updated thanks to Jeremy1026's response

function do_query($db = 'default_db', $query) {
    connect_db();
    $result = $mysqli->query($query);
    if(!$result){
        trigger_error("data selection error");
    }

    while($row = $result->fetch_assoc()){
        $result_array[] = $row;
    }
    return $result_array;
}

My host forces database names and usernames as the same, so if the db name is 'bob' the username to access it will be 'bob' as well, so that's why $db shows up twice in the connection.

The problem I'm having is that these two functions are in functions.php and being called from another page. I want to be able to pull the results from the query in that other page based on the column name. But I also need to be able to do this with formatting, so then maybe the while() loop has to happen on that page and not in a function? I want this to be as universal as possible, regardless of the page or the data, so that I can use these two functions for all connections and all queries of the three databases I'm running for the site.

God I hope I'm being clear.

Big thanks in advance for any suggestions. I've googled this a bit but it's tough to find anything that's not using obsolescent mysql_ prefixes or anything that's actually returning the data in a way that I can use.

Update: I'm now getting the following error when accessing the page in question:

Fatal error: Call to a member function query() on a non-object in /functions.php`

with the line in question being $result = $mysqli->query($query);. I assume that's because it thinks $query is undefined, but shouldn't it be getting the definition from being called in the page? This is that page's code:

$query = "SELECT * FROM `table`";
$myArray = do_query($db, $query);
echo $myArray['column_name'];

WhoaItsAFactorial · Accepted Answer · 2012-11-26 14:11:28Z

3

In your 2nd function you aren't returning any data, so it is getting lost. You need to tell it what to return, see below:

function do_query($db = 'default_db', $query) {
    connect_db();
    $result = $mysqli->query($query);
    if(!$result){
        trigger_error("data selection error");
    }

    while($row = $result->fetch_assoc()){
        $result_array[] = $row;
    }
    return $result_array;
}

Then, when using the function you'll do something like:

$myArray = do_query($db, 'select column from table');

$myArray would then be populated with the results of your query.

edited Nov 26, 2012 at 14:11

answered Nov 26, 2012 at 13:17

WhoaItsAFactorial

3,5484 gold badges31 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Yhilan Over a year ago

I'm getting Fatal error: Cannot use [] for reading in functions.php on line 31. Is this possibly a conflict w/ my PHP version? I did some searching on this error and found that it may not be good for after 5.1. Or am I missing something else.

WhoaItsAFactorial Over a year ago

Sorry, my mistake. It should be return $result_array;. I have edited my answer.

Yhilan Over a year ago

updated the question with your code (thanks!) and the new error I'm getting.

WhoaItsAFactorial Over a year ago

Try executing your query outside of the function. That way you can confirm if its the function or your mysqli that is having the issue.

Yhilan Over a year ago

It's working fine until I rely on the function. If I do the connection outside the function it's fine. So what could be wrong with my function that's keeping it from going through?

Yhilan · Accepted Answer · 2012-11-27 00:57:25Z

This is a half-answer. The following single function works in place of the two.

function query_db($database, $new_query) {
    $sqli = new mysqli('host', $database, 'password', $database);
    $sqli->set_charset("utf8");

    global $result;
    if($result = $sqli->query($new_query)){
        return $result;
    }
}

By adding global $result I was able to access the results from the query, run from another page as

query_db("username","SELECT * FROM `column`");
while($row = $result->fetch_assoc()){
    print_r($row);
}

It's more streamlined than I have without functions, but it's still not idea. If I have the connection to the database in another function, it doesn't work. If I try to put the while loop in the combined function, it doesn't work. Better than nothing, I guess.

Collectives™ on Stack Overflow

custom function for mysqli queries

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related