nested query ??
Results 1 to 11 of 11

Thread: nested query ??

  1. #1
    Senior Member
    Join Date
    Sep 2012
    Posts
    269

    Cool nested query ??

    Here's what I'm trying to accomplish:

    PHP Code:
    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.

    PHP Code:
    $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.

  2. #2
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,773
    Have you tried
    Code:
    SELECT * FROM TOAWorkOrders WHERE 
    (WorkDate = '$WorkDate' AND TechNum = '$TechNum') 
    AND 
    (Type != 'Lunch' OR Type != 'Break') order by Type
    ?
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  3. #3
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    Already tried this:

    PHP Code:
    $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.

  4. #4
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,353
    One problem I see is that this:

    Code:
    (Type != 'Lunch' OR Type != 'Break')
    is the same as this:
    Code:
    ( 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:

    Code:
    (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).

  5. #5
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    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:

    PHP Code:
    $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.

    Code:
    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.
    Last edited by timstring; 05-06-2013 at 05:46 PM.

  6. #6
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,773
    PHP Code:
    $RowCount result3->num_rows;
    $WorkOrders[$Count] = $result3->$RowCount
    That wouldn't even compile. I'll assume the first result3 is actually $result3 and the $ 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 $result3 you want to set $WorkOrders[$Count] equal to $result3->12 - 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 $stmt->num_rows 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).
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  7. #7
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,353
    Quote Originally Posted by timstring View Post
    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:
    Quote Originally Posted by bradgrafelman View Post
    Code:
    (Type != 'Lunch' OR Type != 'Break')
    is the same as this:
    Code:
    ( 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.

  8. #8
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    So, I'm back to where I started with a multi-query, and I am stuck.

  9. #9
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,353
    Not necessarily.. can you explain what this:
    Code:
    (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.

  10. #10
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    Let me step back to the data. Here's what I'm dealing with:

    Code:
    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:

    PHP Code:
    $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
    Last edited by timstring; 05-06-2013 at 06:44 PM.

  11. #11
    Senior Member
    Join Date
    Apr 2003
    Location
    Silver Lake
    Posts
    4,811
    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?
    IMPORTANT: STOP using the mysql extension. Use mysqli or pdo instead.
    World War One happened 100 years ago. Visit Old Grey Horror for the agony and irony.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •