Jump to content

deleting data from multiple tables


witham

Recommended Posts

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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!
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.