Jump to content

Subtracting Options With one table to another


Xtremer360

Recommended Posts

I accidently posted this in the PHP applications board and didn't mean to because its a blizzard here and my comp is getting slow and lagging and I thought I had clicked in the right board but I was wrong so if a mod, admin can delete that other entry I'd be thankful.

 

I'm trying to queries together because I have this query but I want to only put in those characters that have not been taken so I have another table that lists all the characters that are used. TABLE handler_characters which has fields called id, handler_id, character_id. What I want to somehow do is have it get all the character_ids out of that table and then subtract those ids from this query so it won't list them as options. Any thoughts on how to accomplish this?

 

<?php
                $query = 'SELECT id, charactername FROM characters ORDER BY `charactername`';
                $result = mysqli_query ( $dbc, $query ); // Run The Query
                while ( $row = mysqli_fetch_array ( $result, MYSQL_ASSOC ) ) { 
                    print "<option value=\"".$row['id']."\">".$row['charactername']."</option>\r";
                }
                ?>

More of a MySQL question but try:

 

$query = 'SELECT characters.id, characters.charactername
          FROM characters, handler_characters
          WHERE characters.id != handler_characters.character_id
          ORDER BY characters.charactername';

That didn't work however this did.

 

<?php
                $query = 'SELECT id, charactername FROM characters WHERE id NOT IN (SELECT character_id FROM handler_characters) ORDER BY `charactername`';
                $result = mysqli_query ( $dbc, $query ); // Run The Query
                while ( $row = mysqli_fetch_array ( $result, MYSQL_ASSOC ) ) { 
                    print "<option value=\"".$row['id']."\">".$row['charactername']."</option>\r";
                }
                ?>

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.