Created
July 5, 2019 04:20
-
-
Save sumanssaurabh/0a190759072a6c3ddb24f11ca67f02a8 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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(); | |
?> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment