Tag: mysql

Delete / Remove Records In Database Table: PHP & MySQL

Video tutorial demonstrates deleting / removing of data / records from the database table.

First look at these short videos:
Connect to the database
Simple / basic insertion operation
Insertion of Records into Database using Forms: PHP & MySQL (important)
SELECT / LIST Records From Database Table: PHP & MySQL
UPDATE / EDIT Records In Database Table: PHP & MySQL

SELECTING and Displaying Data
index.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

< ?php
include_once('db.php');
 
if(isset($_POST['name']))
{
  $name = $_POST['name'];
 
  if(mysql_query("INSERT INTO apple VALUES('','$name')"))
echo "Successful Insertion!";
  else
echo "Please try again";
}
 
 
$res = mysql_query("SELECT * FROM apple");
 
 
?>
<html>
<head>
 
<style type="text/css">
 li { list-style-type: none; display: inline; padding: 10px; text-align: center;}
// li:hover { background-color: yellow; }
</style>
 
</head>
<body>
<form action="." method="POST">
Name: <input type="text" name="name"/><br />
<input type="submit" value=" Enter "/>
</form>
 
<h1>List of companies ..</h1>
<ul>
< ?php
while( $row = mysql_fetch_array($res) )
  echo "<li>$row[id]. <li>$row[name]</li> 
                <li><a href='edit.php?edit=$row[id]'>edit</a></li>
                <li><a href='delete.php?del=$row[id]'>delete</a></li><br />";
?>
</ul>
</body>
</html>

Some CSS styling for simple / basic presentation: CSS Hover Over Effect

We have added delete link beside each record.

Deleting Records From Database Table

1
2
3
4
5
6
7
8
9
10
11

< ?php
include_once('db.php');
 
if( isset($_GET['del']) )
{
$id = $_GET['del'];
$sql= "DELETE FROM apple WHERE id='$id'";
$res= mysql_query($sql) or die("Failed".mysql_error());
echo "<meta http-equiv='refresh' content='0;url=index.php'>";
}
?>

Here we receive the value of id, passed from index.php and assign it to a local variable called $id.
Then using simple DELETE query, we delete the records associated with the id.

Video Tutorial: Delete / Remove Records In Database Table: PHP & MySQL


[youtube https://www.youtube.com/watch?v=ByaECx2mj9M]

YouTube Link: https://www.youtube.com/watch?v=ByaECx2mj9M [Watch the Video In Full Screen.]



List of companies ..
1. Google USA edit delete
2. Apple USA edit delete
3. Microsoft USA edit delete
4. Oracle USA edit delete

UPDATE / EDIT Records In Database Table: PHP & MySQL

Video tutorial demonstrates updating / editing of data / records from the database table and displaying it on the browser.

First look at these short videos:
Connect to the database
Simple / basic insertion operation
Insertion of Records into Database using Forms: PHP & MySQL (important)
SELECT / LIST Records From Database Table: PHP & MySQL

Source Code: SELECTING and Displaying Data

index.php

<?php

include_once('db.php');
 
if(isset($_POST['name']))
{
  $name = $_POST['name'];
 
  if(mysql_query("INSERT INTO apple VALUES('','$name')"))
echo "Successful Insertion!";
  else
echo "Please try again";
}
 
 
$res = mysql_query("SELECT * FROM apple");
 
 
?>
 
<form action="." method="POST">
Name: <input type="text" name="name"/><br />
<input type="submit" value=" Enter "/>
</form>
 
<h1>List of companies ..</h1>
< ?php
while( $row = mysql_fetch_array($res) )
  echo "$row[id]. $row[name] 
                <a href='edit.php?edit=$row[id]'>edit<br />";
?>

Video Tutorial: UPDATE / EDIT Records In Database Table: PHP & MySQL


[youtube https://www.youtube.com/watch?v=Hw1MwUlekeo]

https://www.youtube.com/watch?v=Hw1MwUlekeo [Watch the Video In Full Screen.]


Source Code: Updating / Editing Data / Records In The Database Table

edit.php

<?php
include_once('db.php');
 
if( isset($_GET['edit']) )
{
$id = $_GET['edit'];
$res= mysql_query("SELECT * FROM apple WHERE id='$id'");
$row= mysql_fetch_array($res);
}
 
if( isset($_POST['newName']) )
{
$newName = $_POST['newName'];
$id   = $_POST['id'];
$sql     = "UPDATE apple SET name='$newName' WHERE id='$id'";
$res  = mysql_query($sql) 
                                    or die("Could not update".mysql_error());
echo "<meta http-equiv='refresh' content='0;url=index.php'>";
}
 
?>

<form action="edit.php" method="POST">
Name: <input type="text" name="newName" value="<?php echo $row[1]; ?/>"><br />
<input type="hidden" name="id" value="<?php echo $row[0]; ?/>">
<input type="submit" value=" Update "/>
</form>

meta tag used to redirect to other page..

<meta http-equiv='refresh' content='0;url=index.php'>

MySQL Query

mysql> UPDATE apple SET name='newName' WHERE id='session_id';

Security Issue:
Make sure not to allow people to explicitly pass random id’s and retrieve and edit/update other’s data. To solve this issue, you can save the id of the logged in user in a session variable and instead of passing id to edit.php we can directly use the id stored in our session variable.
This would solve the issue.

Output: After Updating the records:

  1. Google USA
  2. Apple USA
  3. Microsoft USA
  4. Oracle USA
  5. Technotip IN

Video’s To Watch:
GET method in action
Post method in action
session variable basics
User login and session

SELECT / LIST Records From Database Table: PHP & MySQL

Video tutorial demonstrates selecting / listing / fetching of data / records from the database table and displaying it on the browser.

First look at these short videos:
Connect to the database
Simple / basic insertion operation
Insertion of Records into Database using Forms: PHP & MySQL (important)

SELECTING and Displaying Data
index.php

1
2
3
4
5
6
7
8
9

< ?php
  include_once('db.php');
 
 $res = mysql_query("SELECT * FROM apple");
 
  while( $row = mysql_fetch_array($res) )
  echo "$row[id]. $row[name] <br />";
 
?>

Video Tutorial: SELECT / LIST Records From Database Table: PHP & MySQL


[youtube https://www.youtube.com/watch?v=OAT0iTXlqCk]

YouTube Link: https://www.youtube.com/watch?v=OAT0iTXlqCk [Watch the Video In Full Screen.]



Full code
index.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

< ?php
include_once('db.php');
 
if(isset($_POST['name']))
{
  $name = $_POST['name'];
 
  if(mysql_query("INSERT INTO apple VALUES('','$name')"))
echo "Successful Insertion!";
  else
echo "Please try again";
}
 
 
$res = mysql_query("SELECT * FROM apple");
 
 
?>
 
<form action="." method="POST">
Name: <input type="text" name="name"/><br />
<input type="submit" value=" Enter "/>
</form>
 
<h1>List of companies ..</h1>
< ?php
while( $row = mysql_fetch_array($res) )
  echo "$row[id]. $row[name] <br />";
?>

while loop executes until there is data inside the table, once there is no more data to fetch, loop terminates.

MySQL Query

1

mysql> SELECT * FROM apple;

This mysql statement fetches data from all the fields of the table.
We could specify fields and get only list of those values.

Ex:

1

mysql> SELECT id FROM apple;

Using this we can get list of id’s only.

After fetching data from table apple

List of companies ..

1. Google
2. Apple
3. Microsoft
4. Oracle
5. Technotip

Insertion of Records into Database using Forms: PHP & MySQL

Video tutorial illustrates INSERTION of records into database using simple HTML form and minimal PHP scripting.

First look at these short videos:
Connect to the database
Simple / basic insertion operation

Form and PHP script, both in one file
index.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

< ?php
include_once('db.php');
 
if($_POST['name'])
{
  $name = $_POST['name'];
 
  if(mysql_query("INSERT INTO apple VALUES('','$name')"))
echo "Successful Insertion!";
  else
echo "Please try again";
}
?>
 
<form action="." method="POST">
Name: <input type="text" name="name"/><br />
<input type="submit" value=" Enter "/>
</form>

. in action property of html form simply means that the user entered values will be passed on to itself.
Since we are using . in the action property, we are making use of user entered values in the same file by using if($_POST[‘name’]) PHP code.
we can even use if( isset($_POST[‘name’])) isset() is a PHP function to check whether a variable has some value or not.

Video Tutorial: Insertion of Records into Database using Forms: PHP & MySQL


[youtube https://www.youtube.com/watch?v=9BRiX3rXuRg]

YouTube Link: https://www.youtube.com/watch?v=9BRiX3rXuRg [Watch the Video In Full Screen.]



To know the differences between GET and POST methods:
GET Method In Action: Simple Form
POST Method In Action: Simple Form

Data in the database, as shown in the video:
1. Google
2. Apple
3. Microsoft
4. Oracle
5. Technotip

Insertion of Records into Database: PHP & MySQL

Video tutorial illustrates INSERTION of records into database via PHP script.

Simple / basic insertion operation:

First connect to the database.

Next, using simple MySQL query.
index.php

1
2
3
4
5
6
7
8
9

< ?php
include_once('db.php');
 
 
if(mysql_query("INSERT INTO apple VALUES('','Oracle')"))
echo "Successful Insertion!";
else
echo "Please try again";
?>

make sure to watch other video about connecting to the database, to understand include_once(‘db.php’);

Video Tutorial: Insertion of Records into Database: PHP & MySQL


[youtube https://www.youtube.com/watch?v=GSLn77qbS1w]

YouTube Link: https://www.youtube.com/watch?v=GSLn77qbS1w [Watch the Video In Full Screen.]



die function:

1
2
3
4
5
6
7
8
9

< ?php
 
   include_once('db.php');
 
   $res = mysql_query("INSERT INTO apple VALUES('','Oracle')") 
                            or die("sorry, database is busy!");
 
   echo "Successful insertion";
?>

if the insertion operation fails for some reason, the die function is invoked and the message “Sorry, database is busy!” is shown to the use and the script execution halts, and the “Successful insertion” message won’t be echoed.

Data in the database, as shown in the video:
1. Google
2. Apple
3. Microsoft
4. Oracle