A super simple MySQL class - fail!

blizeH

Hi guys,

I'm trying to create a super simple MySQL class, but having never worked with a MySQL class before (or any class for that matter) I'm having a few issues. More than a few issues to be honest:

	class mysql_db {
		var $conn;
		var $select;
		var $query;		

	function connect($server, $username, $password, $database) {
		$conn = mysql_connect($server, $username, $password);
		$select = mysql_select_db($database, $conn); 
	}

	function query($query) {
		return mysql_query($query);
	}

	function fetch($query) {
		return mysql_fetch_array($query);
	}

	function end() {
		mysql_free_result($conn);
	}
}

So there's my class, and trying to use it like this doesn't work at all :/

[code]$mysql = new mysql_db();
$mysql->connect("localhost", username, password, database);

$test = $mysql->fetch('SELECT * FROM News');

print_r($test);[/code]

Any suggestions please guys? Not entirely sure what's wrong, and for some reason there aren't many (any!?) super basic classes for MySQL that'll just let me select, insert, update and delete using a query.

Cheers

NogDog

See comments:

<?php
class mysql_db
{
   var $conn;
   var $select;

   function connect($server, $username, $password, $database)
   {
      // save results as class properties:
      $this->conn = mysql_connect($server, $username, $password);
      $this->select = mysql_select_db($database, $conn);
   }
   function query($query)
   {
      return mysql_query($query, $this->conn);
   }
   function fetch($query)
   {
      // use the query method to execute the SQL:
      $result = $this->query($query);
      if($result)
      {
         // populate a 2-D array of results:
         $data = array();
         // I would use fetch_assoc here:
         while($row = mysql_fetch_assoc($query, $this->conn)) 
         {
            $data[] = $row;
         }
         return $data;
      }
      error_log(mysql_error()."\n$query");
      return false;
   }
   function end()
   {
      mysql_free_result($conn);
   }
}

halojoy

Like NogDog
I think it is very good to have some error trapping.
Preferably with an echo of the SQL Query string!

If you like to experiment with your own mysql Class
then I really recommend the
PHP MySQLi Class http://www.php.net/manual/en/book.mysqli.php

If you do like me in the mysqli_simple Class I post here
you will have also ALL the parent MySQLi functions in YOUR class.
If you want to use them: $this->mysqli-function-name()

Another advantage using EXTENDS is that you need no $conn object.
As your class is MySQLi and you use $this object
Read more how to EXTENDS parent class into your child classes:
http://php.net/manual/en/keyword.extends.php

mysqli.simple.class.php:

<?php    // by halojoy February 2010

if(!class_exists('MySQLi')) exit('MySQLi Class not supported');

class mysqli_simple extends MySQLi{

private $host = 'localhost';
private $user = 'xxxxxxxx';
private $pass = 'xxxxxxxx';
private $mydb = 'my_data';

function __construct(){

	$this->init();
	@$this->real_connect($this->host,$this->user,$this->pass,$this->mydb);
	if($this->connect_error) exit('DB ConnectError: '.$this->connect_error);
 }

public function sqlfetch($sql){

	$success = $this->real_query($sql);
	if(!$success){
		echo('<br />SQLerror: '.$sql.'<br />'.$this->error.'<br />');
		return;
	}
	$result = $this->store_result();
	$rows = $result->fetch_all(MYSQLI_ASSOC);
	$result->free();
	return $rows;
}

}// END CLASS

<?php
include 'mysqli.simple.class.php';
$db = new mysqli_simple;

$sql = "SELECT * FROM users WHERE name='Admin'";
$rows = $db->sqlfetch($sql);

echo '<br />'.$sql.'<br />'
		.count($rows).' rows in result array<br />';

/* OUTPUT:

SELECT * FROM users WHERE name='Admin'
1 rows in result array
*/

?>

<?php
include 'mysqli.simple.class.php';
$db = new mysqli_simple;

$sql = "SELT * FROM users";
$rows = $db->sqlfetch($sql);

echo '<br />'.$sql.'<br />'
		.count($rows).' rows in result array<br />';

/* OUTPUT:

SQLerror: SELT * FROM users
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELT * FROM users' at line 1

SELT * FROM users
0 rows in result array
*/

?>

<?php
include 'mysqli.simple.class.php';
$db = new mysqli_simple;

$sql = "SELECT * FROM bands";
$rows = $db->sqlfetch($sql);

echo '<br />'.$sql.'<br />'
		.count($rows).' rows in result array<br />';

/* OUTPUT:

SELECT * FROM bands
9 rows in result array
*/

?>

In this last case $rows is the following array
with 9 records returned:

blizeH

Wow! Thank you so much guys! Two really awesome classes there. Huge thanks to Nog for taking the time to fix, improve and comment my code - it's very interesting to see where I went wrong too (many times!) and some great new ideas in there like the error checking.

Also halojoy too, very awesome thank you - I've never used the mysqli stuff before (or even heard of it) but it looks pretty cool, and I really appreciate the example usages and implementations too! Will take a look through the documentation to look at how to run other queries too such as update, insert and delete; hopefully I can put something together, would be a good learning curve and I'm going to have a good go at using both classes too!

Cheers again guys

blizeH

Eek! Tried the first but I think my code is probably wrong, that I'm using to call the class:

$mysql = new mysql_db();
$mysql->connect("localhost","user","pass", "db");

$test = $mysql->fetch('SELECT * FROM News');

print_r($test);

Also, unfortunately it seems MySQLi Class isn't supported! ;_;

MySQLi Class not supported

phpinfo() says I'm running PHP Version 4.3.9, which according to the MySQLi manual should be supported though, very strange...

laserlight

blizeH wrote:
phpinfo() says I'm running PHP Version 4.3.9, which according to the MySQLi manual should be supported though, very strange...

I think that you are mistaken. Read the table at the bottom of the PHP manual's overview of MySQLi. It is stated that the MySQLi extension was introduced with PHP 5, which matches what I remember.

blizeH

Oops, sorry I was reading from this page - http://www.php.net/manual/en/mysqli.requirements.php

Either way it says it's not compatible, so it seems the overview you just posted is correct, thanks 🙂

Having a look at Nog's code now and trying to work out how to actually implement it, I think the database, server etc I entered is fine because my regular code (ie no class) is working 🙁

blizeH

Also, is there any reason why I can connect to the database when I upload the file to the host, but not when it's running on a local server? I'm not using localhost in the php any more by the way, I changed it to the IP address. 🙁

blizeH

Hmm, a little bit of progress, I've made a couple of changes anyhow:

	class mysql_db {
		var $conn;
		var $select;

	function connect($server, $username, $password, $database) {
		$this->conn = mysql_connect($server, $username, $password);
		$this->select = mysql_select_db($database, [B]$this->conn[/B]); 
	}

	function query($query) {
		return mysql_query($query, $this->conn);
	}

	function fetch($query) {
		// use the query method to execute the SQL:
		$result = $this->query($query);

		if($result) {
			// populate a 2-D array of results:
			$data = array();
			// I would use fetch_assoc here:
			while($row = mysql_fetch_assoc($query, $this->conn)) {
				$data[] = $row;
			}
			return $data;
		}
		[B]echo mysql_error()."\n$query";[/B]
		return false;
	}

	function end() {
		mysql_free_result($conn);
	}
}

... but now, with the simple command like this:

It's returning an array but a blank one 🙁 There is definitely a database though, and content in there too...

$test = $mysql->fetch('SELECT * FROM News');

print_r($test);

Not getting any SQL errors either, a bit confused by it all really 🙁

blizeH

How harmful would it be just to completely bypass using a class and use regular PHP stuff? I've been on this for so many hours now I'm starting to lose the will to live 🙁

blizeH

Phew! Working now 🙂 Cobbled together an old MsSQL class and got it working, traced everything back and eventually replaced the 'mysql_fetch_assoc' command variable from $query, $this->conn with just result, so it's working a treat now!

Huge thanks once again Nog, really appreciate it! Seems to be working great now 🙂