select mysql myisam table by secondary key
Posted on 2014-02-01
I have been asked to recreate a 10-year old access system in mySQL and PHP.
The data has been extracted from access and 30 tables created in a mysql database on a Centos linux server. I have written a dozen or so php scripts which are working but have now hit a problem.
I have a table of clients with primary key client_number and a tbl of client_notes with primary key note_id and secondary key client_number.
I have an update program to which I enter a client_id 'sel_id' which has a mySQL SELECT FROM client WHERE client_number='sel_id' followed by a mysql SELECT FROM client_notes WHERE client_number='sel_id'.
Then I create a form, update the data and then REPLACE INTO client and client_notes.
client updates correctly but every time a new record is created in client_notes
I have confirmed that the initial SELECT FROM client_notes fails to read the record I want.
(There is actually a third table involved as well with primary key client_number and that SELECTs correctly and REPLACEs correctly.
Surely I should be able to SELECT by the secondary key.
I've tried googling for an answer but all I can find is arguments about INNODB in place of MySQL