Solved

Select Distinct PHP

Posted on 2011-09-28
6
391 Views
Last Modified: 2012-05-12
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
Comment
Question by:mscn
6 Comments
 
LVL 10

Expert Comment

by:ienaxxx
ID: 36812914
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
 
LVL 17

Expert Comment

by:sonawanekiran
ID: 36812929
Try

$id=$row[0];
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36812933
<?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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 36813134
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
 
LVL 1

Accepted Solution

by:
ob2s earned 500 total points
ID: 36813146
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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36813896
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…

840 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