sql query related issue

Question

I have one more question in want to the user between the operator in php. This is my SQL query.

$results=$wpdb->get_results( "SELECT user_id,day,activity,hotel 
FROM wp_user_activity 
WHERE user_id = '$user_id' AND date BETWEEN $start AND $end ");

it gives me an error

$results=$wpdb->get_results( "SELECT user_id,day,activity,hotel 
FROM wp_user_activity 
WHERE user_id = '$user_id' AND date BETWEEN '26/05/2019' AND '31/05/2019' ");

am getting data in this why in the $start and $end am not getting any data

date is a keyword. Surround the fieldname with ticks i.e. \`date\`. Also the date should be in YYYY-MM-DD format. — , May 29 '19 at 05:19
Possible duplicate of [SQL query to select dates between two dates](https://stackoverflow.com/questions/5125076/sql-query-to-select-dates-between-two-dates) — dWinder, May 29 '19 at 05:21
Can you check the phpmyadmin (database) structure of your 'date' filed ? — Dhruv, May 29 '19 at 05:25
actually the $start date and $end dates both coming from another table where i have stored the date which i took from postman in varchar. — gurpreet, May 29 '19 at 05:28
i will show you how i store date in database 26/05/2019 , in this way the date is stored — gurpreet, May 29 '19 at 05:30
Format your dates before using it in your sql query. $StartDate = date("Y-m-d",$start) and $EndDate = date("Y-m-d",$end) — Rahat Hameed, May 29 '19 at 07:25

score 0 · Answer 1 · answered May 29 '19 at 05:31

0

try the following to see your SQL then review :

$sql = "SELECT user_id,day,activity,hotel FROM wp_user_activity WHERE user_id = '$user_id' AND date BETWEEN $start AND $end ";

echo $sql; exit;

answered May 29 '19 at 05:31

yuannet

11
3

"SELECT user_id,day,activity,hotel FROM wp_user_activity WHERE user_id = '1' AND date BETWEEN 0.0025755324418029 AND 0.0030708271421496 " – gurpreet May 29 '19 at 05:37
i tried am getting this but my start date in 26/05/2019 and end date is 31/05/2019 – gurpreet May 29 '19 at 05:38
See the thing is that i want to delete woocommerce_persistance_cart cart has been delete successfully but when i refresh database same data appears again. $user_id=$data['user_id']; global $woocommerce; get_user_meta($user_id,'_woocommerce_persistent_cart_1',true); delete_user_data($user_id,'_woocommerce_persistent_cart_1'); i want to delete cart and it should not come again if i delete it. – gurpreet Jun 08 '19 at 11:26

score 0 · Accepted Answer · answered May 29 '19 at 05:36

0

your date format is wrong please use standard date format that is YYYY-MM-DD Use this

$results=$wpdb->get_results( "SELECT user_id,day,activity,hotel 
FROM wp_user_activity 
WHERE user_id = '$user_id' AND date BETWEEN '2019-05-26' AND '2019-05-31' ");

Hope this will help you

answered May 29 '19 at 05:36

Balvinder Singh

310
2
8

issue is that when i used between operater with in one month i mean 01/05/2019 to 31/05/2019 i works but when i used 31/05/2019 to 06/06/2019 it returns me empty array but i have data of 31/05/2019 and 02/06/2019 and 03/06/2019 – gurpreet May 29 '19 at 06:27

score 0 · Answer 3 · answered May 29 '19 at 06:29

0

Now it is working. $arr=array(); foreach($results as $single) { $start = $single->trip_start_date; $end = $single->trip_end_date; /* return $sql = "SELECT user_id,day,activity,hotel FROM wp_user_activity WHERE user_id = '$user_id' AND date BETWEEN '$start' AND '$end' "; */ $results=$wpdb->get_results( "SELECT user_id,day,activity,hotel FROM wp_user_activity WHERE user_id = '$user_id' AND date BETWEEN '$start' AND '$end' "); $single->trip_details=$results; $arr[]=$single;

}

answered May 29 '19 at 06:29

gurpreet

17
4

am unable to post question so that is why am asking here i have groci theme in which i have to clear cart but an unable to clear cart it reappears again in data base . i used get_user_meta($user_id,'_woocommerce_persistent_cart_1',true); return delete_user_meta($user_id,'_woocommerce_persistent_cart_1'); – gurpreet Jun 10 '19 at 07:36

sql query related issue

3 Answers3