MySQL CRUD using PHP
Connect to the database.
<?php $con = mysqli_connect("localhost", "root", "", ""); if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error()."<br/>"; } else { echo "Connected to database successfully!<br/>"; }
Create database “mystore”.
<?php $con = mysqli_connect("localhost", "root", "", ""); $createDB = "CREATE DATABASE mystore"; if (mysqli_query($con,$createDB)) { echo "Database mystore created successfully!<br/>"; } else { echo "Error creating database: " . mysqli_error($con)."<br/>"; } mysqli_close($con);
Select the database “mystore”.
<?php $con = mysqli_connect("localhost", "root", "", ""); mysqli_select_db($con, "mystore"); if ($result = mysqli_query($con, "SELECT DATABASE()")) { $row = mysqli_fetch_row($result); echo "Default database is ".$row[0]."<br/>"; mysqli_free_result($result); } mysqli_close($con);
Create query to the table customers in the databse “mystore”.
<?php $con = mysqli_connect("localhost", "root", "", ""); $createCustomersTable = "CREATE TABLE customers ( PID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(PID), FirstName CHAR(15), LastName CHAR(15), Email CHAR(50), Age INT, created_at datetime )"; if (mysqli_query($con,$createCustomersTable)) { echo "Table customers created successfully!<br/>"; } else { echo "Error creating table: " . mysqli_error($con)."<br/>"; } mysqli_close($con);
Insert query to add customers.
<?php $con = mysqli_connect("localhost", "root", "", ""); $insertCustomers="INSERT INTO customers (`FirstName`, `LastName`, `Email`, `Age`,`created_at`) VALUES ('Larry', 'Page', 'larry.page@gmail.com', '22',NOW()), ('Steve', 'Jobs', 's.j@apple.com', '33',NOW()), ('Bill', 'Gates', 'b.g@ms.com', '35',NOW())"; if (mysqli_query($con,$insertCustomers)) { echo "Customers added successfully!<br/>"; } else { echo "Error adding customers: " . mysqli_error($con)."<br/>"; } mysqli_close($con);
Select query to select customers and loop through each customer.
<?php $con = mysqli_connect("localhost", "root", "", ""); $selectCustomers = "select * from customers"; $customers = mysqli_query($con,$selectCustomers); while($row = mysqli_fetch_array($customers)) { echo $row['PID']."|".$row['FirstName']."|".$row['LastName']."|".$row['Email']."|".$row['Age']."|".$row['created_at']."<br/>"; } mysqli_close($con);
Update query to update customer email.
<?php $con = mysqli_connect("localhost", "root", "", ""); $updateCustomers = "update customers set Email='steve.jobs@apple.com' where PID=2"; if(mysqli_query($con,$updateCustomers)) { echo "customer updated successfully!<br/>"; } else { echo "Error updating customers ".mysqli_error($con)."<br/>"; } mysqli_close($con);
Delete query to delete a customer in the customers table.
<?php $con = mysqli_connect("localhost", "root", "", ""); $deleteCustomer="delete from customers where PID=3"; if(mysqli_query($con,$deleteCustomer)) { echo "customer with PID=3 deleted successfully!<br/>"; } else { echo "Error deleting customer with PID=3 ".mysqli_error($con)."<br/>"; } mysqli_close($con);
Search within Codexpedia
Custom Search
Search the entire web
Custom Search
Related Posts