Solved

Select Distinct PHP

Posted on 2011-09-28
6
361 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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 108

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
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…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

757 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

21 Experts available now in Live!

Get 1:1 Help Now