Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

WHERE clause issue....

Codes here !

Moderators: macek, egami, gesf

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

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

Re: WHERE clause issue....

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

change your query...

$sql = "SELECT * FROM drivers WHERE Team = '$Team'";
Acesystech
New php-forum User
New php-forum User
 
Posts: 35
Joined: Mon Aug 20, 2012 10:27 pm
Location: Ernakulam

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>
Scoodo
New php-forum User
New php-forum User
 
Posts: 22
Joined: Tue Mar 20, 2012 12:09 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.
johnj
php-forum Super User
php-forum Super User
 
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm

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

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


Return to mySQL & php coding

Who is online

Users browsing this forum: Bing [Bot] and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron