mysql query returns only one word of string

Question

I am using php and javascript to create a dynamicly populated autocomplete search engine for my wordpress site.

Example: user wants to search for "Nike Shoes" so they type in Nike Shoes into the search engine and they should see Nike Shoes as a suggestion after they type the first 3 letters.

I am having an issue where only the word Nike shows up as a suggestion. This happens with any terms you type into the search engine. Only the first word of the string will show up. I am sure it's to do with the spaces in between words but not so sure on how to fix it.

Here is my code that is populating the autocomplete dynamically:

var availableTags = '<?php $result = mysql_query("select * from state"); 
                    $row = mysql_fetch_array($result); 
                    echo $row['name']; ?>'.split(" ");
                    $( "#title" ).autocomplete({
                    source: availableTags,
                    minLength: 3,
                    position: { my : "left top", at: "left top" },
                    appendTo: "#search-container"
                    });
                    });

I created this code from this site: http://www.tizag.com/mysqlTutorial/mysqlquery.php

If someone could please help me I would be very greatful.

Thanks,

Steve

You are only fetching a single row of results. Use [`json_encode`](http://stackoverflow.com/questions/383631/) instead of quoting the results manually. Stop writing new code with the ancient mysql_* functions. They are no longer maintained and community has begun the [deprecation process](http://news.php.net/php.internals/53799). Instead you should learn about prepared statements and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you care to learn, [here is a quite good PDO-related tutorial](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers). — DCoder, Sep 19 '12 at 05:29
Thankyou for your response, I appreciate your time. I will definitely put that material on the reading list, but for this instance in particular I am editing a wordpress plugin which already uses this old method, and I would prefer if possible to keep it all the same. Just some extra info, the Nike Shoes example is in 1 row, it is not Nike on 1 row and Shoes on another row if that helps. Thanks again — binga30, Sep 19 '12 at 05:44
If you do have a row that has 'nike shoes' then you should expect only one word since you are splitting by a space. It's hard to tell what's going on without the code that actually generates the display. — SimaPro, Sep 19 '12 at 07:54
Thanks for taking the time to reply SimaPro. I am quite new to mysql, and this code was not made by myself. I tried taking the split out but then i got NikeShoes, so logically the split should stay? But why does it break at Nike, how do you output the whole string Nike Shoes? I am using Google to search for other solutions to my question but it seems there are many ways to skin a cat. — binga30, Sep 20 '12 at 00:18

score 1 · Accepted Answer · answered Sep 19 '12 at 05:38

I am using this function for auto suggest list. you can use it with slight modification

function get_autosuggest_list($str=''){

        $q     = strtolower($str);
        $entry = array();   
        $i=0;
        if(!empty($q))
        {
            $entry[$i] = "A.fieldname like '".$q ."%'" ;
            $i++;
        }

        $sqlentry = "";
        $j=0;
        foreach($entry as $data)
        {

            if($j==0)
            $sqlentry  = " where ".$data;
            else
            $sqlentry .= " and ".$data;

            $j++;
        }
        $sql    = "SELECT DISTINCT field_name FROM `table` A ".$sqlentry." ";
        $query  = mysql_query($sql);
        $res    = mysql_fetch_array($query);

        foreach($res as $row)
        {
            $items = $row->fieldname;
            echo "$items\n";
        }

    }

Usage:

get_autosuggest_list(string);

Woah, never actually got back to this problem. My apologies. — binga30, Aug 11 '20 at 07:08

mysql query returns only one word of string

1 Answers1