PHP multiple entries the same but I just need it once

MatthewBOnline · August 25, 2011, 8:27am

Hi,

I am trying to get a heading for compatibility once with a list of products that are compatible below it. Below is the PHP code for the bit where I want the compatibility shown once.

The table includes info like:
Product; Compatible
iphone 4 car charger ; iphone 4
iphone 4 mains charger; iphone 4
iphone 4 usb cable; iphone 4

if(isset($selcat5)) {
   $res = mysql_query("SELECT name, compatible FROM products WHERE compatible = '".$_GET['compatible']."'" );
  while($row = mysql_fetch_array($res)) {
echo "<div id=\\"leftcol\\">";
echo "<div id=\\"sidenav\\">";
echo "<div id=\\"sidenav2\\">";
echo "<ul>";
echo "<li><a href='product.php?productid=".$row['name']."'>".$row['compatible']."</a></li>"; 

}

My problem is my php provides me with a list that says “iphone 4” 3 times in the case above.

How can I make it so that when there are multiple entries with the same value it just uses it once? I did think may be I could remove the .$row bits of code but before I tried that I wanted to get an expert view.

Matt.

goldensona · August 25, 2011, 8:58am

i think my answer is useful to you.otherwise leave it .you may use distinct in select query .

MatthewBOnline · August 25, 2011, 9:02am

How do I use distinct?

goldensona · August 25, 2011, 9:13am

use like this

SELECT distinct(name) FROM products WHERE compatible = '".$_GET['compatible']."'

MatthewBOnline · August 25, 2011, 9:22am

In my code I am using “name, compatible”

2 bits of data

I tried this but it does not work:

$res = mysql_query("SELECT distinct (compatible), name FROM products WHERE compatible = '".$_GET['compatible']."'" );

should I SELECT both with brackets? "(name),(compatible) or something else?

Matt

goldensona · August 25, 2011, 9:28am

what your expected result?

if there is any same name in compatible it will return distinct rows. did u check whether there is any error return from query?

both data are from same table ,we can use any one data as distinct

r937 · August 25, 2011, 9:38am

DISTINCT is ~not~ a function

placing the column that comes after the word DISTINCT in parentheses does ~not~ restrict the distinctness to that column

DISTINCT applies to ~all~ columns in the SELECT clause, and ensures that there are no duplicate rows (i.e. combination of all columns)

if you have multiple compatible values in the results and want only one, you have to have some rule for deciding which name to show along with each compatible – lowest collating name? highest collating name? longest name? shortest name?

pick one…

MatthewBOnline · August 25, 2011, 9:43am

All compatible values are the same

The table includes info like:
Product; Compatible
iphone 4 car charger ; iphone 4
iphone 4 mains charger; iphone 4
iphone 4 usb cable; iphone 4

The DISTINCT way is not working. As I said in my first post I need the iphone 4 once. I do not have multiple compatible values. I need some code to say ‘if all results are the same, use only once’!

Any ideas?

You suggest lowest collating name? highest collating name? longest name? shortest name?

What about identical name only once - can it be done?

Matt.

r937 · August 25, 2011, 9:52am

yes, you do –

Product; Compatible
iphone 4 car charger ; iphone 4
iphone 4 mains charger; iphone 4
iphone 4 usb cable; iphone 4

you have “iphone 4” three times right there

if you only want to show “iphone 4” once, then you must decide which of the product names to show along with it

unless, of course, you don’t actually need to show one of the product names – then the problem becomes somewhat simpler

MatthewBOnline · August 25, 2011, 10:07am

Unfortunately I need multiple products listed. I am using this code:


if(isset($selcat5)) {
   $res = mysql_query("SELECT DISTINCT (name), (compatible) FROM products WHERE compatible = '".$_GET['compatible']."'" );
  while($row = mysql_fetch_array($res)) {
echo "<div id=\\"leftcol\\">";
echo "<div id=\\"sidenav\\">";
echo "<div id=\\"sidenav2\\">";
echo "<ul>";
echo "<li><a href='product.php?productid=".$row['name']."'>".$row['compatible']."</a></li>"; 

}


echo "</ul>";
echo "</div>";
echo "<ul>";
  $smh = mysql_query("SELECT productid,name FROM products WHERE compatible = '".$_GET['compatible']."'");
  while($row = mysql_fetch_array($smh)){
echo "<li><a href='accessory.php?productid=".$row['productid']."'>".$row['name']."</a></li>";

  unset($cats[$selcat4]); //Gets rid of it for later.
}  
}

What happens if I remove the .$row bits of code on line 8!?!

Matt.

r937 · August 25, 2011, 10:10am

okay, in that case, please explain what results you want for these rows of data –

Product; Compatible
iphone 4 car charger ; iphone 4
iphone 4 mains charger; iphone 4
iphone 4 usb cable; iphone 4

MatthewBOnline · August 25, 2011, 10:13am

From those rows of data I want this:

iphone 4
iphone 4 car charger
iphone 4 mains charger
iphone 4 usb cable

It will form part of a navigation bar. Notice the compatible product name is listed once, first in the list.

I will delete iphone 4 from the products so it will read:

iphone 4
car charger
mains charger
usb cable
Matt.

r937 · August 25, 2011, 10:19am

SELECT compatible
     , name 
  FROM products 
UNION ALL
SELECT DISTINCT compatible
     , ' ' 
  FROM products 
ORDER
    BY compatible
     , name

MatthewBOnline · August 25, 2011, 10:31am

This I understand:

SELECT compatible, name FROM products UNION ALL

But is this another select statement?

SELECT DISTINCT compatible, ' ' FROM products ORDER BY compatible, name

Or I am supposed to use both select statements together!?!

Matt.

r937 · August 25, 2011, 10:34am

the whole thing, consisting of two SELECT statements and an ORDER BY clause, is called a union query

test it separately in mysql before embedding it into your php code

MatthewBOnline · August 25, 2011, 10:45am

Do I add this on the end?

WHERE compatible = '".$_GET['compatible']."'" )

…because I am using the Get Function in my original SELECT statement

Matt.

r937 · August 25, 2011, 10:49am

so you want to do this for only one compatible at a time?

MatthewBOnline · August 25, 2011, 12:23pm

yes - exactly - in the navigation bar they might click iphone 4 and get the navigation above or they might click iphone 3 and get that list of accessories instead. But my problem is with the heading…like i say i cannot get it only once.,!

Matt.

r937 · August 25, 2011, 12:44pm

okay, i’m not a php developer so i can’t give you actual code, but let’s go back to what you said you wanted –

the first line, which contains the compatible name, you don’t have to retrieve this from the database because you already know what it is – it’s the value in your GET

the remaining lines simply pull out the products for that compatible, and here all you need is a simple query, no DISTINCT required –

SELECT name
  FROM products
 WHERE compatible = '".$_GET['compatible']."'" )

Topic		Replies	Views
Get more than 1 data entry from DB using PHP PHP	10	952	September 11, 2018
Already exist dont display PHP	14	1131	August 4, 2017
MySQL select distinct and return all data in row Databases	6	3537	December 9, 2019
Populate select list from database PHP	2	1497	July 20, 2011
Reusing an Array PHP	5	687	October 14, 2011

PHP multiple entries the same but I just need it once

Related topics