note 107260 added to ref.pdo-mysql
| From: | MrSmith | Date: | Wed, 25 Jan 2012 16:36:53 +0000 |
| Subject: | note 107260 added to ref.pdo-mysql | ||
| Groups: | php.notes | ||
| Request: | Send a blank email to php-notes+get-185741@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>
----
Server IP: 217.160.235.204
Probable Submitter: 91.10.66.41
----
Manual Page -- http://www.php.net/manual/en/ref.pdo-mysql.php
Edit -- https://master.php.net/note/edit/107260
Del: integrated -- https://master.php.net/note/delete/107260/integrated
Del: useless -- https://master.php.net/note/delete/107260/useless
Del: bad code -- https://master.php.net/note/delete/107260/bad+code
Del: spam -- https://master.php.net/note/delete/107260/spam
Del: non-english -- https://master.php.net/note/delete/107260/non-english
Del: in docs -- https://master.php.net/note/delete/107260/in+docs
Del: other reasons-- https://master.php.net/note/delete/107260
Reject -- https://master.php.net/note/reject/107260
Search -- https://master.php.net/manage/user-notes.php