| perl/sql related id
|
johan12
msg:4442195 | 10:09 pm on Apr 17, 2012 (gmt 0) |
Hello!
I have code about related image and top image view (below). in database I have table name Files and in table Files I have a column name safe_file (this column content only number if file not safety value= 1 and if file is safe will have value = 0.
Now I want the code below check with safe_file column, if file relate is safe so all other relate image will show only safe image, and if file is not safety so it will show all not safety file relate.
how to edit this code below ?
my $top_8 = $db->SelectARef("SELECT f.*, s.*, u.usr_login
FROM Files f
LEFT JOIN Servers s ON s.srv_id=f.srv_id
LEFT JOIN Users u ON u.usr_id=f.usr_id
WHERE file_name RLIKE '.*\.(jpg|jpeg|gif|png|bmp)\$'
ORDER BY file_downloads DESC
LIMIT 15");
$file->{stripped_name} = $file->{file_name};
$file->{stripped_name} =~ s/\..*?$//;
my @keywords = split /[^\w]+/, $file->{stripped_name};
@keywords = grep { $_ !~ /^[0-9]+$/ } @keywords;
my ($words_related, $descr_related);
if(@keywords) {
my $related_filter = 'WHERE '. &combine(' OR ', map { $_ = &mysql_safe($_); "file_name LIKE '%$_%'" } @keywords) if @keywords;
$words_related = $db->SelectARef("SELECT f.*, s.*, u.usr_login FROM Files f
LEFT JOIN Servers s ON s.srv_id=f.srv_id
LEFT JOIN Users u ON u.usr_id=f.usr_id
$related_filter
LIMIT 8");
}
my @descr_keywords = split /[^\w]+/, $file->{file_descr};
if(@descr_keywords) {
my $descr_related_filter = 'WHERE '. &combine(' OR ', map { $_ = &mysql_safe($_); "file_descr LIKE '%$_%'" } @descr_keywords) if @descr_keywords;
$descr_related = $db->SelectARef("SELECT f.*, s.*, u.usr_login FROM Files f
LEFT JOIN Servers s ON s.srv_id=f.srv_id
LEFT JOIN Users u ON u.usr_id=f.usr_id
$descr_related_filter
LIMIT 8");
}
my @related;
push @related, @$descr_related if $descr_related;
push @related, @$words_related if $words_related;
my @related = @related[0..8];
@related = grep { $_->{file_id} != $file->{file_id} } @related;
foreach(@$top_8, @related) {
$ses->getThumbLink($_);
$_->{download_link} = $ses->makeFileLink($_);
$_->{file_name} =~ s/_+/ /g;
$_->{file_name} =~ s/\.\w+$//g;
}
|
|
