Solved

How can i get the select to use and return DISTINCT

Posted on 2004-10-21
190 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
Question by:aot2002
    13 Comments
     
    LVL 4

    Assisted Solution

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

    Expert Comment

    by:Roonaan
    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
    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
     
    LVL 49

    Expert Comment

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

    -r-
    0
     
    LVL 1

    Author Comment

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

    Dang computer !
    0
     
    LVL 1

    Author Comment

    by:aot2002
    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
    SELECT DISTINCT MAKE FROM drilldown6602 order by MAKE

    now this works GREAT
    0
     
    LVL 1

    Author Comment

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

    Expert Comment

    by:brunomsilva
    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
    i want just the DANG column showing once the MAKE !!!!

    its driving me insane !
    0
     
    LVL 49

    Assisted Solution

    by:Roonaan
    <?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:
    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
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    Title # Comments Views Activity
    PHP MySQL and joining two table results 4 26
    sql statement - php 5 25
    Simple Mosaic Web Library 8 25
    True Type Font Files? 4 22
    This is a general how to create your own custom plugin system for your PHP application that you designed (or wish to extend a third party program to have plugin functionality that doesn't have it yet).  This is not how to make plugins for existing s…
    Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
    The viewer will learn how to dynamically set the form action using jQuery.
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    913 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now