getting data using wpdb by comparing some data

Question

I want to get data(time) by comparing userid and date against a post. data stored in this form.
meta key
date - 03-01-2017
time - 5AM-6AM
userid - 1

Now when I click on a button I get the date and userid.

$(".date").click(function(){

    clicked = this; 
    var dates= $(clicked).closest("ul").find(".getdate").val();
    var item= jQuery(this).closest("li.lia");
    var date = jQuery(item).find("input.getdate").val();
    alert(date);

    $.ajax({
         type: 'POST',   // Adding Post method
         url: MyAjax.ajaxurl, // Including ajax file
         data: {"action": "date_time", "date":date, 
         "userid":'<?php echo $uid;?>'}, // Sending data dname to  post_word_countfunction.
         success: function(data){ // Show returned data using thefunction.
         alert(data);
        }
      });
});

Then I send this data through ajax to a function date_time.

function date_time(){
    $date = $_POST['date'];
    $userid = $_POST['userid'];
    global $wpdb;
    $sql = "SELECT * FROM $wpdb->postmeta WHERE meta_key = 'date' AND     meta_value='$date' AND meta_key = 'userid' AND meta_value='$userid'";
    $results = $wpdb->get_results($sql) or die(mysql_error());
    print_r($results);
}

here I match userid and date and try to get time but it is showing 0 in alert of ajax success function.

echo the query and run the query manually on your server and then debug — Amar Singh
– Amar Singh, Commented Jan 3, 2017 at 7:47
I think we need more detail on how you're storing the data in the first place. Your query won't work because you can't have a single row where meta_key = 'date' and meta_key = 'userid' (which is what you're requesting). Can you be clearer as to how the data is stored? Is it three rows in $wpdb->postmeta, one for user, one for date, and one for time? — Hobo
– Hobo, Commented Jan 3, 2017 at 7:57
yes there are 3 rows for each entry date - 03-01-2017 time - 5AM-6AM userid - 1 postid 469 date - 01-01-2017 time - 1AM-2AM userid - 1 postid 468 data is stored in this way. — shahid hamdam
– shahid hamdam, Commented Jan 3, 2017 at 9:16
Please don't edit your question to be a completely different question. It makes all the answers to the original question irrelevant. Ask a new question. — Hobo
– Hobo, Commented Jan 4, 2017 at 9:54

Ashish Patel · Accepted Answer · 2017-01-03 08:55:34Z

0

Try meta query instead of custom query:

WP Meta Query

on base of it,

$args = array(
    'meta_query' => array(
        array(
            'key' => 'date',
            'value' => $date,
            'compare' => '='
        ),
// this array results in no return for both arrays
        array(
            'key' => 'userid',
            'value' => $userid,
            'compare' => '='
        )
    )
);
$the_query  = new WP_Query( $args );

answered Jan 3, 2017 at 8:55

Ashish Patel

3,6521 gold badge17 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hobo · Accepted Answer · 2017-01-03 09:10:24Z

0

If I've understood the question, you'll need a self join on the postmeta table: you need to find and join several rows to get the data you need.

Before that, though, read up on SQL injection in WordPress - your code is vulnerable to malicious POSTed values.

Try something like (untested, but it should give you the idea):

$sql = $wpdb->prepare("
    SELECT metatime.meta_value
      FROM $wpdb->postmeta metadate
           JOIN $wpdb->postmeta metauser on metadate.post_id = metauser.post_id
           JOIN $wpdb->postmeta metatime on metadate.post_id = metatime.post_id
     WHERE metadate.meta_key = 'date'
       AND metauser.meta_key = 'userid'
       AND metatime.meta_key = 'time'
       AND metadate.meta_value = %s
       AND metauser.meta_value = %d",
    $date,
    $userid);

answered Jan 3, 2017 at 9:10

Hobo

7,6215 gold badges44 silver badges52 bronze badges

2 Comments

shahid hamdam Over a year ago

i think i have some error in ajax because my php function is not called. function date_time(){ echo "something"; $date = $_POST['date']; $userid = $_POST['userid']; i tried to echo but its not working which meant my function is not called by ajax.

Hobo Over a year ago

Have you hooked up the Ajax action date_time with your PHP function date_time()? With something like add_action('wp_ajax_date_time', 'date_time'); (and/or add_action('wp_ajax_nopriv_date_time', 'date_time');) - see codex.wordpress.org/AJAX_in_Plugins

Hobo · Accepted Answer · 2017-08-31 03:59:34Z

0

In WordPress meta data table is few different. Here all metadata are individual row. so you can not query multiple meta data in same query as like you doing .
But you can do as following. Try this SQL, i think it will be helpful for you.

SELECT m1.meta_key as date,
       m1.meta_value as dateval,
       m2.meta_key as userid,
       m2.meta_value as useridval 
  FROM `wp_postmeta` as m1 
       LEFT JOIN wp_postmeta as m2 ON m1.post_id = m2.post_id 
 WHERE (m1.meta_key ='date' AND m1.meta_value='$date')
   and (m2.meta_key = 'userid' AND m2.meta_value='$userid')

edited Aug 31, 2017 at 3:59

Hobo

7,6215 gold badges44 silver badges52 bronze badges

answered Jan 3, 2017 at 11:04

sahed moral

4056 silver badges15 bronze badges

Collectives™ on Stack Overflow

getting data using wpdb by comparing some data

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related