On Mon, 21 Jun 2004, David Blomstrom wrote: > Sergio Salvi wrote: >
[...] > > Separate data from how it's displayed. I mean, create > a table called > "states" with the fields "state_id", "state_name" and > "state_url". Put > the data in the according field: > > state_id state_name state_url > 1 Alabama http://www.alabama.gov > 2 Washington http://access.wa.gov > ....and so on > > OK, I see. But I assume you mean <http://www, rather > than http without the beginning tag - or do you insert > that with PHP? It just occurred to me that I probably > don't need to put </a> in the database, because I > probably add that with PHP somehow. I would add the "a href" HTML tag in the application and store only the URL in the database. Just do not remove the "http://"; and assume in your application that every URL starts with that because some of them may be "https://";. If you are space usage paranoid (hehe), you could create a enum("y","n") field called "use_ssl" and remove the "http://"; from every URL, setting the "use_ssl" field to "y" when the URL is a https:// one. Do not assume you'll always display the URLs in an HTML output. What if you decide to send it by plain-text mail? Or output in XML to export it to another system? HTH. []s, SAlvi > > > --- Bob Ramsey <[EMAIL PROTECTED]> wrote: > > Personally, I'd split that into 2 fields. I think > > that's a better way > > to model the data unless there's something I don't > > know. > > > > Otherwise, try this: > > > > mysql> select * from url; > > +-----------------------------------------------+ > > | url | > > +-----------------------------------------------+ > > | <a href="http://www.alabama.gov";>Alabama</a> | > > | <a href="http://access.wa.gov";>Washington</a> | > > +-----------------------------------------------+ > > 2 rows in set (0.00 sec) > > > > mysql> select substring(url,locate('">',url)+2, > > char_length(url)-locate('">',url > > )-5) as state from url; > > +------------+ > > | state | > > +------------+ > > | Alabama | > > | Washington | > > +------------+ > > 2 rows in set (0.00 sec) > > > > mysql> > > > > > > What I had to to was to have mysql take the string: > > > > <a href="http://www.alabama.gov";>Alabama</a> > > > > and give me the parts between "> and </a>. First, I > > had to find the > > position of "> and then add 2 to it. The substring > > function in mysql > > takes the parameters string, starting_position, and > > length. Using > > locate, I got the starting postion and added 2 to > > it. For length, I had > > to use locate again; locating "> gives me the > > position of the " in ">. > > Subtracting 5 gives me the right length after > > discounting the </a> and > > the 2 positions I'm off from ">. > > > > > > Someone more experienced that I can tell you if > > there's a more effecient > > way. My inclination would be that for best results, > > you should split > > the field in two and build your webpage like this: > > > > <a href="$URL">$STATE</a> > > > > Hope this helps. > > I think I understand. Thanks. > > > > > > __________________________________ > Do you Yahoo!? > New and Improved Yahoo! Mail - 100MB free storage! > http://promotions.yahoo.com/new_mail > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
