woocommerce offtopic - Same request MysqlPhp not same result

admin管理员组

文章数量:1394217

I have this PHP query:

$querystrShop = $wpdb->get_results("
                        SELECT productid 
                        AS post_id,( 3959 * acos(cos(radians($latitude)) * cos( radians(latitude))* cos( radians( longitude )- radians($longitude))+ sin(radians($latitude))* sin( radians(latitude)))) 
                        AS distance, phiz_postmeta.meta_value
                        FROM phiz_geo_location
                        JOIN phiz_term_relationships ON phiz_geo_location.productid = phiz_term_relationships.object_id
                        LEFT JOIN phiz_postmeta ON phiz_geo_location.productid = phiz_postmeta.post_id
                        LEFT JOIN phiz_posts AS p ON phiz_postmeta.post_id = p.id
                        WHERE phiz_term_relationships.term_taxonomy_id IN ($catsearched)
                        AND p.post_title ='".$html."'
                        AND p.post_status = 'publish'
                        HAVING distance < '100'
                        ");

And this Mysql query:

SELECT productid AS post_id,( 3959 * acos(cos(radians(43.5586)) * cos( radians(latitude))* cos( radians( longitude )- radians(5.25182))+ sin(radians(43.5586))* sin( radians(latitude)))) AS distance, phiz_postmeta.meta_value FROM phiz_geo_location JOIN phiz_term_relationships ON phiz_geo_location.productid = phiz_term_relationships.object_id LEFT JOIN phiz_postmeta ON phiz_geo_location.productid = phiz_postmeta.post_id LEFT JOIN phiz_posts AS p ON phiz_postmeta.post_id = p.id WHERE phiz_term_relationships.term_taxonomy_id IN (207) AND p.post_title = 'ACTION PARIS MASQ NOIR PEEL OFF X3' AND p.post_status = 'publish'HAVING distance < '100'

The first request have 3 products, the second only 2, why this difference ?

I try to get all products in range distance (100) actualy the PHP query return

Distance : 191 km

Distance : 18,3 km

Distance : 11,3 km

and the Mysql return

Distance : 18,3 km

Distance : 11,3 km

Thanks

EDIT:

have added some corrections but same problem:

$querystrShop = $wpdb->prepare("
                        SELECT productid 
                        AS post_id,( 3959 * ACOS(COS(RADIANS( $latitude )) * COS(RADIANS(phiz_geo_location.latitude)) * COS(RADIANS(phiz_geo_location.longitude) - RADIANS( $longitude )) + SIN(RADIANS( $latitude )) * SIN(RADIANS(phiz_geo_location.latitude))))
                        AS distance, phiz_postmeta.meta_value
                        FROM phiz_geo_location
                        JOIN phiz_term_relationships ON phiz_geo_location.productid = phiz_term_relationships.object_id
                        LEFT JOIN phiz_postmeta ON phiz_geo_location.productid = phiz_postmeta.post_id
                        LEFT JOIN phiz_posts AS p ON phiz_postmeta.post_id = p.id
                        WHERE phiz_term_relationships.term_taxonomy_id IN ($catsearched)
                        AND p.post_title ='".$html."'
                        AND p.post_status = 'publish'
                        HAVING distance < '100'
                        ");

                        $querystrShop = $wpdb->get_results( $querystrShop );

EDIT 2: also tried

$querystrShop = $wpdb->prepare("
                        SELECT productid 
                        AS post_id,( %d * ACOS(COS(RADIANS( %s )) * COS(RADIANS(phiz_geo_location.latitude)) * COS(RADIANS(phiz_geo_location.longitude) - RADIANS( %s )) + SIN(RADIANS( %s )) * SIN(RADIANS(phiz_geo_location.latitude))))
                        AS distance, phiz_postmeta.meta_value
                        FROM phiz_geo_location
                        JOIN phiz_term_relationships ON phiz_geo_location.productid = phiz_term_relationships.object_id
                        LEFT JOIN phiz_postmeta ON phiz_geo_location.productid = phiz_postmeta.post_id
                        LEFT JOIN phiz_posts AS p ON phiz_postmeta.post_id = p.id
                        WHERE phiz_term_relationships.term_taxonomy_id IN ($catsearched)
                        AND p.post_title ='".$html."'
                        AND p.post_status = 'publish'
                        HAVING distance < %f",
                        $earth_radius,
                        $latitude,
                        $longitude,
                        $latitude,
                        $radious
                        );

                        $sql = $wpdb->get_results( $querystrShop );

EDIT 3 The result of the prepare:

SELECT productid AS post_id,( 3959 * ACOS(COS(RADIANS('43.2832512')) * COS(RADIANS(phiz_geo_location.latitude)) * COS(RADIANS(phiz_geo_location.longitude) - RADIANS('5.5115776')) + SIN(RADIANS('43.2832512')) * SIN(RADIANS(phiz_geo_location.latitude)))) AS distance, phiz_postmeta.meta_value 
FROM phiz_geo_location 
JOIN phiz_term_relationships ON phiz_geo_location.productid = phiz_term_relationships.object_id 
LEFT JOIN phiz_postmeta ON phiz_geo_location.productid = phiz_postmeta.post_id 
LEFT JOIN phiz_posts AS p ON phiz_postmeta.post_id = p.id 
WHERE phiz_term_relationships.term_taxonomy_id IN (207) 
AND p.post_title ='ACTION PARIS MASQ NOIR PEEL OFF X3' 
AND p.post_status = 'publish' 
HAVING distance < 100

I have this PHP query:

$querystrShop = $wpdb->get_results("
                        SELECT productid 
                        AS post_id,( 3959 * acos(cos(radians($latitude)) * cos( radians(latitude))* cos( radians( longitude )- radians($longitude))+ sin(radians($latitude))* sin( radians(latitude)))) 
                        AS distance, phiz_postmeta.meta_value
                        FROM phiz_geo_location
                        JOIN phiz_term_relationships ON phiz_geo_location.productid = phiz_term_relationships.object_id
                        LEFT JOIN phiz_postmeta ON phiz_geo_location.productid = phiz_postmeta.post_id
                        LEFT JOIN phiz_posts AS p ON phiz_postmeta.post_id = p.id
                        WHERE phiz_term_relationships.term_taxonomy_id IN ($catsearched)
                        AND p.post_title ='".$html."'
                        AND p.post_status = 'publish'
                        HAVING distance < '100'
                        ");

And this Mysql query:

SELECT productid AS post_id,( 3959 * acos(cos(radians(43.5586)) * cos( radians(latitude))* cos( radians( longitude )- radians(5.25182))+ sin(radians(43.5586))* sin( radians(latitude)))) AS distance, phiz_postmeta.meta_value FROM phiz_geo_location JOIN phiz_term_relationships ON phiz_geo_location.productid = phiz_term_relationships.object_id LEFT JOIN phiz_postmeta ON phiz_geo_location.productid = phiz_postmeta.post_id LEFT JOIN phiz_posts AS p ON phiz_postmeta.post_id = p.id WHERE phiz_term_relationships.term_taxonomy_id IN (207) AND p.post_title = 'ACTION PARIS MASQ NOIR PEEL OFF X3' AND p.post_status = 'publish'HAVING distance < '100'

The first request have 3 products, the second only 2, why this difference ?

I try to get all products in range distance (100) actualy the PHP query return

Distance : 191 km

Distance : 18,3 km

Distance : 11,3 km

and the Mysql return

Distance : 18,3 km

Distance : 11,3 km

Thanks

EDIT:

have added some corrections but same problem:

$querystrShop = $wpdb->prepare("
                        SELECT productid 
                        AS post_id,( 3959 * ACOS(COS(RADIANS( $latitude )) * COS(RADIANS(phiz_geo_location.latitude)) * COS(RADIANS(phiz_geo_location.longitude) - RADIANS( $longitude )) + SIN(RADIANS( $latitude )) * SIN(RADIANS(phiz_geo_location.latitude))))
                        AS distance, phiz_postmeta.meta_value
                        FROM phiz_geo_location
                        JOIN phiz_term_relationships ON phiz_geo_location.productid = phiz_term_relationships.object_id
                        LEFT JOIN phiz_postmeta ON phiz_geo_location.productid = phiz_postmeta.post_id
                        LEFT JOIN phiz_posts AS p ON phiz_postmeta.post_id = p.id
                        WHERE phiz_term_relationships.term_taxonomy_id IN ($catsearched)
                        AND p.post_title ='".$html."'
                        AND p.post_status = 'publish'
                        HAVING distance < '100'
                        ");

                        $querystrShop = $wpdb->get_results( $querystrShop );

EDIT 2: also tried

$querystrShop = $wpdb->prepare("
                        SELECT productid 
                        AS post_id,( %d * ACOS(COS(RADIANS( %s )) * COS(RADIANS(phiz_geo_location.latitude)) * COS(RADIANS(phiz_geo_location.longitude) - RADIANS( %s )) + SIN(RADIANS( %s )) * SIN(RADIANS(phiz_geo_location.latitude))))
                        AS distance, phiz_postmeta.meta_value
                        FROM phiz_geo_location
                        JOIN phiz_term_relationships ON phiz_geo_location.productid = phiz_term_relationships.object_id
                        LEFT JOIN phiz_postmeta ON phiz_geo_location.productid = phiz_postmeta.post_id
                        LEFT JOIN phiz_posts AS p ON phiz_postmeta.post_id = p.id
                        WHERE phiz_term_relationships.term_taxonomy_id IN ($catsearched)
                        AND p.post_title ='".$html."'
                        AND p.post_status = 'publish'
                        HAVING distance < %f",
                        $earth_radius,
                        $latitude,
                        $longitude,
                        $latitude,
                        $radious
                        );

                        $sql = $wpdb->get_results( $querystrShop );

EDIT 3 The result of the prepare:

SELECT productid AS post_id,( 3959 * ACOS(COS(RADIANS('43.2832512')) * COS(RADIANS(phiz_geo_location.latitude)) * COS(RADIANS(phiz_geo_location.longitude) - RADIANS('5.5115776')) + SIN(RADIANS('43.2832512')) * SIN(RADIANS(phiz_geo_location.latitude)))) AS distance, phiz_postmeta.meta_value 
FROM phiz_geo_location 
JOIN phiz_term_relationships ON phiz_geo_location.productid = phiz_term_relationships.object_id 
LEFT JOIN phiz_postmeta ON phiz_geo_location.productid = phiz_postmeta.post_id 
LEFT JOIN phiz_posts AS p ON phiz_postmeta.post_id = p.id 
WHERE phiz_term_relationships.term_taxonomy_id IN (207) 
AND p.post_title ='ACTION PARIS MASQ NOIR PEEL OFF X3' 
AND p.post_status = 'publish' 
HAVING distance < 100

• woocommerce-offtopic
• mysql

Share Improve this question edited Mar 20, 2020 at 19:01 ilanb asked Mar 20, 2020 at 17:34 ilanbilanb 9333 silver badges1212 bronze badges 9

• Some of your variables don't have the $ so all your results are incorrect, even if they were consistent. Also, you shouldn't embed PHP variables directly in a query, use wpdb->prepare to secure them and avoid injection attacks – Tom J Nowell ♦ Commented Mar 20, 2020 at 17:49
• @TomJNowell Thanks, all variable have $ for * cos( radians(latitude))* cos( radians( longitude ) if I had $ this not work on Mysql – ilanb Commented Mar 20, 2020 at 18:02
• Instead of passing the query to get_results, output it instead so you can compare what the actual queries are. Clearly if they give different results they are not the same. Just keep in mind that anybody can check for locations within coordinates similar to this: 123, );DROP TABLES *; and destroy your website because you didn't use prepare – Tom J Nowell ♦ Commented Mar 20, 2020 at 18:33
• @TomJNowell Already EDITED post and corrected the query with prepare and variables but same problem – ilanb Commented Mar 20, 2020 at 18:39
• Can I see the result of the prepare call though? It isn't clear exactly what the value of $querystrShop is, just that it looks like the query passed to prepare with some placeholders. You might think it irrelevant or insignificant, but if that's the case prove it by adding it to your Q. For all we know the problem is the prevision of the numbers, stray quotes, additional spaces, etc, etc. I can already see differences in spacing around various parts of your SQL queries, so they aren't identical – Tom J Nowell ♦ Commented Mar 20, 2020 at 18:47

 |  Show 4 more comments

1 Answer 1

Sorted by: Reset to default 1

They give different results because those queries are not the same. They have different latitudes and longitudes.

For example the latitude in one is '43.2832512' but in the other it's 43.5586. They aren't even the same data type.

The long/lat in your prepare statement is for a park to the east of marseille, the one in your SQL query though is in the town of coudoux, ~36km away to the north west:

本文标签： woocommerce offtopicSame request MysqlPhp not same result