Sub Query errors

Donovan · Client Joined: Oct 07, 2003 Posts: 735 Location: Ohio

What I have is a table with values that I may want to import into another table. I have to check if these values already exist. _tc_teams may have teams I want to display that do not exist in _eto_units. if they don't exist I want them to display so I may import them with the selection of a checkbox. Then import team_id into unit_id and team_name into unit_name into my eto_units table. I'll have to figure out some sort of array for my checkboxes.

Right now my table displays nothing. I have about 20 teams in tc_teams and about 6 or 7 that match in eto_units.

Code:

$sql = $db->sql_query("SELECT * FROM " . $prefix . "_tc_teams WHERE team_id NOT IN (SELECT unit_id FROM " . $prefix . "_eto_units)");
while ( $row = $db->sql_fetchrow($sql) ) {
      $team_id = $row['team_id'];
      $team_name= stripslashes($row['name']);

echo"<tr>"
. "<td align=\"center\" bgcolor=\"#999999\"><font color=\"#000000\">$team_id</font></td>"
. "<td align=\"center\" bgcolor=\"#999999\"><font color=\"#000000\">$team_name</font></td>"
. "<td align=\"center\" bgcolor=\"#666666\"><input type=\"checkbox\" name=\"import[]\" value=\"$team_id\">Import Team? </td>"
. "</tr>";
}

Thanks in advance for any help.

-Donovan

Raven · Posted: Sun Mar 19, 2006 1:48 am

SubSelects (SubQueries) are not available in MySQL 4.0.x series. They are not introduced until MySQL 4.1. So you will have to perform your sub query and then either loop through it with the primary query or put them in an array and use something like if (in_array).

montego · Posted: Sun Mar 19, 2006 11:44 am

Donovan,

Here is another possible solution for you to consider. In the abscense of inner selects, you may be able to use LEFT | RIGHT joins to get you want you want. Here is a possible solution to what you are trying to accomplish:

Code:

$sql = "SELECT * FROM `" . $prefix . "_tc_teams` A LEFT JOIN `" . $prefix . "_eto_units` B "
. "ON A.`team_id` = B.`unit_id` WHERE b.`unit_id` IS NULL";

$result = $db->sql_query($sql);
while ( $row = $db->sql_fetchrow($result) ) {
$team_id = $row['team_id'];
$team_name= stripslashes($row['name']);

echo"<tr>"
. "<td align=\"center\" bgcolor=\"#999999\"><font color=\"#000000\">$team_id</font></td>"
. "<td align=\"center\" bgcolor=\"#999999\"><font color=\"#000000\">$team_name</font></td>"
. "<td align=\"center\" bgcolor=\"#666666\"><input type=\"checkbox\" name=\"import[]\" value=\"$team_id\">Import Team? </td>"
. "</tr>";
}

What this is doing is bringing back your records from your nuke_tc_teams table where there is no corresponding assignment to the nuke_eto_units table. I think this is what you were after? If not, I am sure some variation on this theme might work for you. Just test out your SQL in phpMyAdmin first until it is what you want. I am a big fan in making the DBMS do as much of the work as possible. Wink

I tested this with my RavenNuke76 installation to find all my nuke users for which I have not yet defined them to a NSN Group. The following produced me that list:

Code:

SELECT * FROM `nuke_users` A LEFT JOIN `nuke_nsngr_users` B ON A.user_id = B.uid WHERE B.uid IS NULL

Hope this helps.