PHP/SQL array comparison / nested loop problem...

Discussion in 'Web Design and Development' started by Super Macho Man, May 6, 2007.

  1. Super Macho Man macrumors 6502a

    Super Macho Man

    Joined:
    Jul 24, 2006
    Location:
    Hollywood, CA
    #1
    Well I stared at this one last night til I was cross-eyed, so I guess it's time to ask for help... :)

    I'm working with a map website/database. Each map is in one or more languages. I store languages and their codes (as the pkey) in a separate language table, with a join table to manage the relationships between maps and languages. I'm trying to display an HTML select list of all languages - that works fine. But I want to make the ones that are joined to the current map ($mapid) appear selected. For that, I created a second SQL statement ($sql2) and used a nested loop. The problem is that I don't think I'm comparing my arrays properly ("if $blah1 == $blah2"). If the current map has the first language in the list, that first language will appear selected. But only that first language.

    Please forgive the horrible programming, I was a liberal arts student and my only other programming experience was a high school Pascal class a long time ago. And I scored in the bottom 50% in math on the GRE :D

    PHP:
    // SQL statement for outer loop
    $sql "SELECT code, name FROM lang ORDER BY name";
    $result pg_exec($sql) or die ('Error: Query failed') ;
    $numrows pg_numrows($result);

    // SQL statement for inner loop
    $sql2 "SELECT lang_code FROM map_lang_join WHERE map_id = $mapid";
    $result2 pg_exec($sql2) or die ('Error: Query failed');
    $numrows2 pg_numrows($result2);

    for ( 
    $i 0$i $numrows$i++ ) {
        
    $row pg_fetch_array($result);
        echo 
    "<option value=\"" $row['code'] . "\"";
        
    $blah1 $row['code'];
        for ( 
    $j 0$j $numrows2$j++ ) {
            
    $row2 pg_fetch_array($result2);
            
    $blah2 $row2['lang_code'];
            if (
    $blah1 == $blah2) {
                echo 
    " selected=\"selected\"";
                break;
            }
    }
        echo 
    ">" $row['code'] . " " $row['name'] . "</option>\n";
    }
     
  2. savar macrumors 68000

    savar

    Joined:
    Jun 6, 2003
    Location:
    District of Columbia
    #2
    Yeah I don't really understand what you're trying to do, but I can make a few suggestions right off the bat, and if this doesnt help then maybe you can clarify your terms a bit (not sure if you're talking about cartographic maps or mathematical maps, etc.).

    The problem, I believe, is that in your inner loop you are trying to over all the results in the 2nd query. The thing is that most DBMS will only let you iterate through the results one time. This is why it works for the first item but none of the rest.

    The first you thing you should do is combine your two queries into one query. The join syntax is like this:

    Code:
    SELECT l.code c1, l.name n, m.lang_code c2 
    FROM lang l LEFT JOIN map_lang_join m ON l.code=m.lang_code
    WHERE m.map_id=$mapid
    This will get the list of all codes & names and store them in "c1" and "n", plus anytime the langauge code matches a code in the join table, it will fill in "c2".

    So now you just need one loop. Keep the outer loop, ditch the inner loop, and just check for:

    Code:
    if ($row['c2'] != '') echo ' selected="selected"'; 
    I'm guessing this is PostgreSQL by your variable names, this link might explain things better than I can: http://www.postgresql.org/docs/7.2/static/queries-table-expressions.html
     
  3. Winterfell macrumors regular

    Joined:
    Apr 3, 2007
    Location:
    Tulsa, Oklahoma
    #3
    You only need to fetch query results once. You're trying to do with PHP what the database should be doing.

    Not having any data with which to test, I can only kind of piece together what you're trying to do. I assume you've already populated the drop down list with another query and are just trying to automatically select the option that corresponds to the $mapid.

    The following query should give you all codes and names from the lang table that are associated with the $mapid.
    PHP:
    <?php
    // Modified SQL statement.
    $sql 'SELECT l.code, l.name ' .
        
    'FROM lang AS l, map_lang_join AS mlj ' .
        
    'WHERE l.code = mlj.lang_code ' .
        
    "AND mlj.map_id = $mapid " .
        
    'ORDER BY l.name';
    ?>
    Can you give us the code that you're creating the actual drop down list with?

    Edit: Savar's query may be better ... I'm not familiar with PostgreSQL's syntax, though more than likely both queries will yield the same result.
     
  4. Super Macho Man thread starter macrumors 6502a

    Super Macho Man

    Joined:
    Jul 24, 2006
    Location:
    Hollywood, CA
    #4
    savar, I really appreciate your detailed answer. Yeah, these are cartographic maps. I tried your suggestion and although it brings up all of the map's languages selected, it doesn't retrieve the full contents of the lang table - only the languages the map is in. So it's like the opposite problem now. :) I think I understand what that query is supposed to do, but I don't know how to fix it.

    Just some more info: I'm using an HTML multiple-select list, the kind where you hold down ctrl/option to select multiple items. The lang table has about 20 languages in it. I want them to all appear in the list for every map. But only the languages that the map is in should be selected.

    The two tables involved look like these:

    Code:
    lang:
    
    code	name
    ----	----
    eng	English
    fre	French
    ger	German
    rus	Russian
    ...
    
    map_lang_join:
    
    map_id	lang_code
    ------	---------
    1	eng
    2	eng
    3	spa
    3	ger
    ...
    
    My new code, with the inner loop replaced with that if statement:
    PHP:
    $sql "SELECT l.code AS c1, l.name AS n, m.lang_code AS c2" .
        
    " FROM lang AS l LEFT JOIN map_lang_join AS m ON l.code=m.lang_code" .
        
    " WHERE m.map_id = $mapid " .
        
    " ORDER BY n";
    $result pg_exec($sql) or die ('Error: Query failed') ;
    $numrows pg_numrows($result);

    for ( 
    $i 0$i $numrows$i++ ) {
        
    $row pg_fetch_array($result);
        echo 
    "<option value=\"" $row['c1'] . "\"";
        if (
    $row['c2'] != '') echo ' selected="selected"';
        echo 
    ">" $row['c1'] . " " $row['n'] . "</option>\n";
    }
     
  5. Winterfell macrumors regular

    Joined:
    Apr 3, 2007
    Location:
    Tulsa, Oklahoma
    #5
    Now I see what you're trying to do. Give this a go.

    PHP:
    <?php
    // Generate an array of lang codes used by a particular $mapid.
    $codes = array();
    $sql 'SELECT lang_code ' .
        
    'FROM map_lang_code ' .
        
    "WHERE map_id = $mapid";
    $result pg_exec($sql) or die('Error: Query failed.');
    while (
    $row pg_fetch_assoc($result)) {
        
    $codes array_push($codes$row['lang_code']);
    }

    // Generate the drop down list.
    $sql 'SELECT code, name ' .
        
    'FROM lang ' .
        
    'ORDER BY name ASC';
    $result pg_exec($sql) or die('Error: Query failed.');
    while (
    $row pg_fetch_assoc($result)) {
        echo(
    '<option value="' $row['code'] . '"');
        
    // Determine whether or not the language should be selected.
        
    if (in_array($row['code'], $codes)) {
            echo(
    ' selected="selected"');
        }
        echo(
    '>' $row['code'] . ' ' $row['name'] . '</option>' "\n");
    }
    ?>
     
  6. Super Macho Man thread starter macrumors 6502a

    Super Macho Man

    Joined:
    Jul 24, 2006
    Location:
    Hollywood, CA
    #6
    Hmm I gave it a whirl, but the language menu is populated by error messages like this:

    Warning: in_array() [function.in-array]: Wrong datatype for second argument in blahblah/modifymap.php on line 154 >eng English
    Warning: in_array() [function.in-array]: Wrong datatype for second argument in blahblah/modifymap.php on line 154 >hun Hungarian
    Warning: in_array() [function.in-array]: Wrong datatype for second argument in blahblah/modifymap.php on line 154 >ice Icelandic
    Warning: in_array() [function.in-array]: Wrong datatype for second argument in blahblah/modifymap.php on line 154 >ita Italian

    etc.

    (in over my head :p )
     
  7. Winterfell macrumors regular

    Joined:
    Apr 3, 2007
    Location:
    Tulsa, Oklahoma
    #7
    Sorry -- it was late and I didn't test it. :eek:

    The problem was that I was reassigning the $codes variable: since array_push() performs an action on the array, it returns the number of elements in the new array, which turned $codes into an integer! Whoops! That's why it was complaining about the wrong type. :p

    PHP:
    $codes array_push($codes$row['lang_code']);
    should be
    PHP:
    array_push($codes$row['lang_code']);
    Corrected code:

    PHP:
    <?php
    // Generate an array of lang codes used by a particular $mapid.
    $codes = array();
    $sql 'SELECT lang_code ' .
        
    'FROM map_lang_code ' .
        
    "WHERE map_id = $mapid";
    $result pg_exec($sql) or die('Error: Query failed.' "\n");
    while (
    $row pg_fetch_assoc($result)) {
        
    array_push($codes$row['lang_code']);
    }

    // Generate the drop down list.
    $sql 'SELECT code, name ' .
        
    'FROM lang ' .
        
    'ORDER BY name ASC';
    $result pg_exec($sql) or die('Error: Query failed.' "\n");
    while (
    $row pg_fetch_assoc($result)) {
        echo(
    '<option value="' $row['code'] . '"');
        
    // Determine whether or not the language should be selected.
        
    if (in_array($row['code'], $codes)) {
            echo(
    ' selected="selected"');
        }
        echo(
    '>' $row['code'] . ' ' $row['name'] . '</option>' "\n");
    }
    ?>
     
  8. savar macrumors 68000

    savar

    Joined:
    Jun 6, 2003
    Location:
    District of Columbia
    #8
    Okay, I see what you're trying to do. It's actually something that's not straightforward to do in a DBMS unless your DBMS has subqueries (like Oracle)...I don't know if PostgreSQL has subqueries so here's a different idea. I'm not sure if this is valid, though.

    Code:
    $sql = "SELECT l.code AS c1, l.name AS n, m.lang_code AS c2" . 
        " FROM lang AS l LEFT JOIN map_lang_join AS m " .
        " ON (l.code=m.lang_code AND m.map_id = $mapid)" . 
        " ORDER BY n"; 
    
    I just moved the condition for "map_id=$map_id" from the WHERE clause into the ON clause. Like I said, I'm not sure if that's legal because I've never tried that before. I'd be interested to know if this solves your problem, though.
     
  9. Super Macho Man thread starter macrumors 6502a

    Super Macho Man

    Joined:
    Jul 24, 2006
    Location:
    Hollywood, CA
    #9
    Beautiful - simply beautiful. You sir are a gentleman and a scholar. Thanks. :)

    savar, thank you too. I will try your idea too when I get some time.
     
  10. Winterfell macrumors regular

    Joined:
    Apr 3, 2007
    Location:
    Tulsa, Oklahoma
    #10
    I'm glad we were able to help you.

    This brings up a good point however: when do I step back and decide whether to try and accomplish everything on the backend (i.e. in a database query), or split it up and do it on the front end (i.e. PHP, ASP, etc.)?

    My experience has more or less taught me that it is often times faster to do a couple of simple queries, and let the language itself do some of the processing, rather than having the database do it all on the back end. It's just a matter of balancing both, imo. Either way, the same steps are going to have to be completed to achieve the end result. :)
     
  11. Super Macho Man thread starter macrumors 6502a

    Super Macho Man

    Joined:
    Jul 24, 2006
    Location:
    Hollywood, CA
    #11
    Yeah. I guess I tend to prefer to do things in the front-end at this point for two main reasons, 1) PHP is a bit friendlier in that it uses the same loops, variables, conditonals etc. that I first learned in programming, and it has that C-like syntax that is so similar to so many other popular languages, and 2) I would be hesitant about doing involved coding in SQL because my impression is that it's hard to code complicated SQL in a cross-platform fashion, despite the supposed standardization of SQL.

    Then again, there are some things that SQL makes it easy to accomplish that would be more difficult in a PHP-like language. (PHP can't do "select distinct" in two words, for example.) I'm glad I have the luxury of not having to worry about performance, and that I can just do whatever is easiest. :D Well, that is just my idiot PHP/SQL newbie liberal arts major opinion. :D
     

Share This Page