This is what I tried to find an entry in the column,
but it doesn't work for some reason. This is a
download center, and I want to se if $cfpd[5]
has already been downloaded.
my ($dls, @downloads);
$query = qq{ SELECT downloads,bytesdown,filesdown FROM $utable WHERE handle=? };
$sth = $dbh->prepare ( $query );
$sth->execute ($user);
$sth->bind_col(1, \$dls);
while ($sth->fetch) { @downloads = split(/:/,$dls); }
push(@downloads,$cfpd[5]);
# added this part to find item
$already_downloaded = 0;
foreach $dl_file (@downloads) {
if ($dl_file eq $cfpd[5]) {
$already_downloaded = 1;
last;
}
}
if ($already_downloaded eq 0) {
# end addition
my $downloads = join(':',@downloads);
if ($dl =~ /$song$/) {
$query = qq{ UPDATE $utable SET dlcounter=dlcounter-1, downloads=?,
bytesdown=bytesdown+$fsize, filesdown=filesdown+1 WHERE handle=? };
}elsif ($dl =~ /$video$/) {
$query = qq{ UPDATE $utable SET dlcounter=dlcounter-3, downloads=?,
bytesdown=bytesdown+$fsize, filesdown=filesdown+1 WHERE handle=? };
}
$sth = $dbh->prepare ( $query );
$sth->execute ($downloads,$user);
Any ideas?
On Sat, 29 Nov 2003 00:53:10 +0100
"Hans van Harten" <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote:
> >> From: Bruce Therrien <[EMAIL PROTECTED]>
> >> Date: 2003/11/28 Fri PM 01:42:14 CST
> >> To: [EMAIL PROTECTED]
> >> Subject: Retrieving info from a Joined column
> >>
> >> We have a column in our table that has
> >> new info added each time using a JOIN function.
> The data sample makes me rather think
> update table set field=CONCATE(field,':','more')
>
> >> The data is separated using a : separator.
> > This doesn't answer your question...
> > However, note that in most instances using columns with multiple
> > values is a bad idea in a relational database. If possible, you'd be
> > better off with a design that does not require multiple values in a
> > single column.
> I'ld second that. However, to maintain compatible output to your Perl script
> consider GROUP_CONCAT http://www.mysql.com/doc/en/GROUP-BY-Functions.html
>
> >> How does one retrieve this info from the colomn and put it
> >> into list format,
> In Perl there is 'split'; takes a 'field delimiter and a string to return an
> array of strings.
>
> >> or check for duplicates,
> Using 'where NOT( field LIKE '9807:' and field LIKE ':9807' and field LIKE
> ':9807:' ' ) within your UPDATE should do the tric
>
> >> or do a search in the column for a certan string.
> On an existing data base you might consider LIKE
> ... where field LIKE '9807:' and field LIKE ':9807' and field LIKE ':9807:'
> Adding a ':' to both the beginning and end of the content would obsolete the
> first and second part of the above.
> However, you need to 'shift' and 'pop' a pair of empty fields in Perl AND
> change to CONCATE(field,'more',':')
>
> HansH
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
Bruce Therrien <[EMAIL PROTECTED]>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
