Skip to content

Instantly share code, notes, and snippets.

@sumanssaurabh

sumanssaurabh/sql_cheatsheet2.php

Created July 5, 2019 04:20
Show Gist options
  • Save sumanssaurabh/0a190759072a6c3ddb24f11ca67f02a8 to your computer and use it in GitHub Desktop.
Save sumanssaurabh/0a190759072a6c3ddb24f11ca67f02a8 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. sumanssaurabh renamed this gist Jul 5, 2019. 1 changed file with 0 additions and 0 deletions.
    0 sql_cheatsheet.php → sql_cheatsheet2.php
    View file
    File renamed without changes.
  2. sumanssaurabh created this gist Jul 5, 2019.
    120 changes: 120 additions & 0 deletions sql_cheatsheet.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,120 @@
    <?php

    require 'db_config.php';
    /**
    * <?php
    * $servername = "127.0.0.1";
    * $username = "root";
    * $password = "";
    * $dbname = "myDB";
    * ?>
    */
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
    }

    ////////////////////////////////////////////////////////////////////////////////////////////
    // ****DATABASE CREATION *********/////////////////
    // Create connection
    // $conn = new mysqli($servername, $username, $password);
    // Check connection
    // if ($conn->connect_error) {
    // die("Connection failed: " . $conn->connect_error);
    // }


    // Create database
    $sql = "CREATE DATABASE IF EXISTS myDB";
    if ($conn->query($sql) === true) {
    echo "Database created successfully";
    } else {
    echo "Error creating database: " . $conn->error;
    }
    //////////////////////////////////
    // sql to create table
    $sql = "CREATE TABLE IF EXISTS MyGuests (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP
    )";

    if ($conn->query($sql) === true) {
    echo "Table MyGuests created successfully";
    } else {
    echo "Error creating table: " . $conn->error;
    }

    //insert data
    $sql = "INSERT INTO MyGuests (firstname, lastname, email)
    VALUES ('John', 'Doe', '[email protected]')";

    if ($conn->query($sql) === true) {
    echo "New record created successfully";
    } else {
    echo "Error: " . $sql . "<br>" . $conn->error;
    }

    //get last id
    $sql = "INSERT INTO MyGuests (firstname, lastname, email)
    VALUES ('John', 'Doe', '[email protected]')";

    if ($conn->query($sql) === true) {
    $last_id = $conn->insert_id;
    echo "New record created successfully. Last inserted ID is: " . $last_id;
    } else {
    echo "Error: " . $sql . "<br>" . $conn->error;
    }

    //insert multiple records
    $sql = "INSERT INTO MyGuests (firstname, lastname, email)
    VALUES ('John', 'Doe', '[email protected]');";
    $sql .= "INSERT INTO MyGuests (firstname, lastname, email)
    VALUES ('Mary', 'Moe', '[email protected]');";
    $sql .= "INSERT INTO MyGuests (firstname, lastname, email)
    VALUES ('Julie', 'Dooley', '[email protected]')";

    if ($conn->multi_query($sql) === true) {
    echo "New records created successfully";
    } else {
    echo "Error: " . $sql . "<br>" . $conn->error;
    }

    //select data or showing records
    $sql = "SELECT id, firstname, lastname FROM MyGuests";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
    // output data of each row
    while ($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"].
    " " . $row["lastname"]. "<br>";
    }
    } else {
    echo "0 results";
    }

    //update data
    $sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";

    if ($conn->query($sql) === true) {
    echo "Record updated successfully";
    } else {
    echo "Error updating record: " . $conn->error;
    }

    // sql to delete a record
    $sql = "DELETE FROM MyGuests WHERE id=3";

    if ($conn->query($sql) === true) {
    echo "Record deleted successfully";
    } else {
    echo "Error deleting record: " . $conn->error;
    }

    $conn->close();
    ?>