[RESOLVED] QUERY question

fontener

Well I seem to be frequenting this site quite often lately, but I guess it's the best way for me to learn...

I have a multiple select list being populated by a query:

<?php          

$id=$_GET[song];            

$result =mysql_query( "SELECT cl, sid FROM s ORDER BY cl ");   

if (!$result) {    echo("<p>Error performing query: " .         mysql_error() . "</p>");    exit();  }
  while($row = mysql_fetch_array($result))
      {
      $cl = $row["cl"];
      $sID = $row["sid"];

                echo "<option value=\"$sID\">$cl</option>";
                }
?>

ok, here goes...

Lets say:

Table s ............. | cl | sid |
Table t ............. | sid | song |

Basically, if the "sid" is already being paired with "song" in Table T, I dont want "sid" to be an option in the multiple select list...

please let me know if you are as confused as I am

fontener

I've tried this:


$result =mysql_query( "SELECT s.*, t.* FROM s, t WHERE !(s.sid=t.sid AND t.song=$id)  ORDER BY cl ");

but I know it is not correct (for reasons other than it does not work)

toxic_brain

SELECT S.cl, S.sid FROM S WHERE sid NOT IN (SELECT T.sid FROM T WHERE T.song =$id);

you can use this if you use Mysql 5.0 or higher.

fontener

thanks toxic_brain.. that worked wonderfully for populating the multiple select field.. however, now the information is not being passed on in the post.. any ideas?

toxic_brain

u could check the html source to verify the values set of the list box has been properly output.

fontener

if I take out the NOT IN ( SELECT T.sid FROM T WHERE T.song=$id) it posts.. would it have something to do with :

$cl = $row["cl"];
$sID = $row["sid"];

is it confused as to which "sid" to pull from or is there other problems?

toxic_brain

hm, may be.lets change

.. WHERE sid NOT IN ..

.. WHERE S.sid NOT IN ..

fontener

nope. tried that as well..

toxic_brain

print the values..as let's see what it comes
$cl = $row["cl"];
$sID = $row["sid"];
echo " cl=$cl sID=$sID <br>";

fontener

they print fine... just not being passed for some reason... it's too weird..

Here's the ALL the (related) code:

<form action="thisfile.php" method="post">

<dl>
		<dt><label for="stations">Choose station:</label></dt>
		<dd><select id="stations" class="asmSelect" multiple="multiple" name="stations[]" title="Click to Select a Station">
<?php                      

$result =mysql_query( "SELECT s.cl, s.sid FROM s WHERE s.sid NOT IN ( SELECT t.sid FROM t WHERE t.song=$id) ORDER BY cl ");   

if (!$result) {    echo("<p>Error performing query: " .         mysql_error() . "</p>");    exit();  }
  while($row = mysql_fetch_array($result))
      {
      $cl = $row["cl"];
      $sID = $row["sid"];

                echo "<option value=\"$sID\">$cl</option>";
                }
?>
		</select></dd>
</dl>
      <input type="hidden" name="song" id="song" value="<?php echo $id ?>">

  <input type="submit" name="submit" value="Submit" class="btn">

</form>

</div>

<?php

   if ($_POST["submit"])
   {
   $songID = $_POST['song'];
   $insertStatement = 'INSERT INTO status (song, sid) VALUES '; 
     foreach($_POST['stations'] as $stationID) {
     $insertStatement .= "($songID, $stationID), "; 
}     

$insertStatement = rtrim($insertStatement, ", ");
echo $insertStatement;
$insertThem = mysql_query($insertStatement);
if (!$insertThem) {    echo("<p>Error performing query: " .         mysql_error() . "</p>");    exit();  }
}
?>

like I said.. it'll post if I remove the

NOT IN ( SELECT T.sid FROM T WHERE T.song=$id)

it's just weird that it'll fetch the query and all that, but won't post any info.. thanks for the help

fontener

if this helps debug the problem... after I click the submit button, not only does the submission NOT occur, but the submit button also disappears as well as all of the options in the multiple select..

fontener

I solved the problem myself.. no worries.. but thanks for the help!!

the problem lied where

t.sid=$id

and it should have read

t.sid='$id'

incase anyone else experiences the same problem..