CODEWITHSUNDEEP
phpmysql
Shehary
Shehary

Reputation: 9992

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

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

Upvotes: 0

Views: 102

Answers (3)

Drew
Drew

Reputation: 24959

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

Upvotes: 1

David
David

Reputation: 218847

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.

Upvotes: 0

Halcyon
Halcyon

Reputation: 57729

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.

Upvotes: 0

Related Questions