Is Not Null Is Not Working

derekshull · October 13, 2012

I have an issue with something. I have two records in the database. One has the "orgname" as NULL and the other has it as Syntron.

I did this query:


$query = mysql_query("SELECT * FROM needs WHERE orgname IS NULL AND needsusername='$username' OR workerusername='$username' ORDER BY ID");

while ($rows = mysql_fetch_array($query)) {

$id = $rows['ID'];
$status = $rows['status'];
$firstname = $rows['firstname'];
$city = $rows['city'];
$state = $rows['state'];
$phone = $rows['phone'];
$email = $rows['email'];
$typeofneed = $rows['typeofneed'];
$description = $rows['description'];
$workername = $rows['workername'];
$workeremail = $rows['workeremail'];
$workerphone = $rows['workerphone'];
$completiondate = $rows['completiondate'];
$needsusername = $rows['needsusername'];
$workerusername = $rows['workerusername'];

echo "<center>

<table>
<tr>
<td>ID: $id</td>
</tr>
<tr>
<td>Needs Username: $needsusername</td>
<td>Worker Username: $workerusername</td>
</tr>
<tr>
<td>Needs Phone: $phone</td>
<td>Worker Phone: $workerphone</td>
</tr>
<tr>
<td>Needs Email: $email</td>
<td>Worker Email: $workeremail</td>
</tr>
<tr>
<td>Need Contact: $firstname</td>
<td>Worker Contact: $workername</td>
</tr>
<tr>
<td>Need City & State: $city $state</td>
</tr>
<tr>
<td>Type of Need: $typeofneed</td>
</tr>
<tr>
<td>Description: $description</td>
</tr>
<tr>
<td>Status of Need: $status</td>
</tr>
<tr>
<td>Completion Date: $completiondate</td>
</tr>
</table>
Enter completion date:
<form action='reallyupdate' method='post'><input type='hidden' name='idtosubmit' value=$id>Month:
<select name='month' size='1'>
<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='8'>8</option>
<option value='9'>9</option>
<option value='10'>10</option>
<option value='11'>11</option>
<option value='12'>12</option>
</select>
 Day:
<select name='day' size='1'>
<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='8'>8</option>
<option value='9'>9</option>
<option value='10'>10</option>
<option value='11'>11</option>
<option value='12'>12</option>
<option value='13'>13</option>
<option value='14'>14</option>
<option value='15'>15</option>
<option value='16'>16</option>
<option value='17'>17</option>
<option value='18'>18</option>
<option value='19'>19</option>
<option value='20'>20</option>
<option value='21'>21</option>
<option value='22'>22</option>
<option value='23'>23</option>
<option value='24'>24</option>
<option value='25'>25</option>
<option value='26'>26</option>
<option value='27'>27</option>
<option value='28'>28</option>
<option value='29'>29</option>
<option value='30'>30</option>
<option value='31'>31</option>
</select>
 Year:
<select name='year' size='1'>
<option value='2012'>2012</option>
<option value='2013'>2013</option>
<option value='2014'>2014</option>
<option value='2015'>2015</option>
<option value='2016'>2016</option>
<option value='2017'>2017</option>
<option value='2018'>2018</option>
<option value='2019'>2019</option>
<option value='2020'>2020</option>
<option value='2021'>2021</option>
</select><br><br>
<input type='submit' value='Click to Update'></form>
<br>
If after contact with the other organization/individual you want to cancel the project but post it back on the website click this button:
<form action='putbackonsite' method='post'><input type='hidden' name='idtoapprove' value=$id><input type='submit' value='Post Need on Website'></form>

<br><br>---------------------------------------------------------<br><br></center>";


}

It works fine. But then I did this (in the same script):

$query = mysql_query("SELECT * FROM needs WHERE orgname IS NOT NULL AND needsusername='$username' OR workerusername='$username' ORDER BY ID");
while ($rows = mysql_fetch_array($query)) {
$id = $rows['ID'];
$status = $rows['status'];
$firstname = $rows['firstname'];
$city = $rows['city'];
$state = $rows['state'];
$phone = $rows['phone'];
$email = $rows['email'];
$typeofneed = $rows['typeofneed'];
$description = $rows['description'];
$workername = $rows['workername'];
$workeremail = $rows['workeremail'];
$workerphone = $rows['workerphone'];
$completiondate = $rows['completiondate'];
$needsusername = $rows['needsusername'];
$workerusername = $rows['workerusername'];
$orgname = $rows['orgname'];
echo "<center>
<table>
<tr>
<td>ID: $id</td>
</tr>
<tr>
<td>Organization Name: $orgname</td>
</tr>
<tr>
<td>Needs Username: $needsusername</td>
<td>Worker Username: $workerusername</td>
</tr>
<tr>
<td>Needs Phone: $phone</td>
<td>Worker Phone: $workerphone</td>
</tr>
<tr>
<td>Needs Email: $email</td>
<td>Worker Email: $workeremail</td>
</tr>
<tr>
<td>Need Contact: $firstname</td>
<td>Worker Contact: $workername</td>
</tr>
<tr>
<td>Need City & State: $city $state</td>
</tr>
<tr>
<td>Type of Need: $typeofneed</td>
</tr>
<tr>
<td>Description: $description</td>
</tr>
<tr>
<td>Status of Need: $status</td>
</tr>
<tr>
<td>Completion Date: $completiondate</td>
</tr>
</table>
Enter completion date:
<form action='reallyupdate' method='post'><input type='hidden' name='idtosubmit' value=$id>Month:
<select name='month' size='1'>
<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='8'>8</option>
<option value='9'>9</option>
<option value='10'>10</option>
<option value='11'>11</option>
<option value='12'>12</option>
</select>
 Day:
<select name='day' size='1'>
<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='8'>8</option>
<option value='9'>9</option>
<option value='10'>10</option>
<option value='11'>11</option>
<option value='12'>12</option>
<option value='13'>13</option>
<option value='14'>14</option>
<option value='15'>15</option>
<option value='16'>16</option>
<option value='17'>17</option>
<option value='18'>18</option>
<option value='19'>19</option>
<option value='20'>20</option>
<option value='21'>21</option>
<option value='22'>22</option>
<option value='23'>23</option>
<option value='24'>24</option>
<option value='25'>25</option>
<option value='26'>26</option>
<option value='27'>27</option>
<option value='28'>28</option>
<option value='29'>29</option>
<option value='30'>30</option>
<option value='31'>31</option>
</select>
 Year:
<select name='year' size='1'>
<option value='2012'>2012</option>
<option value='2013'>2013</option>
<option value='2014'>2014</option>
<option value='2015'>2015</option>
<option value='2016'>2016</option>
<option value='2017'>2017</option>
<option value='2018'>2018</option>
<option value='2019'>2019</option>
<option value='2020'>2020</option>
<option value='2021'>2021</option>
</select><br><br>
<input type='submit' value='Click to Update'></form>
<br>
If after contact with the other organization/individual you want to cancel the project but post it back on the website click this button:
<form action='putbackonsite' method='post'><input type='hidden' name='idtoapprove' value=$id><input type='submit' value='Post Need on Website'></form>
<br><br>---------------------------------------------------------<br><br></center>";

}

It still gives me both records! whether "orgname" is NULL or not. What's up with that?

kicken · October 13, 2012

You need to put your conditions inside parenthesis to force precedence. Right now what you have written:

orgname IS NOT NULL AND needsusername='$username' OR workerusername='$username'

Is evaluated as

- Is orgname not null?

- AND Is needsusername equal to $username?

- Then include the row in the output

- OR Is workerusername equal to $username

- Then include the row in the output

Basically so long as the row matches workerusername='$username' it will be included. Note that your above "working" query follows this same logic pattern so it doesn't really work as you expect.

orgname IS NOT NULL AND (needsusername='$username' OR workerusername='$username')

The parenthesis tells mysql to evaluate those two conditions first, then after that the not null condition.

derekshull · October 13, 2012

Got it! Thanks!

Sign In

Is Not Null Is Not Working

Recommended Posts

derekshull

Link to comment

Share on other sites

kicken

Link to comment

Share on other sites

derekshull

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information