SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    a, i hope, simple sql query question.

    Not sure how you do this.

    I want to do a query like

    SELECT * from table_name WHERE name=$name AND group=$blah

    in the query, there will always be a match for name=$name, but there might not be a group=$blah. So what i want is this logic:

    if name=$name + group=$blah match for a row then return that row.

    if for name=$name there is no match for group=$blah then return the result for which group="" ie is empty.

    Does that make sense?

    imagine
    name = chris
    group = 10

    and i have

    name | group

    chris | 10
    chris |
    chris | 20

    initially i want the chris | 10 to be the row returned.
    If however that row does not exist i want the chris | row returned.

    I will always only want 1 row returned so if the way to do this is have more than one row and sort it so the first one is they one i want i am happy with that.

    cheers.

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Chris not sure if this is what you want but its a try:

    SELECT * from table_name WHERE name=$name AND ((group=$blah) OR (group='')) LIMIT 1

    On second thought that probably won't work, let me think about it for a minute
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT * FROM template WHERE name=header AND ((group='fantasy') OR (group='')) LIMIT 1

    is giving an sql error, although it does look like what i want assuming that if it matched the first OR condition it would appear higher in the list. If it wouldn't then an ORDER by group should do it at the end?

  4. #4
    imagine no limitations exbabylon's Avatar
    Join Date
    Dec 2000
    Location
    Idaho, USA
    Posts
    452
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $sql "SELECT * FROM table WHERE name='$name' and group='$group' LIMIT 1";
    $result mysql_query($sql);
    if(!
    mysql_num_rows($result)){
        
    $sql "SELECT * FROM table WHERE name='$name' LIMIT 1";
        
    $result mysql_query($sql);

    The var $result is going to be the data you are after.
    Blamestorming: Sitting around in a group discussing why a deadline was missed or a project failed and who was responsible.

    Exbabylon- Professional Internet Services

  5. #5
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that looks like it will work but cutting it to one query would be nice if possible.

    I think this does it

    Code:
    SELECT * FROM template WHERE name='header' AND (group_name='fantasy' OR group_name='') ORDER BY group_name DESC LIMIT 0,1
    php tags were cutting of the 1, lets see what code makes of it

  6. #6
    imagine no limitations exbabylon's Avatar
    Join Date
    Dec 2000
    Location
    Idaho, USA
    Posts
    452
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ORDER BY was a nice touch sorry I wasn't thinking
    Blamestorming: Sitting around in a group discussing why a deadline was missed or a project failed and who was responsible.

    Exbabylon- Professional Internet Services

  7. #7
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks and thank you for helping out.

    unfortunatly i am not sure if i can use my method. I am using this for getting templates, and basically want to be able to have an override on templates, like a category with a specific set of templates if you see what i mean.

    Problem is, that at the start of the page i get the templates in a group and make a template cache of them (like VB does), this would destory the LIMIT bit because the limit will be based on the number of matches. The query i am using is:

    SELECT template,name
    FROM template
    WHERE (name IN ('$template_list'))

    where template list is like 'header','footer','category'

    sort of thing.

    I don't know how this could be modified i doubt it can.

  8. #8
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the problem with the multi query one is say i have

    NAME | GROUP

    header | fantasy
    header |
    footer |
    footer | fantasy
    main |

    now this normal query will get

    header | fantasy
    footer | fantasy
    on top fine but
    footer |
    main |
    header |

    will be ordered by i don't know. The thing to do is get main to be ordered at the top of footer and header. I have no idea how to do that as basically i want to order it by lack of already in the list.

    Any ideas on this?

  9. #9
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well i got round this i think i got rid of the LIMIT bit of the query and then did something like this:

    PHP Code:
    while ($template $db->row_array()) {

    // have to deal with multipe template results. Ensures that specific template is choosen
    // in preference to non-specific by creating an array of templates that have been created.

    if ($array[$template[name]] != "done") {
    $template_cache[$template[name]] = $template[template];
    $array[$template[name]] = "done";

    it seems to work

  10. #10
    SitePoint Member
    Join Date
    Jun 2001
    Location
    PA
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why not just use conditionals to build your query statement?

    if such and such variable is set, $sql.="this"
    if it doesn't, $sql.="that"

    Or am I missing something?
    Adam W.
    Web Developer / Columnist
    http://www.uscho.com

  11. #11
    imagine no limitations exbabylon's Avatar
    Join Date
    Dec 2000
    Location
    Idaho, USA
    Posts
    452
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you missed it
    Blamestorming: Sitting around in a group discussing why a deadline was missed or a project failed and who was responsible.

    Exbabylon- Professional Internet Services

  12. #12
    SitePoint Member
    Join Date
    Jun 2001
    Location
    PA
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by exbabylon
    you missed it
    Upon further review ... yes, I did ... :-)

    That's what you get for reading too fast ... sorry
    Adam W.
    Web Developer / Columnist
    http://www.uscho.com


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
  •