Sigiii Sigiii - 3 months ago 7
SQL Question

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'");

}
}
}

Answer

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

Comments