WHERE clause issue....

Codes here !

Moderators: macek, egami, gesf

Scoodo
New php-forum User
New php-forum User
Posts: 22
Joined: Tue Mar 20, 2012 12:09 pm

WHERE clause issue....

Postby Scoodo » Tue Aug 21, 2012 12:19 pm

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
php-forum Active User
php-forum Active User
Posts: 450
Joined: Fri Aug 05, 2011 9:53 am

Re: WHERE clause issue....

Postby NigelRen » Tue Aug 21, 2012 1:07 pm

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
New php-forum User
New php-forum User
Posts: 35
Joined: Mon Aug 20, 2012 10:27 pm
Location: Ernakulam
Contact:

Re: WHERE clause issue....

Postby Acesystech » Tue Aug 21, 2012 10:08 pm

change your query...

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

Scoodo
New php-forum User
New php-forum User
Posts: 22
Joined: Tue Mar 20, 2012 12:09 pm

Re: WHERE clause issue....

Postby Scoodo » Tue Aug 21, 2012 11:51 pm

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
New php-forum User
New php-forum User
Posts: 22
Joined: Tue Mar 20, 2012 12:09 pm

Re: WHERE clause issue....

Postby Scoodo » Wed Aug 22, 2012 12:39 pm

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
php-forum Super User
php-forum Super User
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm

Re: WHERE clause issue....

Postby johnj » Wed Aug 22, 2012 9:12 pm

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
php-forum Active User
php-forum Active User
Posts: 450
Joined: Fri Aug 05, 2011 9:53 am

Re: WHERE clause issue....

Postby NigelRen » Wed Aug 22, 2012 11:05 pm

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
New php-forum User
New php-forum User
Posts: 22
Joined: Tue Mar 20, 2012 12:09 pm

Re: WHERE clause issue....

Postby Scoodo » Thu Aug 23, 2012 12:22 pm

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

Thanks again mate.


Return to “mySQL & php coding”

Who is online

Users browsing this forum: No registered users and 1 guest

cron