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 >
<div >
<?php if(have_posts()) :?>
<div id="showmap"></div>
<?php endif?>
</div>
</div>
<div >
<div >
<form method="post"
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 >
<div >
<i ></i>
<input name="search_name" id="search_name"
value="<?php echo $_POST['search_name']; ?>"
placeholder="<?php echo __('Search', 'ismi') ?>..." type="text">
</div>
<div >
<i 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 ></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 >
<div >
<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 >
<div >
<?php if (MemberPostType::getPractice()): ?>
<p>
<i aria-hidden="true"></i>
<span> <?php echo MemberPostType::getPractice(); ?></span>
</p>
<?php endif; ?>
<?php if (MemberPostType::getCity()): ?>
<p>
<i 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 aria-hidden="true"></i>
<span> <?php echo MemberPostType::getCountry(); ?></span>
</p>
<?php endif; ?>
<?php if (MemberPostType::getPhone()): ?>
<p>
<i aria-hidden="true"></i>
<span> <?php echo MemberPostType::getPhone(); ?></span>
</p>
<?php endif; ?>
<?php if (MemberPostType::getFax()): ?>
<p>
<i aria-hidden="true"></i>
<span> <?php echo MemberPostType::getFax(); ?></span>
</p>
<?php endif; ?>
<?php if (MemberPostType::getMail()): ?>
<p>
<i 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 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 >
<i ></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>
CodePudding user response：
Have you tried adding ORDER BY "distance" DESC to the end of the MySql query within get_results?
CodePudding user response：
i found an easy way to build an order function in javascript. its like an table order.
function sortTable() {
let childs, switching, i, x, y, shouldSwitch
switching = true
while (switching) {
switching = false
childs = document.getElementsByClassName('sort-child')
for (i = 0; i < (childs.length - 1); i ) {
shouldSwitch = false
x = document.getElementsByClassName('sort-element')[i]
y = document.getElementsByClassName('sort-element')[i 1]
if (parseFloat(x.innerHTML.slice(0, -2)) > parseFloat(y.innerHTML.slice(0, -2))) {
shouldSwitch = true
break
}
}
if (shouldSwitch) {
childs[i].parentNode.insertBefore(childs[i 1], childs[i]);
switching = true
}
}
}
then just put the elements which is the orderelement as childs loop through them and than let arrange them after checkup.