Jump to content


Photo

Help with mysql output


  • Please log in to reply
2 replies to this topic

#1 dante2010

dante2010
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 09 June 2006 - 09:00 PM

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:


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


The code for the search results page is:


<?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 } ?> 


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???

#2 nogray

nogray
  • Members
  • PipPipPip
  • Advanced Member
  • 930 posts
  • LocationSan Francisco CA

Posted 09 June 2006 - 10:00 PM

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

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

NoGray.com


#3 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 09 June 2006 - 11:25 PM

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.
~ D Kuang




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users