#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2004
    Location
    Boston, MA USA
    Posts
    583
    Rep Power
    51

    Very Long Drop-down Selects


    Hi:

    I have a table of company names with about 8,000 records. I would like to create a selection tool to allow me to select the name of a company on a form. Obviously, the <select> drop-down is cumbersome because:
    1. it take a few seconds to load
    2. scrolling through 8,000 names is impractical

    type-ahead gets you to a name quickly but that does not change the wait time for the list to populate.

    What's a quick but simple way to have a type-ahead form field for a record search/select?

    Thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,122
    Rep Power
    539
    Autocomplete | jQuery UI

    Comments on this post

    • paulh1983 agrees : Thanks google ;)
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2004
    Location
    Boston, MA USA
    Posts
    583
    Rep Power
    51
    Originally Posted by NotionCommotion
    Thanks
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2004
    Location
    Boston, MA USA
    Posts
    583
    Rep Power
    51
    Originally Posted by NotionCommotion
    So I have the following code:
    PHP Code:
        <form action='' method='post'>
            <
    p><label>Company:</label><input type='text' name='company' value='' class='auto'></p>
        </
    form>
     
    <
    script type="text/javascript" src="http://code.jquery.com/jquery-1.9.1.min.js"></script>
    <script type="text/javascript" src="http://code.jquery.com/ui/1.10.1/jquery-ui.min.js"></script>    
    <script type="text/javascript">
    $(function() {
        
        //autocomplete
        $(".auto").autocomplete({
            source: "search.php",
            minLength: 1
        });                
    });
    </script> 
    and search.php looks like this:
    PHP Code:
    if (isset($_GET['term'])){
        
    $return_arr = array();
     
        try {
            
    $conn = new PDO("mysql:host=".DB_SERVER.";port=8889;dbname=".DB_NAMEDB_USERDB_PASSWORD);
            
    $conn->setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION);
            
            
    $stmt $conn->prepare('SELECT company_name FROM pbs_companies WHERE company_name LIKE :term');
            
    $stmt->execute(array('term' => '%'.$_GET['term'].'%'));
            
            while(
    $row $stmt->fetch()) {
                
    $return_arr[] =  $row['company_name'];
            }
     
        } catch(
    PDOException $e) {
            echo 
    'ERROR: ' $e->getMessage();
        }
     
        
    /* Toss back results as json encoded array. */
        
    echo json_encode($return_arr);

    This works really well and the input field populates nicely with filtered company names as I type charters.

    The question I have (and it's probably very basic) is that I want to return the record ID for the company for use by the rest of the routine. There may me multiple companies with the same company name but every record has a unique company_id.

    How can I access company_id using the autocomplete function?

    Thanks
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,082
    Rep Power
    381
    In which case there is a FLAW in your system, say there were two companies called "XYZ" one with id 1, and the other with id 2

    if i typed XY in the autocomplete and i was given two choices, how would I know which company I really wanted?

    in any case I dont think there could ever be TWO companies as they would get sued. i.e. if i created a company called samsung or apple, they wouldnt be too happy about it!
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2004
    Location
    Boston, MA USA
    Posts
    583
    Rep Power
    51
    Originally Posted by paulh1983
    In which case there is a FLAW in your system, say there were two companies called "XYZ" one with id 1, and the other with id 2

    if i typed XY in the autocomplete and i was given two choices, how would I know which company I really wanted?

    in any case I dont think there could ever be TWO companies as they would get sued. i.e. if i created a company called samsung or apple, they wouldnt be too happy about it!
    Thanks for the reply. The flaw isn't so much in my system as it is a potential reality. There could be two "Main Street Pizza's"; you never know. The user could hopefully tell them apart because the auto-complete might also show other information like city, or state, or contact name.

    That said, I did find the following on jQuery Autocomplete showing multiple fields... very helpful.
    jQuery UI Autocomplete Widget with PHP and MySQL | jensbits

    The problem I am having now is that I can't see to access a session variable for my MySQL select query.

    The code from the autocomplete looks like this:
    PHP Code:
            <script type="text/javascript">
            $(function() {
            
                $(
    '#abbrev').val("");
                
                $(
    "#company_name").autocomplete({
                    
    source"company_list.php",
                    
    minLength2,
                    
    select: function(eventui) {
                        $(
    '#company_id').val(ui.item.company_id);
                        $(
    '#company_zip').val(ui.item.company_zip);                    
                    }
                });
            });

            
    </script>

            Type a Name: 
                    <input type="text" id="company_name"  name="company_name" /> 
                    <input readonly="readonly" type="text" id="company_id" name="obj_co_id" maxlength="5" size="5"/>
                 <input type="hidden" id="form_submitted" name="form_submitted" value="true" /> 
    This calls "company_list.php" which looks like this:
    PHP Code:
    if ($conn)
    {
    //$mgt_co_id = $_SESSION['mgt_co_id']; 
    $mgt_co_id 1;
    $term mysql_real_escape_string($_GET['term']);
        
    $fetch mysql_query("SELECT * FROM pbs_companies where company_name like '%$term%' and mgt_co_id = $mgt_co_id ");
         
        
    /* Retrieve and store in array the results of the query.*/
        
    while ($row mysql_fetch_array($fetchMYSQL_ASSOC)) {
            
    $row_array['company_id'] = $row['company_id'];
            
    $row_array['value'] = $row['company_name'];
            
    $row_array['company_zip'] = $row['company_zip'];
             
            
    array_push($return_arr,$row_array);
        }
    }
     
    /* Free connection resources. */
    mysql_close($conn); 
    The session variable $_SESSION['mgt_co_id'] determines who has access to see what records so it needs to be part of the SELECT query. If I hard-code a value of "1" as shown here, the routine runs fine. As soon as I uncomment the assignment to the SESSION variable the routine fails... even if I leave the hard code line in place which so the value of $mgt_co_id remains hard coded at 1.

    I can't figure out why a simple variable assignment causes this to fail... I'm sure it's something pretty straight-forward that I am missing.

    Thoughts?

    Thanks
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2004
    Location
    Boston, MA USA
    Posts
    583
    Rep Power
    51
    Ah: I found the problem... I need a "session_start();" statement in the company_list.php file.

    I'm still confused... why do I need a duplicate "session_start" statement if there is already one in the file calling company_list.php?

    I found the answer, just not sure why.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,082
    Rep Power
    381
    because the two files are different, and they spawn different php process.. you can always get rid of duplication by including file..

    so your include file (include.php) might have session_start; error_logging on, login checks etc etc..

    then just include that in each script at the top. problem solved
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2004
    Location
    Boston, MA USA
    Posts
    583
    Rep Power
    51
    Ah, of course.

    I use includes all the time and, in fact, my "session_start()" comment is in a header file that I include at the top of all top-level files.

    my confusion was that since "company_list.php" was in a sense being called by the top level file (source: "company_list.php",) it was the same as in include and would inherit the session... Now I know otherwise.

    Thanks for the response.

IMN logo majestic logo threadwatch logo seochat tools logo