nested query ??

[timstring]

Here's what I'm trying to accomplish:

SELECT * FROM TOAWorkOrders WHERE 
(WorkDate = '$WorkDate' AND TechNum = '$TechNum') 
AND 
(Type != 'Lunch' OR Type != 'Break') order by Type

I have found this from a few sources, but I can't figure out how to put them into practice.

$query = "SELECT * FROM TOAWorkOrders WHERE TechNum = '$TechNum' AND WorkDate = '$IncDate'";
$query .= "SELECT * FROM TOAWorkOrders WHERE Type != 'Lunch' OR Type != 'Break'";

if ($result3 = $mysqli->multi_query($query));
{	
    $WorkOrders[$Count] = $result3->num_rows;
     $SubWorkOrders = $SubWorkOrders + $WorkOrders[$Count];
}

Help!!

I have my favorite shotgun, a box of ammo, and a faithful black lab at my side. Man, it's cold out here.

[Weedpacket]

Have you tried

SELECT * FROM TOAWorkOrders WHERE 
(WorkDate = '$WorkDate' AND TechNum = '$TechNum') 
AND 
(Type != 'Lunch' OR Type != 'Break') order by Type 

?

[timstring]

Already tried this:

	
$Quest = "SELECT * FROM TOAWorkOrders WHERE 
(WorkDate = '$WorkDate' AND TechNum = '$TechNum') 
AND 
(Type != 'Lunch' OR Type != 'Break') 
order by Type";

$stmt = $mysqli->prepare($Quest) or die('not here again?' . $mysqli->error);
$stmt->execute();
$result3 = $stmt->get_result();
$RowCount = $result3 ->num_rows;
$WorkOrders[$Count] = $result3->$RowCount;

This returns a null data set.

[bradgrafelman]

One problem I see is that this:

(Type != 'Lunch' OR Type != 'Break')

is the same as this:

( 1 == 1 )

(in other words the condition will always evaluate to TRUE for all values of Type). Of course, that wouldn't cause an empty result set (since it wouldn't remove any results at all from the result set), so it sounds like the error lies with the former subcondition:

(WorkDate = '$WorkDate' AND TechNum = '$TechNum') 

You haven't shown us where/how you define $WorkDate or $TechNum, but have you tried echo'ing out the SQL query string and manually executing it yourself with only this WHERE condition to see if you get any rows?

EDIT: And then, of course, one wonders why you're prepare()'ing and execute()'ing a statement that has no placeholders rather than simply executing the SQL query string as-is (unless the variables mentioned above contain user-supplied data, in which case you should instead be bind()'ing those variables to placeholders).

[timstring]

EDIT: And then, of course, one wonders why you're prepare()'ing and execute()'ing a statement that has no placeholders rather than simply executing the SQL query string as-is (unless the variables mentioned above contain user-supplied data, in which case you should instead be bind()'ing those variables to placeholders).

Probably because I am an ignorant, rank beginner to mysqli and haven't even remotely grasped the concepts. This also returned a null data-set:

$Quest = "SELECT * FROM TOAWorkOrders WHERE 
(WorkDate = '$WorkDate' AND TechNum = '$TechNum') 
AND 
(Type != 'Lunch' OR Type != 'Break') 
order by Type";

$stmt = $mysqli->prepare($Quest) or die('not here again?' . $mysqli->error);

$stmt->execute();
$result3 = $stmt->get_result();
$RowCount = result3->num_rows;

 $WorkOrders[$Count] = $result3->$RowCount;
echo $WorkOrders[$Count] . ' !! ' . '<br>';
$SubWorkOrders = $SubWorkOrders + $WorkOrders[$Count];

$WorkDate and $TechNum come from a form on the previous page.

This query doesn't work from the command line.

SELECT * FROM TOAWorkOrders WHERE (WorkDate = '2013-04-20' AND TechNum = '83595') AND (Type != 'Lunch' OR Type != 'Break') order by Type

This code selects the proper person for the WorkDate and TechNum, but doesn't filter according to Type.

[Weedpacket]

$RowCount = result3->num_rows;
$WorkOrders[$Count] = $result3->$RowCount; 

That wouldn't even compile. I'll assume the first [font=monospace]result3[/font] is actually [font=monospace]$result3[/font] and the [font=monospace]$[/font] fell off while you were copying-and-pasting the code into your post.
What you're asking for here is that if there are 12 results in [font=monospace]$result3[/font] you want to set [font=monospace]$WorkOrders[$Count][/font] equal to [font=monospace]$result3->12[/font] - which isn't going to make a lot of sense.

This also returned a null data-set:

How do you know? How did you arrive at that conclusion? What is the value of [font=monospace]$stmt->num_rows[/font] after executing it? What, for that matter, are the values of its error properties? What happened when you followed bradgrafelman's suggestion of executing the query yourself?

(Oh, and SQL has a function for counting the number of rows returned by a query. It's called COUNT - it's there to save you having to count them all yourself).

[bradgrafelman]

timstring;11027783 wrote:

This code selects the proper person for the WorkDate and TechNum, but doesn't filter according to Type.

Again, no filtering will be done by Type because of the logical error I pointed out above:

bradgrafelman;11027775 wrote:

(Type != 'Lunch' OR Type != 'Break')

is the same as this:

( 1 == 1 )

(in other words the condition will always evaluate to TRUE for all values of Type).

The only way that logical statement would ever be FALSE is if Type is equal to both 'Lunch' and 'Break' at the same time.

[timstring]

So, I'm back to where I started with a multi-query, and I am stuck.

[bradgrafelman]

Not necessarily.. can you explain what this:

(Type != 'Lunch' OR Type != 'Break')

was supposed to mean? If you're wanting to filter out any rows that have a Type of 'Lunch' or 'Break' from the result set, then that OR should be an AND.

[timstring]

Let me step back to the data. Here's what I'm dealing with:

BBT	                                Status	Type
85217-LeviCossairt-MillTel	completed	Warehouse
85217-LeviCossairt-MillTel	completed	Install
85217-LeviCossairt-MillTel	completed	Break
85217-LeviCossairt-MillTel	completed	Downgrade
85217-LeviCossairt-MillTel	completed	Install
85217-LeviCossairt-MillTel	not done	Install
85217-LeviCossairt-MillTel	completed	Install
25842-Timothy Lear-BBT IV	completed	Meeting
25842-Timothy Lear-BBT IV	completed	Trouble Call
25842-Timothy Lear-BBT IV	completed	Vehicle Maintenance
25842-Timothy Lear-BBT IV	completed	Lunch
25842-Timothy Lear-BBT IV	completed	Install
25842-Timothy Lear-BBT IV   completed	Install
25842-Timothy Lear-BBT IV	completed	Trouble Call

I want to count the rows for Levi and Timothy each, minus Levi's 'Break' and Timothy's 'Lunch'. Accordingly, this should function:

$Quest = "SELECT COUNT(*) FROM TOAWorkOrders WHERE 
WorkDate = '$IncDate' AND TechNum = '$TechNum' AND 
Type != 'Lunch' AND 
Type != 'Break'
order by Type";

$stmt = $mysqli->prepare($Quest) or die('not here again?' . $mysqli->error);
$stmt->execute();

I'm at a loss to go on from here.

Y'all need to type slower, as I am a dumb Aggie and can't read all that fast

[sneakyimp]

I would recommend starting with a query that seems to return what you want before you start getting into mysqli and all the PHP code. Is there any way you can query the database directly?