Problems displaying multiple array variables on single row

PHP
1

Hi,

I have a database which is used to give descriptions of press cuttings and then given categorisation tags so that they are searchable on both the contents of the descriptions and on their tags. It’s a classic many-to-many relationship, as each cutting can have many tags, and each tag can be attached to many cuttings.

I’m having problems with displaying multiple tags alongside their relevant cuttings.

I’ve stripped back a lot of the non-necessary descriptors in the cuttings table take it down to the basic info so you’re not having to wade through too much code:

Here’s the SQL to build the database I’m using:

CREATE DATABASE press;

USE press;

CREATE TABLE cutting (
	cutting_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	cutting_text TEXT
) DEFAULT CHARACTER SET utf8;

CREATE TABLE tag (
	tag_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	tag_name VARCHAR(255)
) DEFAULT CHARACTER SET utf8;

CREATE TABLE cutting_tag (
	cutting_id INT NOT NULL,
	tag_id INT NOT NULL,
	PRIMARY KEY (cutting_id, tag_id)
) DEFAULT CHARACTER SET utf8;


INSERT INTO cutting (cutting_id, cutting_text) VALUES
(1, 'News Article Example Headline'),
(2, 'Press Review Example Headline');

INSERT INTO tag (tag_id, tag_name) VALUES
(1, 'Mentions CEO'),
(2, 'Mentions Sponsor'),
(3, 'Review'),
(4, 'Great Quotes');

INSERT INTO cutting_tag (cutting_id, tag_id) VALUES
(1, 1),
(1, 2),
(2, 2),
(2, 3),
(2, 4);

I’m then connecting/querying/outputing from the database using PHP as shown in the code below (I should also say I created privileges for a user with username ‘username’ and password ‘password’ just to keep things simple).

<!DOCTYPE html>
<html lang="en">
	<head>
		<meta charset="utf-8">
		<title>Press Cutting Categories</title>
	</head>
	<body>

<?php
$link = mysqli_connect('localhost', 'username', 'password');
if (!$link)
{
	echo $error = 'Unable to connect to the database server.';
	exit();
}

if (!mysqli_set_charset($link, 'utf8'))
{
	echo $error = 'Unable to set database connection encoding.';
	exit();
}

if (!mysqli_select_db($link, 'press'))
{
	echo $error = 'Unable to locate the database.';
	exit();
}


$query = "SELECT cutting.cutting_id, cutting_text, tag_name
FROM cutting 
INNER JOIN tag INNER JOIN cutting_tag ON cutting.cutting_id = cutting_tag.cutting_id AND cutting_tag.tag_id = tag.tag_id";


$result = mysqli_query($link, $query);
if (!$result)
{
	echo $error = 'Error fetching cuttings: ' . mysqli_error($link);
	exit();
}

$cuttings = array();
while ($row = mysqli_fetch_array($result))
{
	$cuttings[] = array(
						'cutting_id' => $row['cutting_id'],
						'cutting_text' => $row['cutting_text']);
	}
	
$query2 = "SELECT cutting.cutting_id, cutting_text, tag_name
FROM cutting 
INNER JOIN tag INNER JOIN cutting_tag ON cutting.cutting_id = cutting_tag.cutting_id AND cutting_tag.tag_id = tag.tag_id";


$result2 = mysqli_query($link, $query2);
if (!$result2)
{
	echo $error = 'Error fetching cuttings: ' . mysqli_error($link);
	exit();
}

$tags = array();
while ($row = mysqli_fetch_array($result2))
{
	$tags[] = array(
						'cutting_id' => $row['cutting_id'],
						'tag_name' => $row['tag_name']);
	}	

?>

		<p>Here are the cuttings with their tags:</p>
<table width="1200" border="1" cellspacing="1" cellpadding="1">
  <thead>
  <tr>
    <th>Cutting ID</th>
    <th>Cutting Text</th>
    <th>Tags</th>
    </tr>
  </thead>
<tbody><?php foreach ($cuttings as $cutting): ?>
  <tr>
    <td><?php echo htmlspecialchars($cutting['cutting_id'], ENT_QUOTES, 'utf-8'); ?></td>
    <td><?php echo htmlspecialchars($cutting['cutting_text'], ENT_QUOTES, 'utf-8'); ?></td>
	<td><?php foreach ($tags as $tag): ?><ul><li><?php echo htmlspecialchars($tag['tag_name'], ENT_QUOTES, 'utf-8'); ?></li></ul><?php endforeach; ?></td>
  </tr>
    		<?php endforeach; ?>
            </tbody>
</table>
	</body>
</html>

This gives an output of 5 rows (two for the news article, and three for the press review, so I know it’s recognising that there are 2 tags for the news article and 3 for the press review) but it’s displaying all 5 unique combinations of cutting_id/tag_id beside each one row, rather than just displaying the tags which are relevant to that cutting.

I’d ideally like the output to be 2 rows (one for each cutting_id/cutting_text, rather than multiple rows for each) with the relevant tags showing in a list in the final cell, like this:

±------------±------------------------------------±-------------------+
|Cutting ID’‘’‘’|Cutting Text’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’|Tags’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’'|
±------------±------------------------------------±-------------------+
|1’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’'|News Article Example Headline’‘’‘’'|Mentions CEO’‘’‘’‘’‘’|
|‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’|‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’'|Mentions Sponsor |
±------------±------------------------------------±-------------------+
|2’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’'|Press Review Example Headline’‘’‘’|Mentions Sponsor |
|‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’|‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’'|Review’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’'|
|‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’|‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’'|Great Quotes’‘’‘’‘’‘’'|
±------------±------------------------------------±-------------------+

I’ve tried various while loops/conditions to try and get only the correct tags to show next to the cuttings, and to only display one row per cutting, but I just seem to be going round and round in circles and never really getting anywhere.

Can anyone help?! As you may be able to tell, I’m fairly new to PHP so any advice (especially idiot-proof advice) would be greatly appreciated!

Thanks in anticipation.

2

Okay there’s two things you need to do here.
GROUP BY,
and decide a format for your data.

Try…

SELECT cutting.cutting_id, cutting_text, GROUP_CONCAT(tag_name ORDER BY tag_name SEPARATOR ',') AS tags
FROM cutting 
INNER JOIN tag 
INNER JOIN cutting_tag ON cutting.cutting_id = cutting_tag.cutting_id AND cutting_tag.tag_id = tag.tag_id 
GROUP BY cutting_tag.cutting_id;

This will return 1 row per article, and the tags field will be a comma-seperated list of tags assigned to that article.

3

PS, you might want to make one of those INNER JOINs a LEFT JOIN… (What if an article has no tags?)

4

Using the query above, which produces the following result-set.

You basically need to track when the row changes, like so…


<?php
$last = null;

while($row = mysql_fetch_assoc($result)){
  
  $canShowIdAndTitle = true;
  
  if($last !== $row['cutting_id']){
    $last = $row['cutting_id'];
    $canShowIdAndTitle = false;
  }
  
  printf(
    "<tr>
      <td>%s</td>
      <td>%s</td>
      <td>%s</td>
    </tr>",
    $canShowIdAndTitle ? $row['cutting_id'] : '&nbsp;',
    $canShowIdAndTitle ? $row['cutting_text'] : '&nbsp',
    $row['tag_name']
  );
  
}

Let me know how you get on. :slight_smile:

5

Be very careful, Anthony - the original query has no ORDER BY …

6

Thanks! Yeah, that would bugger it. :smiley:

7

Hi,

The $query works perfectly in MAMP - thanks StarLion.

Anthony, when I pasted your code in, I’m getting this warning:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, object given

over the following line of code

while($row = mysql_fetch_assoc($result)){

I’d got rid of all my other code apart from

$result = mysqli_query($link, $query);
if (!$result)
{
	echo $error = 'Error fetching cuttings: ' . mysqli_error($link);
	exit();
}

I take it that was the right thing to do…?

Any ideas about the error?

Thanks so much for your speedy responses, I really appreciate it!

8

Error indicates the query was rejected by the mysql server.

what did the mysqli_error command give you?

9

Ah, you need to use mysqli_fetch_assoc, not mysql_fetch_assoc.

10

Woo-hoo!

the mysqli_fetch_assoc fixed the errors, but then had a table with empty values.

A couple of small changes from this code:

    $canShowIdAndTitle ? $row['cutting_id'] : '&nbsp;',
    $canShowIdAndTitle ? $row['cutting_text'] : '&nbsp;',
    $row['tag_name']

to this code:

    $canShowIdAndTitle ? '&nbsp;' : $row['cutting_id'],
    $canShowIdAndTitle ? '&nbsp;' : $row['cutting_text'],
    $row['tags']

fixed that problem and it is now working perfectly.

Thank you so much StarLion and Anthony - you are my PHP heroes!

For anyone else with a similar problem, here’s the final working version of the code:

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <title>Press Cutting Categories</title>
    </head>
    <body>
 
<?php
$link = mysqli_connect('localhost', 'username', 'password');
if (!$link)
{
    echo $error = 'Unable to connect to the database server.';
    exit();
}
 
if (!mysqli_set_charset($link, 'utf8'))
{
    echo $error = 'Unable to set database connection encoding.';
    exit();
}
 
if (!mysqli_select_db($link, 'press'))
{
    echo $error = 'Unable to locate the database.';
    exit();
}

$query = "SELECT cutting.cutting_id, cutting_text, GROUP_CONCAT(tag_name ORDER BY tag_name SEPARATOR ', ') AS tags
FROM cutting
INNER JOIN tag
INNER JOIN cutting_tag ON cutting.cutting_id = cutting_tag.cutting_id AND cutting_tag.tag_id = tag.tag_id
GROUP BY cutting_tag.cutting_id";
 
$result = mysqli_query($link, $query);
if (!$result)
{
	echo $error = 'Error fetching cuttings: ' . mysqli_error($link);
	exit();
}

?>

<p>Here are the cuttings with their tags:</p>
<table width="1200" border="1" cellspacing="1" cellpadding="1">
  <thead>
  <tr>
    <th>Cutting ID</th>
    <th>Cutting Text</th>
    <th>Tags</th>
    </tr>
  </thead>
<tbody>

<?php

$last = null;

while ($row = mysqli_fetch_assoc($result))
{  
  $canShowIdAndTitle = true;
  
  if($last !== $row['cutting_id'])
	{
    	$last = $row['cutting_id'];
    	$canShowIdAndTitle = false;
	}

printf(
    "<tr>
      <td>%s</td>
      <td>%s</td>
      <td>%s</td>
    </tr>",
    $canShowIdAndTitle ? '&nbsp;' : $row['cutting_id'],
    $canShowIdAndTitle ? '&nbsp;' : $row['cutting_text'],
    $row['tags']
  );  
}
?>
</tbody>
</table>


    </body>
</html>