SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast PenguinsRock's Avatar
    Join Date
    Apr 2010
    Location
    Scotland, UK
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problems displaying multiple array variables on single row

    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:

    Code SQL:
    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).

    Code PHP:
    <!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. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    Okay there's two things you need to do here.
    GROUP BY,
    and decide a format for your data.

    Try....

    Code MySQL:
    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. #3
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    PS, you might want to make one of those INNER JOINs a LEFT JOIN... (What if an article has no tags?)

  4. #4
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Using the query above, which produces the following result-set.



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

    PHP Code:
    <?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.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  5. #5
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by AnthonySterling View Post
    Using the query above, which produces the following result-set.


    Let me know how you get on.
    Be very careful, Anthony - the original query has no ORDER BY ....

  6. #6
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks! Yeah, that would bugger it.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  7. #7
    SitePoint Enthusiast PenguinsRock's Avatar
    Join Date
    Apr 2010
    Location
    Scotland, UK
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Code PHP:
    while($row = mysql_fetch_assoc($result)){

    I'd got rid of all my other code apart from
    Code PHP:
    $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. #8
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    Error indicates the query was rejected by the mysql server.

    what did the mysqli_error command give you?

  9. #9
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Ah, you need to use mysqli_fetch_assoc, not mysql_fetch_assoc.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  10. #10
    SitePoint Enthusiast PenguinsRock's Avatar
    Join Date
    Apr 2010
    Location
    Scotland, UK
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

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

    to this code:

    Code PHP:
        $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:

    Code PHP:
    <!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>


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •