• Register
Welcome to Techno Smarter QA, Questions, Answers Community
Welcome to Techno Smarter Q&A, where you can ask questions and receive answers from other members of the community. Ask the tech & programming Question that you want. Guests Posting are allowed.

Related questions

Delete specific row record using PHP and MYSQL database

+5 votes
87 views

I have a table of marks containing (RollNO subject and obtained marks). I want to delete just RollNo 1 record subject only biology and obtained marks 54, not the whole records of RollNo1 not RollNo1 Chemistry I Just Delete eg:- bio or chemistry not the whole 

MySQL database table structure - 

An insert HTML form and data displaying in the table with edit and delete button.

I tried this code. HTML code and PHP code . 

<html>
<head>
    <title>Insert Data</title>
    <style>
    form{
          font-family: "Georgia";
          text-align: center;
          font-size: 26px;
          
          }
    input{
           font-family: "Georgia";
           width: 290px;
           height: 40px;
           font-size: 20px;}
    td {
    font-size: 17px;
    font-family: "Georgia";
    }
   </style>           
</head>
 
  <body>
    
    <br/><br/>
 
    <form action="" method="post" name="form1">
        <table width="25%" border="0">
            <tr>
                <td>RollNo</td>
                <td><input type="number" min = 1  name="RollNo"></td>
            </tr>
            <tr>
                <td>Subject</td>
                <td><input type="text" pattern="[a-zA-Z][a-zA-Z ]{2,}" name="Subject"></td>
            </tr>
            <tr>
                <td>ObtainedMarks</td>
                <td><input type="number" min = 1 name="ObtainedMarks"></td>
            </tr>
            <tr>
                <td></td>
                <td><input type="submit" name="Submit" value="Insert"></td>
            </tr>
        </table>
    </form>
</body>
</html>

<html>
<head>

</head>
 
<body>
<?php
//including the database connection file
$cser=mysqli_connect("localhost","root","","dmc") or die("connection failed:".mysqli_error());
 
if(isset($_POST['Submit'])) {    
    $RollNo = $_POST['RollNo'];
    $Subject = $_POST['Subject'];
    $ObtainedMarks = $_POST['ObtainedMarks'];

        
    // checking empty fields
    if(empty($RollNo) || empty($Subject) || empty($ObtainedMarks)) {                
        if(empty($RollNo)) {
            echo "<font color='red'>RollNo field is empty.</font><br/>";
        }
        
        if(empty($Subject)) {
            echo "<font color='red'>Subject field is empty.</font><br/>";
        }
        
        if(empty($ObtainedMarks)) {
            echo "<font color='red'>ObtainedMarks field is empty.</font><br/>";
        }
        
        //link to the previous page
        echo "<br/><a href='javascript:self.history.back();'>Go Back</a>";
    } else {
        // if all the fields are filled (not empty)             
        //insert data to database
        $result = mysqli_query($cser, "INSERT INTO marks(RollNo,Subject,ObtainedMarks) VALUES('$RollNo','$Subject','$ObtainedMarks')");
        
        //display success message
        echo "<font color='green'>Data added successfully.";
        
    }
}   
?>
</body>
</html>
<?php
//including the database connection file
$cser=mysqli_connect("localhost","root","","dmc") or die("connection failed:".mysqli_error());
 
//fetching data in descending order (lastest entry first)

$result = mysqli_query($cser, "SELECT * FROM marks ORDER BY RollNo "); // using mysqli_query instead
?>
 
<html>
<head>    
    <title>Homepage</title>
</head>
 
<body>
    
 
    <table width='70%' height="50%" border=2 >
        <tr bgcolor='grey'>
            <td>RollNo</td>
            <td>Subject </td>
            <td>ObtainedMarks </td>
            <td>Remove </td>
            <td>Update</td>
            
        </tr>
        <?php
     
        while($res = mysqli_fetch_array($result)) {         
            echo "<tr>";
            echo "<td>".$res['RollNo']."</td>";
            echo "<td>".$res['Subject']."</td>";
            echo "<td>".$res['ObtainedMarks']."</td>";     
            echo"<td> <a href=\"delete1.php?RollNo=$res[RollNo]\" onClick=\"return confirm('Are you sure you want to delete?')\">Delete</a></td>";
            echo "<td><a href=\"edit1.php?RollNo=$res[RollNo]\"><input type='submit' value='Edit'></a>";
           
         echo "</tr>";
        }
        ?>
       
    </table>
</body>
</html>

in PHP by (320 points) ● 6 ● 26
edited by

4 Answers

+2 votes

You can delete data using id and column. Yes, this possible for a particular table using SQL query. Use Alter query and id. 

Like this - 

ALTER TABLE table_name DROP column_name where id  ;

Alter query will help you delete a particular subjects. You can define id or click on the button . 

by (7.3k points) ● 5 ● 22 ● 51
Where I Used This In Delete.php aur Index.php
by (320 points) ● 6 ● 26
<?php
$cser=mysqli_connect("localhost","root","","dmc") or die("connection failed:".mysqli_error());
 

$RollNo = $_GET['RollNo'];

 
//Delete row from database .
$result = mysqli_query($cser, "DELETE FROM marks WHERE RollNo=$RollNo");
 
if($result)
{
header("Location: insert1.php");
}
else
{
    echo "error";
}
?>
this is my delete.php
by (320 points) ● 6 ● 26
<table width='70%' height="50%" border=2 >
        <tr bgcolor='grey'>
            <td>RollNo</td>
            <td>Subject </td>
            <td>ObtainedMarks </td>
            <td>Remove </td>
            <td>Update</td>
            
        </tr>
        <?php
     
        while($res = mysqli_fetch_array($result)) {         
            echo "<tr>";
            echo "<td>".$res['RollNo']."</td>";
            echo "<td>".$res['Subject']."</td>";
            echo "<td>".$res['ObtainedMarks']."</td>";     
            echo"<td> <a href=\"delete1.php?RollNo=$res[RollNo]\" onClick=\"return confirm('Are you sure you want to delete?')\">Delete</a></td>";
            echo "<td><a href=\"edit1.php?RollNo=$res[RollNo]\"><input type='submit' value='Edit'></a>";
           
         echo "</tr>";
        }
        ?>
       
    </table>
this is last part of index.php
by (320 points) ● 6 ● 26
Please share your MYSQL database table data structure or add a new column id with a unique key and delete by id only. It will help you.
by (7.3k points) ● 5 ● 22 ● 51
First, create an id and make it an auto-increment at edit time. And change the link -

$result = mysqli_query($cser, "DELETE FROM marks WHERE RollNo=$RollNo");

to

$result = mysqli_query($cser, "DELETE FROM marks WHERE id=$id");

Edit the index file change RollNo to id.
by (7.3k points) ● 5 ● 22 ● 51
edited by
+2 votes

1. Add a new column id at the beginning of MYSQL table and tick AI ( Auto increment) and save.  

2.  Change this line - 

echo"<td> <a href=\"delete1.php?RollNo=$res[RollNo]\" onClick=\"return confirm('Are you sure you want to delete?')\">Delete</a></td>"

to

echo"<td> <a href=\"delete1.php?id=$res[id\" onClick=\"return confirm('Are you sure you want to delete?')\">Delete</a></td>"

You can delete data by id. One by one row. Never use the roll number. The roll number is containing the same 1,1,1, rows. It deletes 3 rows. That's why create a unique id. 

Now go to delete php file and change the line.

$result = mysqli_query($cser, "DELETE FROM marks WHERE RollNo=$RollNo");

to

$result = mysqli_query($cser, "DELETE FROM marks WHERE id=$id");

It will help you. Comment me if the error will occur. 

by (5.1k points) ● 4 ● 20 ● 49
edited by
Ty Bro Its Work thanks
by (320 points) ● 6 ● 26
Welcome anytime. You can ask PHP and MYSQL related question here.  Please upvote my answer.
by (5.1k points) ● 4 ● 20 ● 49
+1 vote
Please post your table structure. Edit your question and share your table structure.

I am getting your point Kindly create a unique id column.
by (5.1k points) ● 4 ● 20 ● 49
You are not added an id column. Kindly add an id column before roll number and make it unique.
by (5.1k points) ● 4 ● 20 ● 49
RollNo is Also Unique Column ?
by (320 points) ● 6 ● 26
RollNo is not showing unique in your table structure. Kindly add a new column id before roll number (at the beginning ) with auto increment.
See my another answer at first position.
Try that.
by (5.1k points) ● 4 ● 20 ● 49
I updates this line in my answeer check answer at first position .
echo"<td> <a href=\"delete1.php?id=$res[id\" onClick=\"return confirm('Are you sure you want to delete?')\">Delete</a></td>"
by (5.1k points) ● 4 ● 20 ● 49
You can edit your database table structure like this -
Make column id AI (auto increment )
by (5.1k points) ● 4 ● 20 ● 49
thaks Its Work <3
by (320 points) ● 6 ● 26
Just Once For Edit


<?php
$mysqli=mysqli_connect("localhost","root","","dmc") or die("connection failed:".mysqli_error());

$id=$_GET['id'];
$result=mysqli_query($mysqli,"SELECT * FROM marks WHERE RollNo=$RollNo");
while($res=mysqli_fetch_array($result))
{
    $RollNo=$res['RollNo'];
    $Subject=$res['Subject'];
    $ObtainedMarks=$res['ObtainedMarks'];
}

?>   
<?php
if(isset($_POST['update'])){
    $RollNo=$_POST['RollNo'];
    $Subject=$_POST['Subject'];
    $ObtainedMarks=$_POST['ObtainedMarks'];
   $result=mysqli_query($mysqli,"Update marks SET  Subject='$Subject',ObtainedMarks='$ObtainedMarks' WHERE id=$id");
   if($result){
     header("location:insert1.php");
   }
   else
   {
       echo "Failed";
   }
}

?>
 <form name="form1" method="post" action="edit1.php">
        <table border="0">
            <tr>
                <td>RollNo</td>
                <td><input type="number" name="RollNo" value="<?php echo $RollNo;?>"></td>
            </tr>
            <tr>
                <td>Subject</td>
                <td><input type="text" name="Subject" value="<?php echo $Subject;?>"></td>
            </tr>
            <tr>
                <td>ObtainedMarks</td>
                <td><input type="text" name="ObtainedMarks" value="<?php echo $ObtainedMarks;?>"></td>
            </tr>
            <tr>
                <td><input type="hidden" name="id" value=<?php echo $_GET['id'];?>></td>
                <td><input type="submit" name="update" value="Update"></td>
            </tr>
        </table>
    </form>


What i changed here i also create ID AI
by (320 points) ● 6 ● 26
Have you created an id or ID?
id should be in a small alphabet.
Never create database table column names in the uppercase alphabet.
All column names should be in the lowercase alphabet.
Like this -

id, rollno , subjects, marks, class , section, result, etc.

Change this line
$result=mysqli_query($mysqli,"SELECT * FROM marks WHERE RollNo=$RollNo");

 to

$result=mysqli_query($mysqli,"SELECT * FROM marks WHERE id=$id");

Select data by id and update by id.
by (5.1k points) ● 4 ● 20 ● 49
Select data one by one id. In PHP, delete and update operation is done by id. Every row has a unique id . We can delete and update one by one row by id.
by (5.1k points) ● 4 ● 20 ● 49
0 votes

The id column is missing in your MySQL table. You should add an id column with auto increment. 

PHP operations- 

1. Update by id - ( Id should be AI ) auto increment. 

2. Delete by id- ( Id should be AI ) auto increment.

You should focus on id importances. 

Reference for Update and delete by id - 

CRUD application using PHP and MYSQL database

Start learning and understand the id concept using PHP CRUD application. 

by (3.3k points) ● 1 ● 16 ● 43
Copyright © Techno Smarter QA
Powered by Techno Smarter Youtube, Facebook,
...