where clause

[harlequeen]

Hi

 

I'm trying to get my select statement to bring back rows which match 2 'where' clause statements but can't get it to work.  This is the code I'm using to get it.

 

I am passing the weekNo and the divNo from previous pages.

 

<?php
//database connection
include'myconnect.php';
$weekNo=$_GET["weekNo"];
$divNo=$_GET["divNo"];

$sql=mysql_query("SELECT teams.teamName, teams.team_id, teams.totalPoints, teams.gamesWon, teams.played, 
teams.wk_01, contacts.division FROM teams LEFT JOIN contacts ON teams.teamName = contacts.teamName WHERE contacts.division='$divNo' AND '$weekNo' is null ");

 

This doesn't bring me any results back, although I know that some of the rows in the division I'm calling are set to null.

 

If I take out the clause where the '$weekNo' is null I get all the teams in the division I ask for, whether null or not.

 

Any help on this would be appreciated.

 

[cssfreakie]

did you run it in phpmyadmin in or in console? what was the error you got?

 

also did you try pre-pending the table in front of '$weekNo' as you did with 

contacts.division='$divNo'

[harlequeen]

Hi

 

I ran it in mysql and from a web page and didn't get an error, just didn't get the result I know I should have had.  I should have had a list of teams which had no results in, but got nothing.

 

I tried pre-pending but it still didn't work.

[darkfreaks]

ERROR:

nullSELECT(2,150) expected token:NOT NULL OF

[harlequeen]

I'm sorry, I don't know what that means.  I didn't get an error like that.  What do I need to do to fix this?

[jacko_162]

ERROR:

nullSELECT(2,150) expected token:NOT NULL OF

 

doesnt that just mean you havnt set the column to NULL by default?

[darkfreaks]

FIXED IT WITH LEFT OUTER JOIN

SELECT teams.teamname,
       teams.team_id,
       teams.totalpoints,
       teams.gameswon,
       teams.played,
       teams.wk_01,
       contacts.division
FROM   teams
       LEFT  OUTER JOIN contacts
         ON teams.teamname = contacts.teamname
WHERE  contacts.division = '$divNo'
       AND '$weekNo' IS NULL  

[darkfreaks]

also i took alok you need IFNULL i assuming tm.wk_01 is the week column?

 

SELECT teams.teamname,
       teams.team_id,
       teams.totalpoints,
       teams.gameswon,
       teams.played,
       IFNULL(teams.wk_01,0),
       contacts.division
FROM   teams
       LEFT  OUTER JOIN contacts
         ON teams.teamname = contacts.teamname
WHERE  contacts.division = '$divNo'
     

should output:

 

week 0

[harlequeen]

Hi Darkfreaks

 

Thanks for your help, but I tried the LEFT OUTER JOIN and it doesn't work.  Also, the next suggestion doesn't help as I don't have the IFNULL(teams.wk_01,0), in the select as the weekNo is being set by user selection on previous page and could be some other week.

 

When I run the test without the AND '$weekNo' ISNULL I get all the teams in the division as I would expect.  When I include that piece of code I don't get any. 

 

when I look at the page source view this is what I get.

 

<input type="char" name='team_id[]'size="2" value="9"></td><td>
<input type="text" name='teamName[]' size="36" value="Bryn & Trallwm Club A"></td>
<input type="hidden" name= 'totalPoints[]' size="3" value="3"></td>
<input type="hidden" name= 'gamesWon[]' size="2" value="0"></td>
<input type="hidden" name= 'played[]' size="2" value="1"></td>
<input type="text" name='weekNo[]' size="2" value=""></td>


<td>
<SELECT name='Points[]'>
<OPTION value=> </OPTION> 
<OPTION value=1>1</OPTION> 
<OPTION value=2>2</OPTION>
<OPTION value=3>3</OPTION>
<OPTION value=4>4</OPTION>
<OPTION value=5>5</OPTION>
<OPTION value=6>6</OPTION>

<OPTION value=7>7</OPTION>
<OPTION value=0>0</OPTION>"> 
</select>

</td>

 

I know that some of the teams have a value of 3 in wk_01, but yet the result doesn't show that as you can see above.  I also know that some are null.  The value of the weekNo in the section above should be 3.

 

I'm really confused now as I don't know why that value is not being brought out from the dbase in the same manner as all the others.

 

Any help anyone?

 

 

 

[darkfreaks]

yeah it didn't work i figured it wouldn't however it FIXED hidden syntax errors. lets for kicks try this

 

SELECT teams.teamname,
       teams.team_id,
       teams.totalpoints,
       teams.gameswon,
       teams.played,
       teams.wk_01,
       contacts.division
FROM   teams
       LEFT  OUTER JOIN contacts
         ON teams.teamname = contacts.teamname
WHERE  '$WeekNo' IS NULL
       AND  contacts.division = '$divNo'

 

 

 

[DavidAM]

Look at your query:

 

WHERE contacts.division='$divNo' AND '$weekNo' is null "

 

You are putting single quotes around the $weekNo variable. Assuming that $weekNo = 'wk_02' your query says:

 

WHERE contacts.division='1' AND 'wk_02' is null "

 

Look at that closely. It says AND the literal string "wk_02" IS NULL.  The LITERAL is NOT NULL, a literal string will never be null. Assuming you put a column name in $weekNo, your code should NOT have single quotes:

 

WHERE contacts.division='$divNo' AND $weekNo is null "

 

PHP will replace the variable with its value (which is a column name):

 

WHERE contacts.division='1' AND wk_02 is null "

 

 

Note: if the $weekNo variable is coming directly from user input, you MUST make sure that the value is a valid value that you allow. Validate and Sanitize your inputs!!

[harlequeen]

Hi

 

Thanks for that information, I get confused over the punctuation marks.  I realise that there could be problems if a user inputs a value that isn't there, but in reality, in this case, its only going to be me and the whole thing is only to make my life easier in the long run.

 

Thanks for all the help.

 

I used it today to do my league darts fixtures and it worked fine!