Jump to content


Photo

Returning multiple values in a while loop


  • Please log in to reply
21 replies to this topic

#1 djneel

djneel
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationBelgium

Posted 11 September 2006 - 05:33 PM

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.

 

// 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); 

always eager to learn new things

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 September 2006 - 05:45 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 djneel

djneel
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationBelgium

Posted 11 September 2006 - 07:13 PM

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.
always eager to learn new things

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 September 2006 - 07:28 PM

I wish I could... I'm not a PHP whiz... any takers?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 11 September 2006 - 08:10 PM

I've changed the "or die()" because I don't see the variable $queryerror set anywhere.
<?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;
}
?>

//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>
		"
		;
	}


#6 djneel

djneel
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationBelgium

Posted 11 September 2006 - 08:22 PM

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...eblog/index.php
always eager to learn new things

#7 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 11 September 2006 - 10:05 PM

You can change the implode line to the following to ensure that all values have gone through htmlentities
implode(',', array_map("htmlentities", $tags));


#8 djneel

djneel
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationBelgium

Posted 12 September 2006 - 08:29 AM

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?

always eager to learn new things

#9 djneel

djneel
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationBelgium

Posted 12 September 2006 - 11:09 AM

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?
always eager to learn new things

#10 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 12 September 2006 - 11:50 AM

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.
<?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;
}
?>
foreach
preg_match()
type_casting

EDIT: fixed an error.

#11 djneel

djneel
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationBelgium

Posted 12 September 2006 - 11:55 AM

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


always eager to learn new things

#12 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 12 September 2006 - 12:16 PM

I didn't notice the reference table. You'll have to change this section.
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;
}


#13 djneel

djneel
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationBelgium

Posted 12 September 2006 - 12:18 PM

Thank you for your thorough attention, I'll make sure to do this.
always eager to learn new things

#14 djneel

djneel
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationBelgium

Posted 12 September 2006 - 12:32 PM

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.
Warning: Invalid argument supplied for foreach() in /home/chocol/domains/chocolata.be/public_html/weblog/indextest.php on line 91

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

<?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;
}
?>

always eager to learn new things

#15 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 12 September 2006 - 01:18 PM

change the foreach line to the following
foreach ($tags as $tagId => $tag)


#16 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 12 September 2006 - 01:35 PM

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

        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>, ";
            }
        }


#17 djneel

djneel
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationBelgium

Posted 12 September 2006 - 01:37 PM

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:

  • What does $limit do?
  • What does the isset do?

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 same link as my last post.
always eager to learn new things

#18 djneel

djneel
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationBelgium

Posted 12 September 2006 - 01:40 PM

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).
always eager to learn new things

#19 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 12 September 2006 - 02:07 PM

There are some things I don't understand however:

  • What does $limit do?
  • What does the isset do?


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.

isset() 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']".

#20 djneel

djneel
  • Members
  • PipPip
  • Member
  • 22 posts
  • LocationBelgium

Posted 12 September 2006 - 02:12 PM

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.
always eager to learn new things




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users