Solved

Select Distinct PHP

Posted on 2011-09-28
6
382 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

DevOps Toolchain Recommendations

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
insert into database through form with dynamic fields. 2 29
maybe no no httpd.conf 6 47
Php recording post 4 42
Using coldfusion <cfexecute> to restore mysql database 7 30
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
This article discusses four methods for overlaying images in a container on a web page
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
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 …

773 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