Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?php
- $host = 'localhost';
- $dbname = 'wits';
- $username = 'root';
- $password='';
- try{
- $con = new pdo("mysql:host=$host; dbname=$dbname", $username, $password);
- $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
- /*CREATE DATABASE*/
- $query = "CREATE DATABASE IF NOT EXISTS tochukwu_base ";
- $con->exec($query); //When ever no results is returned you can always use exec()
- /*CREATE TABLE*/
- $query = "CREATE TABLE IF NOT EXISTS people(
- id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
- name VARCHAR(20) NOT NULL,
- email VARCHAR(25) NOT NULL UNIQUE,
- sex enum('male','female') DEFAULT 'male' NOT NULL,
- birthdate DATE
- ) ";
- $con->exec($query);
- /*INSERT RECORD */
- $name = 'Tochukwu';
- $email = 'tochi@me.com';
- $sex = 'male';
- $date = '1960-11-24';
- $query = "INSERT INTO people(name, email, sex, birthdate) VALUES('$name', '$email', '$sex', '$date')";
- $con->exec($query);
- /* INSERT RECORD USING PREPARED STATEMENT */
- $name = 'Uche';
- $email = 'star@yahoo.com';
- $sex = 'male';
- $date = '1986-10-28';
- $query = $con->prepare( "INSERT INTO people(name, email, sex, birthdate) VALUES(:name, :email, :sex, :date)" );
- $query->execute([
- ':name'=>$name,
- ':email'=>$email,
- ':sex'=>$sex,
- ':date'=>$date
- ]);
- /*GET LAST ID*/
- $query = "INSERT INTO people(name, email, sex, birthdate) VALUES('Chuks', 'chuk@dev.com', 'male', '1975-10-28')";
- $con->exec($query);
- $lastID= $con->lastInsertId();
- echo $lastID;
- /*SELECT 1*/
- $stmt = $con->prepare("SELECT * FROM people");
- $stmt->execute();
- while($result = $stmt->fetch(PDO::FETCH_ASSOC)){
- echo $result['name'].' '.$result['email'].' '.$result['sex'].'<br />';
- }
- echo '<hr />';
- /*SELECT 2*/
- $stmt = $con->prepare("SELECT name, email, sex FROM people");
- $stmt->execute();
- while($result = $stmt->fetch(PDO::FETCH_NUM)){
- echo $result[0].' '.$result[1].' '.$result[2].'<br />';
- }
- echo '<hr />';
- /*SELECT 3*/
- $stmt = $con->prepare("SELECT * FROM people");
- $stmt->execute();
- $result = $stmt->fetchAll(PDO::FETCH_NUM); //could also be $stmt->fetchAll(PDO::FETCH_ASSOC)
- foreach($result as $record){
- echo $record[1].' '.$record[2].' '.$record[3].'<br />';
- }
- /*UPDATE 1*/
- $query = "UPDATE people SET sex='female' WHERE name='chichi'";
- $con->exec($query);
- /*UPDATE 2*/
- $stmt = $con->prepare("UPDATE people SET sex='female' WHERE name='uche' ");
- $stmt->execute();
- echo 'Number of records affect is '.$stmt->rowCount();
- /* DISCONNECT */
- $con=null;
- }catch(PDOException $e){
- echo $e->getMessage();
- }
Add Comment
Please, Sign In to add comment