Re: Creating a trigger to search in another table...
OK, I've got it (with alot of help from Rene) all working now except for the multiplying the values by -1 if they are 'S' or 'W'.
mysql> select *
-> from gridtable;
+---------------+------------+------------+------------+-------------+
| title | lathigh | latlow | longhigh | longlow |
+---------------+------------+------------+------------+-------------+
| South Of Grid | -23.500000 | -90.000000 | 180.000000 | -180.000000 |
| North Of Grid | 90.000000 | -20.000000 | 180.000000 | -180.000000 |
| Area A | -20.000000 | -21.000000 | 32.000000 | 31.000000 |
| Area B | -21.000000 | -22.000000 | 32.000000 | 31.000000 |
| Area C | -22.000000 | -23.000000 | 32.000000 | 31.000000 |
| Area D | -23.000000 | -23.500000 | 32.000000 | 31.000000 |
| Area E | -20.000000 | -21.000000 | 33.000000 | 32.000000 |
| Area F | -21.000000 | -22.000000 | 33.000000 | 32.000000 |
| Area G | -22.000000 | -23.000000 | 33.000000 | 32.000000 |
| Area H | -23.000000 | -23.500000 | 33.000000 | 32.000000 |
| West Of Grid | -20.000000 | -23.500000 | 180.000000 | 33.000000 |
| East Of Grid | -20.000000 | -23.500000 | 31.000000 | -180.000000 |
+---------------+------------+------------+------------+-------------+
12 rows in set (0.00 sec)
mysql> INSERT INTO DATATABLE (ID, latvalue, NorthOrSouth, longvalue, EastOrWest,
Title)
-> VALUES ('00005', 22.5, 'S', 31.2, 'E', 'Banana');
Query OK, 1 row affected (0.04 sec)
**** Through looking at this INSERT query and the above GRIDTABLE, the values that should be inserted are as follows '00005', 22.5, 'S', 31.2, 'E' and 'Area C' as -22.5 is between -22 and -23, as well as 31.2 being between 31.0 and 32.0
mysql> select *
-> from datatable;
+-------+----------+--------------+-----------+------------+-------------------+
| ID | latvalue | NorthOrSouth | longvalue | EastOrWest | Title |
+-------+----------+--------------+-----------+------------+-------------------+
| 00001 | 20.30 | S | 31.20 | E | No title inserted |
| 00002 | 20.50 | S | 31.50 | E | North Of Grid |
| 00003 | 20.50 | S | 31.50 | E | North Of Grid |
| 00004 | 22.50 | S | 31.00 | E | North Of Grid |
| 00005 | 22.50 | S | 31.20 | E | North Of Grid |
+-------+----------+--------------+-----------+------------+-------------------+
5 rows in set (0.00 sec)
Yet come out as 'North Of Grid' which means that V1 and V2 are not being "negatived" is NorthOrSouth is 'S'. Can anyone help?
Q. What is big and has no (inter)-face?
A. My database.