test param:1105
| From: | Mai Levesque | Date: | Wed, 25 Jan 2012 21:06:05 +0000 |
| Subject: | test param:1105 | ||
| Groups: | php.test | ||
| Request: | Send a blank email to php-test+get-921@lists.php.net to get a copy of this message | ||
MySQL Example class with functions to work with an mysql database:
Example MySQL table:
<code>
CREATE TABLE
table1 (
column1 varchar(50) NOT NULL,
column2 text NOT NULL,
PRIMARY KEY (column1)
) ENGINE=MyISAM;
</code>
Example MySQL class:
<code>
<?php
class mysqldb {
private $db;
public function __construct() {
// Connect to mysql server and select database
try {
$this->db = new PDO ("mysql:host=localhost;dbname=database","user","password");
} catch (PDOException $e) {
throw new Exception ('Connection failed: ' .$e->getMessage());
}
}
public function getData ($column1) {
// Get data from table1
$a = array();
if (!$stmt=$this->db->prepare('SELECT column2 FROM table1 WHERE column1 LIKE :column1'))
throw new Exception ('PDOStatement::prepare failed!');
if (!$stmt->bindParam(':column1',$column1))
throw new Exception ('PDOStatement::bindParam failed!');
if (!$stmt->execute())
throw new Exception ('PDOStatement::execute failed!');
// Save data to an array
while($table1=$stmt->fetchObject()) {
array_push($a, $table1->column2);
}
// Return result
return ($a);
}
public function insertData ($column1, $column2) {
// Insert data into table1
// Update data if primary key (column1) exists
if (!$stmt=$this->db->prepare('INSERT INTO table1 (column1, column2) VALUES (:column1,:column2)
ON DUPLICATE KEY UPDATE column2=:column2'))
throw new Exception ('PDOStatement::prepare failed!');
if (!$stmt->bindParam(':column1',$column1)||!$stmt->bindParam(':column2',$column2))
throw new Exception ('PDOStatement::bindParam failed!');
if(!$stmt->execute())
throw new Exception ('PDOStatement::execute failed!');
}
public function deleteData ($column1) {
// Delete data from table1
if (!$stmt=$this->db->prepare('DELETE FROM table1 WHERE column1=:column1'))
throw new Exception ('PDOStatement::prepare failed!');
if (!$stmt->bindParam(':column1',$column1))
throw new Exception ('PDOStatement::bindParam failed!');
if(!$stmt->execute())
throw new Exception ('PDOStatement::execute failed!');
}
}
?>
</code>
Example code to use this class:
<code>
<?php
//Create new instance
$mysql = new mysqldb();
//Insert data
$mysql->insertData("key1","value1");
$mysql->insertData("key2","value2");
$mysql->insertData("key3","value3");
//Get data
$output = array();
$output = $mysql->getData("key1");
reset($output);
while( key($output) !== NULL )
{
echo current($output);
next($output);
}
//Delete data
$mysql->deleteData("key2");
?>
</code>