PDO delete mysql records from multiple tables where the id is the same

[cobusbo]

Hi I got 3 tables

 

Table 1

id

room

pin

creator

mxitid

time

 

 

Table 2

id

roomid

user

message

time

mxitid

 

Table 3

id

user

mxitid

room

roomid

rank

kick

unid

 

Each room I create I place the new epoch time of when the room expire in Table 1 time field. But now im trying to create a script to check if my current time is bigger than the time in the Table 1 time field and if it is so it should delete the row in table 1, the rows in table 2 with the same roomid as the id of table 1 and the rows in table 3 with the same roomid as the id of table 1

 

How can I loop through all the records to delete the expired rooms info using PDO mysql?

Edited December 10, 2015 by cobusbo

[Barand]

try

DELETE table1, table2, table3
FROM table1
    INNER JOIN table2 USING (roomid)
    INNER JOIN table3 USING (roomid)
WHERE table1.time < :currenttime

[cobusbo]

try

DELETE table1, table2, table3
FROM table1
    INNER JOIN table2 USING (roomid)
    INNER JOIN table3 USING (roomid)
WHERE table1.time < :currenttime

Doesn't seem to work

$sqldel = "DELETE Rooms, Room_users, Room_chats
FROM Rooms
    INNER JOIN Room_users USING (Roomid)
    INNER JOIN Room_chats USING (roomid)
WHERE Rooms.time < :currenttime";

    // use exec() because no results are returned
    $conn->exec($sqldel);

Here is my database tables layout

-- phpMyAdmin SQL Dump
-- version 4.0.10deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 10, 2015 at 05:42 PM
-- Server version: 5.5.46-0ubuntu0.14.04.2
-- PHP Version: 5.5.9-1ubuntu4.14


SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";




/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;


--
-- Database: `spamchat`
--


-- --------------------------------------------------------


--
-- Table structure for table `Rooms`
--


CREATE TABLE IF NOT EXISTS `Rooms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `room` varchar(30) NOT NULL,
  `pin` varchar(30) NOT NULL,
  `creator` varchar(30) NOT NULL,
  `mxitid` varchar(30) NOT NULL,
  `time` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;


--
-- Dumping data for table `Rooms`
--


INSERT INTO `Rooms` (`id`, `room`, `pin`, `creator`, `mxitid`, `time`) VALUES
(1, 'testing', '123', 'Cobusbo', 'Debater', ' 1449751215'),
(2, 'testing', '123', 'Cobusbo', 'Debater', '1449836897'),
(3, 'bleh', '123', 'Cobusbo', 'Debater', '1449754328');


-- --------------------------------------------------------


--
-- Table structure for table `Room_chats`
--


CREATE TABLE IF NOT EXISTS `Room_chats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `roomid` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `User` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `Message` varchar(1600) COLLATE utf8_unicode_ci NOT NULL,
  `Time` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `mxitid` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;


--
-- Dumping data for table `Room_chats`
--


INSERT INTO `Room_chats` (`id`, `roomid`, `User`, `Message`, `Time`, `mxitid`) VALUES
(1, '1', 'Cobusbo', '123', '1449769303', 'Debater');


-- --------------------------------------------------------


--
-- Table structure for table `Room_users`
--


CREATE TABLE IF NOT EXISTS `Room_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `User` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `mxitid` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `Room` varchar(160) COLLATE utf8_unicode_ci NOT NULL,
  `Roomid` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `Rank` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `kick` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `unid` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unid` (`unid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;


--
-- Dumping data for table `Room_users`
--


INSERT INTO `Room_users` (`id`, `User`, `mxitid`, `Room`, `Roomid`, `Rank`, `kick`, `unid`) VALUES
(1, 'Cobusbo', 'Debater', 'testing', '1', '1', '', 'Debater_1'),
(2, 'Cobusbo', 'Debater', 'testing', '2', '1', '', 'Debater_2'),
(4, 'Cobusbo', 'Debater', 'bleh', '3', '1', '', 'Debater_3');


/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Edited December 10, 2015 by cobusbo

[Barand]

If there are possibly no matching records in table2 or table3 then use LEFT JOINS.

 

Where are you binding the time parameter? As I've no idea what format the time is in I left that for you to set a value for "currenttime"

[cobusbo]

If there are possibly no matching records in table2 or table3 then use LEFT JOINS.

 

Where are you binding the time parameter? As I've no idea what format the time is in I left that for you to set a value for "currenttime"

yes table 2 Room_chats might not have a matching record the current time woud be

 $time = date("U");

epoch time

[Barand]

then

DELETE table1, table2, table3
FROM table1
    LEFT JOIN table2 USING (roomid)
    LEFT JOIN table3 USING (roomid)
WHERE table1.time < UNIX_TIMESTAMP()

[cobusbo]

then

DELETE table1, table2, table3
FROM table1
    LEFT JOIN table2 USING (roomid)
    LEFT JOIN table3 USING (roomid)
WHERE table1.time < UNIX_TIMESTAMP()

I tried

 

$sqldel = "DELETE Rooms, Room_users, Room_chats
FROM Rooms
    LEFT JOIN Room_users USING (Roomid)
    LEFT JOIN Room_chats USING (roomid)
WHERE Rooms.time < UNIX_TIMESTAMP()";

    // use exec() because no results are returned
    $conn->exec($sqldel);

but getting the error

 

Connection failed: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Roomid' in 'from clause'

Can you please check entry post  #3 I posted an SQL dump of my database table setup...

[Barand]

your column names may be case-sensitive

[cobusbo]

your column names may be case-sensitive

 

Yes I checked the Roomid column name is in Caps just like in the query but I still get that error

[Barand]

Is it using caps in all tables? If not, why the inconsistency. (Why use caps at all?)

[cobusbo]

Is it using caps in all tables? If not, why the inconsistency. (Why use caps at all?)

 

Table 1 (Rooms)

id

time

 

Table 2 (Room_users)

Roomid

 

Table 3 (Room_chats)

roomid

 

this is the exact spelling and caps of the table names and column names.

 

I need to delete all rows from table1,2 and 3 where table 2 and 3 column is the same as the id of table 1 if the current time is bigger than the time column in table 1

 

I checked all the caps cant seem to find any problem with it.

[Barand]

I see, different in each table. Need to change the syntax

DELETE table1, table2, table3
FROM table1
    LEFT JOIN table2 ON Rooms.id = Room_users.Roomid
    LEFT JOIN table3 ON Rooms.id = Room_chats.roomid
WHERE table1.time < UNIX_TIMESTAMP()

[cobusbo]

Thank you very much, helpful as always