Re: [PHP-DB] Help with a query please

[heilo]

Hi!

I hope, I understood you right: You want to delete a user completely from
your database?
If yes, you can just do it step by step (I hope the last step - putting all
the queries together works):

$uid = 1;

$qry = 'SELECT `Allocation_ID` FROM `WMS_Allocations` WHERE
`User_ID`='.$uid;
$ent = mysql_fetch_array(mysql_query($qry));

$del_qry1 = 'DELETE FROM `WMS_Allocations` WHERE `User_ID`='.$uid;
$del_qry2 = 'DELETE FROM `WMS_Bookings` WHERE
`Allocation_ID`='.$ent['Allocation_ID'];
$del_qry3 = 'DELETE FROM `WMS_User` WHERE `User_ID`='.$uid;

$del_qry = $del_qry1.";\n".$del_qry2.";\n".$del_qry3.";\n";

mysql_query($del_qry);


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Help with a query please

[shaun]

Hi,

I have the following table structure, as my installation of MySQL doesnt
support Foreign keys i have to maintain the referential integrity myself. So
if i delete a user, how can i make sure that the allocations from the
allocations table where the user_id is the same as the one being deleted and
that bookings are deleted where the allocations_id is the same as the
allocations deleted for that user_id?

Thanks for your help

# -- MySQL dump --
#
# Table structure for table 'WMS_Allocations'
#
CREATE TABLE WMS_Allocations (
  Allocation_ID int(11)  DEFAULT '' NOT NULL auto_increment,
  Project_ID int(11)  DEFAULT '0' NOT NULL ,
  User_ID int(11)  DEFAULT '0' NOT NULL ,
  PRIMARY KEY (Allocation_ID),
  KEY Project_ID (Project_ID,User_ID)
);

#
# Table structure for table 'WMS_Bookings'
#
CREATE TABLE WMS_Bookings (
  Booking_ID int(11)  DEFAULT '' NOT NULL auto_increment,
  Booking_Date date  DEFAULT '-00-00' NOT NULL ,
  PCT_address varchar(255),
  PCT_postcode varchar(255),
  PCT_telephone varchar(255),
  PCT_manager varchar(255),
  PCT_gp varchar(255),
  P_address varchar(255),
  P_postcode varchar(255),
  P_telephone varchar(255),
  P_manager varchar(255),
  P_gp varchar(255),
  Allocation_ID int(11),
  PRIMARY KEY (Booking_ID),
  KEY Allocation_ID (Allocation_ID)
);

#
# Table structure for table 'WMS_Projects'
#
CREATE TABLE WMS_Projects (
  Project_ID int(11)  DEFAULT '' NOT NULL auto_increment,
  Project_Name varchar(255),
  PRIMARY KEY (Project_ID)
);

#
# Table structure for table 'WMS_User'
#
CREATE TABLE WMS_User (
  User_ID int(11)  DEFAULT '' NOT NULL auto_increment,
  User_Username varchar(100)  DEFAULT '' NOT NULL ,
  User_Password varchar(100)  DEFAULT '' NOT NULL ,
  User_Name varchar(100)  DEFAULT '' NOT NULL ,
  User_Type int(11)  DEFAULT '0' NOT NULL ,
  User_Email varchar(100),
  PRIMARY KEY (User_ID),
  UNIQUE User_Username (User_Username)
);




-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php