Function syntax help

[epseix1]

I am storing the following MySQL prep statement as a function and calling it before its required, however I've either misunderstood the point of a function or I'm using the wrong syntax somewhere. Can somebody advise?

 

function addressArea(){
$addressAreaStmt = $mysqli->prepare("SELECT addressArea FROM events WHERE strId = ? AND dateTime < CURDATE() AND areaCountry = '$areaCountry' GROUP BY addressArea ORDER BY addressArea");
}
addressArea();
... etc.

Now I have considered trying to define the function as purely the statement query, but this also fails...

 

function areaCountry(){
$query1 = 'SELECT areaCountry FROM events WHERE strId = ? AND dateTime < CURDATE() GROUP BY areaCountry ORDER BY areaCountry';
}
$stmt = $mysqli->prepare($query1);

Any help? Cheers!

[mac_gyver]

functions have their own local program scope (in most programming languages, javascript didn't follow this convention, making for messier code). all code and variables inside a function only exist inside of that function. this causes a function to behave as if it is an autonomous "black box". it doesn't directly use or modify anything outside of the function's scope. you input the data it needs at one end, the function uses any code and local variables it needs to preform its task, and the result comes out the other end (the function call is replaced by the data the function returns.)

 

in your first example, the $mysqli variable inside your function isn't the $mysqli variable in your main code. you need to pass the $mysqli variable, along to any of the data you are putting into the query, as parameters to the function when you call it.

$mysqli = new mysqli('');

function addressArea($mysqli, other parameters as needed){
$addressAreaStmt = $mysqli->prepare("SELECT addressArea FROM events WHERE strId = ? AND dateTime < CURDATE() AND areaCountry = '$areaCountry' GROUP BY addressArea ORDER BY addressArea");
}
addressArea($mysqli, other parameters as needed);

in your second example, the $query1 variable only exists inside the function. to use it outside the function you would need to return the value from the function (doesn't have to be an actual variable inside the function). the function call is replaced by the value it returned. you can either assign the returned value to a variable where it was called at or directly use the returned value.

function areaCountry(){
return  'SELECT areaCountry FROM events WHERE strId = ? AND dateTime < CURDATE() GROUP BY areaCountry ORDER BY areaCountry';
}
$stmt = $mysqli->prepare(areaCountry());

a general word about functions. they should be low-level building-blocks that do something useful. a function that executes any arbitrary SELECT query (where you pass the sql query statement into the function), uses a prepared statement, performs error checking, and returns the result set or a false value if no result set would be a building block that you can call any place you need to run a SELECT query (any query that returns a result set.) another function to do the same for an INSERT/UPDATE query (doesn't return a result set) would be useful.

 

because using mysqli (or PDO) to access a database needs an instance of the database class, it is actually better to use a class (instead of functions) to do what you are trying, since the instance of the mysqli/pdo database class can be passed into a class once and used in any of the class methods without needing to be passed as a call time parameter to each method.

[epseix1]

Thank you!

 

Are there any useful links you recommend regarding explaining how to use classes as opposed to functions as you advise for what I'm trying to do?

[mac_gyver]

assuming that your class methods (functions) are closely related so that they belong in the one class, you would use dependency injection to get the instance of the database class into your class.

class some_class {

    private $mysqli; // instance of the injected database class
    
    public function __construct($mysqli){
        $this->mysqli = $mysqli; // store the instance of the injected database class
    }
    
    // method to run a prepared query that returns a result set
    public function prquery($query,$data){
    
        $stmt = $this->mysqli->prepare($query); // use the instance of the database class

        ...

        return $result; // return result set or false
    }
}

$mysqli = new mysqli(''); // instance of the database class
$a = new some_class($mysqli); // instance of your class, injecting the instance of the db class in the constructor

// form and run a select query
$query = 'SELECT areaCountry FROM events WHERE strId = ? AND dateTime < CURDATE() GROUP BY areaCountry ORDER BY areaCountry';
$result = $a->prquery($query,array($id));