deleting data from multiple tables

[witham]

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:

[code]$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");[/code]

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

[gmwebs]

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.

[url=http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html]MySQL Foreign Key Constraints[/url]

[witham]

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!

[gmwebs]

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.

[witham]

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?

[gmwebs]

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.

[url=http://dev.mysql.com/tech-resources/articles/storage-engine/part_1.html]Here[/url] is a good article discussing the different types of engines.

[witham]

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

[gmwebs]

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?

[gmwebs]

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

[witham]

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?

[gmwebs]

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

[gmwebs]

Oh... And is it Windows or Linux?

[witham]

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

[gmwebs]

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 [url=http://213.136.52.29/doc/refman/4.1/en/innodb-in-mysql-3-23.html]this link[/url] to configure it.

[witham]

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

[gmwebs]

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

[fenway]

You could, of course, just handle the cascading yourself in scriptl

[gmwebs]

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.

[witham]

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

[gmwebs]

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?

[fenway]

Didn't mean to subvert the intention to convert to InnoDB, but you get much more than just ACID compliance.  Indexing is very different, queries are different, and so on -- these are huge performance issues if you don't know how to handle them (e.g. COUNT(*) being an excellent example).  As for InnoDB itself, it's possible your server isn't set up to load InnoDB, you'll have to restart it without --skip-innodb, as mentioned earlier.

[witham]

The version is MySQL client version: 5.0.24a

When I run
SQL query:

SHOW INNODB STATUS

MySQL said: Documentation
#1235 - Cannot call SHOW INNODB STATUS because skip-innodb is defined

which confirms your earlier comment:

When I alter the table with the command "alter man engine=innodb" I get

Your SQL query has been executed successfully (Query took 0.7944 sec)

But the engine is still showing disabled on the engine tab and when I go ino operations for the table it still shows storage as myisam?
I now can't even remember where I found the ini file to comment out skip innodb!

[gmwebs]

The file is my.ini found in C:\Program Files\MySQL\MySQL Server 5.0. The InnoDB conversion will not work until you can get rid of skip-innodb.

[witham]

I am using xaamp so I do not have a independant mysql installation but it is really frustrating that I can't find the in file agian!! I 'll keep looking

[gmwebs]

The file should either be called my.ini or my.cnf and should be in the mysql\bin folder under your XAMPP installation. If you search your drives using the windows file search utility for either of these files, then you should find it.