altimit

I must admit, that is quite strange.Using phpMyAdmin, can you please verify that the engine being used is indeed InnoDB? Let us hope that there is no server configuration blocking the engine from being used, and automatically substituting MyISAM instead.I have done my testing on a live server with MySQL version 4.1.22. phpMyAdmin's version is 2.11.0, but the does not matter as I used SSH and connected via MySQL's console.Please also remember that InnoDB is transactional; you might want to create the tables first, before adding any data (in a separate phpMyAdmin command "instance").

Thanks for the quick response; It would seem that the Child ID is not currently being indexed, would that be correct? Kindly try the following set of statements: CREATE TABLE parent( id INT NOT NULL, PRIMARY KEY (id)) ENGINE = INNODB;CREATE TABLE child ( parent_id INT NOT NULL, INDEX (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ) ENGINE = INNODB;INSERT INTO parent VALUES (1);INSERT INTO child VALUES (2); My installation flags the final statement with an error "#1216 - Cannot add or update a child row: a foreign key constraint fails". How about yours?

Thanks very much.Yes this is quite a common scenario, but would boil down to the fact that MySQL treats NULLs as actual "values" (to put it very bluntly). I would suggest you add a "NOT NULL" clause to the Id's you are trying to associate (i.e., "id INT NOT NULL"); perhaps a good way of thinking on why the current scenario does not raise an exception is that the Child Id has successfully been matched against a NULL Parent Id.Did it do the trick?

Hi turbopowerdmaxsteel,A bit of a late reply, but I noticed no one made a response to your post. I do hope this is still relevant, but welcome to MySQL. Coming from an Oracle background, I know how you feel. I'm sure that (by now) you have already figured out why MySQL's behavior is as such, but for the benefit of others who may find this useful, here is an explanation.MySQL NULLs and empty values are different. When inserting new values, MySQL will use the default value based on your column type. In this case for CHARs, it will be an empty value. Had you provided a different default (on the table schema), it will be used instead of an empty value in your "INSERT INTO test VALUES();" statement. Given this, we see that there really is no issue in the "not null" constraint. Try inserting a NULL instead.As for Foreign Keys, it is available for all storage engines as of MySQL 5.2. For lower versions, only select engines such as InnoDB supports it. The storage engine can be chosen upon creating a table.Cheers.

Hi vujsa,The key here is in addition to maintaining a list (in the database) of each user's (hereby referred to as "key") items (hereby referred to as "value"), maintain a separate "master list" of all values associated with all the keys, sorted by their occurence. Hence, whenever a suggestion is needed to be made for a particular key, find a value in the master list that is not present in the key's list of values with the highest occurence.Note that this is global, all keys are considered regardless of how high key X and key Y are related. To make the system more specific and tailored to each user, the existing system as described above needs to be slightly modified and extended, and will be described in detail below. Be forewarned that, if improperly designed, this will take a lot of database space since you are attempting to match every user against every user; that would be N^2 matches for N keys.To reduce complexity, maintain a highest relation list that contains two fields: the key A, and the other key B most associated with A. You would need to provide an algorithm that gives a proper association value for two keys; a suggestion would be to take the number of matching values, and subtract it with the number of non-matching values to give the assocativity rating. This is very simple and fast to compute, but may not give the desired and most exact behavior depending on your data.Once we have a highest relation list, we use the master list to be the arbitrator when we have to make a suggestion for key A. The system will suggest a value which has the highest occurence in the master list, and has to be a value for key B, and not a value for key A. If there are no matches (i.e. keys A and B have the exact same values), the system may suggest a new value depending on an algorithm; a suggestion would be to take the value with the highest occurence in the master list which are not values of both key A and B.In cases where a key has changed values, we must recompute the highest relation list. This is less costly than having to compute the relation of arbitrary groups of users; while it can be done, it is best employed with a very fast back-end with good processing power, and even then, it would not scale well. The proposed design here is simple and effective, without requiring extreme amounts of power and storage.What do you think?

Hi pbolduc,PHP Directory and Filesystem Functions are your friends here. Have you considered looking at the PHP Manual for the relevant function already?In particular, the Directory Function "opendir" allows you to specify the directory you wish to access, and after which using "readdir" to get the filename of each suceeding entry, in a loop. In order for your script to know whether the returned entry by readdir is a file or a directory, you may use the Filesystem Function "is_dir", which returns a boolean true if the passed parameter corresponds to a directory entry.The PHP Manual entries for the functions I gave provide much sample code that will give you good insight on how to solve your problem; it should be very simple though as what you need is quite straightforward.Good luck.