Two or more SQL queries in PHP function

Learning a graph-building PHP script I met a situation when PHP function performs only first query. Original code

class Student
{
    private $dbConn;
    public function __construct()
    {
        require_once __DIR__ . '/DataSource.php';
        $this->dbConn = new DataSource();
    }
    function getStudentMark()
    {
        $query = "SELECT student_id,student_name,marks FROM tbl_marks ORDER BY student_id";
        $result = $this->dbConn->select($query);
        return $result;
    }
}

ALL OK, everything works, I have a pie graph with correct data. But when I add one more query to update data before further processing

function getStudentMark()
{
    $test1 = "UPDATE tbl_marks SET marks = marks +1"; 
    $update = $this->dbConn->select($test1);
    $update->execute();
    
    $query = "SELECT student_id,student_name,marks FROM tbl_marks ORDER BY student_id";
    $result = $this->dbConn->select($query);
    return $result;
}

this case the function performs only first query adding +1 to all ‘marks’ – but instead of graph I have a white browser screen. When I comment in PHP code first query I have added – all OK again, I see a pie graph with new data. I was supposing I could request PHP function to perform two queries – first update something – and second collect updated data.

Is it an error in my syntax – or such a sequence of operations with MySQL queries does not work with PHP functions in principle ? Or we can suppose a flaw of a PHP script itself ? (what I doubt because everything works until I add one more query)

Thx for any opinion for better understanding or any idea what I should try,

In addition – next file catching data has the following code

require_once __DIR__ . '/../lib/Student.php';
$student = new Student();
$result = $student->getStudentMark();
$data = array();

etc – but I suppose function should return second query $result as above after first $update. May be I’m wrong but I try to understand where and why

As per comment – DataSource.php rules by dbConn and functions relating to MySQL queries – pls, see below

<?php
namespace Phppot;
class DataSource
{
    // PHP 7.1.0 visibility modifiers are allowed for class constants.
    // when using above 7.1.0, declare the below constants as private
    const HOST = 'localhost';
    const USERNAME = 'root';
    const PASSWORD = 'password';
    const DATABASENAME = 'dbname';
    private $conn;
    function __construct()
    {
        $this->conn = $this->getConnection();
    }

    /**
     * If connection object is needed use this method and get access to it.
     * Otherwise, use the below methods for insert / update / etc.
     *
     * @return mysqli
     */
    public function getConnection()
    {
        $conn = new mysqli(self::HOST, self::USERNAME, self::PASSWORD, self::DATABASENAME);
        if (mysqli_connect_errno()) {
            trigger_error("Problem with connecting to database.");
        }
        $conn->set_charset("utf8");
        return $conn;
    }

    /**
     * To get database results
     *
     * @param string $query
     * @param string $paramType
     * @param array $paramArray
     * @return array
     */
    public function select($query, $paramType = "", $paramArray = array())
    {
        $stmt = $this->conn->prepare($query);
        if (! empty($paramType) && ! empty($paramArray)) {
            $this->bindQueryParams($stmt, $paramType, $paramArray);
        }
        $stmt->execute();
        $result = $stmt->get_result();
        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $resultset[] = $row;
            }
        }

        if (! empty($resultset)) {
            return $resultset;
        }
    }
    /**
     * To insert
     *
     * @param string $query
     * @param string $paramType
     * @param array $paramArray
     * @return int
     */
    public function insert($query, $paramType, $paramArray)
    {
        $stmt = $this->conn->prepare($query);
        $this->bindQueryParams($stmt, $paramType, $paramArray);
        $stmt->execute();
        $insertId = $stmt->insert_id;
        return $insertId;
    }
    /**
     * To execute query
     *
     * @param string $query
     * @param string $paramType
     * @param array $paramArray
     */
    public function execute($query, $paramType = "", $paramArray = array())
    {
        $stmt = $this->conn->prepare($query);

        if (! empty($paramType) && ! empty($paramArray)) {
            $this->bindQueryParams($stmt, $paramType, $paramArray);
        }
        $stmt->execute();
    }
    /**
     * 1.
     * Prepares parameter binding
     * 2. Bind prameters to the sql statement
     *
     * @param string $stmt
     * @param string $paramType
     * @param array $paramArray
     */
    public function bindQueryParams($stmt, $paramType, $paramArray = array())
    {
        $paramValueReference[] = &$paramType;
        for ($i = 0; $i < count($paramArray); $i ++) {
            $paramValueReference[] = &$paramArray[$i];
        }
        call_user_func_array(array(
            $stmt,
            'bind_param'
        ), $paramValueReference);
    }
}

Answer

OK, so you already prepared and executed the query inside the badly named ->select() method.

As you are passing an UPDATE this time to the ->select() method, you will get a simple TRUE or FALSE back as thats all UPDATE and INSERT queries return.

So if you remove the $update->execute(); your error will likely disappear as well.

$test1 = "UPDATE tbl_marks SET marks = marks +1"; 
$update = $this->dbConn->select($test1);
//$update->execute();

Advertisement

Answer