Guest User

Untitled

a guest
Aug 14th, 2018
368
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.79 KB | None | 0 0
  1. <?php
  2. $host = 'localhost';
  3. $dbname = 'wits';
  4. $username = 'root';
  5. $password='';
  6. try{
  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. }
Add Comment
Please, Sign In to add comment