How to delete a record from table when dealing with two different tables

Question

I've two tables let's say booking and supplier

Fetching the records from both tables with-in date-range as follow

$query = "SELECT booking.book_id AS Id,
        booking.referance_no AS RefNo,
        booking.entry_date AS DepDate,
        booking.name AS Name,
        booking.mobile AS mobile,
        booking.comp_title AS Company 
    FROM booking WHERE active='1' $WHERE1
    GROUP BY booking.book_id
    UNION ALL
        SELECT supplier.id AS Id,
        supplier.reference_no AS RefNo,
        supplier.departure_date_time AS DepDate,
        supplier.name AS Name,
        supplier.mobile AS Mobile,
        supplier.company AS Company
        FROM supplier WHERE active='1' $WHERE2  
    ORDER BY `DepDate` DESC LIMIT 1000";

Note: I remove lots of code lines as they are not relevant to this question so as $WHERE1 and $WHERE2, they are just date range clause.

After query fetching data (while loop) to HTML table

<td><?php echo $row['RefNo'];?></td>
<td><?php echo $row['Name'];?></td>
<td><?php echo $row['Mobile'];?></td>
<td><?php echo $row['DepDate'];?></td>
<td><?php echo $row['Company'];?></td>
<td><a class="btn" href="delete.php?RefNo=<?php echo $row['RefNo'];?>">Delete</a></td>

In HTML view, I know that <?php echo $row['RefNo'];?> in href belongs to which table booking Or supplier but PHP doesn't know it and on delete.php I've to call both tables and first have to check the RefNo against each table and then if it's true delete the record

delete.php

 $ReferenceNo = $_GET['RefNo'];

 //Fetch records from both tables
 //Check records against `$ReferenceNo`
 //If true against `booking` table
 "Delete From booking where referance_no=$ReferenceNo"
 //else
 "Delete From supplier where reference_no=$ReferenceNo"

The question, is there better approach to delete the record where I don't have to call both tables and first check RefNo against each table.

Edit to make Question more clear:

As I mentioned somewhere above that PHP doesn't know <?php echo $row['RefNo'];?> belongs to which table booking or supplier so I need the work around where before any action (Delete, Cancel, Edit) I can tell PHP that <?php echo $row['RefNo'];?> belongs to this table booking or supplier so no need to check <?php echo $row['RefNo'];?> against both tables before any action Delete, Cancel, Edit

I just can't think how a result where some `id`s are suppliers and some are bookings is useful — Strawberry, Aug 31 '15 at 12:46
Why do you have to check before deleting? Just do two DELETE commands. — jtheman, Aug 31 '15 at 12:50
What do the results of this query even represent? Some data is from one entity, some is from another. And the identifiers are indistinguishable. When the user "deletes" something, what actual semantic business logic object are they deleting? A booking or a supplier? — David, Aug 31 '15 at 12:52
It's basically a daily report showing all the records on one page and all of you asking about it's usefulness, when dealing in real time with customers it's useful to know who is coming and who has been dealt `ORDER BY `DepDate`` i didn't put the time in question and delete is not the only action, there are more actions and if you don't understand something doesn't mean its wrong — Shehary, Aug 31 '15 at 12:59

Drew · Accepted Answer · 2015-08-31T13:38:44.757

1

Each row in html table has something like:

<input type="hidden" name="myIncrNNN" value="tableX">

that is not visible, and picked up to clue you in to what to do upon processing.

Edit: to make it more clear

your

<td><a class="btn" href="delete.php?RefNo=<?php echo $row['RefNo'];?>">Delete</a></td>

Would be altered to pick up the hidden column clue.

Yours now would be an ultimate call to :

http://example.com/somedir/delete.php?RefNo=7

in my imaginary world it would become

http://example.com/somedir/delete.php?RefNo=7&tc=1

Where tc means table clue from the hidden input field

Does not require a schema change, and added table, and is obvious that the client is telling the server what to do, no more or less than the original in the face of it all, and does not say the client is an Authority of anything, like @Halcyon is suggesting.

Edit 2: (to show UNION chg)

$query = "SELECT booking.book_id AS Id,
    booking.referance_no AS RefNo,
    booking.entry_date AS DepDate,
    booking.name AS Name,
    booking.mobile AS mobile,
    booking.comp_title AS Company,
    'booking' as TableClue  -- <------ RIGHT THERE
FROM booking WHERE active='1' $WHERE1
GROUP BY booking.book_id
UNION ALL
    SELECT supplier.id AS Id,
    supplier.reference_no AS RefNo,
    supplier.departure_date_time AS DepDate,
    supplier.name AS Name,
    supplier.mobile AS Mobile,
    supplier.company AS Company,
    'supplier' as TableClue   -- <------ RIGHT THERE
    FROM supplier WHERE active='1' $WHERE2  
ORDER BY `DepDate` DESC LIMIT 1000";

edited Aug 31 '15 at 13:38

answered Aug 31 '15 at 12:51

Drew

24,851
10
43
78

1

This is not information that should be sent trough the client. – Halcyon Aug 31 '15 at 12:52
"In HTML view, I know that in href belongs to which table booking Or supplier but PHP doesn't know it and on delete.php I've to " ... so why the heck not alert delete.php? The client is the one calling the php anyway. Maybe I am dense this morning (or all mornings) – Drew Aug 31 '15 at 12:55
Because now you're making the client the authority on where the record is located. – Halcyon Aug 31 '15 at 12:57
This client is the authority on what it wants to delete. You cant have your cake and eat it too – Drew Aug 31 '15 at 12:57
Sure you can. You just need to run a `SELECT` query on both tables. This is an artifact of the non-normalized table design. And no, the client only needs to know about `RefNo`. – Halcyon Aug 31 '15 at 12:59
ok, you can write an interface to over-tax the back end. Hundred ways to do it, like your Answer :> – Drew Aug 31 '15 at 13:00
First of all this is not a client thing, this is administrator thing so no worries about authority and if you guys this much eager to see the complete code i can put it up there so spare some 10 good minutes to read the whole query and code – Shehary Aug 31 '15 at 13:08
@Drew can't understand your answer, it's over my head – Shehary Aug 31 '15 at 13:08
@Drew what is `value="tableX"` – Shehary Aug 31 '15 at 13:17
you said in your question that you know where the data is coming from. TableX is the table clue that you embed. like A or B. Cat or Mouse. – Drew Aug 31 '15 at 13:18
If the whole concept is tough enough as it is, I for one would not stir the muddy water more by doing a schema change for gosh sake just for one obscure front-end interface need. For that matter, every app that has 20 tables in schema could be converted into 2000 tables of intersect baggage like @Halcyon just suggested. – Drew Aug 31 '15 at 13:20
I'm not gona change the table schema it's not even possible, then i have to make changes in email parsing and 5 sites to which this database is connected and i think more then 10 suppliers access too and front end booking module, so as the back end booking module so as the affiliates booking module, I have to watch the movies too, haven't watched Avengers 2 yet :( – Shehary Aug 31 '15 at 13:24
yup, i see that, many thanks, it will work even if not will make it work :) – Shehary Aug 31 '15 at 13:41
if you need to codify the info so the delete.php does multiple table action, it is completely flexible. It can do 1 delete, 2, 7, whatever you need. Can also tell delete.php to send an SMS message to wear green socks. Or to enter your review of Avengers 2. Now go watch that :> – Drew Aug 31 '15 at 13:46

score 0 · Answer 2 · edited May 23 '17 at 11:58

0

You're dealing with non-normalized data. In essence you have two tables that contain the same data (more or less). To normalize you have to add a table that maps reference_no to entries in booking and supplier. You can query that table to see which record to delete or go the full mile set up a foreign-key cascade.

Something like this:

CREATE TABLE `booking` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reference_no` int(11) NOT NULL,
  `etc` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `supplier` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reference_no` int(11) NOT NULL,
  `etc` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `reference_no` (
  `reference_no` int(11) NOT NULL,
  `booking` int(11) DEFAULT NULL,
  `supplier` int(11) DEFAULT NULL,
  KEY `booking` (`booking`),
  KEY `supplier` (`supplier`),
  CONSTRAINT `reference_no_ibfk_4` FOREIGN KEY (`booking`) REFERENCES `booking` (`id`) ON DELETE CASCADE,
  CONSTRAINT `reference_no_ibfk_5` FOREIGN KEY (`supplier`) REFERENCES `supplier` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In practise though, you probably don't have to do this. Delete on both tables seems fine to me.

edited May 23 '17 at 11:58

Community

1
1

answered Aug 31 '15 at 12:47

Halcyon

57,230
10
89
128

short story, I can't recreate the tables and I'm not the one who already creates it, it already has thousands of records and now I'm just dealing with it. – Shehary Aug 31 '15 at 13:07
You don't have to recreate the tables, you just need to add the `reference_no` table (and fill it). Thousands is not a lot by the way ;) Some of us deal with millions, MySQL is tougher than you might think. – Halcyon Aug 31 '15 at 13:10
ok but this won't be effective for the future records unless I make changes in relative PHP files who deals with these 2 tables for new records – Shehary Aug 31 '15 at 13:18
Yep, some bookkeeping is required. This is usually how it is, you trade CPU cycles for memory or vice versa. I'm sticking with the recommendation that two deletes is fine, it's by far the simplest solution and that has merrit in and of itself. – Halcyon Aug 31 '15 at 13:29

score 0 · Answer 3 · answered Aug 31 '15 at 13:33

first have to check the RefNo against each table and then if it's true delete the record

No you don't. Just issue the DELETE statements:

DELETE FROM booking WHERE referance_no=$ReferenceNo
DELETE FROM supplier WHERE reference_no=$ReferenceNo

If no matching records exist when a DELETE statement executes then no records will be deleted. The default behavior is what you want... delete matching records if they exist.

How to delete a record from table when dealing with two different tables

3 Answers3

Edit: to make it more clear

Edit 2: (to show UNION chg)