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.
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…
e.g.
// 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
$stmt->execute();
// store
$stmt->store_result();
// get numrows
$total_numrows = $stmt->num_rows;
// bind results to variables
$stmt->bind_result($clientdata_urn);
// fetch values
$stmt->fetch();
// close statement
$stmt->close();
} 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.
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.
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.
$stmt->bind_param('s',$district);
//Insert loop
foreach($distinct_districts AS $district) {
$stmt->execute();
$stmt->store_result();
//Increment this, don't set it.
$total_numrows += $stmt->num_rows;
$stmt->bind_result($clientdata_urn);
//Fetch ALL results.
while($stmt->fetch()) {
//Do something with $clientdata_urn.
}
//End Loop
}
$stmt->close();
} 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;
