I'm hesitant to post this, as I'd really prefer to figure this out myself, but I don't think I will. I'm just trying to set a class for mysqli stuff, to make it as dynamic as possible, and yes I'm newer to OOP, but have been using PHP and Mysql as a hobby, and more heavily lately, for quite some time. I figured it was time to switch, but there just isn't that much on oop classes with mysqli and prepared statements with a possibility of multiple results (yes I've check documentation, guess I'm just not getting it or something). After quite a few hours, this is what I have. I'm not necessarily looking for a "quick fix". I really want to understand this and learn, so please explain thoroughly.
I'm using a dbconfig.php file to store my database info at root/config/dbconfig.php
in root/classes/mysqlicon.php
<?php
/*
* class MYSQLIDB
* @param Host
* @param User
* @param Password
* @param Name
*/
class MYSQLIDB
{
private $host; //MySQL Host
private $user; //MySQL User
private $pass; //MySQL Password
private $name; //MySQL Name
private $mysqli; //MySQLi Object
private $last_query; //Last Query Run
/*
* Class Constructor
* Creates a new MySQLi Object
*/
public function __construct()
{
include('./config/dbconfig.php');
$this->host = $db_host;
$this->user = $db_user;
$this->pass = $db_pass;
$this->name = $db_name;
$this->mysqli = new mysqli($this->host, $this->user, $this->pass, $this->name);
if ($mysqli->connect_errno) {
return "Failed to connect to MySQLi: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
}
private function __destruct()
{
$mysqli->close();
}
public function select($fields, $from, $where, $whereVal, $type, $orderByVal, $ASDESC, $limitVal)
{
if (is_int($whereVal))
{
$bindVal = 'i';
} else {
$bindVal = 's';
}
switch($type)
{
case "regular":
$queryPre = "SELECT " . $fields;
$querySuff = " WHERE " . $where . " = ?";
break;
case "orderByLimit":
$queryPre = "SELECT " . $fields;
$querySuff = " ORDER BY " . $orderByVal . " " . $ASDESC . " LIMIT " . $limitVal;
break;
}
//$query = "SELECT * FROM news ORDER BY id DESC LIMIT 4";
if ($stmt = $mysqli->prepare('$queryPre . " FROM " . $from . " " . $querySuff'))
{
if ($type == 'regular') {
$stmt->bind_param($bindVal, $whereVal);
}
$stmt->execute();
$stmt->bind_result($values);
$stmt->store_result();
$sr = new Statement_Result($stmt);
$stmt->fetch();
// call by this style printf("ID: %d\n", $sr->Get('id') );
//$stmt->fetch();
//$stmt->close();
//return $value;
printf("ID: %d\n", $sr->Get_Array() );
} else return null;
}
//use to call $db = new MYSQLI('localhost', 'root', '', 'blog');
/*
* Function Select
* @param fields
* @param from
* @param where
* @returns Query Result Set
function select($fields, $from, $where, $orderBy, $ASDESC, $limit, varNamesSent)
{
if ($orderBy == null &&)
$query = "SELECT " . $fields . " FROM `" . $from . "` WHERE " . $where;
$result = $this->mysqli->query($query) or exit("Error code ({$sql->errno}): {$sql->error}");
$this->last_query = $query;
return $result;
}
/*
* Function Insert
* @param into
* @param values
* @returns boolean
*/
public function insert($into, $values)
{
$query = "INSERT INTO " . $into . " VALUES(" . $values . ")";
$this->last_query = $query;
if($this->mysqli->query($query))
{
return true;
} else {
return false;
}
}
/*
* Function Delete
* @param from
* @param where
* @returns boolean
*/
public function delete($from, $where)
{
$query = "DELETE FROM " . $from . " WHERE " . $where;
$this->last_query = $query;
if($this->mysqli->query($query))
{
return true;
} else {
return false;
}
}
}
//Hand arrays for multiple returned items from database
class Statement_Result
{
private $_bindVarsArray = array();
private $_results = array();
public function __construct(&$stmt)
{
$meta = $stmt->result_metadata();
while ($columnName = $meta->fetch_field())
$this->_bindVarsArray[] = &$this->_results[$columnName->name];
call_user_func_array(array($stmt, 'bind_result'), $this->_bindVarsArray);
$meta->close();
}
public function Get_Array()
{
return $this->_results;
}
public function Get($column_name)
{
return $this->_results[$column_name];
}
}
?>
And just as a test, I'm trying to pull all the news in my db by:
<?php
require_once('classes/mysqlicon.php');
$testing = new MYSQLIDB;
$testing->select('*','news',null,null,'orderByLimit','id','DESC',4);
?>
But what I really want is stuff that can do the equivalent of this:
<?php
/*
require('config/dbconfig.php');
$query = "SELECT * FROM news ORDER BY id DESC LIMIT 4";
if ($stmt = $mysqli->prepare($query)) {
// execute statement
$stmt->execute();
// bind result variables
$stmt->bind_result($idn, $titlen, $categoryn, $descn, $postdaten, $authorn);
// fetch values
while ($stmt->fetch()) {*/
//echo 'id: '. $id .' title: '. $title;
echo "<table border='0'>";
$shortDescLengthn = strlen($descn);
if ($shortDescLengthn > 106) {
$sDCutn = 106 - $shortDescLengthn;
$shortDescn = substr($descn, 0, $sDCutn);
} else {
$shortDescn = $descn;
}
echo "<h1>$titlen</h1>";
echo "<tr><td>$shortDescn...</td></tr>";
echo '<tr><td><a href="javascript:void(0);" onclick="'
. 'readMore(' . $idn . ',' . htmlspecialchars(json_encode($titlen)) . ','
. htmlspecialchars(json_encode($categoryn)) . ','
. htmlspecialchars(json_encode($descn)) . ',' . htmlspecialchars(json_encode($postdaten)) . ','
. htmlspecialchars(json_encode($authorn)) . ')">Read More</a></td></tr>';
echo "<tr><td>Written by: $authorn</td></tr>";
echo '<tr><td><img src="images/hardcore-games-newsbar-border.png" width="468px" /></td></tr>';
}
echo "</table><br />";
/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();
?>
Again, please, please explain in detail. I'm a blockhead sometimes.
