Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

Review.cgi -> Links SQL - Table Select Prob.

Quote Reply
Review.cgi -> Links SQL - Table Select Prob.
Okay...I have most of Widgetz's (jerrysu) Review.cgi working. The only problem I am experiencing is pulling values from the Users where I have Email Address and other information pertinent to the reviews stored.

I have added the following codes in sub show_review:

Code:

$sth = $DB->prepare ("SELECT * FROM User_Reviews WHERE LinkID = $id AND Review != '' AND UserID !='' ORDER BY ID Desc LIMIT $OUT{start}, $LINKS{reviews_per_page}");
$sth->execute() or die $DBI::errstr;
while (my $rev = $sth->fetchrow_hashref) {
my $stj = $DB->prepare ("SELECT COUNT(ID), AVG(SiteRating) FROM User_Reviews WHERE UserID = '${$rev}{UserID}'");
$stj->execute() or die $DBI::errstr;
(${$rev}{USER_Total}, ${$rev}{USER_Average}) = $stj->fetchrow_array;
my $stk = $DB->prepare ("SELECT * FROM Users WHERE '${$rev}{UserID}'");
$stk->execute() or die $DBI::errstr;
$stk->fetchrow_array;
$OUT{reviews} .= &site_html_review ($rev, $dynamic);
$OUT{finish}++;
}


The only text that is printed includes Review Title, Review, and Site Rating. User information is not pulled or printed. I've been working on this for two days now (about 6 hours) and I can't get it to work.

Any suggestions would be greatly appreciated.

Thanks in advance.

Regards,

Eliot Lee

Quote Reply
Re: Review.cgi -> Links SQL - Table Select Prob. In reply to
is that all my code??

if it is.. oh boy.. i messed up..

Code:
$sth = $DB->prepare ("SELECT * FROM User_Reviews WHERE LinkID = $id AND Review != '' AND UserID != '' ORDER BY ID Desc LIMIT $OUT{start}, $LINKS{reviews_per_page}");
$sth->execute() or die $DBI::errstr;
while (my $rev = $sth->fetchrow_hashref) {
my $stj = $DB->prepare ("SELECT COUNT(ID), AVG(SiteRating) FROM User_Reviews WHERE UserID = '${$rev}{UserID}'");
$stj->execute() or die $DBI::errstr;
(${$rev}{USER_Total}, ${$rev}{USER_Average}) = $stj->fetchrow_array;
my $usr = $uDB->get_record ($rev->{UserID}, 'HASH');
foreach (keys %$usr) { $rev->{"USER_$_"} = $usr->{$_}; }
$OUT{reviews} .= &site_html_review ($rev, $dynamic);
$OUT{finish}++;
}
$uDB is the DBSQL object for Users.def

Jerry Su
Quote Reply
Re: Review.cgi -> Links SQL - Table Select Prob. In reply to
Yes...most of that was your codes...I simply added the SELECT codes for the Users table...Thanks, Jerry...I will give it a shot.

Regards,

Eliot Lee

Quote Reply
Re: Review.cgi -> Links SQL - Table Select Prob. In reply to
Thanks for the nudge, widgetz....with a little bit of tweaking...it now works....

I used the following codes:

Code:

$rev->{$_} = $usr->{$_};


rather than...

Code:

$rev->{"USER_$_"} = $usr->{$_};


These codes messed up the rating graph.

Regards,

Eliot Lee


Quote Reply
Re: Review.cgi -> Links SQL - Table Select Prob. In reply to
umm.. i did "USER_$_" because often there are the same fields in two databases..

ie.. the review table may have the field.. ID and the user table has it too.. so instead of getting the review ID you'd get the user id.. but that lets it so all the tags are < % USER_.... % > which is better..

Jerry Su
Quote Reply
Re: Review.cgi -> Links SQL - Table Select Prob. In reply to
Well...it works..anyway..and all I have to do is use the same field names from the different tables for tags in the reviews.html template file...

Regards,

Eliot Lee