Hmm, FKs definately do work (I know, I use them in a legacy app at work under MySQL). I'm curious that the example doesn't work for you though.. Have you checked to see that foreign_key_checks aren't set to 0?
If they are enabled I'd be surprised and interested enough to do some testing.
Using PHPMyAdmin I went to the engine tab and then on InnoDB.
It states "Supports transactions, row-level locking, and foreign keys".
But searching the website using the Ctrl+F string search no other "foreign" was found.
I don't think my hosting copy has allowed support for this. Not sure why that is ...
I just found this out, and I wanted to share with the rest of the world, as I thought it was pretty god damn cool of the PHP engine to be able to handle things such as this. It turns out that PHP can take any UTF-8 charater as a function name. So consequently, this works:
How cool is that! And it out puts the correct answer of 48 when 'cast' to hex it returns 30. I think this is awesome, but that's just me I'm sure. Never the less, a delta (Δ) function in PHP.
I understand Foreign Keys (FK) now, thanks to TAA pointing me in the right direction, but now I've discovered something interesting that I've never seen in MySQL. Sub-Query's, and I think I can do some things that I've wanted to do for some time now, Dynamic Foreign Keys.
I have 4 tables (appointed, class, elected, status) that I want to cross reference into a single table's (members) column. The values of the of 4 tables are time sensitive based off a history table (members_history). The desired result is that the query should output all members and the current appointed position or current elected position, class, and status within the members row and include additional information obtained from the foreign rows.
So instead of returning
`id`, `username`, `password`, `salt`, `name_first`, `name_last`, `date_join` & `date_leave`;
The query would return
`id`, `username`, `password`, `salt`, `name_prefix`, `name_first`, `name_last`, `hours_extra` `date_join`, `date_leave`, `appointed`, `class`, `elected` & `status`;
Wherever an added column does not have a current value in history it's result should be NULL.
Now I think I can do this with sub-querys, but have been so far banging my head against the keyboard. I'll take another swing at it later, but until then, anyone else willing to give it a shot, or attempt to point me in the right direction?
The structure of my SQL (no pun intended) tables is as follows:
CREATE TABLE IF NOT EXISTS `members` ( `id` mediumint(3) unsigned NOT NULL auto_increment COMMENT 'Members Unique Id', `username` varchar(32) collate utf8_bin NOT NULL COMMENT 'Mebers Username', `password` varchar(64) collate utf8_bin NOT NULL COMMENT 'Members Password Hash', `salt` varchar(32) collate utf8_bin NOT NULL COMMENT 'Members Password Salt', `name_first` varchar(32) collate utf8_bin NOT NULL COMMENT 'Members First Name', `name_last` varchar(32) collate utf8_bin NOT NULL COMMENT 'Members Last Name', `date_join` date NOT NULL COMMENT 'Members Join Date', `date_leave` date default NULL COMMENT 'Members Resgination Date (If Applicable)', PRIMARY KEY (`id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Members id in this table = mid in other tables';
CREATE TABLE IF NOT EXISTS `members:apointed` ( `id` tinyint(3) unsigned NOT NULL auto_increment COMMENT 'Unique value', `name_prefix` varchar(8) collate utf8_bin NOT NULL COMMENT 'Prefix Added to Members Name', `hours_extra` decimal(4,2) NOT NULL COMMENT 'Hours Given as Bonus for Holding this Posision.', `posisiton` varchar(40) collate utf8_bin NOT NULL COMMENT 'Name of the Posisiton', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Undefined within the SOP or By-Laws.';
CREATE TABLE IF NOT EXISTS `members:class` ( `id` tinyint(3) unsigned NOT NULL auto_increment COMMENT 'Unique Id', `class` varchar(8) collate utf8_bin NOT NULL COMMENT 'Unique Value', PRIMARY KEY (`id`), UNIQUE KEY `value` (`class`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Article I, Section 1 Subsection B: Classes of Membership';
CREATE TABLE IF NOT EXISTS `members:elected` ( `id` tinyint(3) unsigned NOT NULL auto_increment COMMENT 'Unique value', `name_prefix` varchar(8) collate utf8_bin NOT NULL COMMENT 'Prefix Added to Members Name', `hours_extra` decimal(4,2) NOT NULL COMMENT 'Hours Given as Bonus for Holding this Posision.', `posisiton` varchar(40) collate utf8_bin NOT NULL COMMENT 'Name of the Posisiton', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Article II';
CREATE TABLE IF NOT EXISTS `members:status` ( `id` tinyint(3) unsigned NOT NULL auto_increment COMMENT 'Bit''s Place', `status` varchar(16) collate utf8_bin NOT NULL COMMENT 'Categorie''s Name', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Article I, Section 1, Subsection A: Categories of Membership';
CREATE TABLE IF NOT EXISTS `members_history` ( `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique Id', `mid` tinyint(3) unsigned NOT NULL COMMENT 'Members Unique Id.', `table` enum('class','elected','appointed','status') NOT NULL COMMENT 'Name of Table that was Edited.', `value` tinyint(3) unsigned NOT NULL COMMENT 'Value', `start` date NOT NULL COMMENT 'Value''s Effect Date', `end` date default NULL COMMENT 'Value''s Expiration Date', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Member History';
`members_history`.`mid` is a FK for `id` in the members table, not every member will have history on them (but eventually they all will, as every member will have to have a class and status). `members_history`.`value` is a FK for `members:{`members_history`.`table`}`.`id`;
Sounds what you want is an outer join, but I will admit that I've not had the time to read your post properly... If I'm barking up the wrong tree I'll either add to this post after work or add a new one in
Well, I don't see any foreign keys. They would go after to your PRIMARY KEY constraint near the bottom. The foreign key tells which primary key of another table to connect to so you can join them. Something like..
CREATE TABLE class ( id integer, class_name text, PRIMARY KEY (id) );
CREATE TABLE member ( id integer, first_name text, last_name text, class_id integer, PRIMARY KEY (id), FOREIGN KEY (class_id) REFERENCES class (id) );
SELECT m.id, m.first_name, m.last_name, c.class_name FROM member m, class c WHERE m.class_id = c.id;
SELECT m.id, m.first_name, m.last_name, c.class_name FROM member m OUTER JOIN class c ON m.class_id = c.id;
Above is just an example and of course may not work. I'm a little rusty and TAA might have a better way but this will get you started/thinking. Note, make the foreign keys the same data type as the primary keys they reference. And of course placing them in the right spot to have logical flow all depends on your data. Just do some more reading into keys, joins, etc and we'll be OK.
Yeah, I've been reading, and reading and reading trying to brute force programming (typing statements, into the phpMyAdmin query window pressing submit and preying that it works.) So far no joy. The question really does fall back onto a question I've asked before: Is there a way to get data from one database based of the query from another database?
In this case the `member_history`.`table` tells me what other table I have to query to get the data I want. The possible values of `member_history`.`table` are 'class','elected','appointed','status', telling me to query either of these tables: `member:class`, `member:elected`, `member:appointed`, and `member:status`. From there we know that `member_history`.`value` will be the `id` from one of those tables.
Where this query gets only the current (not expired) values for all users.
SELECT * FROM `members_history` WHERE NOW() > UNIX_TIMESTAMP(`start`) AND UNIX_TIMESTAMP(`end`) < NOW() OR ISNULL(`end`);
I would like a query that get joins this data with the data from the other tables into the members table.
I offer everyone something of advice... Never try to interact directly with a USB HID device... It's gross as the documentation is few and far between, and it's very picky as to everything (particularly in windows, where it needs a specific set of Libraries, Include files, and everything)...
It made me >|this|< close to suicide... until I finally got it to work by performing some sort of exotic voodoo rituals around printed copies of source code.
did you try one of these?
a) java -Dlog4j.configuration=<path-to-config-file>/log4j.properties MyApp ...
b) Place the directory name in the class path. It will work fine.
I don't see why not as long as your crediting the devs for the work they did, I really don't see the devs biting your head off for doing that, they are reasonable people. On the other hand make sure it's cool with your professor that it's ok to use someone elses work.
We've talked many times about learning regular expression within this thread, I was thinking, how about we use the context of normal program flow constructs to explain regular expressions.
For example an if (expression) is equivalent to /expression/ ... I really don't know regular expression that well to make examples ... so I'm hope that you all know what I mean and can make your own examples and post them here. As 99% of the people who are going to learn regular expression are programmers, so why not then talk in the same language as them to explain it?
TAA pointing me in the right direction, but now I've discovered something interesting that I've never seen in MySQL. Sub-Query's, and I think I can do some things that I've wanted to do for some time now, Dynamic Foreign Keys.
