WHERE clause issue....

[Scoodo]

Hiya guys,

I'm posting what is likely a simple problem but I'm over looking it I think. I've got a small script to post to a URL including what I want my WHERE clause to be defined by, which is then being carried into my SQL command, however I'm getting a message on the page generated stating that my defining information is not a column. My problem is that I should be a column anyway but data held within that column, where the information passed through should be the name of the team, so that the page only shows that one teams information out of x amount of teams... I hope that makes sense.. lets look at the code, hopefully that will be more useful than my ramblings...

SELECTION PAGE CODE

Code: Select all

<a href="display_team.php?Team_Name=<?php echo $row['Team_Name']; ?>">
            <?php echo $row['Team_Name']; ?>


PAGE GENERATED CODE

Code: Select all

if ($_GET && !$_POST) {
  if (isset($_GET['Team_Name'])) {
    $Team = $_GET['Team_Name'];
   }
  else {
    $Team = NULL;
   }
  if ($Team) {
    $sql = "SELECT * FROM drivers WHERE Team = $Team";
    $result = mysql_query($sql) or die (mysql_error());
    $row = mysql_fetch_assoc($result);
   }


URL GENERATED

Code: Select all

http://localhost/FantasyLeagueSoftware/display_team.php?Team_Name=Ferrari

RESULT

Code: Select all

Unknown column 'Ferrari' in 'where clause'

No idea what I've done wrong, or missing, so if anyone can help then great.
Thank you in advanced.

[NigelRen]

I think what you want is something like

Code: Select all

$sql = "SELECT * FROM drivers WHERE Team = '$Team'";

The quotes round the value will treat the $Team value as a string literal.

[Acesystech]

change your query...

$sql = "SELECT * FROM drivers WHERE Team = '$Team'";

[Scoodo]

Damn you know what? I feel like an idiot for not figuring that one out. It's rather simple. Never the less thank you guys for noticing what I did not.

[Scoodo]

That worked! However it's resulted in an unexpected issue.

The result is only of one record, and not multiple records where the Team string is correct, so where I should get two results, I'm only getting one. This is however all ways the second result, and never the first one to be read from the table, as though it's reading, writing the first result and then replacing it with the second. Any ideas why this may be the case.

I have the following code for displaying the results which should be working fine, and I've tested it with a simple SQL statement.

Code: Select all

      <table>
                      <tr>
            <th>            
                    <?php echo 'Team Name: '.$Team; ?>            
            </th>
            </tr>
                      <?php
                              while($row = mysql_fetch_assoc($result)) {
                      ?>
                   <tr>   
            <td><?php echo ($row['Driver_Name']); ?></td>
            <td><?php echo $row['Team']; ?></td>                  
         </tr>
                      <?php } ?>
       </table>


[johnj]

before your while loop, write $row = mysql_fetch_assoc($result); and then print it using print_r($row). This will tell you if you have more than one record.

[NigelRen]

Each time you call fetch_assoc it will read a record from the list, so when you've called it in

Code: Select all

 $result = mysql_query($sql) or die (mysql_error());
    $row = mysql_fetch_assoc($result);

That has already read the first row - and so your later loop will only read from the second record onwards.

If you want to check how many rows there are - call mysql_num_rows ( http://php.net/manual/en/function.mysql-num-rows.php ).

[Scoodo]

Thanks NigelRen,
you nailed it right on the head. I believe this thread is now closed.

Thanks again mate.