• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

How can i get the select to use and return DISTINCT

This works great but not when using a DISTINCT or anything other then SELECT * ......

How can i get the select to use and return DISTINCT





$name_of_table = "model_years6602"; //table connection
            $querystatement = "Select * from model_years6602"; //query statement
                $results = mysqlstatement($querystatement, $name_of_table); //query call function here !
            for($a=0; $a < $results['totalrows']; $a++){
                $model = $_REQUEST['MODEL'][$a];
           }






function mysqlstatement($querystatement, $name_of_table){

if(stristr($querystatement, $name_of_table)=="FALSE"){
echo "ERROR MYSQL STATEMENT TABLE AND QUERY DONT MATCH !";
exit;
}


$link = mysql_connect("localhost", "root", "***")
        or die("Could not connect: " . mysql_error());

     $database_name="***";
     mysql_select_db($database_name,$link) or die("Mysql Query Failed on Error Connecting to Database <br> Error WAS->".mysql_error());


    if($querystatement==""){
echo "Error in mysql Module Query Statment is Blank Check your query again !";
exit;
}

if(stristr($querystatement, "SELECT")=== false){

//must be an update or delete or insert statement
if(stristr($querystatement, "DELETE")!== false){
$outputtext = "Delete statement found";
$query_results = mysql_query($querystatement) or die("Mysql Query Failed on Error <br> $querystatement<br> Error WAS->".mysql_error()); //setup query for statement
return;
}

if(stristr($querystatement, "UPDATE")!== false){
$outputtext = "Update statement found";
$query_results = mysql_query($querystatement) or die("Mysql Query Failed on Error <br> $querystatement<br> Error WAS->".mysql_error()); //setup query for statement
return;
}

if(stristr($querystatement, "INSERT")!== false){
$outputtext = "INSERT statement found";
$query_results = mysql_query($querystatement) or die("Mysql Query Failed on Error <br> $querystatement<br> Error WAS->".mysql_error()); //setup query for statement
return;
}

//if this is reached then nothing legit for sql query found
    die("Invalid SQL STATEMENT found");


}else{
$outputtext = "select statment found"; //setup any text to return

$query_results = mysql_query($querystatement) or die("Mysql Query Failed on Error <br> $querystatement<br> Error WAS->".mysql_error()); //setup query for statement

$total_num_fields = mysql_num_fields($query_results); //get total number of fields in table

$table_field_name = mysql_list_fields($database_name, $name_of_table, $link); //of course list field names


for($a=0; $a < $total_num_fields; $a++){ //run through total number of fields and get field names >...
    $Field_name[] = mysql_field_name($table_field_name, $a);                      // > into an array !
}

$totalrows=0; //set

while($row = mysql_fetch_row($query_results)){ //run through total rows in query

for($counter=0; $counter < $total_num_fields; $counter++){ //go through rows and catch data

    $tmp = $Field_name[$counter]; //put into tmp variable for below array
    $multi_array[$tmp][] = $row[$counter]; //get the row according to counter and set it into name$tmp multi array !
}
$totalrows++;
}

$multi_array['totalrows'] = $totalrows;
return $multi_array;// $totalrows;
mysql_close($link);
}

}
0
aot2002
Asked:
aot2002
  • 7
  • 3
  • 2
  • +1
3 Solutions
 
brunomsilvaCommented:
what happens exactly when you try: $querystatement = "Select DISTINCT(*) from model_years6602"; //query statement
0
 
RoonaanCommented:
Well you could always specify the fiels you need:

SELECT distinct `field1`,` field2`, `field3`, `field4` FROM `model_years6602`

Should work fine.

Regards

-r-
0
 
aot2002Author Commented:
well sorta except that array_unique is not working right its skipping alot of items.
i want a DISTINCT list of the items in the MAKE column




$name_of_table = "drilldown6602"; //table connection
            $querystatement = "SELECT MODEL, ORIGIN, MAKE, MAKEMODEL FROM drilldown6602 order by MAKE";
                $results = mysqlstatement($querystatement, $name_of_table); //query statement

            for($a=0; $a < $results['totalrows']; $a++){
                $make[] = $results['MAKE'][$a];
            }
            $make2 = array_unique($make);
            for($a=0; $a < count($make2); $a++)
             echo "<a href='?whatcategory=models&make=".$make2[$a]."'>".$make2[$a]."</a><br>";


0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
RoonaanCommented:
well use the query below:
SELECT DISTINCT `MAKE`, `MODEL`, `ORIGIN`, `MAKEMODEL` FROM `drilldown6602` order by `MAKE`

-r-
0
 
aot2002Author Commented:
yes but i did get an error
somehow its deciding to work now !!!

Dang computer !
0
 
aot2002Author Commented:
ohh thats right no error but it returned all the rows


SELECT DISTINCT MAKE, ORIGIN, MODEL, MAKEMODEL FROM drilldown6602 order by MAKE

doesnt work
0
 
aot2002Author Commented:
SELECT DISTINCT MAKE FROM drilldown6602 order by MAKE

now this works GREAT
0
 
aot2002Author Commented:
how can i get it to work with your query ??????
0
 
brunomsilvaCommented:
what exactly do you want show? if you just show one make you will not show the other records with the same make but with different origin or models.
0
 
aot2002Author Commented:
i want just the DANG column showing once the MAKE !!!!

its driving me insane !
0
 
RoonaanCommented:
<?php
$m = mysql_query('SELECT DISTINCT `MAKE` FROM `drilldown6602` order by `MAKE');
while($make = mysql_fetch_assoc($m))
  echo '<a href="?whatcategory=models&make='.$make['MAKE'].'">'.$make['MAKE'].'</a><br>';
?>

Regards

-r-
0
 
unreal400Commented:
Your problem is with your SQL statement

The following line you used will return distinct make
SELECT DISTINCT MAKE FROM drilldown6602 order by MAKE

this statement just returns  the distinct groupings of make origin model etc  distinct appliest to everything not just column you place it infront of.

SELECT DISTINCT MAKE, ORIGIN, MODEL, MAKEMODEL FROM drilldown6602 order by MAKE


what are you trying to populate with this sql statement?  are you just trying to get distinct values for each column or  distinct make only?

Let me know which way you want it and I can give you the sql statement that will make it happen.

Kelly
0
 
aot2002Author Commented:
i found it was a cause of using a function for running basic Select statements not really the sql at all.
once i put in the sql basic connections and sql statements in a different page it worked as it should of.

The Distinct was throwing off my function i built months ago.....
anyone know of a good MySQL function with PHP to use ?

mine goes like this right now.

include 'mysqlquery_functions.php';
$results = mysqlstatment("SELECT * from mytable");

for($a=0; $a < $results['totalrows']; $a++){
echo $results['COLUMNNAMEHERE'][$a]."<br>";


         }


That makes each and every page much simplier !
except i never anticipated on any DISTINCT in a query which at that time i didnt need one.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 7
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now