SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update field to portion of another fields value

    I have a field (field_id_25) in my table exp_weblog_data which I need to update to a portion of the value of another field (field_id_5).

    Where field_id_5 = <img src="{filedir_1}Reel1-1.gif" style="border: 0;" alt="image" width="480" height="360" />

    I need to grab the filename+extension portion of the above string.

    I would assume I'm going to SET field_id_25 = to a subquery(?).

    All the values for field_id_5 start with <img src="{filedir_1}. Is there some way to grab the value I need?

    Thanks

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why do you need field_id_25 at all? when you need the data from field_id_5 just use a string function to grab and display it. You shouldn't need to store redundant data.

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This ultimately will not be redundant data but in an effort to not overwrite these strings and consequently loose the file reference I'm creating a new field to store this data I need. Once updated and double checked the original field will be removed.

    Additionally, I don't want the entire string stored because it affects what I can do with the data within my CMS. I just need the filename and it's extension.

  4. #4
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    With the "string function" how would I select the substring from
    Code:
    <img src="{filedir_1}Reel1-1.gif" style="border: 0;" alt="image" width="480" height="360" />
    that falls between } and " from within my src parameter?

    I think I'd use
    Code MySQL:
    SELECT substring('field_id_5', 22); //22 includes 1 space - is that correct?
    How to I tell it to stop parsing at the closing quote?
    Last edited by hothousegraphix; Mar 30, 2009 at 20:36.

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Look into the use of SUBSTRING_INDEX to break apart your string.

  6. #6
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    K, I'm not too sure I'm not ending up with the same situation. Below I've replaced my variable with it's value to be more clear
    Code MySQL:
    SELECT SUBSTRING_INDEX('<img src="{filedir_1}Reel1-1.gif" style="border: 0;" alt="image" width="480" height="360" /> ', '}', -1);
    My understanding is that this query should return the following value:
    Reel1-1.gif" style="border: 0;" alt="image" width="480" height="360" />
    Using the negative value will return everything right of the delimiter. I still need to eliminate everything right of the first quote.

    Is this possible? Though it does seem a bit convoluted:
    Code MySQL:
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('<img src="{filedir_1}Reel1-1.gif" style="border: 0;" alt="image" width="480" height="360" /> ', '}', -1), '"', 1);

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by hothousegraphix View Post
    Is this possible?
    what happened when you tested it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well...when I test
    Code MySQL:
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(field_id_5, '}', -1), '"', 1); AS file_name FROM exp_weblog_data
    I get a paginated result set of 50 entries per page and for those entries with images it seems the SUBSTING_INDEX function is producing the intended results, but...when I select one of the pagination links I get an error message of "invalid GET DATA".

    It seems I'm running into a system issue which I certainly don't expect this thread to be able to address. However, the query above addresses every entry in the system and I wonder if this might just be the root cause of the error. If it were more efficient, only targeting those entries where field_id_5 is populated, I wonder is this would address what I'm seeing.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    did you test it with that semicolon in the middle of the sql?

    did you test it outside of php?

    if the query works correctly, i suggest we move this thread out of the mysql forum and into the php form
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Aug 2006
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You know what, I'm sorry the semicolon is a typo here. It's not been apart of my test query.

    I'm testing within my CMS which is PHP based however, this query is not occurring as part of a php function.

    When testing within phpMyAdmin it seems I'm getting the results I want.

    So, to switch this to an update I'd simply do:
    Code MySQL:
    UPDATE exp_weblog_data SET field_id_25 = SUBSTRING_INDEX(SUBSTRING_INDEX(field_id_5, '}', -1), '"', 1)

    ?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •