Only 1 Result shown!

[shujjah]

Alright I use a function to fetch reviews of a specific game and I use a similar type of function to fetch news associated with that game. Now the problem is I have more than 1 review for that game and I want both the reviews to be listed but only 1 the latest one is shown? However in the news case all the news are shown. What I want is to display all the reviews of the game and not just the latest one and I am having trouble in doing so.this is the function

function getReviews1($gameId = '', $category = '', $template = 'reviewsList1') {		$sql = "SELECT				 gamecms_article.articleId,				 gamecms_article.title,				 gamecms_article.timestamp,				 gamecms_article.description,				 gamecms_article.userId				FROM				 gamecms_article				LEFT JOIN				 gamecms_category				ON				 gamecms_article.categoryId = gamecms_category.categoryId				WHERE				 gamecms_article.gameId = '$gameId'				AND				 gamecms_category.category = '$category'				AND				 gamecms_article.auth='1'				AND				 gamecms_article.deleted='0'				ORDER BY				 gamecms_article.timestamp				DESC";		$this->db->query($sql);		$numResults = $this->db->nf($this->db->query($sql));		If ($numResults > 0) {			while($this->db->next_record()) {				$articleId = $this->db->f("articleId");				$authorId = $this->db->f("userId");				$title = $this->db->f("title");				$description = $this->db->f("description");				$timestamp = $this->db->f("timestamp");				$year = trim(substr($timestamp, 0, 4));				$month = trim(substr($timestamp, 5, 2));				$sql = "SELECT * FROM gamecms_admin WHERE adminId='$authorId'";				$this->db->query($sql);				$this->db->next_record();				$author = $this->db->f("name");				$day = trim(substr($timestamp, 8, 2));				$newDate = date("m/d/Y", mktime(0,0,0,$month,$day,$year));				$timestamp = $newDate;				include "./templates/".$template.".tpl.php";			}		} else {			echo "No reviews available";		}	}

this is the .tpl

	<tr><td class="list" colspan="2"><strong><a href="./review.php?articleId=<?php echo $articleId; ?>&page=1"><?php if ($system != "") { echo $title." (".$system.")"; } else { echo $title; } ?></a></strong><br />Posted by <?php echo $author; ?> on <?php echo $day; echo "/"; echo $month; echo "/"; echo $year;?></td></tr>

and I am calling the function in gameinfo.inc.php

<?php $game->getReviews1($gameId, 'Reviews'); ?>

Thanks for your help in advance!

[justsomeguy]

I'm not sure how the database class works, but you might be skipping the first result. Use a do..while loop instead if that's the case.

		If ($numResults > 0) {			do {				$articleId = $this->db->f("articleId");				$authorId = $this->db->f("userId");				$title = $this->db->f("title");				$description = $this->db->f("description");				$timestamp = $this->db->f("timestamp");				$year = trim(substr($timestamp, 0, 4));				$month = trim(substr($timestamp, 5, 2));				$sql = "SELECT * FROM gamecms_admin WHERE adminId='$authorId'";				$this->db->query($sql);				$this->db->next_record();				$author = $this->db->f("name");				$day = trim(substr($timestamp, 8, 2));				$newDate = date("m/d/Y", mktime(0,0,0,$month,$day,$year));				$timestamp = $newDate;				include "./templates/".$template.".tpl.php";			} while ($this->db->next_record());		} else {			echo "No reviews available";		}

Also try printing out numResults to see if you actually are getting more than one result. If so, and they still aren't printing, then the problem might be in the db->next_record method.

[shujjah]

I am using this function to fetch news and it seems to show all the results and not just only the latest one

	function getNews($gameId = '', $template = 'latestHeadlines') {		$sql = "SELECT				 gamecms_news.newsId,				 gamecms_news.subject,				 gamecms_news.releaseDateTime				FROM				 gamecms_news				LEFT JOIN				 gamecms_newsGameLink				ON				 gamecms_news.newsId = gamecms_newsGameLink.newsId				WHERE				 gamecms_newsGameLink.gameId = '$gameId'				AND				 gamecms_news.auth='1'				AND				 gamecms_news.deleted='0'				ORDER BY				 gamecms_news.releaseDateTime				DESC";		$this->db->query($sql);		$numResults = $this->db->nf($this->db->query($sql));		If ($numResults > 0) {			while($this->db->next_record()) {				$newsId = $this->db->f("newsId");				$subject = $this->db->f("subject");				$timestamp = $this->db->f("releaseDateTime");				$year = trim(substr($timestamp, 0, 4));				$month = trim(substr($timestamp, 5, 2));				$day = trim(substr($timestamp, 8, 2));				$hour = trim(substr($timestamp, 11, 2));				$minute = trim(substr($timestamp, 14, 2));				$date = $month . "-" . $day . "-" . $year;				$newDate = date("F j, Y", mktime(0,0,0,$month,$day,$year));				$time = $hour . ":" . $minute;				include "./templates/".$template.".tpl.php";			}		} else {			echo "No news available";		}	}

[shujjah]

I tried the do code that you gave me and it still shows only 1 result!

[justsomeguy]

Did you print out the number of records to make sure you're getting more than one from the database?

[shujjah]

uff this is getting a bit too confusing alright If I use this function

	function getArticles($gameId = '', $category = '', $template = 'articlesList') {		$sql = "SELECT				 gamecms_article.articleId,				 gamecms_article.title,				 gamecms_article.timestamp				FROM				 gamecms_article				LEFT JOIN				 gamecms_category				ON				 gamecms_article.categoryId = gamecms_category.categoryId				WHERE				 gamecms_article.gameId = '$gameId'				AND				 gamecms_category.category = '$category'				AND				 gamecms_article.auth='1'				AND				 gamecms_article.deleted='0'				ORDER BY				 gamecms_article.timestamp				DESC";		$this->db->query($sql);		$numResults = $this->db->nf($this->db->query($sql));		If ($numResults > 0) {			while($this->db->next_record()) {				$articleId = $this->db->f("articleId");				$title = $this->db->f("title");				$timestamp = $this->db->f("timestamp");				$year = trim(substr($timestamp, 0, 4));				$month = trim(substr($timestamp, 5, 2));				$day = trim(substr($timestamp, 8, 2));				$newDate = date("m/d/Y", mktime(0,0,0,$month,$day,$year));				$timestamp = $newDate;				include "./templates/".$template.".tpl.php";			}		} else {			echo "No articles available";		}	}

here is the .tpl

	<tr><td class="list" colspan="2"><strong><a href="./article.php?articleId=<?php echo $articleId; ?>&page=1"><?php if ($system != "") { echo $title." (".$system.")"; } else { echo $title; } ?></a></strong></td></tr>

both the reviews show up but they don't show up if I use the function I posted in my first post!and both these ( getArticles and getReviews are somewhat similar ) ! .................. :)

[shadowayex]

Once again, read what justsomeguy is trying to tell you. PRINT THE NUMBER OF RESULTS TO MAKE SURE THE FUNCTION IS GETTING MORE THAN ONE. This will help out find where the problem is. It's much easier to solve a problem when you actually figure out where it is.

[justsomeguy]

I'm trying to figure out if the problem is in the SQL code or the PHP. If it's only returning one result, then the reason for that is in the SQL code. If it's returning more than one result but only printing one then the problem is in the PHP code.Also, I know the reason why it's doing this, but if I just tell you then you're not learning how to find out where the problem is. Figure out if the problem is in the SQL code or the PHP code, and then use a file compare tool to see what the differences are between the code that works and the code that doesn't. One of the lines that is different is causing this. If you need a file compare tool, download ConTEXT from my signature, paste the two functions into separate files in ConTEXT, then go to Tools->Compare and select the opened files to compare.

[shujjah]

Alright first of all I did echo the $numResults using reviews1 function and it did print 2 that means there is some problem with the php code as justsomeguy said. I did compare both the files and there are only a few difference firstly Articles uses articleslist.tpl whereas reviews1 uses reviewslist1.tplsecondly I select two more things in reviews1 those are userid and description thirdly I select the username by using the userid in reviews1 whereas I don't use this in articles so something tells me there is something wrong with this piece of code ? Am I right justomeguy and can you please tell me how to fix it ?

function getReviews1($gameId = '', $category = '', $template = 'reviewsList1') { $sql = "SELECT gamecms_article.articleId, gamecms_article.title, gamecms_article.timestamp, gamecms_article.description, gamecms_article.userId FROM gamecms_article LEFT JOIN gamecms_category ON gamecms_article.categoryId = gamecms_category.categoryId WHERE gamecms_article.gameId = '$gameId' AND gamecms_category.category = '$category' AND gamecms_article.auth='1' AND gamecms_article.deleted='0' ORDER BY gamecms_article.timestamp DESC"; $this->db->query($sql); $numResults = $this->db->nf($this->db->query($sql)); If ($numResults > 0) { while($this->db->next_record()) { $articleId = $this->db->f("articleId"); $authorId = $this->db->f("userId"); $title = $this->db->f("title"); $description = $this->db->f("description"); $timestamp = $this->db->f("timestamp"); $year = trim(substr($timestamp, 0, 4)); $month = trim(substr($timestamp, 5, 2)); $sql = "SELECT * FROM gamecms_admin WHERE adminId='$authorId'"; $this->db->query($sql); $this->db->next_record(); $author = $this->db->f("name"); $day = trim(substr($timestamp, 8, 2)); $newDate = date("m/d/Y", mktime(0,0,0,$month,$day,$year)); $timestamp = $newDate; include "./templates/".$template.".tpl.php"; } } else { echo "No reviews available"; } }

[justsomeguy]

If ($numResults > 0) {while($this->db->next_record()) {$articleId = $this->db->f("articleId");$authorId = $this->db->f("userId");$title = $this->db->f("title");$description = $this->db->f("description");$timestamp = $this->db->f("timestamp");$year = trim(substr($timestamp, 0, 4));$month = trim(substr($timestamp, 5, 2));$sql = "SELECT * FROM gamecms_admin WHERE adminId='$authorId'";$this->db->query($sql);$this->db->next_record();$author = $this->db->f("name");$day = trim(substr($timestamp, 8, 2));$newDate = date("m/d/Y", mktime(0,0,0,$month,$day,$year));$timestamp = $newDate;include "./templates/".$template.".tpl.php";}}

You're changing $this->db in the middle of the loop.

[shadowayex]

In loops you have to be careful not to change the thing you're looping by inside of it. If you do that, then it messes up the loop. I've run into complications like that before.

[shujjah]

You're changing $this->db in the middle of the loop.

I changed the variable name

				$ooo = "SELECT * FROM gamecms_admin WHERE adminId='$authorId'";				$aaa->db->query($ooo);				$aaa->db->next_record();				$author = $aaa->db->f("name");

but then I get this Notice: Undefined variable: aaa in D:\xampp\htdocs\includes\game.inc.php on line 593Notice: Trying to get property of non-object in D:\xampp\htdocs\includes\game.inc.php on line 593Fatal error: Call to a member function query() on a non-object in D:\xampp\htdocs\includes\game.inc.php on line 593so how can I select the username while inside the loop ? there will be only one result for that userid so I dont need to loop at all ? just fetch ? how ?

[justsomeguy]

If $this->db can only handle one query and result set at a time then you need to use a second database object. What does the class look like that this function is a part of?

[shujjah]

Ok so I did not get you sorry ?

[justsomeguy]

The function you posted is part of a class. I can tell it's part of a class because it uses $this. Somewhere else in the class there is something called db defined, which is referenced as $this->db inside the class. The db member is also a class though (or an object), I can tell that because the class member operator is still used to work with it (e.g. $this->db->query). So there are at least 2 class definitions, one for whatever this code is a part of and one for db. Depending on whatever is in those definitions, it might be possible to use the db class a different way to get the results so that you can have more than one result set open at a time, or at a minimum it will be possible to make a second database object in the class that you can use if the first one is busy. Ideally the database class would have a way to return the entire result set, a database class that can only handle one query at a time isn't very useful.