BLOG TIPS CODES

Showing posts with label Database. Show all posts

Delete a record from mysql database using php

Posted by LAHAUL SETH ~ Comments

This post is about how to delete a record from mysql database using php.

The php code :

<?php
$connection=mysql_connect("localhost","root","")
   or die("Couldn't connect to the server");
$db=mysql_select_db("produce",$connection)
   or die ("Couldn't select database");
$result=mysql_query("DELETE FROM fruit WHERE name='CD'");
$result=mysql_query("SELECT * FROM fruit");
echo "<table border='1'>";
echo "<tr>";
echo "<th>Name</th><th>Number</th><th>Quantity</th>";
echo "</tr>";
while($row=mysql_fetch_array($result))
{
     echo "<tr>";
     echo "<td>",$row['name'],"</td><td>",$row['number'],"
            </td><td>",$row['quantity'],"</td>";
    echo "</tr>";
}
echo "</table>";
mysql_close($connection);
?>

Before running the program :

Name	Number	Quantity
DVD	1	10
PENDRIVE	2	50
CD	3	25
KEYBOARD	4	15

After running the program :

Name	Number	Quantity
DVD	1	10
PENDRIVE	2	50
KEYBOARD	4	15

Sort data in the mysql database using php

Posted by LAHAUL SETH ~ Comments

This post is about sorting data in the mysql database using php.

The php code :

<?php
$connection=mysql_connect("localhost","root","")
   or die("Couldn't connect to the server");
$db=mysql_select_db("produce",$connection)
   or die ("Couldn't select database");
$result=mysql_query("SELECT * FROM fruit ORDER BY name");
echo "<table border='1'>";
echo "<tr>";
echo "<th>Name</th><th>Number</th><th>Quantity</th>";
echo "</tr>";
while($row=mysql_fetch_array($result))
{
     echo "<tr>";
     echo "<td>",$row['name'],"</td><td>",$row['number'],"
    </td><td>",$row['quantity'],"</td>";
    echo "</tr>";
}
echo "</table>";
mysql_close($connection);
?>

Before executing the program :

Name	Number	Quantity
DVD	1	10
PENDRIVE	2	50
CD	3	25
KEYBOARD	4	15

After executing the program :

Name	Number	Quantity
CD	3	25
DVD	1	10
KEYBOARD	4	15
PENDRIVE	2	50

Update a record in the mysql database using php

Posted by LAHAUL SETH ~ Comments

This program is about how to update a record already present in the mysql database using php.

Check out my previous two posts for convenience.

Displaying table of data from mysql database using php
Inserting data into mysql database using php

In SQL, the update command is usually written like this

UPDATE table_name SET col1='new_data' WHERE col2='present data'

Here is the php code for the update process.

<?php
$connection=mysql_connect("localhost","root","")
   or die("Couldn't connect to the server");
$db=mysql_select_db("produce",$connection)
   or die ("Couldn't select database");
$result=mysql_query("UPDATE fruit SET quantity='30kg' WHERE name='PINEAPPLE'");
$result=mysql_query("SELECT * FROM fruit");
echo "<table border='1'>";
echo "<tr>";
echo "<th>Name</th><th>Number</th><th>Quantity</th>";
echo "</tr>";
while($row=mysql_fetch_array($result))
{
     echo "<tr>";
     echo "<td>",$row['name'],"</td><td>",$row['number'],"
            </td><td>",$row['quantity'],"</td>";
     echo "</tr>";
}
echo "</table>";
mysql_close($connection);
?>

Output before running the program :

Name	Number	Quantity
APPLE	1	5KG
MANGO	2	10KG
Orange	3	15kg
PINEAPPLE	4	20KG

Output after running the program :

Name	Number	Quantity
APPLE	1	5KG
MANGO	2	10KG
Orange	3	15kg
PINEAPPLE	4	30KG

Inserting data into MySql database using php

Posted by LAHAUL SETH ~ Comments

In my last post, I have discussed about displaying a table from mysql database using php. Click here to read my previous post.

This program is about inserting a new record into the database using php. To insert a new rcord into the databse, the program is kind of similar to the displaying the table but one extra piece of code is needed.

For SQL, we use the "Insert" query to enter a new record into the database. If you don't know the format of the "Insert" query then here it is.

INSERT INTO table_name VALUES("col1","col2",......);

Now, here is the php code.

<?php
$connection=mysql_connect("localhost","root","")
   or die("Couldn't connect to the server");
$db=mysql_select_db("produce",$connection)
   or die ("Couldn't select database");
$result=mysql_query("INSERT INTO fruit VALUES('PINEAPPLE','04','20KG')");
$result=mysql_query("SELECT * FROM fruit");
echo "<table border='1'>";
echo "<tr>";
echo "<th>Name</th><th>Number</th><th>Quantity</th>";
echo "</tr>";
while($row=mysql_fetch_array($result))
{
     echo "<tr>";
     echo "<td>",$row['name'],"</td><td>",$row['number'],"

</td><td>",$row['quantity'],"</td>";
echo "</tr>";
}
echo "</table>";
mysql_close($connection);
?>

Output before running the php file.

Name	Number	Quantity
APPLE	1	5KG
MANGO	2	10KG
Orange	3	15kg

Output after running the php file

Name	Number	Quantity
APPLE	1	5KG
MANGO	2	10KG
Orange	3	15kg
PINEAPPLE	4	20KG

NOTE : If you keep on refreshing the page it will keep on adding the same row like this .

Name	Number	Quantity
APPLE	1	5KG
MANGO	2	10KG
Orange	3	15kg
PINEAPPLE	4	20KG
PINEAPPLE	4	20KG
PINEAPPLE	4	20KG
PINEAPPLE	4	20KG
PINEAPPLE	4	20KG

Displaying table from MySql database using php

Posted by LAHAUL SETH ~ Comments

To display a table using php first we need to create a database using mysql and then we need to create a table inside the database. For this, I have used WAMP server because it is relatively easy to create a database.

If you don't have WAMP Server, you can download it from here.

Now, here are the steps to create a database.

1) Go to your phpMyAdmin page.

2) Click on "Databases". This page will give you the option of creating a new database and it will also show you the existing database in your server.

3) After creating your database, you will be asked to create a table. Create your table with the no. of columns you desire giving them your desired data type which depends on the type of data you are willing to store in the columns fo eg: text,integer and so on.

4) After creating your table, you will need to insert data into the table by using the following sql queries.

INSERT INTO TABLE_NAME VALUES("COL1","COL2",....);

Note : No need to give " " for integer fields. Replace your own table name with "TABLE_NAME".

5) Go on inserting data into table as much as you like.

6) Now you need to write the php file to make a connection with the database and display the table on your web browser.

For this I have used a database name "produce" and table name "fruit" with fields name(text), number(integer) and quantity(text).

Here is the php file for the program.

<?php

$connection=mysql_connect("localhost","root","")

or die("Couldn't connect to the server");

$db=mysql_select_db("produce",$connection)

or die ("Couldn't select database");

$result=mysql_query("SELECT * FROM fruit");

echo "<table border='1'>";

echo "<tr>";

echo "<th>Name</th><th>Number</th><th>Quantity</th>";

echo "</tr>";

while($row=mysql_fetch_array($result))

{

echo "<tr>";

echo "<td>",$row['name'],"</td><td>",$row['number'],"

</td><td>",$row['quantity'],"</td>";

echo "</tr>";

}

echo "</table>";

mysql_close($connection);

Note : The default username for WAMP Server is "root" and no default password is given so I have used " " as password while making connection with the database.

Here is the output of the php file.