Unanswered: using a text field to search a recordset
Hello, apologies if this is a) in the wrong group or b) a little basic, I'm a newbie working for a charity and don't have anywhere else to turn.
I have a php based site linked to a MySQL database and all works lovely.
There's a table called 'images' which has two columns, image_id and image_name.
The design of this website calls for a text box on the left of the page and on the right a list of image names alphabetically, with a maximum of ten displayed.
Very simply what I want to be able to do is type in the text field, and filter the results on the right so for e.g. if the list on the right has the colours of the rainbow, as I type 'Y E L L O W' I want the list on the right to filter with every keystroke so that only one is shown. I don't want a 'search' button and I do want the list to alter with each character entered.
I'm working in Dreamweaver CS6, and on the page in question have already set up a text field named 'search' in a form also naked 'search'. I have a recordset which gets the image names, but just don't know where to start. I'm guessing I need to edit the recordset set to somehow get a value from the text field 'search' but as to how to do it I'm lost.
Please help or point me in the direction of a good tutorial
WHERE image_name LIKE '%yellow%'
LIMIT 0, 10
You should make the above a stored procedure with a parameter to replace the currently hard-coded "yellow" to be your search parameter.
A good little introduction to AJAX can be found here: AJAX Tutorial
George, thank you so much for your time in replying!
I anticipated the lack of a 'search' button would be problematic, however I'm not too concerned with different users as the site will actually be local (it'll be in the waiting room of a charity and not hosted online) so the system will be configured to suit.
Perhaps I'll look at your example and links tonight and see what I come up with using a button and then try and replace it for no button. The key is however that the right hand 'results' want to change dynamically and a button just wont achieve that.
thanks and I'll come back if I struggle (which is highly likely)
Hey George, finally had time to have a play with your suggestion.
Stored procedures are something new, so looked into that, and found a good site at MySQL :: MySQL Stored procedure Tutorial from which with you code suggestion (which in fairness I wasn't far off) I created procedure through phpMyAdmin called getImage(IN 'imageName' VARCHAT(255)) )
The procedure is:
WHERE image_name LIKE 'imageName'
ORDER BY image_name
LIMIT 0, 10
When I try to execute this from within MyAdmin, I'm asked to provide a function and Value. I assume Value is the variable imageName will use, but no idea what to use as Function. Running this with no details for either I get no results.
I think I'm halfway there with the stored procedure but not sure what's stopping me.
My other question is now I do have a form on the php page with a text field called 'search' and a button called 'submit'... I'm not entirely sure how to attach the procedure to these. I assumed that once I wrote a procedure as above it would show up in the 'Stored Procedures' under my databases but no! Am I being stupid?
You were close with your stored procedure, but there was a couple of teeny mistakes.
Try this (air code):
CREATE PROCEDURE getImage (
IN imageName varchar(255)
WHERE image_name LIKE Concat('%', imageName, '%')
LIMIT 0, 10;