Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How can i get the select to use and return DISTINCT

Posted on 2004-10-21
13
Medium Priority
?
193 Views
Last Modified: 2006-11-17
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
Comment
Question by:aot2002
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
  • +1
13 Comments
 
LVL 4

Assisted Solution

by:brunomsilva
brunomsilva earned 200 total points
ID: 12371378
what happens exactly when you try: $querystatement = "Select DISTINCT(*) from model_years6602"; //query statement
0
 
LVL 49

Expert Comment

by:Roonaan
ID: 12371430
Well you could always specify the fiels you need:

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

Should work fine.

Regards

-r-
0
 
LVL 1

Author Comment

by:aot2002
ID: 12371627
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Expert Comment

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

-r-
0
 
LVL 1

Author Comment

by:aot2002
ID: 12371679
yes but i did get an error
somehow its deciding to work now !!!

Dang computer !
0
 
LVL 1

Author Comment

by:aot2002
ID: 12371707
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
 
LVL 1

Author Comment

by:aot2002
ID: 12371711
SELECT DISTINCT MAKE FROM drilldown6602 order by MAKE

now this works GREAT
0
 
LVL 1

Author Comment

by:aot2002
ID: 12371716
how can i get it to work with your query ??????
0
 
LVL 4

Expert Comment

by:brunomsilva
ID: 12371986
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
 
LVL 1

Author Comment

by:aot2002
ID: 12372822
i want just the DANG column showing once the MAKE !!!!

its driving me insane !
0
 
LVL 49

Assisted Solution

by:Roonaan
Roonaan earned 400 total points
ID: 12372884
<?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
 
LVL 2

Accepted Solution

by:
unreal400 earned 400 total points
ID: 12438629
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
 
LVL 1

Author Comment

by:aot2002
ID: 12438749
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question