Gossamer Forum
(Advanced Search)
for
Home : General : Perl Programming :

mySQL -> array of values

May 14, 2001, 2:24 PM
sponge
Veteran (1936 posts)
sponge's avatar
May 14, 2001, 2:24 PM
Post #1 of 11
Views: 5246
Shortcut
Quote Reply
mySQL -> array of values
Looking at documenation, the only way to get an array of values is with with column type SET, but you must preset all the posible values. Is there a way to have multiple values without preseting them in the field definition? So far the only solution I've come up with is to use join() on that field before adding the record, then split() the field back into an array when I want to view the values.

--Drew
Free, hot camel soup for Links hackers...
http://www.camelsoup.com
May 14, 2001, 3:08 PM
Stealth
Veteran (17240 posts)
Stealth's avatar
May 14, 2001, 3:08 PM
Post #2 of 11
Views: 5138
Shortcut
Quote Reply
Re: mySQL -> array of values In reply to
In terms of database design and relational databases, you are not really adding an "array" of values into one field/column. You should never have multi-values within columns/fields.

What you need to do is add an intersection table if any of your columns/fields are supposed to have multiple values.

And actually, if you want to have a BINARY field that has multiple values to select from, but inputting one value into the field/column itself, you need to use ENUM.

And for the "set", you would input:

Code:

'Orange','Yellow','Green'


Then for the default property of the field/column, you can use:

Code:

Green


Regards,

Eliot Lee
May 14, 2001, 5:32 PM
sponge
Veteran (1936 posts)
sponge's avatar
May 14, 2001, 5:32 PM
Post #3 of 11
Views: 5142
Shortcut
Quote Reply
Re: mySQL -> array of values In reply to
In Reply To:
What you need to do is add an intersection table if any of your columns/fields are supposed to have multiple values.
Thanks Eliot. I've got your suggestion working. This is so much easier than flatfile Smile. I've actually managed to get an SQL version of my PortalLinks mod working in just a couple hours learning.

--Drew
Free, hot camel soup for Links hackers...
http://www.camelsoup.com
May 14, 2001, 5:44 PM
Stealth
Veteran (17240 posts)
Stealth's avatar
May 14, 2001, 5:44 PM
Post #4 of 11
Views: 5140
Shortcut
Quote Reply
Re: mySQL -> array of values In reply to
Great...BTW: I posted the structure I am using for my "Mylinks" (which I have re-named My Bookmarks) awhile back in the Links 2.0 Customization and also in the Links SQL Plugins forums.

See, I told you awhile back that MySQL is much easier than flat file systems...hehe!

Regards,

Eliot Lee
May 15, 2001, 4:32 PM
sponge
Veteran (1936 posts)
sponge's avatar
May 15, 2001, 4:32 PM
Post #5 of 11
Views: 5114
Shortcut
Quote Reply
Re: mySQL -> array of values In reply to
Hm... rather than quering mysql to see if a particular UserID/LinkID pair already exixts for the user before adding the record to the bookmarks table, how can I set up the database so it will error out automaticly when trying to add it? I've looked over a couple threads dealing with primary/index/unique atributes but I can't figure out what combination I'm supposed to be using.

--Drew
Free, hot camel soup for Links hackers...
http://www.camelsoup.com
May 15, 2001, 4:48 PM
Stealth
Veteran (17240 posts)
Stealth's avatar
May 15, 2001, 4:48 PM
Post #6 of 11
Views: 5116
Shortcut
Quote Reply
Re: mySQL -> array of values In reply to
Simply set LinkID and UserID as index fields via MySQLMan. And the LinkID field/column should be UNIQUE. That's all to it.

But in terms of providing a user-friendly error message, you will have to query the table to check whether UserID/LinkID combination exists.

The problem is that if you simply set up a secondary index wihin the Portal table, then you will get an non-user-friendly "Software Error" message.

Regards,

Eliot Lee
May 15, 2001, 11:57 PM
sponge
Veteran (1936 posts)
sponge's avatar
May 15, 2001, 11:57 PM
Post #7 of 11
Views: 5119
Shortcut
Quote Reply
Re: mySQL -> array of values In reply to
I had to set both columns as index/unique in order to make it work. setting UserID as index and LinkID as index/unique kept causing "duplicate entry '1' for key '1'" and similar errors.

In Reply To:
But in terms of providing a user-friendly error message, you will have to query the table to check whether UserID/LinkID combination exists.
This seems to work just fine:
Code:
$sth = $dbh->prepare("INSERT INTO Links SET UserID='$uid', LinkID='$id'");
$sth->execute() or &error("Already saved to favorites.");
Hm.. using $! as the error string really isn't that unfriendly. it just says, 'try again'. Smile

--Drew
Free, hot camel soup for Links hackers...
http://www.camelsoup.com
May 16, 2001, 9:56 AM
Stealth
Veteran (17240 posts)
Stealth's avatar
May 16, 2001, 9:56 AM
Post #8 of 11
Views: 5124
Shortcut
Quote Reply
Re: mySQL -> array of values In reply to
Yep..that will work, too.

Glad you figured it out.

Regards,

Eliot Lee
May 17, 2001, 8:41 PM
Stealth
Veteran (17240 posts)
Stealth's avatar
May 17, 2001, 8:41 PM
Post #9 of 11
Views: 5064
Shortcut
Quote Reply
Re: mySQL -> array of values In reply to
Drew,

Out of curiosity, were you able to add the "Delete all Links" within your upgraded Portal script? This is one feature that I was not able to fix in my My Bookmarks script for Links SQL v.1.3.

If you were able to implement this feature, I'd be interested to see what codes you used and compare them with what I have written.

Thanks in advance.

Regards,

Eliot Lee
May 17, 2001, 9:58 PM
sponge
Veteran (1936 posts)
sponge's avatar
May 17, 2001, 9:58 PM
Post #10 of 11
Views: 5069
Shortcut
Quote Reply
Re: mySQL -> array of values In reply to
Yes I did. You can view a copy of the whole script at ftp://ftp.camelsoup.com/pub/PortalLinks_2.0_sql/portal.txt
It's about 130 lines shorter than the flatfile version Smile

Here's the relevant code:
Code:
$sth = $dbh->prepare("DELETE FROM Links WHERE UserID='$uid'");
($sth->execute() ne '0E0') or &error("An unkown error occured while clearing your favorites.");
--Drew
Free, hot camel soup for Links hackers...
http://www.camelsoup.com
May 18, 2001, 7:12 AM
Stealth
Veteran (17240 posts)
Stealth's avatar
May 18, 2001, 7:12 AM
Post #11 of 11
Views: 5033
Shortcut
Quote Reply
Re: mySQL -> array of values In reply to
Thanks, Drew!

Weird...I am using similar codes....

in sub main
Code:

if ($in->param('delete_all')) {
&delete_all($in,$dynamic);
}


Code:

sub delete_all {
# --------------------------------------------------------
my ($in, $dynamic) = @_;
my $userid = $USER->{UserID};

# Connect to Bookmarks Table
my $rec = $BOOKMARKSDB->get_record ($userid, 'HASH');
my $title_linked = &build_linked_cgi_title ("My Bookmarks/Error: Unable
to Process Form");

($rec) or &site_html_error ({error => "Bookmarks not found or already de
leted.", title_linked => $title_linked}, $in, $dynamic),return;
$rec = $BOOKMARKSDB->prepare ("SELECT *
FROM Bookmarks
WHERE UserID = '$userid'
");
$rec->execute() or die $DBI::errstr;
# Delete Bookmarks from Bookmarks Table
$BOOKMARKSDB->do (" DELETE
FROM Bookmarks
WHERE UserID = '$userid'
");

my $title_linked = &build_linked_cgi_title ("My Bookmarks/All Bookmarks
Deleted");
&site_html_bookmarks_delete_all_success ({title_linked => $title_linked}
, $in, $dynamic);
}


The only thing that happens is that the bookmark.cgi script re-loads to the home page after clicking on the following link:

Code:

<a href="http://www.mydomain.com/bin/bookmark.cgi?delete_all=1">Delete All Links</a>


Very weird....

Thanks for posting your codes...I'll continue to play around with it.

Regards,

Eliot Lee