1

I'm trying to retrieve a set of records that have not already been presented for processing. I have three tables being joined in order to get the initial set of results including ones that have already been processed, I then use a construct like is not in (select distinct id from table_that_contains_list_of_processed_ids).

For some reason, when I run this query (verbatim, copy pasted in each direction to be sure) in MySQL Workbench, I get the answers expected, but when I run it in mysqli using the following code, I get an empty response.

 <?php
   $conn = new mysqli($dbsrv, $dbuser, $dbpass);
   $sql = "select muid.userid as BOSSUserUrn, muid.data as BASUserUrn, mcc.id as CompletionId, mcc.course as CourseId, FROM_UNIXTIME(mcc.timecompleted) as TimeCompleted, course.fullname as CourseFullName, course.shortname as CourseShortName from moodle.mdl_user_info_data muid join moodle.mdl_course_completions mcc on mcc.userid = muid.userid join moodle.mdl_course course on course.id = mcc.course where muid.fieldid = 18 and muid.data is not null and mcc.course != 5 and mcc.timecompleted is not null and mcc.id not in (select distinct coursecompid from mdl_bas_training_records)";
   $result = $conn->query($sql);

   $api_response = array();

   if( $result && $result->num_rows > 0 ) {
     error_log("Fetched " . $result->num_rows);
     // do the processing
   }
 %>

I think the prescient information here is that if I take out the final clause, I get results (8, in case you're wondering) in both MySQL Workbench and in the code, but if I put it back I get 4 results in MySQL Workbench (correct) but in PHP the if statement doesn't resolve to true (my problem).

I can see nothing that suggests mysqli can't cope with these kind of subqueries. Am I hitting an issue with the length of the text in the variable $sql? I'm flummoxed!

Edit: solved by ensuring that the database is specified in the subquery (actually, by specifying it in the new mysqli instantiation).

Martin Greenaway
  • 545
  • 4
  • 16
  • 1
    First off, you have to make mysqli throw errors, as shown in the linked answer. For now, you are just successfully silencing all the possible error messages that may lead you to the root of the problem. – Your Common Sense Mar 11 '16 at 15:17
  • @YourCommonSense Thanks for the link - it's helpful in taking this further, but that's not a duplicate of this question, it's a completely different question. – Martin Greenaway Mar 11 '16 at 15:31
  • Honestly, question itself makes very little sense. Mysqli has nothing to do with subqueries. Mysqli doesn't execute your SQL. – Your Common Sense Mar 11 '16 at 15:32
  • OK so I could rephrase the question then? But the question wasn't "how to output errors". How to output errors is something that I didn't know I needed to configure. That's fine. It's a step towards solving the question. The actual answer to the question turns out to be "You need to specify the database your table is in in the subquery", which is not an answer to the other question, and hence this can't be a duplicate. – Martin Greenaway Mar 11 '16 at 15:36
  • 1
    @MartinGreenaway A more correct answer is that you should use [`$mysqli->select_db`](http://php.net/manual/en/mysqli.select-db.php) or specify it in the instantiator: – h2ooooooo Mar 11 '16 at 15:37
  • 1
    `$conn = new mysqli($dbsrv, $dbuser, $dbpass, $database);` *problem solved* if there's no error in your query that is. http://php.net/manual/en/mysqli.error.php – Funk Forty Niner Mar 11 '16 at 15:37
  • 1
    I'd upvote/accept an answer but, y'know, duplicate, and all that. – Martin Greenaway Mar 11 '16 at 15:51
  • 3
    @MartinGreenaway *Yeah... sigh.* Some are just too quick on the gun sometimes and don't really take joy/pride in helping people ;-) Glad it worked out, *cheers* from all the "happy" people! – Funk Forty Niner Mar 11 '16 at 15:58
  • Come on. "You should specify a database" is a duplicate of *thousands* as well. Yet, thinking of other people, whose subquery would not run due to whatever else error, the proper error reporting will help much better than a correction of this particular mistake. This is a site for sharing knowledge, not a service for hunting down silly typos. Nothing to upvote/accept. Just move on. – Your Common Sense Mar 11 '16 at 18:03

0 Answers0