×

Please Login or Register to continue.

5
(1.1K 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>

 

(90 Points)
in PHP

Share

4 Answers
(9.2K Points)
2

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 . 


Comment
Where I Used This In Delete.php aur Index.php
osama 05 Sep 2019
<?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
osama 05 Sep 2019
<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
osama 05 Sep 2019
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.
shakti 05 Sep 2019
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.
shakti 05 Sep 2019

(7.3K Points)
1
Please post your table structure. Edit your question and share your table structure. I am getting your point Kindly create a unique id column.

Comment
You are not added an id column. Kindly add an id column before roll number and make it unique.
vishalrana1 05 Sep 2019
RollNo is Also Unique Column ?
osama 05 Sep 2019
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.
vishalrana1 05 Sep 2019
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>"
vishalrana1 05 Sep 2019
You can edit your database table structure like this - Make column id AI (auto increment )
vishalrana1 05 Sep 2019
thaks Its Work <3
osama 05 Sep 2019
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
osama 05 Sep 2019
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.
vishalrana1 05 Sep 2019
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.
vishalrana1 05 Sep 2019

(7.3K Points)
(edited)
2

1. Add a new column id at the beginning of the 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 the 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. 

 


Comment
My problem has been solved. It works. Thank you.
osama 05 Sep 2019
Welcome anytime. You can ask PHP and MYSQL related question here. Please upvote my answer.
vishalrana1 05 Sep 2019

(4.2K Points)
0

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. 


Comment

Related Questions :-

Featured Items:-


Certificate system in PHP website | PHP scripts

$37



Home Credito loan finance management multipurpose system in PHP

$98



Result management system with Marksheet in PHP website | PHP Scripts

$48




Your Answer

You can post your answer after login..