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

[Super Macho Man]

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

// 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";
}

 

[savar]

Well I stared at this one last night til I was cross-eyed, so I guess it's time to ask for help...

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:

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:

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

 

[Winterfell]

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
// 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.

 

[Super Macho Man]

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:

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:

$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";
}

 

[Winterfell]

Now I see what you're trying to do. Give this a go.

<?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");
}
?>

 

[Super Macho Man]

Now I see what you're trying to do. Give this a go.

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 )

 

[Winterfell]

Sorry -- it was late and I didn't test it.

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.

$codes = array_push($codes, $row['lang_code']);

should be

array_push($codes, $row['lang_code']);

Corrected code:

<?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");
}
?>

 

[savar]

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.

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.

$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.

 

[Super Macho Man]

Sorry -- it was late and I didn't test it.

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.

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.

 

[Winterfell]

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.

 

[Super Macho Man]

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.

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. Well, that is just my idiot PHP/SQL newbie liberal arts major opinion.