MySQL read sql statements not working

[ugifarukh]

Hi Guys,

 

I am totally new to this forum and hoping to get some help regarding my open source php projects and also give back to this community through my knowledge.

 

 

Recently I have written a single page mysql connector script for PHP. The goal of my project is to make this mysql connection and query script easier to use, optimised performance and to have better security. At this moment the script is working fine with UPDATE, INSERT queries. But I tried to use it with SELECT query for reading sql table but it throws an exception which am not sure on how to fix. Please help me out with this problem.

 

 

The script:

<?php
defined('MySQL') OR exit(header('Direct Access Forbidden', true, 403));
require_once('config.php');
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT | MYSQLI_REPORT_INDEX);
class DBConnect {
	private $dbcon;
	private $paramquery;
	private $result;
	public function __construct() {
		try {
			$this->dbcon = mysqli_init();
			mysqli_real_connect($this->dbcon, DBHOST, DBUSER, DBPASS, DBNAME, 3306, '', MYSQLI_CLIENT_COMPRESS);
			$this->paramquery = $this->dbcon->stmt_init();
		} catch (mysqli_sql_exception $e) {
			exit('Database Connection Failed');
		}
	}
	public function dbquery($querysql, $querydata) {
		try {
			mysqli_ping($this->dbcon);
			$this->paramquery->prepare($querysql);
			array_walk($querydata, function(&$escval){$escval = mysqli_real_escape_string($this->dbcon, $escval);});
			call_user_func_array(array($this->paramquery, 'bind_param'), $querydata);
			$this->paramquery->execute();
		} catch (mysqli_sql_exception $e) {
			exit('Database Query Failed');
		}
		$this->result = $this->paramquery->get_result();
		if ($this->result) {
			$drs = $this->result->fetch_array();
			$this->result->free_result();
			return $drs;
		}
	}
	public function __destruct() {
		if ((null !== $this->dbcon) && (null !== $this->paramquery) && (null !== $this->result)) {
			$this->paramquery->close();
			$this->dbcon->close();
		}
		unset($this->result);
		unset($this->paramquery);
		unset($this->dbcon);
	}
}
?>

Original Link to the script: https://github.com/FSMySQL/PHP-FSMySQL/blob/master/connection.php

 

Github Repo: https://github.com/FSMySQL/PHP-FSMySQL

 

Also, please do check my github repo for this project and provide your feedback or suggestions on how to further improve this script.

 

 

 

Thanks in advance...

[Sepodati]

How about showing the code you ran that didn't work (how did you interact with this class) and the exact error that came up?

[ugifarukh]

Sorry guys, I have been trying to map my own code for the past few days to get the class worker code. But seems like I couldn't get the proper usage instruction for the code because I have written this code about an year ago. If anybody can help me figure out on how to use the code it would be really helpful. Meanwhile I am trying myself to get the code working for at least database connection and updates statement. Then maybe we can go debug the SELECT statement problem.

[ginerjm]

What is the 'exception' message?  And what is your query statement?  Those would be the ideal things to be looking at.  Your other concerns, whatever they are, come after solving this basic thing.

[mac_gyver]

 open source php projects

 

 

then your code shouldn't be doing anything that is not portable across different server configurations or it should detect the server configuration and provide alternate code that does work. specifically, and this may be why your SELECT query doesn't work, the ->get_result() method is both dependent on the php installation being built to use the mysqlnd driver and the mysqlnd driver being installed. since you cannot guarantee both of these conditions, your code either shouldn't use ->get_result() at all or it should detect if it is present before trying to use it.

 

if you are trying to make a general purpose prepared query method, you should extend the base mysqli (or even better the PDO) class. this will let you directly use all the methods and properties of the base class.

 

to make your code general purpose and reusable, you should not use defined constants to supply the connection parameters. you should supply the connection parameters as constructor call-time parameters, duplicating the constructor parameters for the base mysqli or PDO class you are using.

 

array_walk($querydata, function(&$escval){$escval = mysqli_real_escape_string($this->dbcon, $escval);}); - this is missing the point of using prepared queries. with a prepared query, you do NOT escape the data.

 

if you let php catch the exceptions, it will use it's error_reporting/display_errors/log_errors settings to determine what happens with the actual error information. for debugging purposes, a programmer using your class should be able to either display the actual error information or log the actual error information. by removing the try/catch code you have now and let php handle the exception, they can do this simply by altering php's display_errors and log_errors settings. your current code doesn't give them this option. the only time you should have a try/catch around database statements is if the application code needs to handle a specific error type, such as a duplicate index error that would mean that duplicate data was trying to be inserted/updated.

 

if you want to handle the exceptions yourself, all in one place, so that you don't have to write out the same code everywhere, set up your own exception handler.

 

lastly, the php PDO extension is much better designed and easier to use and provides the additional benefit of letting the same php PDO statements work with different database types. by doing this using the php msyqli extesion, you are tied to just the mysqli/ maria database type. doing it with the php PDO extension will make a more general purpose class that more people would be interested in using.

Edited October 10, 2017 by mac_gyver