how to show values from mysql database without repeating values in php

Question

When Iam showing database value from table its just repeating them according to content. First table which is content.

 id | category_id  |  name
-----------------------------
 1  |     2        |  Jason
 2  |     1        |  Richard
 3  |     2        |  John

category table:-

 id | name
 ---------
  1 | Manager
  2 | Employee

I use query:

$query=mysql_query("SELECT * FROM content");
while($row=mysql_fetch_array($query)){
                 $data[] = array('id'=> $row['id'],
                                'category_id' => $row['category_id'], 
                                'name' => $row['name']
                           );
            }
  for($i=0;$i<count($data);$i++){
     echo $data[$i]['category_id'];
     echo $data[$i]['name']."<br/>";
  }

  OUTPUT:-
  2 Jason 
  2 John
  1 Richard

But what i want is:-

 OUTPUT:-
 2 Jason John
 1 Richard

Now when echo out the result it shows me category_id 2 times having category_id=2 with its 2 name but what i want is to show only category_id 1 times and its corresponding 2 name of category_id=2. Please help.

mysql_query is an obsolete interface and should not be used in new applications as it's being removed in future versions of PHP. A modern replacement like PDO is not hard to learn. If you're new to PHP, a guide like PHP The Right Way can help explain best practices. — tadman
– tadman, Commented Oct 31, 2014 at 1:08
@tadman yeah i get that but i want to do with using mysql_query. — Vipin
– Vipin, Commented Oct 31, 2014 at 1:09
"Now when echo out the result it shows me category_id 2 times having category_id=2 with its 2 name but what i want is to show only category_id 1 times and its corresponding 2 name of category_id=2." - I'm having trouble understanding this. If you don't want repeats, use DISTINCT. — Funk Forty Niner
– Funk Forty Niner, Commented Oct 31, 2014 at 1:12
Use DISTINCT in your SELECT - Edit: Wait, there's something else, but I'll have to think about it. — Funk Forty Niner
– Funk Forty Niner, Commented Oct 31, 2014 at 1:18

Funk Forty Niner · Accepted Answer · 2014-10-31 03:07:51Z

2

Try this approach, it worked for me:

$query = mysql_query("SELECT COUNT(*), category_id, name, 

 GROUP_CONCAT(name separator ' ') as name FROM content  
 WHERE category_id=1 OR category_id=2 GROUP BY category_id 
 ORDER BY category_id DESC");

    while($row=mysql_fetch_array($query)){

        echo $row['category_id'] . " " . $row['name'] . "<br>";

    }

Plus, just a quick note about mysql_* functions being deprecated.

Use mysqli with prepared statements, or PDO with prepared statements, they're much safer.

edited Oct 31, 2014 at 3:07

answered Oct 31, 2014 at 2:52

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wolfgangwalther · Accepted Answer · 2014-10-31 01:41:16Z

1

Use this query:

SELECT id, category_id, GROUP_CONCAT(name SEPARATOR ' ') AS name
FROM content
GROUP BY category_id

See here for some quick tutorials on how to use GROUP BY and GROUP_CONCAT:
w3schools.com / GROUP BY
mysqltutorial.org / GROUP_CONCAT

edited Oct 31, 2014 at 1:41

answered Oct 31, 2014 at 1:32

wolfgangwalther

1,2367 silver badges15 bronze badges

1 Comment

wolfgangwalther Over a year ago

Hi @Jason if this or any answer has solved your question please consider accepting it by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. There is no obligation to do this.

akaBase · Accepted Answer · 2014-10-31 01:58:56Z

You shouldn't be using mysql as it is deprecated so to help you along I've done this with PDO to show its not as daunting as you may think.

PDO connection to database:

try{
    $db = new PDO ('mysql:host=YourHost;dbname=YourDatabaseName', 'YourDatabaseUserName', 'YourDatabasePassword');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
    echo $e->getMessage();
    die();
}

The above connection uses a a try catch with a way of making any errors cleaner and easier to read by making use of the getMessage() function which removes a lot of the un needed info.

The query you're after in PDO:

$sql = "SELECT GROUP_CONCAT(name SEPARATOR ' ') AS names FROM yourtable GROUP BY category_id";
$query = $db->query($sql);
$results = $query->fetchAll(PDO::FETCH_OBJ);

foreach($results as $result){
    echo "<pre>", ($result->names), "</pre>";
}

This is the most basic way to use PDO to query your database but if you start using it like this you will find it easier to then develop the more in depth and secure ways of putting data in and getting data out of your database that help to prevent sql injection.

Hope this is a little food for thought and helps in getting you to move to a more secure API.

superdb · Accepted Answer · 2014-10-31 01:29:41Z

0

try this

select DISTINCT(*) from content or you can be more specific with ie DISTINCT(columnname)

answered Oct 31, 2014 at 1:29

superdb

651 gold badge2 silver badges14 bronze badges

Comments

alu · Accepted Answer · 2014-10-31 01:35:22Z

<?php

// Using MySQL GROUP_CONCAT

$sql = 'SELECT category_id, GROUP_CONCAT(name separator \' \') FROM content GROUP BY category_id';


// Or PHP reduce as below
// Dummy Records
$records = [
    ['id' => 1, 'category_id' => 2, 'name' => 'Jason'],
    ['id' => 2, 'category_id' => 1, 'name' => 'Richard'],
    ['id' => 3, 'category_id' => 2, 'name' => 'John']
];

$result = array_reduce($records, function($carry, $record) {
    if (!array_key_exists($record['category_id'], $carry)) {
        $carry[$record['category_id']] = [];
    }

    $carry[$record['category_id']][] = $record['name'];

    return $carry;
}, []);

foreach ($result as $categoryId => $names) {
    echo $categoryId . ' ' . implode(' ', $names) . "\n";
}

Collectives™ on Stack Overflow

how to show values from mysql database without repeating values in php

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related