Use of PHP function to make multiple MySQL queries

Question

I have a MySQL table named "orders". I want to make functions so I can easily access how many orders was added during Last: Hours, Day, Week, Month and Year.

If I don't require file dbconfig.php in each function than I get ERROR :(

So far the code below works exactly as I want but I think its possible to shorten it?

<?php

function count_db_last_hour(){
require("../dbconfig.php");
$sql = "SELECT * FROM orders WHERE checkdate >= date_sub(now(), INTERVAL 1 HOUR) ORDER BY checkdate DESC";
$result = $connect->query($sql);
$connect->close();
return $result->num_rows;
}

function count_db_last_week(){
require("../dbconfig.php");
$sql = "SELECT * FROM orders WHERE checkdate >= date_sub(now(), INTERVAL 1 WEEK) ORDER BY checkdate DESC";
$result = $connect->query($sql);
$connect->close();
return $result->num_rows;
}

function count_db_last_day(){
$sql = "SELECT * FROM orders WHERE checkdate >= date_sub(now(), INTERVAL 1 DAY) ORDER BY checkdate DESC";
require("../dbconfig.php");
$result = $connect->query($sql);
$connect->close();
return $result->num_rows;
}

function count_db_last_month(){
$sql = "SELECT * FROM orders WHERE checkdate >= date_sub(now(), INTERVAL 1 MONTH) ORDER BY checkdate DESC";
require("../dbconfig.php");
$result = $connect->query($sql);
$connect->close();
return $result->num_rows;
}

function count_db_last_year(){
$sql = "SELECT * FROM orders WHERE checkdate >= date_sub(now(), INTERVAL 1 YEAR) ORDER BY checkdate DESC";
require("../dbconfig.php");
$result = $connect->query($sql);
$connect->close();
return $result->num_rows;
}

echo count_db_last_hour() . '<br>' . count_db_last_day() . '<br>' . count_db_last_month() . '<br>' . count_db_last_year() ;

?>

Is it possible to shorten this code? Thank you for helping out.

UPDATED:

If I don't require dbconfig.php inside function I get ERROR:

PHP Notice: Undefined variable: connect in /home/test2.php on line 9
PHP Fatal error: Uncaught Error: Call to a member function query() on null in /home/test2.php:9 Stack trace:
#0 /home/test2.php(35): count_db_last_hour()
#1 {main} thrown in /home/test2.php on line 9

The dbconfig.php file has this code:

<?php
$servername = "localhost";
$username = "ordzxq_usar";
$password = "9ir3Grd_fXz";
$dbname = "store_orders";
$connect = new mysqli($servername, $username, $password, $dbname);
if ($connect->connect_error) {
    die("Connection failed: " . $connect->connect_error);
} 
?>

Try using include, then put it in the global scope

Carl Binalla
– Carl Binalla

2017-05-24 09:18:46 +00:00
Commented May 24, 2017 at 9:18 — Carl Binalla
– Carl Binalla, Commented May 24, 2017 at 9:18
Can you post your error ?

Saad Suri
– Saad Suri

2017-05-24 09:22:02 +00:00
Commented May 24, 2017 at 9:22 — Saad Suri
– Saad Suri, Commented May 24, 2017 at 9:22
Read about the scope of variables in PHP.

axiac
– axiac

2017-05-24 09:31:18 +00:00
Commented May 24, 2017 at 9:31 — axiac
– axiac, Commented May 24, 2017 at 9:31

u_mulder · Accepted Answer · 2017-05-24 09:28:50Z

2

Create one function and pass type to it:

function count_db_last_item($type) {
    $allowedTypes = ['HOUR', 'DAY',];   // and more
    // if type not allowed - return from function
    if (!in_array($type, $allowedTypes)) {
        return false;
    }

    require("../dbconfig.php");
    // add $type to query text
    $sql = "SELECT * FROM orders WHERE checkdate >= date_sub(now(), INTERVAL 1 $type) ORDER BY checkdate DESC";
    $result = $connect->query($sql);
    $connect->close();
    return $result->num_rows;
}

If I don't require file dbconfig.php in each function than I get ERROR :(

You get error because $connect variable is not seen in your function. Just pass it as argument:

require("../dbconfig.php");

function count_db_last_item($type, $connect) {
    $allowedTypes = ['HOUR', 'DAY',];   // and more
    // if type not allowed - return from function
    if (!in_array($type, $allowedTypes)) {
        return false;
    }

    // add $type to query text
    $sql = "SELECT * FROM orders WHERE checkdate >= date_sub(now(), INTERVAL 1 $type) ORDER BY checkdate DESC";
    $result = $connect->query($sql);
    // if you really need to close connection - close it
    $connect->close();
    return $result->num_rows;
}

edited May 24, 2017 at 9:28

answered May 24, 2017 at 9:21

u_mulder

54.8k5 gold badges54 silver badges72 bronze badges

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

5 Comments

Kaddath Over a year ago

that's almost what i was about to answer. i would have answered using require_once outside of the function and global $connect; in the function, but it really depends on what is in dbconfig.php

Nigel Ren Over a year ago

Also if the connect is made global, then closing it isn't probably the most friendly thing to do in case any other code wants to use it.

Kaddath Over a year ago

@cyborg if you use the first version, you have to call it with this format: echo count_db_last_item('HOUR') . '<br>' . count_db_last_item('DAY') . [... the rest]. With the second solution, you must add $connect as second parameter to each function call too

Cyborg Over a year ago

Thanks a Million! Its really A NICE code! Much shorter than what I had :D

Cyborg Over a year ago

@Kaddath Thank you, it works perfectly now, I used the 1. code. The $type selection is really awesome!

Arun Kumaresh · Accepted Answer · 2017-05-24 09:22:45Z

0

Create a one common function and just pass the $sql as the parameter dnt include dbconfig in each function instead of that pass the $connect as second parameter to the function

 function count($sql,$connect){       //$connect is your $conn variable
    $result = $connect->query($sql);
    $connect->close();
    return $result->num_rows;
    }
 $sql= "SELECT * FROM orders WHERE checkdate >= date_sub(now(), INTERVAL 1 HOUR) ORDER BY checkdate DESC";

$count_db_last_week =count($sql);

echo $count_db_las_week;

answered May 24, 2017 at 9:22

Arun Kumaresh

6,2966 gold badges36 silver badges50 bronze badges

Collectives™ on Stack Overflow

Use of PHP function to make multiple MySQL queries

UPDATED:

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

UPDATED:

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related