Database Class

[andersonmat]

Okay, recently I've seen a lot of people messing with PHP and really having difficulties. It's not that it's hard but some of it's meticulous. :P Anyways, in the past like 30 minutes I created this so you guys can use it. If you're looking for an efficient way to working with MySQL I highly suggest using a class to do so.

Here's my Database Class:

<?php
class database{
	private $mysqlConnection = NULL;
	
	public function __construct($dbhost, $dbuser, $dbpass, $dbname){
		$this->mysqlConnection = mysql_connect($dbhost, $dbuser, $dbpass) or die ("There was an error connecting to the MySQL Server");
		mysql_select_db($dbname, $this->mysqlConnection);
	}
	
	public function query($databaseQuery){
		return mysql_query($databaseQuery, $this->mysqlConnection);
	}
	
	public function sanatize($inputData){
		if(count($inputData) > 1){
			$counter = 0;
			foreach($inputData as $dataToBeCleaned){
				$inputData[$counter] = $this->sanatize($dataToBeCleaned);
				$counter++;
			}
		} else {
			$inputData = mysql_real_escape_string($inputData);
		}
		return $inputData;
	}
	
	public function disconnect(){
		mysql_close($this->mysqlConnection);
		return 1;
	}
}
?>

The class allows you to connect to a database and then subsequently run more queries to it. This would be an example of the use of it and the implementation.

	$databaseCommunication = new database('localhost', 'admin', 'lulz', 'forums');
	mysql_fetch_array($databaseCommunication->query("SELECT * FROM users"));

Assuming that you'd want user input into your database, you'd want to clean your data that you're about to insert into the database. I've included a function called sanitize that utilizes recursion to clean arrays as well as single variables. :D

To clean data, you'd do this:

$userInput = array($_REQUEST['age'], $_REQUEST['sex'], $_REQUEST['location']);
$userInput = $databaseCommunication->sanatize($userInput);
$databaseCommunication->query("INSERT INTO users (age, sex, location) VALUES('" . $userInput[0] . "', '" . $userInput[1] . "', '" . $userInput[2] . "')");

Then, when you're finished, close your connection:

$databaseCommunication->disconnect();

Enough of my elaborate methods.

Enjoy! :v:

[Wyzard]

[url=http://php.net/manual/en/book.pdo.php]PDO[/url]

[andersonmat]

[QUOTE=Wyzard;18307396][url=http://php.net/manual/en/book.pdo.php]PDO[/url][/QUOTE]

That works too. xD

[Wyzard]

BTW, you shouldn't use htmlspecialchars() on stuff you store in the database. Store the original text, and use htmlspecialchars() later after you pull the value [i]out[/i] of the database and want to display it in an HTML page.

[andersonmat]

[QUOTE=Wyzard;18307534]BTW, you shouldn't use htmlspecialchars() on stuff you store in the database. Store the original text, and use htmlspecialchars() later after you pull the value [i]out[/i] of the database and want to display it in an HTML page.[/QUOTE]

I suppose that would probably be a better means of doing that. :v:

[aixxe]

You think you could add something like this?

$mysqldb = new database('localhost', 'root', '', 'testdb');  
mysql_fetch_array($mysqldb->query("SELECT * FROM `users`"));
$mysqldb->query("INSERT INTO `users` (`username`, `password`) VALUES ('shadiku', MD5('hai2u'));");

$mysqldb->getQueryCount(); // 2

Would be useful for something to stick at the bottom of a page. Like this.

// Footer.
<?php echo($mysqldb->getQueryCount()); ?> queries executed.

Thanks.

[andersonmat]

I'll add that, I also added a new function for an insert. I'm revising a major portion of it. :)

    public function insertQuery($table, $data, $counter = 0){
        foreach($data as $keyName => $dataToInsert){
                if($counter != count($data) - 1){
                    $tableIndexes .= $keyName . ", ";
                    $valueInsertion .= "'" . $dataToInsert . "', ";
                } else {
                    $tableIndexes .= $keyName;
                    $valueInsertion .= "'" . $dataToInsert . "'";
                }
            $counter++;
        }
        return mysql_query("INSERT INTO $table($tableIndexes) VALUES($valueInsertion)");
    }

Insert data like so:

$data = array("columnName" => "Value");
$mysqlCommunication->insertQuery("testTable", $data);

[aixxe]

Hah, that's really good. Well done!

I'll be using this when I next make a PHP project.

[Blade]

Imo this class is pointless. It would make more sense if you made something like...

$sql = new SqlClass($info);
$sql->InsertInto('sometable', array( 'field' => 'value' ));

were it handles sanitizing/creating the queries.

O I actually had a neat idea.

$sql = new SqlClass($info)
$query = new InsertQuery('sometable');
$query->Ignore = true;
$query->Data = array('field' => 'value');
$sql->Execute($query);

[andersonmat]

[QUOTE=high;18423087]Imo this class is pointless. It would make more sense if you made something like...

$sql = new SqlClass($info);
$sql->InsertInto('sometable', array( 'field' => 'value' ));

were it handles sanitizing/creating the queries.

O I actually had a neat idea.

$sql = new SqlClass($info)
$query = new InsertQuery('sometable');
$query->Ignore = true;
$query->Data = array('field' => 'value');
$sql->Execute($query);[/QUOTE]

That looks like what I just did, didn't I?

[Blade]

Didn't see it until after I posted. Anyways, should remove "counter". Either join the array or improve the for loop.

[andersonmat]

[QUOTE=high;18426365]Didn't see it until after I posted. Anyways, should remove "counter". Either join the array or improve the for loop.[/QUOTE]

I'm going to improve the loops since I can use a for loop and then use array_keys().

[-[ Fizzadar ]-]

I need to rewrite some of my DB class, although I find it easier (when querying) to return an array (if there's data to be had), because all I end up doing is while( $thing = mysql_fetch_array( $results ) ): which sucks :P

foreach( dbQuery( 'query' ) as $test ): is far easier!

[editline]03:23AM[/editline]

Also, nice class :P You should add it to PHPClasses.org

[andersonmat]

This does it:

	public function insertQuery($table, $data){
		$columnNames = array_keys($data);
		for($x = 0; $x < count($data); $x++){
			if($x != count($data) - 1){
				$tableIndexes .= $columnNames[$x] . ", ";
				$valueInsertion .= "'" . $data[$columnNames[$x]] . "', ";
			} else {
				$tableIndexes .= $columnNames[$x];
				$valueInsertion .= "'" . $data[$columnNames[$x]] . "'";
			}
		}
		return mysql_query("INSERT INTO $table($tableIndexes) VALUES($valueInsertion)");
	}

[editline]10:23PM[/editline]

[QUOTE='-[ Fizzadar ]-;18431363']I need to rewrite some of my DB class, although I find it easier (when querying) to return an array (if there's data to be had), because all I end up doing is while( $thing = mysql_fetch_array( $results ) ): which sucks :P

foreach( dbQuery( 'query' ) as $test ): is far easier!

[editline]03:23AM[/editline]

Also, nice class :P You should add it to PHPClasses.org[/QUOTE]

I love my foreach especially with querying. :P

Also, thanks!

[Blade]

[QUOTE=andersonmat;18431370]This does it:

	public function insertQuery($table, $data){
		$columnNames = array_keys($data);
		for($x = 0; $x < count($data); $x++){
			if($x != count($data) - 1){
				$tableIndexes .= $columnNames[$x] . ", ";
				$valueInsertion .= "'" . $data[$columnNames[$x]] . "', ";
			} else {
				$tableIndexes .= $columnNames[$x];
				$valueInsertion .= "'" . $data[$columnNames[$x]] . "'";
			}
		}
		return mysql_query("INSERT INTO $table($tableIndexes) VALUES($valueInsertion)");
	}

[editline]10:23PM[/editline]

I love my foreach especially with querying. :P

Also, thanks![/QUOTE]

	public function insertQuery($table, $data){
		$columnNames = array_keys($data);
		if (count($data) > 0)
		{
			$tableIndexes .= $columnNames[0];
			$valueInsertion .= "'" . $data[$columnNames[0]] . "'";
			for($x = 1; $x < count($data); $x++){
				$tableIndexes .= ", " . $columnNames[$x];
				$valueInsertion .= ", '" . $data[$columnNames[$x]] . "'";
			}
		}
		return mysql_query("INSERT INTO $table($tableIndexes) VALUES($valueInsertion)");
	}

[Wyzard]

[QUOTE=high;18423087]Imo this class is pointless. It would make more sense if you made something like...

$sql = new SqlClass($info);
$sql->InsertInto('sometable', array( 'field' => 'value' ));

were it handles sanitizing/creating the queries.[/QUOTE]
Once again, [url=http://php.net/manual/en/book.pdo.php]PDO[/url].

Why do people keep writing these database abstraction layers when PHP [i]comes with one[/i] that's much safer and more powerful than anything you could write yourself?

[andersonmat]

[QUOTE=Wyzard;18432191]Once again, [url=http://php.net/manual/en/book.pdo.php]PDO[/url].

Why do people keep writing these database abstraction layers when PHP [i]comes with one[/i] that's much safer and more powerful than anything you could write yourself?[/QUOTE]

Because I can and I have the ability to?

Just because there are other options doesn't mean that I'm not allowed to create my own method to do it and learn in the process.