SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    Confirmed Halfwit
    Join Date
    Oct 1999
    Location
    Vancouver, BC, Canada
    Posts
    983
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Manipulate text result as part of a query?

    Hello,

    I have the following query:
    Code:
    $query = "SELECT server, type, description "
           . "FROM information "
           . "WHERE server='$server' OR server = 'all' "
           . "ORDER BY id DESC LIMIT 0, $show_how_many;";
    What I'd like to do is manipulate the "type" string for each resulting row.. as part of the query.

    For example, if the type string value is "comment1".. then I want to replace the text with "mytext1".
    If type string value is "comment2".. then I want to replace the text with "mytext2".

    Does that make sense? Is it possible to do this as part of the query? I can't do it in a loop after the query, because the result set has to stay intact.

    Thanks!
    - A simple online WYSIWYG editor for HTML code snippets.
    - Managed Web Hosting - $3.95/month (resellers welcome)
    - Why pay more? $8.95 domains & $9.95 SSL certificates!

  2. #2
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    AFAIK, you can't do that in a query.
    You can however do it after the query, and keep the result set intact by just storing the result in a 2-dimensional array with a loop.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  3. #3
    Confirmed Halfwit
    Join Date
    Oct 1999
    Location
    Vancouver, BC, Canada
    Posts
    983
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Manipulate text result as part of a query?

    OK.. here's what I have.. can you show me how to do the array so that the result set is intact and contains the "replaced" strings?
    PHP Code:
    $query "SELECT * "
           
    "FROM information "
           
    "WHERE server='$server' OR server = 'all' "
           
    "ORDER BY id DESC LIMIT 0, $show_how_many;";

    $result mysql_db_query("status"$query$connection) or die ("Error in query: $query. " mysql_error());

    while(list(
    $id$date$desc$type$serv) = mysql_fetch_row($result)) {
        if(
    $type != "comment") { $type "($type)"; } 
        else { 
    $type =""; }

    (It's the if and else part I wanted to try and do in with SQL.

    Thanks for your help!
    - A simple online WYSIWYG editor for HTML code snippets.
    - Managed Web Hosting - $3.95/month (resellers welcome)
    - Why pay more? $8.95 domains & $9.95 SSL certificates!

  4. #4
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So whats wrong with the code you posted in your second post? What is it doing/not doing?
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  5. #5
    Confirmed Halfwit
    Join Date
    Oct 1999
    Location
    Vancouver, BC, Canada
    Posts
    983
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Defender1
    So whats wrong with the code you posted in your second post? What is it doing/not doing?
    I need the corrected strings values to be in a mysql result set. With the way I am doing it now.. I get a result set from the query, and then I do the processing to get the text I want for each row.

    You see what I mean? If I can shove the resulting string values back into the result set without screwing it up, that would be ok. I just don't know how to do that. (Mostly, because I don't know what a result set really is... I just use the php functions like "mysql_fetch_row" to get access to it.)
    - A simple online WYSIWYG editor for HTML code snippets.
    - Managed Web Hosting - $3.95/month (resellers welcome)
    - Why pay more? $8.95 domains & $9.95 SSL certificates!

  6. #6
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, just store result into a variable.
    instead of using list, do something like
    PHP Code:
    while($row mysql_fetch_array($result))
    {
      
    // do some stuff

    that will store the entire row into $row, and you can call it in the loop with $row['column_name']
    also, if you want to store the entire result, you'd need to make $row into a 2 dimensional array, and just have $row['$i']['$j'] where i would be the number of the row returned, and j would be the row itself.
    just bump up i each time the loop ends.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  7. #7
    Confirmed Halfwit
    Join Date
    Oct 1999
    Location
    Vancouver, BC, Canada
    Posts
    983
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So how would I put everything back into a workable mysql result set?

    I understand how to use mysql result sets such as you describe, however I need to actually keep everything in the set..

    In other words, I want to do this:

    $resultset['column_name'] or however that works..

    Make sense?
    - A simple online WYSIWYG editor for HTML code snippets.
    - Managed Web Hosting - $3.95/month (resellers welcome)
    - Why pay more? $8.95 domains & $9.95 SSL certificates!

  8. #8
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not sure that i understand what your saying, but i'm pretty sure that's what
    PHP Code:
    $row mysql_fetch_array($result
    does.
    it stores one row of the result set in the array $row[]
    if you want every row to be in there, you'll need a 2 dimensional array.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  9. #9
    Confirmed Halfwit
    Join Date
    Oct 1999
    Location
    Vancouver, BC, Canada
    Posts
    983
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What I'm trying to do is manipulate the data in the result set, and NOT data that is extracted from the result set.

    Using your example just extracts one row into the $set variable... but then I would need to put that row back into the result set.

    See what I mean?
    - A simple online WYSIWYG editor for HTML code snippets.
    - Managed Web Hosting - $3.95/month (resellers welcome)
    - Why pay more? $8.95 domains & $9.95 SSL certificates!

  10. #10
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Defender1
    if you want every row to be in there, you'll need a 2 dimensional array.
    That is the result set.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  11. #11
    SitePoint Columnist Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,066
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Question: Why do you want to modify the result set anyway? Presumably you are going to use the result set a bit later in your script - if so, why not apply the logic which deals with different replacements for the "type" variable just before you use the data from the result set in your script?

    /reaches for MySQL book

    If you are dead set on performing text manipulation within the query then I'm pretty sure mySQL can do it - it's got some very powerful built in functions. I would strongly recommend NOT doing it as you'll end up with a horribly complex query which will be dependant on mySQL and virtually impossible to port to other databases but that probably won't be an issue for what you are doing...

    Your initial problem is a bit vague as well:
    For example, if the type string value is "comment1".. then I want to replace the text with "mytext1".
    If type string value is "comment2".. then I want to replace the text with "mytext2".
    I'm guessing by text you mean the contents of the "description" field.

    Unless... are you saying you actually want to UPDATE the information saved in the database table? If so then you're looking at a completely different solution to your problem - however I'll assume for the moment that you aren't intending to permanently alter what is saved in the table (mainly because I want to have a hack at the query now that I've grabbed the book )

    Here's the query. It uses a pair of nested IF statements which look horrible but I'm pretty sure will work (I haven't tested this as I don't have mySQL on the PC I'm using):

    SELECT server, type, IF((type != 'comment1' AND type != 'comment2'), description, IF((type = 'comment1'), 'mytext1', 'mytext2')) AS text
    FROM information
    WHERE server='$server' OR server = 'all'
    ORDER BY id DESC LIMIT 0, $show_how_many

    The scary bit is this:

    IF((type != 'comment1' AND type != 'comment2'), description, IF((type = 'comment1'), 'mytext1', 'mytext2')) AS text

    Here's how IF works in mySQL:

    IF(expr1, expr2, expr3)

    IF expr1 evaluates to true then expr2 will be returned (and assignedto text in my example), otherwise expr3 will be returned. I had to use two IF statements because your code required 3 possible outcomes (the contents of description if type wasn't comment1 or comment2, or 'mytext1' or 'mytext2').

    Hope that makes sense and answers your query, if it doesn't then post more details of what you're trying to achieve

    Cheers,

    Simon


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
  •