Help with mysql output

[dante2010]

Hi,

I'm having a problem with a search that I'm building for a site. The problem is, I can get results to show when I search for everything but one combination of items.

Code for the search page is:

[code]

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

<center><font face="arial, helvetica, sans-serif" size="2">Enter your search criteria:</font></center><br>


<table cellspacing="0" cellpadding="2" border="0" width="100%">
<tr>
    <td valign="top" align="center">
    
    
<table width="60%" align="center" cellpadding="2" cellspacing="2" border="0">
<tr bgcolor="#000000"><td><font face="arial, helvetica, sans-serif" size="2" color="#FFFFFF"><b><center>Division</center></b></font></td><td><font face="arial, helvetica, sans-serif" size="2" color="#FFFFFF"><b><center>Position</center></b></font></td></tr>
<tr bgcolor="#E8E7E7"><td>

<center>

<select name="division">
    <option>A<option>B
    <option value="one">1
    <option value="two">2
    <option value="three">3
    <option value="four">4
    <option value="five">5
    <option value="six">6
    <option value="seven">7
    <option value="eight">8
    <option value="nine">9
    <option value="ten">10
    <option value="eleven">11
    <option value="twelve">12
    <option>Open<option>Comp<option value="Intermed">Int<option>Rec
</select>

</center>

</td>
<td><center><select name="position">
<option value="g">Goalie
<option value="all">All Positions
<option value="d">Defence
<option value="f">Forward
<option value="lw">Left Wing
<option value="c">Centre
<option value="rw">Right Wing
<option value="ld">Left Defence
<option value="rd">Right Defence
</select></center></td></tr>
<tr><td colspan="2"><center>
<input type="submit" value="Search">
</center></td></tr></table>

</form>

[/code]

The code for the search results page is:

[code]

<?php

        $division = $_POST['division'];
        $position = $_POST['position'];
        
        
        $sql = "SELECT * FROM spareplayers
                
                WHERE $division = 'TRUE'
                AND position = '$position'
                ORDER BY lastName";
        
        // echo $sql;
        
            
        $result = @mysql_query($sql);
        
        
    ?>    

<font face="arial, helvetica, sans-serif" size="3"><center><b>Search Results</b></center></font><br><br>

<table width="90%" cellpadding="2" cellspacing="2" border="0" align="center">
<tr bgcolor="#C2C1CA">
    <td><font face="arial, helvetica, sans-serif" size="2"><b><center>Name</center></b></font></td>
    <td><font face="arial, helvetica, sans-serif" size="2"><b><center>Email</center></b></font></td>
    <td><font face="arial, helvetica, sans-serif" size="2"><b><center>Home Tel</center></b></font></td>
    <td><font face="arial, helvetica, sans-serif" size="2"><b><center>Work Tel</center></b></font></td>
    <td><font face="arial, helvetica, sans-serif" size="2"><b><center>Cell</center></b></font></td>
</tr>

<?php

$counter = 1;
while($line = mysql_fetch_array($result, MYSQL_ASSOC)) { ?>    
    
<tr bgcolor="<?php if (($counter % 2) == 0) { echo '#FFFFFF'; } else { echo '#E5E5F0'; } ?>">
    <td><font face="arial, helvetica, sans-serif" size="2"><?php echo $line['firstName'] . " " . $line['lastName']; ?></font></td>
    <td><font face="arial, helvetica, sans-serif" size="2"><?php if (strlen($line['email'])) {?>  <a href="mailto:<?php echo $line['email']; ?>">Email Player</a>   <?php } else { echo "N/A"; } ?></font></td>
    <td><font face="arial, helvetica, sans-serif" size="2"><center><?php echo $line['homeNumber']; ?></center></font></td>
    <td><font face="arial, helvetica, sans-serif" size="2"><center><?php echo $line['workNumber']; ?></center></font></td>
    <td><font face="arial, helvetica, sans-serif" size="2"><center><?php echo $line['cellNumber']; ?></center></font></td>
</tr>

<?php } ?>

[/code]

Now I get search results for every combination EXCEPT when I select "Intermed" as the division.

I've outputted the sql that is generated and when I run it thru phpmyadmin I get results for the combos where "Intermed" is used.

Ex: This SQL will get results in phpmyadmin, but doesn't seem to in the above code.

SELECT * FROM spareplayers
WHERE Intermed = 'TRUE'
AND position = 'all'
ORDER BY lastName

Why do I not get any results showing on the page in this case???

[nogray]

try to use the `` around the $division and use mysql_error() to see if there are any errors
[code]
$sql = "SELECT * FROM spareplayers
     WHERE `$division` = 'TRUE'
     AND `position` = '$position'
     ORDER BY `lastName`";

$result = @mysql_query($sql) or die(mysql_error());
[/code]
maybe it'll work

[poirot]

I think your database structure is a little weird.

Why don't you have a field named division and fill it with the values, instead of having several fields for division (like what you do with positions)? This is bad design.

This also will likely be easier to handle.

EDIT: 665th post, the beast's neighbor.