hi
Some data first:
mysql> select * from booking;
+----+---------------------+--------+------------+---------+
| id | booking_date | places | motorcycle | user_id |
+----+---------------------+--------+------------+---------+
| 10 | 2013-02-19 08:50:14 | 2 | auto_50 | 1 |
| 14 | 2013-02-20 00:00:00 | 2 | auto_120 | 3 |
| 15 | 2013-02-28 00:00:00 | 3 | gear_125 | 1 |
| 16 | 2013-02-20 00:00:00 | 2 | auto_120 | 3 |
+----+---------------------+--------+------------+---------+
4 rows in set (0.00 sec)
I have a class below. The query() method performs insert queries fine. It's when I want to retreive results through a select query that I come unstuck.
<?php
final class BookingMySQLi
{
private $mysqli;
public function __construct($hostname, $username, $password, $database)
{
$this->mysqli = new mysqli($hostname, $username, $password, $database);
if ($this->mysqli->connect_error)
{
trigger_error('Error: Could not make a database link (' . $this->mysqli->connect_errno . ') ' . $this->mysqli->connect_error);
}
$this->mysqli->query("SET NAMES 'utf8'");
$this->mysqli->query("SET CHARACTER SET utf8");
$this->mysqli->query("SET CHARACTER_SET_CONNECTION=utf8");
$this->mysqli->query("SET SQL_MODE = ''");
}
public function query($sql)
{
$result = $this->mysqli->query($sql);
if ($this->mysqli->errno)
{
trigger_error('Error: ' . $this->mysqli->error . '<br />Error No: ' . $this->mysqli->errno . '<br />' . $sql);
exit();
}
if (is_resource($this->mysqli))
{
$i = 0;
$data = array();
while ($row = $result->fetch_object())
{
$data[$i] = $row;
var_dump( $data);
$i++;
}
$result->close();
$query = new stdClass();
$query->row = isset($data[0]) ? $data[0] : array();
$query->rows = $data;
$query->num_rows = $result->num_rows;
unset($data);
return $query;
} else {
return true;
}
} // query($sql)
public function escape($value) {
return $this->mysqli->real_escape_string($value);
}
public function countAffected() {
return $this->mysqli->affected_rows;
}
public function getLastId() {
return $this->mysqli->insert_id;
}
public function __destruct() {
$this->mysqli->close();
}
}
I mean, if I use:
require './inc/database.class.php';
$db = new BookingMySQLi('localhost','user','pass','bookings01');
$sql = "select * from booking";
$db->query($sql);
in my view file, then I would expect to see the results of
var_dump( $data);
displayed in my browser? All I see is white space. Usually white space is associated with a PHP critical error. Well I'm working locally with errors turned on and nothing is displayed so I'm at a loss.
I then tried a method as follows:
public function getRows($query)
{
if ($result = $this->mysqli->query($query))
{
/* fetch object array */
while ($obj = $result->fetch_object())
{
printf ("%s %s \n<br>", $obj->places, $obj->booking_date);
}
/* free result set */
$result->close();
}
}
and with a call to:
$db->getRows($sql);
in my view file, sure enough the browser displays:
2 2013-02-19 08:50:14
4 2013-02-20 00:00:00
3 2013-02-28 00:00:00
but I'm duplicating code, and I already have
$result->fetch_object()
functionality built in from my
public function query($sql)
method.
I'd like to utilise the
public function query($sql)
method to perform INSERT, UPDATE, SELECT & DELETE queries.
any ideas?
help
