PHP/MySQLi - looping through an array to build dynamic WHERE clause


I’ve been stuck on something for a couple of days now and I’m really hoping someone can help me please.

I have an array called $default_districts which generated by a query to find the postcode districts within a predefined radius from a point… so the number of values stored is dynamic.

[0] => NR1
[1] => NR12
[2] => NR13
[3] => NR14
… etc.

Ultimately, I’m trying to find out how many customers are within a “radius” from another address.
So, I want to use those values binded to a prepared statement…


// get data records within the default radius
if ($stmt = $mysqli->prepare("SELECT clientdata_urn, clientdata_district
                                       FROM tblhub_clientdata
                                       WHERE clientdata_district = ?)) {

// bind param to vars
$stmt->bind_param( // LOOP THROUGH $default_districts HERE // ));

// execute statement

// store

// get numrows
$total_numrows = $stmt->num_rows;

// bind results to variables

// fetch values

// close statement

  } else {

    // error
    printf("Error counting total data within default radius: %s\n", $mysqli->error);


If I hardcode what I’m trying to create it would be something like this…

SELECT clientdata_urn, clientdata_district
FROM tblhub_clientdata
WHERE clientdata_district = 'NR1' OR clientdata_district = 'NR12' OR clientdata_district = 'NR13' OR clientdata_district = 'NR14'

I’m a bit of a newbie and any help would be greatly appreciated.

Thank you.

you only bind the (search) param once. where you apply the loop is on the execute/fetch stage.

You need Latitude and Longitude data for both points. You then define the query to determine the distance. (Hint: Search this forum for “distance formula” - it’s a pretty complex formula, but only requires 4 data points - the lat and long of the two locations.)

Then you simply search your database for locations where the distance is <= your given radius.

Thank you. Are you able to help me with how I would do that please?

I’ve already processed the distance calculations and my array contains the results from that. I now need to loop these values and bind to a prepared statement.

Okay… I dont see why you would have done them seperately. But alright.

Well, to loop through an array to bind and execute would be to foreach your array, putting the desired value into the variable you’re using to bind, encapsulating everything from execute through fetch (and handling)

Yeah, that’s what I’m trying to do but I don’t know how - and I’ve tried so many tutorials, etc. I’m starting to lose it with this project! I’m 2 days behind and no solution in sight!

Given my code above, please can you help me with the syntax to build the loop, bind and execute.

With your code above…

   //Insert loop
   foreach($distinct_districts AS $district) {
    //Increment this, don't set it.
    $total_numrows += $stmt->num_rows;
    //Fetch ALL results.
    while($stmt->fetch()) {
        //Do something with $clientdata_urn.

    //End Loop

    } else {
    // error
    printf("Error counting total data within default radius: %s\n", $mysqli->error);

Again, if all you want is the count, this could be done by a single query rather than all of this code. I would encourage you to post a question in the Databases forum about how this could be accomplished;

1 Like

I can’t thank you enough! That works perfectly :smiley:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.