1

For a Google Maps application, I built an AJAX call that dynamically checks whether there are markers in a given viewport range. All marker information is stored in a node (note that I am using projective coordinates x, y instead of lat,lng) and the text in the body field also matches a condition. The MySQL query is as follows:

var $text = 'Test';
$query = db_select('field_data_body', 'b');
$query->join('field_data_field_x', 'x', 'b.entity_id = x.entity_id');
$query
  ->fields('b', array('entity_id'))
  ->fields('x', array('field_x_value'))
$query->where('b.body_value LIKE :text', array(':text' => $text));
$query->where('x.field_x_value >= :num1', array(':num2' => $xmin));
$query->where('x.field_x_value <= :num2', array(':num2' => $xmax));
$result = $query->execute();
if ($result->rowCount() < $maximum) {
 process_data($row->entity_id, $row->field_x_value);
}

Now, whenever the amount of markers exceeds a number $maximum, a clustering mechamism sets in. For this purpose, I really need to

1) check if there a less than $maximum results,

and only in this case

2) pull that data and process it.

Since quite many markers are stored in my database, my MySQL query should be as slim as possible, ideally it would first only give me the number of results (similar to a COUNT* query) and pull the result data only if needed.

Can anybody advice me on whether my $query->execute()->rowCount() code does that job? Or does $query->execute() automatically pull all results, and rowCount() is a PHP counting routine? The $result object does not seem to contain the explicit data, therefore it seems plausible to me that the data might not have been pulled yet at that stage...

Thanks for your help!

  • I removed the request for a new set of MySQL queries as that's off topic here, and as it happens redundant because the db engine doesn't provide anything that would help you do what you're asking – Clive Sep 8 '13 at 10:54
0

MySQL has no mechanism like that; you'll need to run two queries, the first a count query, and the second for the data if your condition on the first query result passes.

The PDO rowCount() method isn't used for SELECT queries, just DELETE, INSERT and UPDATE queries.

You'll need the countQuery() method to determine the count with a separate query, then simply run the original query if the count query gives the right result

  • Thanks Clive, countQuery() is what I was looking for. By the way, I just found another item stackoverflow.com/questions/8490324/… that gives an example on how to use it within the Drupal db_selct terminology. – tobias Sep 9 '13 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.