Jump to content


Photo

deleting data from multiple tables


  • Please log in to reply
26 replies to this topic

#1 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 27 October 2006 - 03:32 PM

Hi I would really appreciate some help, I have three tables and in a database called 'man', 'prodname' & 'produse' and I have allowed users to amend, add & delete data but I am having particular trouble when a user deletes a manufacturer in as much as I also want to delete the associated data in the prodname table, the code I currently have is:

$Manufacturer = $_GET['Manufacturer'];

$sqlDELETE	= "DELETE from man where manid = '$Manufacturer';";


//put in the sql statement that will display the results

$sqlSELECT	= "SELECT * from man order by manname;";


// mysql_connect connects to the database server and returns a link to the the resource
$dblink = @mysql_connect("$dbhost","$dbuser","$dbpass") 
	or die("<p><b>Could not connect to database server: ($dbhost)</b></p>\n");

// mysql_select_db selects a database to use on the database server pointers to by $dblink
// the @ sign before the command supresses any error messages
@mysql_select_db ($dbname , $dblink)
	or die ("<p><b>Could not connect to database ($dbname)</b></p>\n");



// now execute the query to compare the table data to display and if the number of rows returned
// is less than zero send the user straight back to Addman.html as the entry is a duplicate

// now execute the next query to insert the data into the table

$result  = mysql_query($sqlDELETE, $dblink)
	or die("<p>Error Processing Query</p><hr /><p>".mysql_error()."</p>\n");

// now execute the next query to return the table data to display



$sqlDELETE	= "DELETE prodname
		FROM prodname AS p
		LEFT JOIN man AS m ON m.manid = p.manno
		WHERE m.manid IS NULL";

$result  = mysql_query($sqlDELETE, $dblink)
	or die("<p>Error Processing Query mick</p><hr /><p>".mysql_error()."</p>\n");


the manufacturer is deleted but the data in prodname is not.

#2 gmwebs

gmwebs
  • Members
  • PipPipPip
  • Advanced Member
  • 174 posts
  • LocationLondon

Posted 28 October 2006 - 11:16 AM

I think what you are referring to is foreign key constraints having an ON DELETE CASCADE parameter. The actual MySQL DB will then take care of removing associated data from the other tables.

MySQL Foreign Key Constraints

#3 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 28 October 2006 - 01:30 PM

Thanks for that, I have looked at the link you kindly included by cannot figure out where to place the "on delete cascade" command?
Could you please if possble elaborate a little more!

#4 gmwebs

gmwebs
  • Members
  • PipPipPip
  • Advanced Member
  • 174 posts
  • LocationLondon

Posted 28 October 2006 - 01:48 PM

Yeah sure... I guess the best way for you to add these constraints is by using PHPMyAdmin as your tables already exist. Your tables would have to be InnoDB and not MyISAM, but you can convert them using PHPMyAdmin too.

If you take the following example... Let's say we have a table called USERS which has 3 fields - id, name, surname. Then let's say we have another table called USERDETAILS which has 4 fields - id, user_id, age, height.

Let's say that if we delete a user record from the USERS table, then we want the details pertaining to that user to be deleted from the USERDETAILS table automatically.

The USERDETAILS table has a foreign key called user_ID which corresponds to the id column in the USERS table. If you click on the USERDETAILS table in PHPMyAdmin on the left and then click on the link which says Relation view on the right hand side, it will take you to a page where you can define the relationship that user_id has with the USERS.id column.

So if you find the user_id field on the left, then choose users->id in the first drop-down box, then choose cascade in the second drop-down box, it should do exactly what we are trying to do.

#5 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 28 October 2006 - 02:03 PM

great I got to the relation view but no second drop down box, although I think my storage is in MyISAM will it cause any problems if I use InnoDB instead? How do I change this as I don't have InnoDB listed in the drop down box?

#6 gmwebs

gmwebs
  • Members
  • PipPipPip
  • Advanced Member
  • 174 posts
  • LocationLondon

Posted 28 October 2006 - 02:20 PM

I don't want to be the one to tell you to convert to InnoDB and then you run into problems! ;) If this is a development DB and not a production one, then as long as you have a way to restore the DB and data, I can't see there being a problem with trying it. On the Operations tab for your table, you should be able to choose InnoDB from the table type drop-down box.

Here is a good article discussing the different types of engines.

#7 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 28 October 2006 - 02:24 PM

That is the problem, i only have three further options that are: MEMORY, ARCHIVE & MRG_MYISAM?

#8 gmwebs

gmwebs
  • Members
  • PipPipPip
  • Advanced Member
  • 174 posts
  • LocationLondon

Posted 28 October 2006 - 02:36 PM

mmmm.... strange... You could always try to convert the engine manually then, using ALTER TABLE tablename ENGINE=INNODB in a query window. What version of MySQL and PHPMyAdmin are you using?

#9 gmwebs

gmwebs
  • Members
  • PipPipPip
  • Advanced Member
  • 174 posts
  • LocationLondon

Posted 28 October 2006 - 02:38 PM

You can also run SHOW INNODB STATUS in a query window to determing if support for InnoDB is enabled.

#10 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 28 October 2006 - 02:53 PM

On the engines tab myphpadmin shows that  Supports transactions, row-level locking, and foreign keys

InnoDB has been disabled for this MySQL server. So it woud seem that it is there but how do I enable it?

#11 gmwebs

gmwebs
  • Members
  • PipPipPip
  • Advanced Member
  • 174 posts
  • LocationLondon

Posted 28 October 2006 - 03:01 PM

Do you have full access to the server, or is it on a shared host?

#12 gmwebs

gmwebs
  • Members
  • PipPipPip
  • Advanced Member
  • 174 posts
  • LocationLondon

Posted 28 October 2006 - 03:01 PM

Oh... And is it Windows or Linux?

#13 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 28 October 2006 - 03:05 PM

Yes it is a hobby database on a localhost so I full access and it is on windows.

#14 gmwebs

gmwebs
  • Members
  • PipPipPip
  • Advanced Member
  • 174 posts
  • LocationLondon

Posted 28 October 2006 - 03:14 PM

If you have MySQL version 4.0 or later, InnoDB is enabled by default. You might have a skip-innodb set in your my.ini file, so best check that. It should be in the root of your MySQL server location on your install drive. Open it in a text editor and look for that command. Comment it out if it is uncommented, and then restart your MySQL service.

If your version is below 4.0, then I first suggest you consider upgrading, but failing that, follow this link to configure it.

#15 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 28 October 2006 - 03:34 PM

skip innodb is there, how do I comment it out?

#16 gmwebs

gmwebs
  • Members
  • PipPipPip
  • Advanced Member
  • 174 posts
  • LocationLondon

Posted 28 October 2006 - 03:39 PM

You would just need to put a # in front of it... Don't forget to restart your MySQL service, or just reboot your computer.

#17 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 28 October 2006 - 04:14 PM

You could, of course, just handle the cascading yourself in scriptl
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#18 gmwebs

gmwebs
  • Members
  • PipPipPip
  • Advanced Member
  • 174 posts
  • LocationLondon

Posted 28 October 2006 - 04:24 PM

True Fenway, but then you would not have all the benefits of the DB handling referential integrity for you. My honest opinion is that the DB is the best place to handle it. As you would not be reliant on code to enforce these rules, you are able to change languages and platforms at any stage, secure in the knowledge that your data is safe and all your rules are taken care of.

#19 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 28 October 2006 - 04:41 PM

Thank you for joining us Fenway, I must be missing something because I have commented out this skp innodb and restarted the computer but it still shows it as inactive on the engines tab of myphpadmin?
I would appreciate both approaches as it all adds to my lmited knowledge

#20 gmwebs

gmwebs
  • Members
  • PipPipPip
  • Advanced Member
  • 174 posts
  • LocationLondon

Posted 28 October 2006 - 04:44 PM

Have you tried to alter the table directly as I explained earlier? There was an issue quite a while back with PHPMyAdmin where it didn't show the InnoDB option, but that was fixed in a later version. What version do you have?




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users