Query help, please? (hopefully simple)

[Moron]

Okay, this query works fine:

 

where BoardName='Youth Advisory Board' and RepresentativeFor = 'City Staff Representative'

 

But if I add another element:

 

where BoardName='Youth Advisory Board' and RepresentativeFor = 'City Staff Representative' and Active = '-1'

 

...it crashes.

 

The "Active = '-1'" part works fine in other queries.

 

Is my syntax wrong when I go to three criterias in the WHERE statement?

 

[conker87]

It shouldn't do.

 

Try using backquotes (`) to surround your field names.

[OkBoy]

Is Active a string field in the DB? If it isn't, you don't need the quotes and I am pretty sure it will crash if you put them there.

[Moron]

Is Active a string field in the DB? If it isn't, you don't need the quotes and I am pretty sure it will crash if you put them there.

 

"Active" corresponds to a Y/N checkbox in the database. It works fine in other queries.

 

[BlueSkyIS]

Is Active a string field in the DB? If it isn't, you don't need the quotes and I am pretty sure it will crash if you put them there.

 

incorrect. i suggest always quoting all values so that you never have to worry about what should or shouldn't be quoted. and, as noted, quoted -1 works elsewhere.

 

question: What do you mean by 'it crashes'? What is the error message?

[revraz]

Show us the whole query you use and not just a snippet.

[soycharliente]

<?php
$sql = "[insert the rest of the query]
WHERE `BoardName`='Youth Advisory Board'
AND `RepresentativeFor`='City Staff Representative'
AND `Active`='-1'";
$result = mysql_query($sql) OR DIE ("$sql<br />".mysql_error());
?>

 

What does this give you? And I mean, copy/paste the entire message. Entire query and error message.

[Moron]

 

What does this give you? And I mean, copy/paste the entire message. Entire query and error message.

 

Here's the entire query:

 

$RESULTMEMBER=mssql_query("select * from MemberInfo MI 

where BoardName='Youth Advisory Board' and RepresentativeFor = 'City Staff Representative'

order by MI.[LName]");

 

It works fine.

 

But if I make it:

 

$RESULTMEMBER=mssql_query("select * from MemberInfo MI 

where BoardName='Youth Advisory Board' and RepresentativeFor = 'City Staff Representative' and Active = '-1' 

order by MI.[LName]");

 

(adding the Active part)

 

..it crashes. The "Active" part works fine elsewhere.

 

The error this causes is:

 

Warning: mssql_data_seek() [function.mssql-data-seek]: Bad row offset in C:\Wamp\www\boards\YAB\YAB.php on line 117

 

 

 

[soycharliente]

$RESULTMEMBER=mssql_query("select * from MemberInfo MI 
where BoardName='Youth Advisory Board' and RepresentativeFor = 'City Staff Representative'
order by MI.[LName]");

 

What is:

MemberInfo MI

Why is there a space in therE?

 

What is:

MI.[LName]

Brackets for what?

[BlueSkyIS]

MI is defined as an alias name for the table MemberInfo.

 

not sure you want square brackets around [LName], though.

[Moron]

$RESULTMEMBER=mssql_query("select * from MemberInfo MI 
where BoardName='Youth Advisory Board' and RepresentativeFor = 'City Staff Representative'
order by MI.[LName]");

 

What is:

MemberInfo MI

Why is there a space in therE?

 

What is:

MI.[LName]

Brackets for what?

 

MI is the abbreviation for MemberInfo. The space is normal.

 

The bottom line seems to be that I can only use two stipulations in the WHERE clause, but not three. That's why I'm wondering if my syntax is just wrong. I've tried every bracketing combination I can think of.

 

[soycharliente]

Why do you want to abbreviate MemberInfo? Should it not be MemberInfo AS MI?

 

What about the brackets? I've never seen brackets in a query before.

[Caberman]

mssql_data_seek will return "Bad row offset" if you try and read a record outside the result set. My guess is that you are returning 0 rows and automatically try and read the first one. The easiest way to catch this error is something like:

 

if (!@mssql_data_seek($result, $rownumToLookUp))
{
    print ("Looking for data outside the resultset.");
}
else
{
    print ("Found the row we expected. Do stuff here.");
}

 

Basically, it sounds like you aren't checking that you returned a row before you seek it. Try running that query against the DB directly and see if it returns rows that way. If so, I am not sure what the issue is.

 

If you could give us the code where you use the mssql_data_seek() function, it would help as well.

 

Cheers.

 

EDIT: Fixed typo. Plus, I presume he is using MS SQL Server as the SQL standards he is using seem to be what you see with it a lot (Weird brackets).

[Moron]

Here's the main snippet:

 

$RESULTMEMBER=mssql_query("select * from MemberInfo MI 

where BoardName='Youth Advisory Board' and RepresentativeFor = 'City Staff Representative'  

order by MI.[LName]");

$RESULTYAB=mssql_fetch_assoc($RESULTMEMBER);
$RESULTMEMBERCOUNT=mssql_num_rows($RESULTMEMBER);

mssql_data_seek($RESULTMEMBER,0);

while ($RESULTYAB = mssql_fetch_assoc($RESULTMEMBER))   {

(Do Stuff Here)

 

So why can't I add the "and Active = '-1'" part into the query here? It works fine here:

 

$RESULTMEMBER=mssql_query("select * from MemberInfo MI where BoardName='Youth Advisory Board' and Active = '-1' order by MI.[LName]");
$RESULTYAB=mssql_fetch_assoc($RESULTMEMBER);
$RESULTMEMBERCOUNT=mssql_num_rows($RESULTMEMBER);

[Caberman]

How many rows does this return?

 

where BoardName='Youth Advisory Board' and RepresentativeFor = 'City Staff Representative' and Active = '-1'

 

If it returns 0 rows, you would see the issue you are seeing.

 

mssql_data_seek($RESULTMEMBER,0);

 

If you return 0 rows, mssql_data_seek would give you bad row offset because you are saying "Give me the first record" and the error is saying "There is no first record".

 

This has nothing to do with the syntax. It would work with

 

where BoardName='Youth Advisory Board' and Active = '-1'

because, I presume that actually returns a row. What you need to do is take the query that isn't working and run it directly against the DB. If it returns 0 rows, you have found your problem. If it returns at least 1 row then come back and we can look at it more. 

 

Cheers.

[Moron]

where BoardName='Youth Advisory Board' and Active = '-1'

because, I presume that actually returns a row. What you need to do is take the query that isn't working and run it directly against the DB. If it returns 0 rows, you have found your problem. If it returns at least 1 row then come back and we can look at it more. 

 

Cheers.

 

The above works fine. It's selecting all members who are on that board and who are checked as Active. It just doesn't work when I put in more than two perimeters.

 

[soycharliente]

What does your database table structure look like?

[Caberman]

where BoardName='Youth Advisory Board' and Active = '-1'

because, I presume that actually returns a row. What you need to do is take the query that isn't working and run it directly against the DB. If it returns 0 rows, you have found your problem. If it returns at least 1 row then come back and we can look at it more. 

 

Cheers.

 

The above works fine. It's selecting all members who are on that board and who are checked as Active. It just doesn't work when I put in more than two perimeters.

 

 

Yes, I expect that to work fine. How does

where BoardName='Youth Advisory Board' and RepresentativeFor = 'City Staff Representative' and Active = '-1'

 

work when run directly against the DB? Does it return a record? I would expect not which means the problem is not a code problem, rather it is a data problem. You have no records that match the criteria in your WHERE clause thereby breaking your mssql_data_seek() call.

 

Cheers.