Results 1 to 9 of 9
  1. #1
    Join Date
    May 2013
    Posts
    14

    Question 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

    Thank you

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your MySQL query should look something like this:
    Code:
    SELECT image_id
         , image_name
    FROM   images
    WHERE  image_name LIKE '%yellow%'
    ORDER
        BY image_name
    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.

    First of all I would definitely advise you to start by producing something with a search button. The reason for this is that the functionality you are requesting requires JavaScript, which won't be enabled for all your users, so you need to ensure that these people can still use the search (albeit with lessened functionality).

    I mentioned JavaScript up there. The thing you want to Google around for in this instance is AJAX, which stands for Asynchronous JavaScript and XML.

    A good little introduction to AJAX can be found here: AJAX Tutorial

    Have a go and post back if you get stuck
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    AJAX, PHP and database access: PHP Example AJAX and MySQL
    George
    Home | Blog

  4. #4
    Join Date
    May 2013
    Posts
    14

    reply

    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)

    Andrew

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Genuinely your best bet it to do the work with a submit button first. Once you have this working then you already have the foundations to get it working without
    George
    Home | Blog

  6. #6
    Join Date
    May 2013
    Posts
    14
    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:

    BEGIN
    SELECT image_id
    , image_name
    FROM image_infomation
    WHERE image_name LIKE 'imageName'
    ORDER BY image_name
    LIMIT 0, 10
    ;
    END

    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?

    Thanks in advance

    Andrew

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You were close with your stored procedure, but there was a couple of teeny mistakes.
    Try this (air code):
    Code:
    DELIMITER //
    
    CREATE PROCEDURE getImage (
       IN imageName varchar(255)
    )
    BEGIN
      SELECT image_id
           , image_name
      FROM   image_infomation
      WHERE  image_name LIKE Concat('%', imageName, '%')
      ORDER
          BY image_name
      LIMIT 0, 10;
    END //
    
    DELIMITER ;
    George
    Home | Blog

  8. #8
    Join Date
    May 2013
    Posts
    14
    thanks for that, I owe you a pint x

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I've written a bit of a tutorial for the AJAX search request in this thread:

    JQuery AJAX Tutorial
    George
    Home | Blog

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •