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

Select Distinct PHP

I have the following code that is currently not printing anything. Getting this to print the unique items in the type column is a preliminary step. After I get this working, I want to store the values in a way that would allow me to recursively say select * where type=x. The goal of this is to handle each type separately from one another. Any help in developing this code further would be greatly appreciated. I'm stuck.

Header:
<?php
$sql="SELECT Distinct type FROM $table"; 
	$result=mysql_query($sql); 
	
	$type=""; 
	
	while ($row=mysql_fetch_array($results)) { 
	
		$id=$row["type"]; 
		$type.=$id; 
	} 
?>

Open in new window


Body:
<?php print $type?>

Open in new window

0
mscn
Asked:
mscn
1 Solution
 
ienaxxxCommented:
type is a proprietary column name. you should enclose it in `` or [] if it's sql and not mysql.

try to echo mysql_error() after query execution
0
 
sonawanekiranCommented:
Try

$id=$row[0];
0
 
Pratima PharandeCommented:
<?php
$sql="SELECT Distinct type FROM $table";
      $result=mysql_query($sql);
      
      $typeval="";
      
      while ($row=mysql_fetch_array($results)) {
      
            $id=$row["type"];
            $typeval=$typeval.$id;
      }

echo $typeval;
?>
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Lukasz ChmielewskiCommented:
I think you may find useful storing the types in an array

<?php
$types = array();
$sql="SELECT Distinct type FROM $table"; 
      $result=mysql_query($sql); 
      
      while ($row=mysql_fetch_array($results)) { 
            $types[] = $row["type"]; 
      } 

print_r($types);
?>

Open in new window

0
 
ob2sCommented:
Hi,

You were using two different variables: $result and $results.  I made them the same and added a little code to make a connection (see below) for a quick test. That worked fine on my MySQL install.

Hope this helps.
 
<?php
        $link = mysql_connect('localhost', 'myusername', 'mypassword');
        if (!$link) {
                die('Could not connect: ' . mysql_error());
        }
        mysql_select_db('mydatabase');
        $table='foo';

        $sql="SELECT Distinct type FROM $table";  
        $results=mysql_query($sql);

        $type="";

        while ($row=mysql_fetch_array($results)) { 

                $id=$row['type'];
                $type.=$id;
        }
        print_r($type."\n");    
?>

Open in new window

0
 
Ray PaseurCommented:
It looks like the query string is created but is never fed to the mysql_query() function.  Here is an example code snippet that teaches how to do some of the basics in PHP and MySQL.  Please read it over and post back with any specific questions.  

And save yourself a lot of trouble going forward.  Add error_reporting(E_ALL) to the top of all your scripts.

Best regards, ~Ray
<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF THE SCRIPT GETS THIS FAR IT CAN DO QUERIES




// ESCAPE AN EXTERNAL DATA FIELD FOR USE IN MYSQL QUERIES
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, SHOW THE ERROR
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = mysql_num_rows($res);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/>$sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $num ROWS OF DATA ";
    echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
while ($row = mysql_fetch_assoc($res))
{
    var_dump($row);
}




// A WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
$sql = "SELECT COUNT(*) FROM my_table";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];
$fmt = number_format($num);
echo "<br/>THERE ARE $fmt ROWS IN THE TABLE";




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES ('$safe_username')";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
$id  = mysql_insert_id($db_connection);
echo "<br/>YOU JUST INSERTED A RECORD WITH AUTO_INCREMENT ID = $id";

Open in new window

0

Featured Post

Technology Partners: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now