How to connect to MYSQL database using PHP
To connect MYSQL using PHP go to: http://localhost//phpmyadmin
Enter the username and password
Give the database name in the field ‘create new database’
Click on create button
Create a new table in the database by giving a table name and number of fields then click on Go
To give field name to the created table, write the field name in the ‘field’ column, select the data types for each fields, specify the length of each field then click on save to save the fields and click on Go
When clicked on Go the table field details will be displayed
To insert values in the field, go to insert and enter the values. Then click on Go
To view the created table, go to browse
The functions used to connect web form to the MYSQL database:
To insert values in the
field, go to insert and enter the values. Then click on Go
To view the created table, go to browse.
To insert the values, go to
SQL and write the query to insert the values and click on Go
SQL query
for insert:
Syntax:
Insert into table_name values(‘value1’,’value2’,…);
Example:
Insert into Login values(‘Radha’,’hello’);
To update the values, go to
SQL and write the query to update the values and click on Go
SQL query
for update:
Syntax:
Update table_name set field_name=’value’ where field_name=’value’;
Example:
Update Login set password=’abcde’ where name=’Radha’;
To delete the values, go to
SQL and write the query to delete the values and click on go
SQL query
for delete:
Syntax:
Delete from table_name where field_name=’value’;
Example:
Delete from Login where name=’Radha’;
The functions used to connect web form to the MYSQL database:
mysql_connect():
This
function opens a link to a MySQL server on the specified host (in this case
it's localhost)along with a username (root) and password(q1w2e3r4/). The
result of the connection is stored in the variable $db.
mysql_select_db():
This tells PHP that any queries we
make are against the mydb database.
mysql_query():
Using
the database connection identifier, it sends a line of SQL to the MySQL server
to be processed. The results that are returned are stored in the variable
$result.
mysql_result():
This
is used to display the values of fields from our query. Using $result, we go to
the first row, which is numbered 0, and display the value of the specified
fields.
mysql_result($result,0,"position")):
This
should be treated as a string and printed.
Display the data from MYSQL database in web form
<html>
<body>
< ?php
//Open MYSQL server connection
$db = mysql_connect("localhost",
"root","q1w2e3r4/");
//Select the databaseusingMYSQL server connection
mysql_select_db("mydb",$db);
/*Using the database
connection identifier, it sends
a line of SQL to the MySQL server to be processed
and
theresults are stored in the variable
$result. */
$result = mysql_query("SELECT * FROM
employees",$db);
//Displaying the details in a
table
echo "<table border=1>";
echo
"<tr><th>Name</th><th>Position</th></tr>";
while ($myrow = mysql_fetch_row($result)) {
printf("<tr><td>%s
%s</td><td>%s</td></tr>",
$myrow[1],
$myrow[2],$myrow[4]);
}
echo "</table>";
? ?>
</body>
</body>
</html>
OUTPUT of the above given Example would be:
OUTPUT of the above given Example would be:
Insert the data into MYSQL database using web form
<html>
<body>
<?php
if
($submit) {
//Open MYSQL server connection
$db = mysql_connect("localhost",
"root","q1w2e3r4/");
//Select the database using MYSQL server connection
mysql_select_db("mydb",$db);
/*Write insert query and assign the query in $sql
Variable*/
$sql = "INSERT INTO employees
(first,last,address,position)
VALUES('$first','$last','$address','$position')";
//Execute the query
$result = mysql_query($sql);
echo "Thank you! Information entered.";
}
else
{
//display form
?>
<form method="post" action="<?php
echo $PHP_SELF?>">
First name:<input type="Text"
name="first"><br>
Last name:<input type="Text"
name="last"><br>
Address:<input type="Text"
name="address"><br>
Position:<input type="Text"
name="position"><br>
<input type="Submit"
name="submit" value="Enter
information">
</form>
<?php
}// end if
?>
</body>
</html>
OUTPUT of the above given Example would be:
Update
the data present in MYSQL database using web form
OUTPUT of the above given Example would be:
Update
the data present in MYSQL database using web form
<body>
<?php
//Open MYSQL server connection
$db =
mysql_connect("localhost", "root","q1w2e3r4/");
//Select the database using
MYSQL server connection
mysql_select_db("mydb",$db);
if ($id) {
if
($submit) {
//Write UPDATE query and assign to $sqlVariable
$sql =
"UPDATE employees SET
first='$first',last='$last',
address='$address',
position='$position'
WHERE id=$id";
//Execute the query
$result = mysql_query($sql);
echo "Thank you! Information
updated.";
}
else
{
//Write query to SELECT data from table
$sql = "SELECT * FROM employees
WHERE id=$id";
//Execute the query
$result = mysql_query($sql);
//Fetch the values
$myrow = mysql_fetch_array($result);
?>
<form method="post" action="<?php
echo $PHP_SELF?>">
<input type=hidden name="id"
value="<?php echo
$myrow["id"]
?>">
First name:<input type="Text"
name="first"
value="<?php
echo $myrow["first"] ?>"><br>
Last name:<input type="Text"
name="last"
value="<?php
echo $myrow["last"] ?>"><br>
Address:<input type="Text"
name="address"
value="<?php
echo $myrow["address"]?>"><br>
Position:<input type="Text"
name="position"
value="<?php
echo $myrow["position"]?>"><br>
<input type="Submit"
name="submit" value="Enter
information">
</form>
<?php
}
}
}
else
{
//display list of employees
$result =
mysql_query("SELECT * FROM employees",$db);
while
($myrow = mysql_fetch_array($result)) {
printf("<a
href=\"%s?id=%s\">%s %s</a><br>",
$PHP_SELF,
$myrow["id"],$myrow["first"],
$myrow["last"]);
}
}
?>
</body>
</html>
OUTPUT of the above given Example would be:
Delete
the data from MYSQL database using web form
Delete
the data from MYSQL database using web form
<html>
<body>
<?php
//Open MYSQL server connection
$db =
mysql_connect("localhost", "root","q1w2e3r4/");
//Select the database using
MYSQL server connection
mysql_select_db("mydb",$db);
if ($id) {
if
($submit) {
//Write DELETE query to delete data from table based on
ID
$sql =
"DELETE FROM employees WHERE id=$id";
//Execute the query
$result = mysql_query($sql);
echo "Thank you! Information deleted.";
}
else
{
//Write SELECT query to select
data from table based on ID
$sql =
"SELECT * FROM employees WHERE id=$id";
$result =
mysql_query($sql);
$ myrow =
mysql_fetch_array($result);
?>
<form method="post"
action="<?php echo $PHP_SELF?>">
<input
type=hidden name="id"
value="<?php echo
$myrow["id"] ?>">
First name:<input type="Text"
name="first"
readonly="readonly"
value="<?php echo
$myrow["first"] ?>"><br>
Last
name:<input type="Text" name="last"
readonly="readonly"
value="<?php echo $myrow["last"]
?>"><br>
Address:<input type="Text"
name="address"
readonly="readonly"
value="<?php echo
$myrow["address"]?>"><br>
Position:<input type="Text"
name="position"
value="<?php echo
$myrow["position"]?>"><br>
<input type="Submit"
name="submit"
value="Delete information">
</form>
<?php
{ //display list of employees
$result =
mysql_query("SELECT * FROM
employees",$db);
while
($myrow = mysql_fetch_array($result)) {
printf("<a
href=\"%s?id=%s\">%s %s</a><br>",
$PHP_SELF,
$myrow["id"],$myrow["first"],
$myrow["last"]);
}
}
}
?>
</body>
</html>
