Returning multiple values in a while loop

[djneel]

Dear everyone,

I'm fairly new to MySQL/PHP and therefore I have the following problem:

I'm making a tagging system for my blog. I have three MySQL tables: article (articleId, title, article, date) , tag (tagId, tag) and reference (refId, refArticleId, refTagId).

As you might expect, I am connecting the article table to the tag table via a third table. This third table (reference) only contains a unique identifyer (refId) and the unique identifyers of the respective two other tables.

Now, everything works fine up to the tagging system. I just let a while loop iterate over my article table, which outputs the title of my article, the article and the date. But then, in the location where I would like to output the different tags that apply to that certain article, things go wrong.

As you can see I call the function getTags in my first while loop, so as to output the tags in connection to a certain article. Within this function I use "return". This causes the script to output only one tag (where usually there are more). If I try to replace the return by an "echo" or "print", I do get the right tags. However, they are placed totally wrong.

What am I doing wrong here? I'd appreciate it if anyone could help me. Thanks in advance.

[hr]

[code]
// the connection data and general strings
include("../php/server.inc.php");

// connection
$db = mysql_connect($dbserver,$dbuser,$dbpass)
or die ($dbconnecterror);
mysql_select_db($dbnaam, $db)
or die ($dbselecterror);

// the query that outputs the title, article and date per article
$query = "SELECT * FROM articles ORDER BY date DESC LIMIT 3";
$result = mysql_query($query)
or die ($queryerror);


// Kijk of er resultaten zijn
if (mysql_num_rows($result) > 0){

// itereer over records en print elk veld
while($row = mysql_fetch_array($result)){

$title = stripslashes($row["title"]);
$article = stripslashes($row["article"]);
$date = stripslashes($row["date"]);
$articleId = trim($row["articleId"]);

echo
"
<h4>".$title."</h4>
<p>".$article."</p>
<span class='specs'>Geplaatst op: ".$date."</span><br />
<span class='specs'>Tags:".getTags(articleId)."</span>
"
;
}
} else {
echo "".$norecords."";
}
//function that gets the tags
function getTags($articleId) {

$query = "SELECT tag FROM reference, tags WHERE refArticleId = '".$articleId."' AND tagId = refTagId ORDER BY tag";
$result = mysql_query($query)
or die($queryerror);

if (mysql_num_rows($result) > 0) {
while($row = mysql_fetch_array($result)){
return $row[tag];
}
} else {
echo $norecords;
}
}

// free the set of results
mysql_free_result($result);

//close this connection
mysql_close($db);
[/code]

[fenway]

Well, sure, you're breaking out of your function call during the first iteration of that while loop with the return statement.  Also, you're missing the $ sigil on articleID in your function call.  You could just return the array of tags, or have the function do the concatenation for you.

[djneel]

Thanks for your reply. I noticed the missing $. It was a copy-paste error.

I understand now, that I break out of my while loop thanks to the return.
Could you give me a literal example of how I could output to an array?
I've read the php manual, but I can't understand the syntax.

Thanks again.

[fenway]

I wish I could... I'm not a PHP whiz... any takers?

[shoz]

I've changed the "or die()" because I don't see the variable $queryerror set anywhere.
[code]
<?php
//function that gets the tags
function getTags($articleId)
{
    $query = "SELECT tag FROM reference, tags WHERE refArticleId = '".$articleId."' AND tagId = refTagId ORDER BY tag";
    $result = mysql_query($query) or die($query."<br />\n".mysql_error());
   
    $tags = array();
    if (mysql_num_rows($result) > 0)
    {
        while($row = mysql_fetch_array($result))
        {
                    $tags[] =  $row['tag'];
        }
    }
    return $tags;
}
?>
[/code]

[code]
//itereer over records en print elk veld
while($row = mysql_fetch_array($result)){

$title = stripslashes($row["title"]);
$article = stripslashes($row["article"]);
$date = stripslashes($row["date"]);
$articleId = trim($row["articleId"]);

                //tags are handled here and made into a list
$tags = getTags($articleId);
                $tag_list = 'no_records';
                if (count($tags))
                {
                    $tag_list = implode(',', $tags);
                }
echo
"
<h4>".$title."</h4>
<p>".$article."</p>
<span class='specs'>Geplaatst op: ".$date."</span><br />
<span class='specs'>Tags:".$tag_list."</span>
"
;
}
[/code]

[djneel]

Thank you very much! It worked like a charm. I could have never discovered this by myself. Thank you again. See the result at http://www.chocolata.be/weblog/index.php

[shoz]

You can change the implode line to the following to ensure that all values have gone through [url=http://www.php.net/htmlentities]htmlentities[/url]
[code]
implode(',', array_map("htmlentities", $tags));
[/code]

[djneel]

Thank you. I made the changes.

The tags work nicely now, but I was thinking: how could I make a different link out of every tag? I want to show all articles related to that tag after clicking a certain tag. Is there any way to do this? Could you get me started?

[djneel]

I think all I need to know is how to cut the $tag_list in explicit strings or something. That way, I can use these strings within my while loop. Or am I thinking too simplisticly?

[shoz]

This does it using the same file. All the articles are displayed at once, but you should try to incorporate some form of pagination. There should be a tutorial on this site on pagination. A google search on pagination should also be informative.

I don't see any errors in the following but there may be.
[code]
<?php
// the connection data and general strings
include("../php/server.inc.php");

// connection
$db =  mysql_connect($dbserver,$dbuser,$dbpass)
            or die ($dbconnecterror);
        mysql_select_db($dbnaam, $db)
            or die ($dbselecterror);

// the query that outputs the title, article and date per article
$query = 'SELECT * FROM articles ';
$limit = TRUE;

/**
* If a tagId has been sent, show only those articles
* with that tagId. Validated by preg_match and casted
* to an (int)
*/
if (isset($_GET['tagid']) && preg_match('/^[0-9]+$/', $_GET['tagid']))
{
    $query .= 'WHERE refTagId = '.(int)$GET['tagid'].' ';
    $limit = FALSE;
}

$query .= 'ORDER BY date DESC ';
if ($limit)
{
    $query .= 'LIMIT 3 ';
}

$result = mysql_query($query)
            or die ($queryerror);


// Kijk of er resultaten zijn
if (mysql_num_rows($result) > 0){
//itereer over records en print elk veld
    while($row = mysql_fetch_array($result)){

        $title = stripslashes($row["title"]);
        $article = stripslashes($row["article"]);
        $date = stripslashes($row["date"]);
        $articleId = trim($row["articleId"]);

                //tags are handled here and made into a list
        $tags = getTags($articleId);
        $tag_list = 'no_records';
        if (count($tags))
        {
            /**
            * Each tag is made into a link with the tagId
            * as a GET variable
            */
            foreach ($tag_list as $tagId => $tag)
            {
                    $tag_list = "<a href=\"{$_SERVER['SCRIPT_NAME']}?tagid=
{$tagId}\">".htmlentities($tag, ENT_QUOTES)."</a>, ";
            }
        }

        //htmlentities applies to output
        echo
        "
        <h4>".htmlentities($title, ENT_QUOTES)."</h4>
        <p>".htmlentities($article, ENT_QUOTES)."</p>
        <span class='specs'>Geplaatst op: ".$date."</span><br />
        <span class='specs'>Tags:".$tag_list."</span>
        "
        ;
    }

} else {
    echo "".$norecords."";
}
// free the set of results
mysql_free_result($result);

//close this connection
mysql_close($db);

//function that gets the tags
function getTags($articleId)
{
    $query = "SELECT tagId, tag FROM reference, tags WHERE refArticleId = '
".$articleId."' AND tagId = refTagId ORDER BY tag";
    $result = mysql_query($query) or die($query."<br />\n".mysql_error());


    $tags = array();

    if (mysql_num_rows($result) > 0)
    {
        while($row = mysql_fetch_array($result))
        {
                    $tags[($row['tagId'])] =  $row['tag'];
        }
    }
    return $tags;
}
?>
[/code]
[url=http://www.php.net/foreach]foreach[/url]
[url=http://www.php.net/preg_match]preg_match()[/url]
[url=http://www.php.net/manual/en/language.types.type-juggling.php#language.types.typecasting]type_casting[/url]

EDIT: fixed an error.

[djneel]

Wow, that's a lot to chew at once. I can't thank you enough for your efforts. I'm going to take my time to try and understand this. I'll get back to you with the results. This sure is harder than I thought it would be :) .

[shoz]

I didn't notice the reference table. You'll have to change this section.
[code]
if (isset($_GET['tagid']) && preg_match('/^[0-9]+$/', $_GET['tagid']))
{
    $query .= 'INNER JOIN reference AS r ON r.refArtcleId = articles.articleId WHERE r.refTagId = '.(int)$GET['tagid'].' ';
    $limit = FALSE;
}
[/code]

[djneel]

Thank you for your thorough attention, I'll make sure to do this.

[djneel]

I tested out your script just now, but unfortunately, I get an error message. Sorry to trouble you with this, but most of the code you entered I do not yet understand. Thus, I cannot figure out what the problem is. 

The following error code is printed out just below the tags. The tags state: no_records.
[code]Warning: Invalid argument supplied for foreach() in /home/chocol/domains/chocolata.be/public_html/weblog/indextest.php on line 91[/code]

If you would like to see the html version of this page, go to http://chocolata.be/weblog/indextest.php. Then you can see first hand where the problem is (if you'd like to do that).

[code]<?php
// the connection data and general strings
include("../php/server.inc.php");

// connection
$db =  mysql_connect($dbserver,$dbuser,$dbpass)
            or die ($dbconnecterror);
        mysql_select_db($dbnaam, $db)
            or die ($dbselecterror);

// the query that outputs the title, article and date per article
$query = 'SELECT * FROM articles ';
$limit = TRUE;

/**
* If a tagId has been sent, show only those articles
* with that tagId. Validated by preg_match and casted
* to an (int)
*/
if (isset($_GET['tagid']) && preg_match('/^[0-9]+$/', $_GET['tagid']))
{
    $query .= 'INNER JOIN reference AS r ON r.refArtcleId = articles.articleId WHERE r.refTagId = '.(int)$GET['tagid'].' ';
    $limit = FALSE;
}

$query .= 'ORDER BY date DESC ';
if ($limit)
{
    $query .= 'LIMIT 3 ';
}

$result = mysql_query($query)
            or die ($queryerror);


// Kijk of er resultaten zijn
if (mysql_num_rows($result) > 0){
//itereer over records en print elk veld
    while($row = mysql_fetch_array($result)){

        $title = stripslashes($row["title"]);
        $article = stripslashes($row["article"]);
        $date = stripslashes($row["date"]);
        $articleId = trim($row["articleId"]);

        //tags are handled here and made into a list
        $tags = getTags($articleId);
        $tag_list = 'no_records';
        if (count($tags))
        {
            /**
            * Each tag is made into a link with the tagId
            * as a GET variable
            */
            foreach ($tag_list as $tagId => $tag)
            {
                    $tag_list = "<a href=\"{$_SERVER['articles.php']}?tagid=
{$tagId}\">".htmlentities($tag, ENT_QUOTES)."</a>, ";
            }
        }

        //htmlentities applies to output
        echo
        "
        <h4>".htmlentities($title, ENT_QUOTES)."</h4>
        <p>".htmlentities($article, ENT_QUOTES)."</p>
        <span class='specs'>Geplaatst op: ".$date."</span><br />
        <span class='specs'>Tags:".$tag_list."</span>
        "
        ;
    }

} else {
    echo "".$norecords."";
}
// free the set of results
mysql_free_result($result);

//close this connection
mysql_close($db);

//function that gets the tags
function getTags($articleId)
{
    $query = "SELECT tagId, tag FROM reference, tags WHERE refArticleId = '
".$articleId."' AND tagId = refTagId ORDER BY tag";
    $result = mysql_query($query) or die($query."<br />\n".mysql_error());


    $tags = array();

    if (mysql_num_rows($result) > 0)
    {
        while($row = mysql_fetch_array($result))
        {
                    $tags[($row['tagId'])] =  $row['tag'];
        }
    }
    return $tags;
}
?>[/code]

[shoz]

change the foreach line to the following
[code]
foreach ($tags as $tagId => $tag)
[/code]

[shoz]

I also now see that the links aren't being concatenated, so you should change the entire tag formatting section to the following
[code]

        if (count($tags))
        {
            /**
            * Each tag is made into a link with the tagId
            * as a GET variable
            */
            $tag_list = '';
            foreach ($tags as $tagId => $tag)
            {
                    $tag_list .= "<a href=\"{$_SERVER['articles.php']}?tagid=
{$tagId}\">".htmlentities($tag, ENT_QUOTES)."</a>, ";
            }
        }
[/code]

[djneel]

Fantastic. It works great! Thank you!

As my signature says, I'm eager to learn new things. I've reviewed your script, and I'd like to understand it a little better. At high level I think I understand it: the script gets an array of tags for each article, then splits the array into variables and then assigns an a href to every tag.

There are some things I don't understand however:

[list]
[*]What does $limit do?
[*]What does the isset do?
[/list]

Thanks again for everything. I don't mind if you don't answer these questions. I will find them out eventually, I guess. The result of the script is temporarily available at the [url=http://chocolata.be/weblog/indextest.php]same link[/url] as my last post.

[djneel]

I updated the script with your new adjustments. Thank you. It's gonna be a while before I can make a script like this. But I've got high hopes (because of good support).

[shoz]

[quote author=djneel link=topic=107665.msg432698#msg432698 date=1158068225]
There are some things I don't understand however:

[list]
[*]What does $limit do?
[*]What does the isset do?
[/list]
[/quote]

The $limit is a normal variable that I've used to determine whether or not to add the "LIMIT 3" to the query. If the script has been asked to show articles based on the tagId then no LIMIT is put on the number of articles shown. Otherwise the LIMIT is added to the query.

[url=http://www.php.net/isset]isset()[/url] is a PHP function that can be used to determine if a variable exists. It's being used in the code to test whether the user sent a tagid or not.

In one of my previous posts I posted links at the end that cover some of the things I used in the script.

One more error you'll have to deal with is the "(int)$GET['tagid']". Notice that the underscore(_) is missing. it should be "(int)$_GET['tagid']".

[djneel]

I see. I think I understand the majority of it. I did indeed notice the explanatory links at the end of one of your posts. It's just that I've always found the PHP manual somewhat hard to understand. I think in time, things will go better.

I dealt with the error. Thanks. Everything seems to work fine. I hope other people are helped with this too.

[shoz]

I didn't know that the article's text was inserted into the database with HTML tags with the relevant characters converted to their HTML entities.

That being the case, you should remove the htmlentities calls from the script. I have to assume that everything, including the tags have been HTML formatted on insertion.

eg:

[code]
//change
htmlentities($tag, ENT_QUOTES)

//to
$tag
[/code]

You should be able to change all the other htmlentities calls.

[djneel]

No problem. I was going to remove that bit of code, to see what would happen. I'm going to write the content of the blog, using a handmade CMS with the [url=http://"http://tinymce.moxiecode.com/"]tinyMCE [/url]plugin. For those who don't know tinyMCE: it is a free "what you see is what you get"-editor, which converts a text field of a form into a word-like interface which you can use to format text in real life. So, yes I'm going to need the contents of the tables as they are. But thank you for your useful remark.