Jump to content

Returning multiple values in a while loop


djneel

Recommended Posts

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]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

[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']".
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.