Whats the best way to build a query that will only return one of each result

[monkeytooth]

Don't know if I know how to explain this well.

I have a DB with 400,000 + records in it...

 

I want to create a drop down menu that populates based off a query in that DB, without having it pull all 400,000 records to do that. The column I am looking to populate the drop down with is a column that has many many doubles, triples, quadruples, etc.. I think all in all there is maybe of the 400,000+ rows 300 or so unique values I only want to pull based on the unique value one of each

 

I hope this makes sense I know im not the best explaining what I want. If it helps I am also working on an auto-complete concept for it as well. So I wont have to use the drop down as above listed. I just still need to figure out how to pull one of each vs 400,000

[premiso]

Sounds like the DB design is way flawed. Look into MySQL Distinct and or Group By.

[monkeytooth]

I had actually just found distinct in another search attempt, before coming back here.. my issue is now that I am getting an error..

 

place is the actual table name

item is the row im looking to pull from.

 

$query = $dbconnAC->query("SELECT DISTINCT item  FROM place WHERE item  LIKE '$queryString%' LIMIT 10");

 

the error I am getting is

Fatal error: Call to a member function query() on a non-object in /home/campus/public_html/v10/asf/sub_autocomplete.php on line 9

 

Line 9 is the code snippet above. What did I do wrong?

[premiso]

Are you sure that you instantiated the class $dbconnAC prior to calling this function?

 

Without more code, I cannot help much further than to tell you that error means you did not instantiate the $dbconnAC class and is throwing that error because $dbconnAC is not a class object.

[monkeytooth]

$dbconnAC = mysql_connect($sq_hst, $sq_unme, $sq_pzwrd) or die('Error, AC101: Transaction failed.');
mysql_select_db($database) or die('Error, AC102: Transaction failed.');
    if(isset($_POST['queryString'])) {
        $queryString = $_POST['queryString'];
        if(strlen($queryString) >0) {
        $query = $dbconnAC->query("SELECT DISTINCT item  FROM place WHERE item  LIKE '$queryString%' LIMIT 10");
        if($query) {
            while ($result = $query ->fetch_object()) {
                echo '<li onclick="fill('.$result->value.');">'.$result->value.'</li>';
            }
        } else {
            echo 'ERROR: There was a problem with the query.';
        }
    } else {
    } 
} else {

}
mysql_close($dbconnAC);

[premiso]

You need to use mysql_query instead, as $dbconnAC is not an object but a resource link to your MySQL connection.

 

$dbconnAC = mysql_connect($sq_hst, $sq_unme, $sq_pzwrd) or trigger_error('Error, AC101: Transaction failed.');
mysql_select_db($database) or trigger_error('Error, AC102: Transaction failed.');
    if(isset($_POST['queryString'])) {
        $queryString = $_POST['queryString'];
        if(strlen($queryString) >0) {
        $query = mysql_query("SELECT DISTINCT item  FROM place WHERE item  LIKE '$queryString%' LIMIT 10") or trigger_error("Query Failed: " . mysql_error());
        if($query) {
            while ($result = $query ->fetch_object()) {
                echo '<li onclick="fill('.$result->value.');">'.$result->value.'</li>';
            }
        } else {
            echo 'ERROR: There was a problem with the query.';
        }
    } else {
    }
} else {

}
mysql_close($dbconnAC);