Welcome to Techno Smarter QA, Question, Answer forum. Ask questions, receive answers, or help someone. Join Now
+5 votes
270 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 an 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 (90 points) 2 18 31
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 (9k points) 10 25 62
Where I Used This In Delete.php aur Index.php
<?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
<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
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.
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.
+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 (7.1k points) 12 28 62
edited by
My problem has been solved. It works. Thank you.
Welcome anytime. You can ask PHP and MYSQL related question here.  Please upvote my answer.
+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 (7.1k points) 12 28 62
You are not added an id column. Kindly add an id column before roll number and make it unique.
RollNo is Also Unique Column ?
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.
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>"
You can edit your database table structure like this -
Make column id AI (auto increment )
thaks Its Work <3
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
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.
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.
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 (4k points) 5 26 52
...