7gaxhfBM

1<?php
2$host = 'localhost';
3$dbname = 'wits';
4$username = 'root';
5$password='';
6try{
7    $con = new pdo("mysql:host=$host; dbname=$dbname", $username, $password);
8    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
9    
10    /*CREATE DATABASE*/
11    $query = "CREATE DATABASE IF NOT EXISTS tochukwu_base ";
12    $con->exec($query); //When ever no results is returned you can always use exec()
13    
14    
15    /*CREATE TABLE*/
16    $query  = "CREATE TABLE IF NOT EXISTS people(
17                id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
18                name VARCHAR(20) NOT NULL,
19                email VARCHAR(25) NOT NULL UNIQUE,
20                sex enum('male','female') DEFAULT 'male' NOT NULL,
21                birthdate DATE 
22                ) ";
23    $con->exec($query);
24    
25    /*INSERT RECORD */
26    $name = 'Tochukwu';
27    $email = 'tochi@me.com';
28    $sex = 'male'; 
29    $date = '1960-11-24';
30    
31    $query = "INSERT INTO people(name, email, sex, birthdate) VALUES('$name', '$email', '$sex', '$date')";
32    $con->exec($query);
33    
34     /* INSERT RECORD USING PREPARED STATEMENT */
35    $name = 'Uche';
36    $email = 'star@yahoo.com';
37    $sex = 'male';
38    $date = '1986-10-28';
39    
40   $query = $con->prepare( "INSERT INTO people(name, email, sex, birthdate) VALUES(:name, :email, :sex,  :date)" );
41   $query->execute([
42       ':name'=>$name, 
43       ':email'=>$email, 
44       ':sex'=>$sex, 
45       ':date'=>$date
46   ]);
47    
48    /*GET LAST ID*/
49   $query = "INSERT INTO people(name, email, sex, birthdate) VALUES('Chuks', 'chuk@dev.com', 'male', '1975-10-28')";
50   $con->exec($query);
51   $lastID= $con->lastInsertId();
52   echo $lastID;
53    
54    /*SELECT 1*/
55    $stmt = $con->prepare("SELECT * FROM people");
56    $stmt->execute();
57    while($result = $stmt->fetch(PDO::FETCH_ASSOC)){
58        echo $result['name'].' '.$result['email'].' '.$result['sex'].'<br />';  
59    }  
60    echo '<hr />';
61    
62    /*SELECT 2*/
63    $stmt = $con->prepare("SELECT name, email, sex FROM people");
64    $stmt->execute();
65    while($result = $stmt->fetch(PDO::FETCH_NUM)){
66        echo $result[0].' '.$result[1].' '.$result[2].'<br />';
67    }
68    echo '<hr />';
69    
70    
71    /*SELECT 3*/
72    $stmt = $con->prepare("SELECT * FROM people");
73    $stmt->execute();
74    $result = $stmt->fetchAll(PDO::FETCH_NUM); //could also be  $stmt->fetchAll(PDO::FETCH_ASSOC)
75    foreach($result as $record){
76        echo $record[1].' '.$record[2].' '.$record[3].'<br />';
77    }
78    
79    /*UPDATE 1*/
80    $query  = "UPDATE people SET sex='female' WHERE name='chichi'";
81    $con->exec($query);
82
83    /*UPDATE 2*/
84    $stmt = $con->prepare("UPDATE people SET sex='female' WHERE name='uche' ");
85    $stmt->execute();
86    echo 'Number of records affect is '.$stmt->rowCount();
87    
88    /* DISCONNECT */
89    $con=null;
90
91}catch(PDOException $e){
92    echo $e->getMessage();
93}