The Problem is following in we build an member website with an search field to get their citys, that works pretty well, while it searches the city it calculates the distance from the userinput state. But now we have the problem its not sorted with the distance. is there any possibility to sort the data everytime a user makes another input?
The functions in php for the member post
public static function getDistance(string $latitude, string $longitude, int $postId): ?string
{
global $wpdb;
$result = $wpdb->get_results('
SELECT ((ACOS(SIN(' . $latitude . ' * PI() / 180) * SIN(latitude.meta_value * PI() / 180) + COS(' . $latitude . ' * PI() / 180) * COS(latitude.meta_value * PI() / 180) * COS((' . $longitude . ' - longitude.meta_value) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance
FROM kas_wp_posts p
LEFT JOIN kas_wp_postmeta latitude ON latitude.post_id = p.ID and latitude.meta_key = 'member_latitude'
LEFT JOIN kas_wp_postmeta longitude ON longitude.post_id = p.ID and longitude.meta_key = 'member_longitude'
WHERE p.ID = ' . $postId . ';
', ARRAY_N);
return round((float)$result[0][0], 2) . ' km';
}
protected static function getMemberIdsAround(string $lat, string $lng, int $distance): array
{
global $wpdb;
$result = $wpdb->get_results('
SELECT p.ID,
((ACOS(SIN(' . $lat . ' * PI() / 180) * SIN(latitude.meta_value * PI() / 180) + COS(' . $lat . ' * PI() / 180) * COS(latitude.meta_value * PI() / 180) * COS((' . $lng . ' - longitude.meta_value) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance
FROM kas_wp_posts p
LEFT JOIN kas_wp_postmeta latitude ON latitude.post_id = p.ID and latitude.meta_key = 'member_latitude'
LEFT JOIN kas_wp_postmeta longitude ON longitude.post_id = p.ID and longitude.meta_key = 'member_longitude'
WHERE p.post_type = 'member'
HAVING distance <= ' . $distance . ';
', ARRAY_N);
$ids = [];
foreach ($result as $event) {
$ids[] = $event[0];
}
return $ids;
}
The view of the php file
<?php
MemberPostType::queryFilteredMembers($_POST['search_name'], $_POST['search_lat'], $_POST['search_lng'], $_POST['search_distance'], $_POST['search_only_specialists']);
?>
<div class="row">
<div class="col-xs-12 col-sm-12 map">
<?php if(have_posts()) :?>
<div class="showmap more-info" id="showmap"></div>
<?php endif?>
</div>
</div>
<div class="row">
<div class="col-xs-12 col-sm-12 member-search">
<form method="post"
class="event-search-form" id="search_form">
<input type="hidden" name="search_lat" id="search_lat"
value="<?php echo $_POST['search_lat']; ?>"/>
<input type="hidden" name="search_lng" id="search_lng"
value="<?php echo $_POST['search_lng']; ?>"/>
<input type="hidden" name="google_api_key" id="google_api_key"
value="<?php echo GOOGLE_API_KEY; ?>"/>
<div class="row search-div">
<div class="col-xs-12 col-sm-6">
<i class="fa fa-search"></i>
<input name="search_name" id="search_name"
value="<?php echo $_POST['search_name']; ?>"
placeholder="<?php echo __('Search', 'ismi') ?>..." type="text">
</div>
<div class="col-xs-12 col-sm-6">
<i class="fa fa-map-marker" id="map_marker"></i>
<input name="search_location" id="search_location"
value="<?php echo $_POST['search_location']; ?>"
placeholder="<?php echo __('Near', 'ismi') ?>..." type="text">
<select name="search_distance" id="search_distance" title="Umkreis">
<?php for ($distance = 20; $distance <= 500; $distance += 10): ?>
<option value="<?php echo $distance; ?>" <?php if ($_POST['search_distance'] == $distance): echo 'selected'; endif; ?>>
<?php echo $distance; ?> km
</option>
<?php endfor; ?>
</select>
</div>
<button type="submit" id="search-button" disabled="true">
<i class="fa fa-search"></i>
</button>
</div>
<label for="search_only_specialists">
<input type="checkbox" name="search_only_specialists"
id="search_only_specialists" <?= $_POST['search_only_specialists'] ? 'checked' : '' ?>>
<?= __('show only specialists', 'ismi') ?></label>
</form>
</div>
</div>
<?php $data = []; ?>
<?php if(!have_posts()) : ?>
<div id="error">
<h2><b>Es ist ein Fehler augetreten!</b></h2>
<p>Leider wurde kein Mitglied mit diesen Informationen gefunden.</p>
<p>Versuchen Sie es mit einer erneuten Suche!</p>
</div>
<?php else : while (have_posts()) : the_post(); ?>
<hr/>
<div class="row <?= MemberPostType::isSpecialist() ? 'isSpecialist' : '' ?>">
<div class="col-xs-12 col-sm-12">
<h4>
<a title="<?php the_title() ?>" href="<?php the_permalink(); ?>">
<?php echo MemberPostType::getSalutation(); ?>
<?php echo MemberPostType::getFirstName(); ?>
<?php echo MemberPostType::getLastName(); ?>
</a>
</h4>
</div>
</div>
<div class="row <?= MemberPostType::isSpecialist() ? 'isSpecialist' : '' ?>">
<div class="col-xs-12 col-sm-10">
<?php if (MemberPostType::getPractice()): ?>
<p>
<i class="fa fa-university fa-fw" aria-hidden="true"></i>
<span> <?php echo MemberPostType::getPractice(); ?></span>
</p>
<?php endif; ?>
<?php if (MemberPostType::getCity()): ?>
<p>
<i class="fa fa-map-marker fa-fw" aria-hidden="true"></i>
<span> <?php echo MemberPostType::getZip(); ?>
<?php echo MemberPostType::getCity(); ?>
, <?php echo MemberPostType::getStreet(); ?></span>
</p>
<?php endif; ?>
<?php if (MemberPostType::getCountry()): ?>
<p>
<i class="fa fa-globe fa-fw" aria-hidden="true"></i>
<span> <?php echo MemberPostType::getCountry(); ?></span>
</p>
<?php endif; ?>
<?php if (MemberPostType::getPhone()): ?>
<p>
<i class="fa fa-phone fa-fw" aria-hidden="true"></i>
<span> <?php echo MemberPostType::getPhone(); ?></span>
</p>
<?php endif; ?>
<?php if (MemberPostType::getFax()): ?>
<p>
<i class="fa fa-fax fa-fw" aria-hidden="true"></i>
<span> <?php echo MemberPostType::getFax(); ?></span>
</p>
<?php endif; ?>
<?php if (MemberPostType::getMail()): ?>
<p>
<i class="fa fa-envelope fa-fw" aria-hidden="true"></i>
<a href="mailto:<?php echo MemberPostType::getMail(); ?>"> <?php echo MemberPostType::getMail(); ?></a>
</p>
<?php endif; ?>
<?php if (MemberPostType::getWebsite()): ?>
<p>
<i class="fa fa-anchor fa-fw" aria-hidden="true"></i>
<a target="_blank" href="<?php echo MemberPostType::getWebsite(); ?>"> <?php echo MemberPostType::getWebsite(); ?></a>
</p>
<?php endif; ?>
<?php if ($_POST['search_lat'] && $_POST['search_lng']): ?>
<p class="distance">
<i class="fa fa-road"></i>
<i> <?php echo MemberPostType::getDistance($_POST['search_lat'], $_POST['search_lng'], get_the_ID()); ?></i>
</p> ^<---- right here is the thing to sort to
<?php endif; ?>
</div>
2
Answers
i found an easy way to build an order function in javascript. its like an table order.
then just put the elements which is the orderelement as childs loop through them and than let arrange them after checkup.
Have you tried adding
ORDER BY "distance" DESCto the end of the MySql query within get_results?