Block reviews

Worker Joined: May 18, 2005 Posts: 119 Location: SVCDPlaza

On 1 site i use the block reviews but sometimes i see double entries from different users.
Now i want to rebuild the block so i see the first entries of a review and the next entries with the same title i don't see.
So basically i want to skip those double entries in the block on the main.

I'm using the block review of the latest raven 2.20.01

Code:

if ( !defined('BLOCK_FILE') ) {


   Header('Location: ../index.php');


   die();


}





global $prefix, $db;





$content = '';


$sql = 'SELECT id, title FROM '.$prefix.'_reviews ORDER BY id DESC LIMIT 0,10';


$result = $db->sql_query($sql);


while (list($id, $title) = $db->sql_fetchrow($result)) {


   $id = intval($id); // montego:0000763 - even this line is unnecessary because this field is ALWAYS an integer coming off the db!


//   $title = stripslashes($title); // montego:0000763 - check_html was already done prior to adding to the DB and will end up stripping slashes again!


   $content .= '<strong><big>&middot;</big></strong>&nbsp;<a href="reviews.html?amp;rop=showcontent&amp;id='.$id.'">'.$title.'</a><br />';


}


?>

Some one have a suggestion??

registered · Posted: Mon Jul 07, 2008 9:29 pm

Use the SELECT DISTINCT syntax. This may not work if the reviews have the same title but different ids, as they are not duplicated rows. In that case you'd have to grab the ID and titles in two queries.

Quote:

$sql = 'SELECT DISTINCT id, title FROM '.$prefix.'_reviews ORDER BY id DESC LIMIT 0,10';

registered · Posted: Tue Jul 08, 2008 6:11 am

Yeah, there is no quick and easy answer. My original thought was to push each title used onto an array and then check each new title against that array and if already used, ignore it. However, if you still want the limit of 10 reviews to show up, you would also have to add the logic to remove the LIMIT from the query and then stop once you have collected 10 reviews.

Posted: Tue Jul 08, 2008 11:29 am

The limit is not the problem thats working but it only works with

Code:

$sql = 'SELECT DISTINCT title FROM '.$prefix.'_reviews LIMIT 0,10';

If i put the ID variable in then the trick will not working because only the title is the same and not the id.
And without the id variable i get the oldest from that table.

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

Have you tried

Code:




$sql = 'SELECT DISTINCT title, id FROM '.$prefix.'_reviews ORDER BY id DESC LIMIT 0,10';

Posted: Tue Jul 08, 2008 6:28 pm

kenwood, Pretty sure you need the 'id' for the link that goes in the block content and my original post was meant to steer you in the right direction with words rather than code. I could code this for you in about 15 minutes (including testing) as I am pretty confident that I know what you are asking for, but I am juggling too many balls at the moment. I'll try and do this in the morning for you...

Posted: Wed Jul 09, 2008 6:35 am

kenwood, I believe that I have what you are looking for. Create yourself a new block file within the blocks directory, maybe call it block-ReviewsNoDups.php or whatever you like. Make the contents the following between the beginning and ending php tags:

Code:




if ( !defined('BLOCK_FILE') ) {


   Header('Location: ../index.php');


   die();


}





global $prefix, $db;





$content = '';


$sql = 'SELECT id, title FROM '.$prefix.'_reviews ORDER BY id DESC LIMIT 0,50';


$result = $db->sql_query($sql);


$usedReviews = array();


$i = 0;


while (list($id, $title) = $db->sql_fetchrow($result)) {


   $id = intval($id);


   if (in_array($title, $usedReviews)) continue;


   array_push($usedReviews, $title);


   $content .= '<strong><big>&middot;</big></strong>&nbsp;<a href="reviews.html?amp;rop=showcontent&amp;id='.$id.'">'.$title.'</a><br />';


   if (++$i == 10) break;


}

What this will do is still pull the list of Reviews from most recent to oldest order, like it does with the original block, and then keeps track of the titles that its already added to the block content. If its already been used, it just skips to the next one in line.

If you have tons of duplicates, you may need to bump up the LIMIT 0,50, the "50", to a higher number.

Have fun!

Posted: Wed Jul 09, 2008 10:01 am

It works greet montego thanks so far.
But my plan was to show the link to the review under the title .

Code:




if ( !defined('BLOCK_FILE') ) {


   Header('Location: ../index.php');


   die();


}





global $prefix, $db;





$content = '';


$sql = 'SELECT id, title, date FROM '.$prefix.'_reviews ORDER BY id DESC LIMIT 0,50';


$result = $db->sql_query($sql);


$usedReviews = array();


$i = 0;


while (list($id, $title, $date) = $db->sql_fetchrow($result)) {


   $id = intval($id);


   if (in_array($title, $usedReviews)) continue;


   array_push($usedReviews, $title);


   $content .= '<strong><big>&middot;</big></strong>&nbsp;'.$date.' '.$title.'<br />';


   $content .= '<strong><big>&middot;</big></strong>&nbsp;<a href="reviews.html?amp;rop=showcontent&amp;id='.$id.'">'LINK'</a><br />';


   if (++$i == 10) break;


}

But now i only see 1 link from the first entry from the first review.
My plan was to put all the links from the same title under the title if there are from the same date
So if the are 2 reviews wit the same name from the same date it looks like.

date title
link
link

other wise if there is 1 review ore there are from a different date it looks like

date title
link

date title
link

Posted: Thu Jul 10, 2008 7:03 am

Unfortunately, that is quite a bit more work... sorry, but I'm a bit swamped atm. If you still need/want help with this after RavenNuke release 2.30.00 comes out, give me a "shout".

Posted: Thu Jul 10, 2008 12:06 pm

Oke i have the time montego