PHP: Creating a MYSQL query inside a function

Question

I have a function which performs an SQL query, "queryValue" the actual query to perform is passed later.

Function queryCreate($queryValue){
    //DB connection variables
    $host = "";
    $user = "";
    $password = "";
    $database = "";

    //create the connection
    $conn = new mysqli($host, $user, $password, $database);

    if ($conn->connect_error) {
        die('DB Connection error: (' . $conn->connect_error . ') ' . $conn->connection_errorno);
    }
    $query = mysqli_query($conn,$queryValue) or die(mysqli_error($conn));

    if ($result = mysqli_fetch_array($query,MYSQLI_ASSOC)) {
        fputcsv($fh, array_keys($result));
        fputcsv($fh, $result);
        while ($result = mysqli_fetch_array($query,MYSQLI_ASSOC)) {     
            fputcsv($fh, $result);
        }
    }
    return $queryValue;
}

I'm then attempting to assign the query value in a separate if statement. Below:

if(isset($_POST["Submit"]) && ($_POST['Weight'] == 'Weight')) {
    $fh = csvCreate("Output Weight Null ".date('m-d-Y-His').".csv");
    $queryValue = queryCreate('SELECT * FROM  `table` WHERE WEIGHT = 0 OR weight IS NULL');
}

The problem I have is that the query does not appear to be being passed to the function. Could anyone suggest where I have gone wrong here? Many thanks.

The csvCreate function is shown here:

   function csvCreate($filename){
   header("Cache=Control: must-revalidate, post-check=0, pre-check=0");
   header('Content-Description: File Transfer');
   header("Content-type: text/csv");
   header("Content-Disposition: attachment; filename={$filename}");
   header("Expires: 0");
   header("Pragma: public");
   $fh = @fopen( 'php://output', 'w' );
   return $fh;
}

How do you know that the parameter is not passed to the function? Why do you return the query as a result of the function? — Shadow
– Shadow, Commented Nov 2, 2015 at 14:45
Perhaps that was an assumption. The CSV is appearing blank. I was attempting to pass 'queryValue' in to the Function as it is called in the IF statement. — Liam Fell
– Liam Fell, Commented Nov 2, 2015 at 14:49
Did you try to run the query directly from your favourite mysql manager application (e.g. phpmyadmin)? If yes, did you get any records back? Where does $fh variable (file handler for outputting data to csv) come from? If it is not declared as global within the function, php will assume $fh to be a local variable. — Shadow
– Shadow, Commented Nov 2, 2015 at 14:56
The query is returning values when used in phpMyAdmin. The function for the file stream is now included. This is working in that a file with the correct title is being created, it just isn't populated. Thanks — Liam Fell
– Liam Fell, Commented Nov 2, 2015 at 15:02
Are you running this with full error reporting and displaying, so you see every possible warning and notice? — Erik
– Erik, Commented Nov 2, 2015 at 15:03

Shadow · Accepted Answer · 2015-11-02 16:31:10Z

The problem is with the parameters of fputcsv() calls within queryCreate() function.

The file handler ($fh variable) is declared outside of queryCreate() function using the csvCreate() function:

$fh = csvCreate("Output Weight Null ".date('m-d-Y-His').".csv");

However,$fh is not passed as a parameter to queryCreate(), nor is $fh declared as a global variable, yet $fh variable is used to reference the file in all fputcsv() calls:

fputcsv($fh, array_keys($result));

In this case, $fh within queryCreate() will not refer to $fh variable where queryCreate() is called, but it will create a local $fh variable (empty at that), therefore the fputcsv() call will fail. The csv file is created in csvCreate(), this is independent from putting the values within the file.

The best solution would be to either pass $fh as a parameter to queryCreate(), or call csvCreate() from queryCreate(). In the latter case, the name of the dataset should be passed as a parameter.

UPDATE Let's see some code as well:

//declaration of queryCreate()
Function queryCreate($queryValue, $reportName){ //$reportName is the name of the report
    ...
    //create the csv file, put some parameter checks here as well
    $fh = csvCreate($reportName.date('m-d-Y-His').".csv");
    //and some error handling here
    ...
    //output the contents of the query to the csv file
    if ($result = mysqli_fetch_array($query,MYSQLI_ASSOC)) {
        fputcsv($fh, array_keys($result));
        fputcsv($fh, $result);
        while ($result = mysqli_fetch_array($query,MYSQLI_ASSOC)) {     
            fputcsv($fh, $result);
        }
    }       
    ... //should include closing of the csv file
} //end queryCreate()

...

//call the queryCreate()
if(isset($_POST["Submit"]) && ($_POST['Weight'] == 'Weight')) {
    $queryValue = queryCreate('SELECT * FROM  `table` WHERE WEIGHT = 0 OR weight IS NULL','Output Weight Null ');
}

Would you mind giving me an example of how you would do the latter solution. I'm a little lost. Cheers
Which part poses you a problem: adding another parameter to a function or moving a function call from one place to another?
Calling csvCreate in queryCreate(), I'm unsure how I can do that dynamically as it were, because I need to assign the fh-filename inside the if statement.

Collectives™ on Stack Overflow

PHP: Creating a MYSQL query inside a function

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related