CODEWITHSUNDEEP
phpsql
Sigiii
Sigiii

Reputation: 23

delete a certain record from a rows in database

Hi guys I am trying to delete only one record from a database for example:

 ID     Flower_type           Flower_name        Price
  1      Bouquet                 Beauty            150
  2      Basket                  Sunshine          120

Now i will have another table to edit the flower_types:

ID              Flower_type              
1                Bouquet
2                Basket
3              Flower Arrangement

Now if i need to edit "Bouquet" to "Bouquets" i want to delete the record with ID "1" in the other table so the flower_type will change immediately.

I stuck where i need to delete because i can't delete flower_type record only.

This is what it tried but the query is not good. There is a way to chnage it? .. or i can update the table ?

This is the code i tried :

{
    $update = mysqli_query($conn,"SELECT id FROM tbl_flower WHERE flower_type ='$_SESSION[prevname]'");
    $update_rows = mysqli_num_rows($update);

    for($i = 0;$i < $update_rows; $i++){
        while($row = $update -> fetch_assoc()){
           $i++;
           echo $row['id']; 
           $id = $row['id'];
           $DELETE = mysqli_query($conn,"DELETE flower_type FROM tbl_flower WHERE id='$id'");

        }
    }
}

Upvotes: 1

Views: 84

Answers (2)

Edwin Wong
Edwin Wong

Reputation: 713

Your table design is goes wrong and duplicate columns. Your table should be design in this way following example:

Flower Table
+-----+-------------+-------+
| ID  | flower_name | price |
|-----+-------------+-------|
| 1   | Beauty      | 150   |
|-----+-------------+-------|
| 2   | Sunshine    | 120   |
+-----+-------------+-------+

Flower Type Table
+------------+----------------+
| flower_id  | flower_type_id | 
|------------+----------------+
| 1          | 1              | 
+------------+----------------+

Type Table
+-----+-------------------------+
| ID  | flower_type             |
|-----+-------------------------+
| 1   | Bouquet                 |
|-----+-------------------------+
| 2   | Basket                  |
|-----+-------------------------+
| 3   | Flower Arrangement      |
+-----+-------------------------+

In your case, your need to separate into a new table which is called flower type table.

The advantage to doing this way, when you update flower type name you don't bother any duplicate column you need to update in other table. Also, when you delete flower type just delete along with the Flower type table where the flower_type_id is equal to your flower type ID.

When you want to list all the flower with its type, you can use MySql join those tables.

You should learn Database Relationship and join statement to design your database in practice way

Upvotes: 1

serverSentinel
serverSentinel

Reputation: 994

You're problem is your tables are not normalized and you can do this with a simple update statement instead of delete. You have flower_type in 2 different tables but it should only be in 1. You use joins in your select statements to bring these tables together.

I added names to your sample data for reference. 

FlowerArrangements
 ID     Flower_name        Price    Flower_type
  1      Beauty            150      1
  2      Sunshine          120      2

ArrangementType
ID              Flower_type              
1                Bouquet
2                Basket
3              Flower Arrangement

Joining the 2 tables can be done like this

select *
from FlowerArrangements fa
join ArrangementType at
   on fa.Flower_type = at.ID

Now updating Bouquet to Bouquets is as easy as

update ArrangementType set Flower_type='Bouquets' where Flower_type = 'Bouquet'

All FlowerArrangements returning Bouquet before will return Bouquets when you rerun the select query.

Upvotes: 1

Related Questions