Sparhawk
10-15-07, 11:26 PM
THE BACKGROUND
I have a MySQL database containing the following columns:
X: the unique index (a number)
name: filename of photograph
mimetype: mime type of picture file (like image/jpeg, etc)
dateuploaded: date the picture was uploaded to the server
datetaken: date the user reports the picture was taken
description: description of picture contents, possibly including multiple lines separated by \n
folders: list of "\n"-separated "folders" -- like folder1\subfolder1\n
folder2\subfolder2\n
folder1\subfolder1\subfolder1b\subfolder1c\n
folder1\subfolder1\subfolder1d\subfolder1e\n
filelocation: location of the picture file on the server
thumbnail: a 'blob' containing a 150px-wide thumbnail of the picture
viewcount: number of times the picture has been viewed
I am trying to construct a MySQL query that returns any values of X when the folders field contains a given folder name, or subfolder of that folder.
My code (assume that I have already opened the access to the database outside of this function):
sub grabPicturesInFolderAndSubFolders()
{
my $folder = $_[0];
my @returnPics;
my $sth = $dbh->prepare("SELECT * FROM pictures WHERE X <> '0' AND folders REGEXP '^$folder' OR folders REGEXP '\n$folder' ORDER BY X;") or die "$!";
$sth->execute;
while ($thisLine = ($sth->fetchrow_array())[0]) {
push @returnPics, $thisLine;
}
$sth->finish;
return @returnPics;
}
This function takes the input of a desired folder, and SHOULD return an array of values of X that contain that folder in it's list of folders.
A breakdown of the query:
SELECT * FROM pictures: pictures is the name of the table
WHERE X <> '0': the picture at X = 0 should always be ignored
AND
folders REGEXP '^$folder': the first folder listed contains the desired folder
OR
folders REGEXP '\n$folder': any other folder listed contains the desired folder
ORDER BY X;: sorted in ascending order by X
The reason for the two "folders REGEXP" expressions is that the first folder isn't preceded by a \n.
THE PROBLEM
If the desired folder (stored in the $folder variable) contains any backslashes, the query returns no values.
For example, if I run the query for the value $folder = "folder1" using the values in the first quote above, I get the following array:
folder1\subfolder1
folder1\subfolder1\subfolder1b\subfolder1c
folder1\subfolder1\subfolder1d\subfolder1e
And, if I run the query for the value $folder = "folder1\subfolder1", it should return the following array:
folder1\subfolder1\subfolder1b\subfolder1c
folder1\subfolder1\subfolder1d\subfolder1e
But it actually returns:
So, any time my $folder variable contains a slash, the function always returns no values.
This is obviously not the desired result.
Just in case I needed to 'escape' out the slash character, I tried adding the following line before the query:
$folder =~ s/\\/\\\\/g;
This would alter my $folder value from "folder1\subfolder1" to "folder1\\subfolder1" thereby escaping out the backslash. The query returned the same result.
How do I use a variable whose value contains a backslash as a test variable in the regexp?
I have a MySQL database containing the following columns:
X: the unique index (a number)
name: filename of photograph
mimetype: mime type of picture file (like image/jpeg, etc)
dateuploaded: date the picture was uploaded to the server
datetaken: date the user reports the picture was taken
description: description of picture contents, possibly including multiple lines separated by \n
folders: list of "\n"-separated "folders" -- like folder1\subfolder1\n
folder2\subfolder2\n
folder1\subfolder1\subfolder1b\subfolder1c\n
folder1\subfolder1\subfolder1d\subfolder1e\n
filelocation: location of the picture file on the server
thumbnail: a 'blob' containing a 150px-wide thumbnail of the picture
viewcount: number of times the picture has been viewed
I am trying to construct a MySQL query that returns any values of X when the folders field contains a given folder name, or subfolder of that folder.
My code (assume that I have already opened the access to the database outside of this function):
sub grabPicturesInFolderAndSubFolders()
{
my $folder = $_[0];
my @returnPics;
my $sth = $dbh->prepare("SELECT * FROM pictures WHERE X <> '0' AND folders REGEXP '^$folder' OR folders REGEXP '\n$folder' ORDER BY X;") or die "$!";
$sth->execute;
while ($thisLine = ($sth->fetchrow_array())[0]) {
push @returnPics, $thisLine;
}
$sth->finish;
return @returnPics;
}
This function takes the input of a desired folder, and SHOULD return an array of values of X that contain that folder in it's list of folders.
A breakdown of the query:
SELECT * FROM pictures: pictures is the name of the table
WHERE X <> '0': the picture at X = 0 should always be ignored
AND
folders REGEXP '^$folder': the first folder listed contains the desired folder
OR
folders REGEXP '\n$folder': any other folder listed contains the desired folder
ORDER BY X;: sorted in ascending order by X
The reason for the two "folders REGEXP" expressions is that the first folder isn't preceded by a \n.
THE PROBLEM
If the desired folder (stored in the $folder variable) contains any backslashes, the query returns no values.
For example, if I run the query for the value $folder = "folder1" using the values in the first quote above, I get the following array:
folder1\subfolder1
folder1\subfolder1\subfolder1b\subfolder1c
folder1\subfolder1\subfolder1d\subfolder1e
And, if I run the query for the value $folder = "folder1\subfolder1", it should return the following array:
folder1\subfolder1\subfolder1b\subfolder1c
folder1\subfolder1\subfolder1d\subfolder1e
But it actually returns:
So, any time my $folder variable contains a slash, the function always returns no values.
This is obviously not the desired result.
Just in case I needed to 'escape' out the slash character, I tried adding the following line before the query:
$folder =~ s/\\/\\\\/g;
This would alter my $folder value from "folder1\subfolder1" to "folder1\\subfolder1" thereby escaping out the backslash. The query returned the same result.
How do I use a variable whose value contains a backslash as a test variable in the regexp?