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

x
?
Solved

Select Distinct PHP

Posted on 2011-09-28
6
Medium Priority
?
425 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
[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
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 2000 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 111

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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