Mysql functions class?

[ringworld]

I have been looking for a mysql class written in php to make my project more OO. I have found tons of scripts but they are all overly complicated, don't do everything I want, or are just confusing. I cant help it but I am kind of a noob.......lol. Does anyone know of a class or tutorial that will show me how to make a class that can do the following: connect/read/write/delete/handle multiple tables. i am really looking for a short an concise script because I really want to be able to understand it. Thanks for your help.

[unkwntech]

I would suggest just working out the code on your own, you will learn more that way, I will get you started.

 

<?php
class mysql
{
	function connect($host, $username, $password)
	 {
	$link = mysql_connect($host, $username, $password);
	if(!$link)
	 {
		return 0;
	 }
	else
	 {
		return $link;
	 }
 }
function insert($table, $record, $link)
 {
	$sql = "INSERT INTO " . $table . " VALUES (" . $record . ")";
	$result = mysql_query($sql, $link);
	if(!$result)
	 {
		return 0;
	 }
	else
	 {
		return 1;
	 }
 }
}
$mysql = new mysql;
$record = "'', '$username', '$password'";
$mysql->insert('users', $record, $mysql->connect('host', 'username', 'password'));
?>

 

This is ugly, and quick.

However from my experience this kind of thing is unnesicary, you almost always end up writing just as much if not more to do the same things.

[trq]

I wouldn't even bother, pdo is your friend.

[ringworld]

Why is it not better to use a class? I have tons of reading and writing going on in my project, its it only needed in large applications?

 

Also I don't know much about PDO, isn't it just meant to make your program more portable?

 

Also what does Mysqli do that mysql can't, Is Mysqli becoming a standard?

[unkwntech]

Usings a class to preform your sql functions is not going to save you much.

 

If you make a file say database.config.php for example that looks like this:

<?php
$hostname = 'localhost';
$username = 'username';
$password = 'password';
$databaseName = 'myDB';
$link = mysql_connect($hostname, $username, $password) or die("There was an error connecting to the database<br />" . mysql_error());
mysql_select_db($databaseName) or die("There was an error selecting the database<br />" . mysql_error());

Then when ever you need to do a query do this:

<?php
require('database.config.php');
$sql = "SELECT * FROM users";
$result = mysql_query($sql, $link);

You are acutaly going to save a line over using a class:

<?php
require('mysql.class.php');
$mysql = new mysql;
$sql = "SELECT * FROM users";
$mysql -> select($sql);

Not to mention you will have to write all the code for the class.

[next]

Building a class for database operations is good. I use PDO, but PDO is just a universal interaction with databases, so it's not going to give you a nice and easy read/write access, to get that you'll need to wrap it with static methods for instance:

<?php 		
class Database {
	private static $dbh;

	private function __construct() {}

	private static function connect() {
		if(!isset(self::$dbh)) {
			try {
				self::$dbh = new PDO(DB_DSN, DB_USER, DB_PASSWORD, array(ATTR_PERSISTENT => true));
			}
			catch(PDOException $e) {
				self::close();
				trigger_error($e->getMessage(), E_USER_ERROR);
			}
		}

	return self::$dbh;
	} 

	public static function close() {
		self::$dbh = null;
	}

	public static function execute($sql) {
		try {
			$handle = self::connect();
			$stmt = $handle->prepare($sql);
			$stmt->execute();
		}
		catch(PDOException $e) {
			self::close();
			trigger_error($e->getMessage(), E_USER_ERROR);
		}
	}

	public static function getAll($sql, $style = PDO::FETCH_ASSOC) {
		$result = null;

		try {
			$handle = self::connect();
			$stmt = $handle->prepare($sql);
			$stmt->execute();
			$result = $stmt->fetchAll($style);
		}
		catch(PDOException $e) {
			self::close();
			trigger_error($e->getMessage(), E_USER_ERROR);
		}

	return $result;
	}

	public static function getRow($sql, $style = PDO::FETCH_ASSOC) {
		$result = null;

		try {
			$handler = self::connect();
			$stmt = $handler->prepare($sql);
			$stmt->execute();
			$result = $stmt->fetch($style);
		}
		catch(PDOException $e) {
			self::close();
			trigger_error($e->getMessage(), E_USER_ERROR);
		}

	return $result;
	}
}
?>

so now when i need to get some data from DB i can simply write:

Database::getRow($sql)

and i'm done.

This is just an incomplete example though.

 

PDO has a bug under windows though, it might give you problems with query execution, so you might want to use MySQL under windows.

[ringworld]

Reply to: unkwntech

 

Yah Im starting to see what you mean.

 

Here is a snippet of code that reads a users data and also updates a users data. This is a lot of crap already and, I just added 2 more tables and tons more rows to my data base that all need to be accounted for in the same way. Do you have any suggestions to make this any smaller or cleaner.

 

	$result = mysql_query("SELECT * FROM users WHERE username = '$userhandle'");
if (!$result) {
    echo 'Unable to get data ' . mysql_error();
    exit;
}
$row = mysql_fetch_row($result);

//echo $row[0]; //userID
//echo $row[1]; //username
//echo $row[2]; //password
//echo $row[3]; //email
//echo $row[4]; //active
//echo $row[5]; //name
//echo $row[6]; //tagline
//echo $row[7]; //content1
//echo $row[8]; //content2
//echo $row[9]; //content3  
//echo $row[10]; //content4 
//echo $row[11]; //content5
//echo $row[12];  //text_color_title
//echo $row[13];  //text_color_main
//echo $row[14];  //content_border_color
//echo $row[15];  //content_border_type
//echo $row[16];  //content_border_size
//echo $row[17];  //content_background_color_title
//echo $row[18];  //content_background_color_main
//echo $row[19];  //background_color_body
//echo $row[20];  //background_image_body

}
if(isset($_POST['update']))
{
$name = $_POST['name'];
$tagline = $_POST['tagline'];
$email = $_POST['email'];
$content1 = $_POST['content1'];
$content2 = $_POST['content2'];
$content3 = $_POST['content3'];
$content4 = $_POST['content4'];
$content5 = $_POST['content5'];
$text_color_title = $_POST['text_color_title'];
$text_color_main = $_POST['text_color_main'];
$content_border_color = $_POST['content_border_color'];
$content_border_type = $_POST['content_border_type'];
$content_border_size = $_POST['content_border_size'];
$content_background_color_title = $_POST['content_background_color_title'];
$content_background_color_main = $_POST['content_background_color_main'];
$background_color_body = $_POST['background_color_body'];
$background_image_body = $_POST['background_image_body'];

$query = "UPDATE users SET email='$email', name='$name', tagline='$tagline', content1='$content1', content2='$content2', content3='$content3', content4='$content4', content5='$content5',text_color_title='$text_color_title', text_color_main='$text_color_main', content_border_color='$content_border_color', content_border_type='$content_border_type', content_border_size='$content_border_size', content_background_color_title='$content_background_color_title', content_background_color_main='$content_background_color_main', background_color_body='$background_color_body', background_image_body='$background_image_body'   WHERE username = '$userhandle'";
mysql_query($query) or die(mysql_error());  


echo "Your profile has been updated.";

[unkwntech]

Like I said just make a file that contains your config and a $link, include the file and you are already connected.  If the database you used in the config file is not the one you are working with then a quick mysql_select_db(); will fix that.  If your establishing a link to a different database you can either create a different config file say database2.config.php and include that or if its a different database, like something user entered, then you can just make the $link2 inline with the executing script.  Feel free to hit me up with any questions: unkwntech at unkwndesign.com