1

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);
    }
}
7
  • What does dbConn->select() do. It either badly named or not doing a Prepare Commented Mar 2, 2022 at 14:55
  • A white screen is probably a 500/Internal Server Error. Double-check your server logs to see if there's any information there. But a select query function isn't usually for updates and inserts since they don't return a result set. Commented Mar 2, 2022 at 14:55
  • Apache log refers exactly to next file after Student.php - get-chart-data.php?chart_type=pie HTTP/1.1" 500 but does not explain why it does not get data from Student.php Commented Mar 2, 2022 at 15:08
  • That is the access log. You'll want the error log which provides more information. Commented Mar 2, 2022 at 15:09
  • you gave me a direction where to dig, thx... Commented Mar 2, 2022 at 15:30

1 Answer 1

1

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();
Sign up to request clarification or add additional context in comments.

1 Comment

Thx, solved ! so simple but I couldn't catch a logic of syntax :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.