Blog Details

PHP Database



                                

What is MySQL ?

  • MySQL is a database system used on the web
  • MySQL is a database system that runs on a server
  • MySQL is ideal for both small and large applications
  • MySQL is very fast, reliable, and easy to use
  • MySQL supports standard SQL
  • MySQL compiles on a number of platforms
  • MySQL is free to download and use
 

Open a Connection to the MySQL Server :-

Syntax :-

mysqli_connect(host,username,password,dbname);

Example  :-

<?php // Create connection $con=mysqli_connect("example.com","peter","abc123","my_db"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } ?>

Close a Connection :-

<?php $con=mysqli_connect("example.com","peter","abc123","my_db"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } mysqli_close($con); ?>

* All operation on Database :-

1)  Create a Database :-

<?php $con=mysqli_connect("example.com","peter","abc123","my_db"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } // Create table $sql="CREATE TABLE Persons(FirstName CHAR(30),LastName CHAR(30),Age INT)"; // Execute query if (mysqli_query($con,$sql)) { echo "Table persons created successfully"; } else { echo "Error creating table: " . mysqli_error($con); } ?>

2) Insert Data Into a Database Table :-

Syntax :-

  INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

Example :-

 

1) HTML form:

<html> <body> <form action="insert.php" method="post"> Firstname: <input type="text" name="firstname"> Lastname: <input type="text" name="lastname"> Age: <input type="text" name="age"> <input type="submit"> </form> </body> </html>

2) insert.php :

<?php $con=mysqli_connect("example.com","peter","abc123","my_db"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } $sql="INSERT INTO Persons (FirstName, LastName, Age) VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[age]')"; if (!mysqli_query($con,$sql)) { die('Error: ' . mysqli_error($con)); } echo "1 record added"; mysqli_close($con); ?>

3) Select Data From a Database Table :-

Syntax :-

  SELECT column_name(s) FROM table_name

Example :-

  <?php $con=mysqli_connect("example.com","peter","abc123","my_db"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } $result = mysqli_query($con,"SELECT * FROM Persons"); echo "<table border='1'> <tr> <th>Firstname</th> <th>Lastname</th> </tr>"; while($row = mysqli_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['FirstName'] . "</td>"; echo "<td>" . $row['LastName'] . "</td>"; echo "</tr>"; } echo "</table>"; mysqli_close($con); ?>

4) Update Data In a Database :-

Syntax :-

  UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value

Example :-

 
<?php $con=mysqli_connect("example.com","peter","abc123","my_db"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); }mysqli_query($con,"UPDATE Persons SET Age=36 WHERE FirstName='Peter' AND LastName='Griffin'");
mysqli_close($con); ?>

5) Delete Data In a Database :-

Syntax :-

  DELETE FROM table_name WHERE some_column = some_value

Example :-

  <?php $con=mysqli_connect("example.com","peter","abc123","my_db"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } mysqli_query($con,"DELETE FROM Persons WHERE LastName='Griffin'"); mysqli_close($con); ?>


Categories

Core_PHP

Latest posts

13.05.14
Implementing CRUD Operations ¶

<ul> <li>Now comes the fun part. We would like t

13.05.14
View

<ol> <li><a href="http://www.yiiframework.com/do

13.05.14
Creating Model

<ol> <li><a href="http://www.yiiframework.com/do

Get Free Quote