SitePoint Sponsor

User Tag List

Results 1 to 12 of 12

Thread: Advanced sql

  1. #1
    SitePoint Zealot Nova's Avatar
    Join Date
    Sep 2002
    Location
    Netherlands
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Advanced sql

    i got the following query
    PHP Code:
    SELECT T2.IDB1.BIOGRAFIE_IDT1.TITEL,
                     
    CONCAT(GROUP_CONCAT(DISTINCT T1.MEDEWERKER 
                                     ORDER BY T1
    .VOLGNUMMERMEDEWERKER ASC SEPARATOR '1'), ' (',T1.LEEF_JAREN,')') AS MEDEWERKERS,
                                     
    GROUP_CONCAT(DISTINCT T1.MEDEWERKER_ID 
                                     ORDER BY T1
    .VOLGNUMMERMEDEWERKER ASC SEPARATOR '1') AS MEDEWERKERS_ID
                    
    FROM TITEL
    .TITEL AS T1
                 
    TMP_GELEEND AS T2
    LEFT JOIN TITEL
    .BIOGRAFIE AS B1 ON (T1.MEDEWERKER_ID B1.MEDEWERKER_ID)
                             
    WHERE T1.TITELNUMMER T2.TITELNUMMER 
    GROUP BY T1
    .TITELNUMMER  LIMIT 3 
    and the result is something like this

    PHP Code:
    'ID','BIOGRAFIE_ID','TITEL','MEDEWERKERS','MEDEWERKERS_ID'
    '16'
    ,'[NULL]','9 symphonies/gardiner (6)','Beethoven, Ludwig van ()','0'
    '118'
    ,'[NULL]','Symphony no.2','Arnold, Malcolm (1921-)','38559'
    '234'
    ,'[NULL]','The English Bach','Lukas, Victor1Lukas Consort1Bach, Johann Christian (1735-1782)','2637112637012425' 
    note the empty () how can i change the query so that if there is no year found that the () disapear
    if you can't beat them call them cheaters

    www.phppatterns.nl

  2. #2
    Kitty Lizard reptilianfeline's Avatar
    Join Date
    Aug 2002
    Location
    Somewhere in Cyberspace
    Posts
    385
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Remove the ( and ) from the query, and place it in the year instead.
    Don't write 1972-1998
    Write (1972-1998) instead.

    Unless you're going to do a lot of math with the year of birth and the year of death. Then you need to put it in the output HTML instead.
    Reptilian Feline
    | www.reptilian-feline.net | Art | Tutorials | Pets | Music |
    Webdesign the hard way...

  3. #3
    SitePoint Zealot Nova's Avatar
    Join Date
    Sep 2002
    Location
    Netherlands
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well that's the problem i can't change the values in the database coz all the scripts that are running the site now are based on the 1972-1998 values and to change all the scripts and delfi acplications is not what we are willing

    and can't put them in html coz it wil take to much (regexp) extra loadtime

    that's wy i was tying to get it in the query
    if you can't beat them call them cheaters

    www.phppatterns.nl

  4. #4
    Kitty Lizard reptilianfeline's Avatar
    Join Date
    Aug 2002
    Location
    Somewhere in Cyberspace
    Posts
    385
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What are you doing with the result? Are you creating a table in HTML, or...?

  5. #5
    SitePoint Zealot Nova's Avatar
    Join Date
    Sep 2002
    Location
    Netherlands
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the function where the MEDEWERKERS and MEDEWERKERS_ID
    are transformed into a link
    PHP Code:
    //deze functie maakt een artiest url aan
    function render_artiest_link($MEDEWERKERS$MEDEWERKERS_ID) {
    $m explode('%'$MEDEWERKERS);
    $mid explode('%'$MEDEWERKERS_ID);
    $totall_m count($m);
    $link '';
     
    for(
    $i 0$i $totall_m$i++) {
        
    $link .= '<a href="some_url.php?medewerker_id='.$mid[$i].'">'.$ [$i].'</a><br>';
    }
    return 
    $link;

    this is a line code in a loop that will call the function
    PHP Code:
    '<td nowrap><u>'.render_artiest_link($result[$i]['artiest'], $result[$i]['medewerker_id']).
    if you can't beat them call them cheaters

    www.phppatterns.nl

  6. #6
    Kitty Lizard reptilianfeline's Avatar
    Join Date
    Aug 2002
    Location
    Somewhere in Cyberspace
    Posts
    385
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK... so when it's outputed the whole thing with composer name as well as year is displayed?
    Reptilian Feline
    | www.reptilian-feline.net | Art | Tutorials | Pets | Music |
    Webdesign the hard way...

  7. #7
    SitePoint Zealot Nova's Avatar
    Join Date
    Sep 2002
    Location
    Netherlands
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yup
    if you can't beat them call them cheaters

    www.phppatterns.nl

  8. #8
    Kitty Lizard reptilianfeline's Avatar
    Join Date
    Aug 2002
    Location
    Somewhere in Cyberspace
    Posts
    385
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah... I see... MEDEWERKERS is the part with the name as well as the year. OK...

    And $i is the same as MEDEWERKERS, right? I think I see the point...

    OK... let's see... you explode $MEDEWERKERS to get the parts. Can you devide it into $name, (, $years and )? That is, 4 parts.
    Reptilian Feline
    | www.reptilian-feline.net | Art | Tutorials | Pets | Music |
    Webdesign the hard way...

  9. #9
    SitePoint Zealot Nova's Avatar
    Join Date
    Sep 2002
    Location
    Netherlands
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeay finaly got it, Thanx reptilianfeline
    did some examples so maby i can help out other people.
    example:
    PHP Code:
    $string 'name, lastname %name2, lastname2'
    this string contains 2 names
    and get it with a explode
    PHP Code:
    $names explode('%'$string); 
    PHP Code:
     $names[0] = namelastname 
    $names
    [1] = name2lastname2 
    well this is ok but now the dates
    (keep in mind there are not always dates)

    PHP Code:
    $string 'name, lastname ^2003 %name2, lastname2'
    ok now we got a date but it needs () so the 2 way to do it

    1. do a regexp that will search for the date en add () (very very slow )
    2. do a double explode on ^ so the name is in [0] and the year in [1]

    there is always a [0] with the name in it
    then i can check if there is a [1] and if there is add () like $string = '('.$string[1].')'; and voila it's done..

    thanx for all the help and it was so simple ... some times i can't get the right manner to figure it out
    if you can't beat them call them cheaters

    www.phppatterns.nl

  10. #10
    Kitty Lizard reptilianfeline's Avatar
    Join Date
    Aug 2002
    Location
    Somewhere in Cyberspace
    Posts
    385
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP and MySQL seems so complicated, so it's very easy to overlook the simple sollution to the problem. Glad I could help.

  11. #11
    SitePoint Member
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Or something like this:

    CONCAT('foo', IF(ISNULL(LEEF_JAREN), '', CONCAT('(', LEEF_JAREN, ')')));

    HTH Andre

  12. #12
    SitePoint Zealot Nova's Avatar
    Join Date
    Sep 2002
    Location
    Netherlands
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yyup was also trying it but didn't work...
    after a while i realised that the returnd data ca's be a null but is a empty string

    so CONCAT('foo', IF((LEEF_JAREN) = '', '', CONCAT('(', LEEF_JAREN, ')')));

    worked fine
    if you can't beat them call them cheaters

    www.phppatterns.nl


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
  •