[SOLVED] Multiple WHERE statements

[Clinton]

Hey,

 

I'm trying to run multiple WHERE statements in a single query but it's not working out. How would I do this?

 

 $q1=("SELECT * FROM roster WHERE (Position<='4' AND Terminated='0') ORDER BY Position"); 

[almightyegg]

$q1 = mysql_query("SELECT * FROM roster WHERE Position <= '4' AND Terminated = '0' ORDER BY Position") or die(mysql_error());

[Clinton]

Error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Terminated = '0' ORDER BY Position' at line 1

 

<?

// connect to database
include("inc/connect.php");

// include auth and nav
include("inc/auth.php");
include("inc/nav.php");

?>

<?
$rn=("SELECT * FROM users WHERE
username='" . $_SESSION['username']."'");

$rz=mysql_query($rn);

while($row=mysql_fetch_array($rz))
{
if($row["Status"]=="0"){
echo ("<br>Have a great day! <a href='logout.php'>Close</a>");
}else{
?>

<center><h3>Explosive Personnel</h3></center>

<?
$q1=mysql_query("SELECT * FROM roster WHERE Position <= '4' AND Terminated = '0' ORDER BY Position") or die(mysql_error());
$q1rz=mysql_query($q1);

while($row1 = mysql_fetch_array($q1rz)){
?>


<b><font color="orange" size="3"><? echo $row1['Name']; ?></font></b><br>
<i><? echo $row1['Title']; ?></i><br>
Cell: <? echo $row1['CellPhone']; ?> &nbsp &nbsp &nbsp Home: <? echo $row1['HomePhone']; ?><br>
E-Mail: <a href=mailto:<? echo $row1['Email']; ?>><? echo $row1['Email']; ?></a><br>
<? echo $row1['Notes']; ?><p>


<?
}
?>

</body>


</html>

<?
}}
?>

[almightyegg]

$q1 = "SELECT * FROM roster WHERE Position<='4',Terminated='0' ORDER BY Position"

$q1rz=mysql_query($q1);

[Clinton]

Still getting that same error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Terminated='0' ORDER BY Position' at line 1

 

 

[almightyegg]

hmm....try

$q1rz = mysql_query("SELECT * FROM roster WHERE Position<='4',Terminated='0' ORDER BY Position ") or die(mysql_error());

$q1rz=mysql_query($q1); scrap that line

[Clinton]

LoL. Wow. Same Error. I was seriously not expecting this to be that hard. :-\

[almightyegg]

for god's sake!! >.< lol

$q1rz = mysql_query("SELECT * FROM roster WHERE Position=<'4',Terminated='0' ORDER BY Position ") or die(mysql_error());

 

I thought the <= was the right way round but better just check it by putting it =<

[Clinton]

Yea, it just created more of an error when I reversed it. I know it has to do with the whole Termination thing for the simple fact that it was working, the query, before I tried to add that.

[almightyegg]

$q1rz = mysql_query("SELECT * FROM roster WHERE Position=<'4' and Terminated='0' ORDER BY Position ") or die(mysql_error());

 

I replaced the and in lowercase in hope that the other chages I've made have made it need the and now

 

I really help WHEREs now >.<

[Jessica]

Did you try:

$q1= "SELECT * FROM roster WHERE Position<=4 AND Terminated=0 ORDER BY Position DESC"; 

[Clinton]

No I didn't but I just did and it didn't work either:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Terminated=0 ORDER BY Position DESC' at line 1

[Jessica]

Do those columns actually exist, btw?

What happens if you just do:

$q1= "SELECT * FROM roster WHERE Terminated=0 ORDER BY Position DESC"; 

[almightyegg]

If Jesirose's suggestion doesn't work try this -

$q1rz = mysql_query("SELECT * FROM roster WHERE `Position`=<'4' and `Terminated`='0' ORDER BY Position ") or die(mysql_error());

[Clinton]

Interesting. I never thought about doing that but when I do I get an error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Terminated=0 ORDER BY Position DESC' at line 1

 

But if I do it the other way, Position, it works just fine. And yes, i'm not a complete idiot. ;-) LoL THe columns do exist. I've checked and rechecked my PHPAdmin and everything looks good.

[almightyegg]

it is case sensitive, maybe try without the capital at the beginning

[Jessica]

OH. Terminated is a keyword. You need to either rename it or use backticks

`Terminated`

[Clinton]

Holy crap it worked!

 

SELECT * FROM roster WHERE `Position`=<'4' and `Terminated`='0' ORDER BY Position

 

What's the purpose of all the those weird dashes? (And how did you make them? Can't find them on my keyboard.)

 

[Clinton]

You know, I wondered if it might have been something like that. Thanks a ton.

[almightyegg]

....wow, something I suggested worked

 

On my keyboard they are above tab key, press shift and it

[Jessica]

Whenever you get an error like that check the reserved words list:

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

 

They're backticks, next to the 1.

[Clinton]

Got it. Thanks a million.