Sergio Salvi wrote: "Doing the way you're suggesting would create some problems:
- data redundancy (<a href="http:// and </a> on every field) - storing two different information on a single field (state name and URL) - the MySQL index would be filled with at least 8 bytes of useless data (a href="), wasting more disk space - no database-friendly way to search for states or even to sort them by name 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. --- 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]
