0

mates, please can you help me with the following sql query attempt. This query controls whether a person has performed a form action for 24 hours.

This is the SQL:

SELECT min(TIMESTAMPDIFF(DAY, `fecha_inscripcion`, now())) FROM `wp_cf7dbplugin_submits` WHERE `field_name`="cedula" and `field_value` = "1144093762"

This is the result:

min(TIMESTAMPDIFF(DAY, `fecha_inscripcion`, now()))
5

enter image description here

Now, in WordPress I did so

global $wpdb;
$day = 'DAY';
$now = 'now()';
$fieldvalue = '1144093762';
$fieldname = 'cedula';

$post_count = $wpdb->get_var("
    SELECT min(TIMESTAMPDIFF($day,$wpdb->cf7dbplugin_submits.fecha_inscripcion, $now))
    FROM  $wpdb->cf7dbplugin_submits
    WHERE field_name=$fieldname and field_value=$fieldvalue");

print_r($post_count);
echo "Resultado"+$post_count;

But it returns me in 0, when it should be 5 in this case.

Thank

2
  • What do you get when you echo $wpdb->cf7dbplugin_submits.fecha_inscripcion – czerspalace Apr 10 '17 at 17:04
  • @czerspalace Hi, It is a mistake I made, the thought that has to call the field of a table that way. haha – iDuruiz Apr 10 '17 at 19:14
2

The main problem is how you're specifying the custom table name (e.g., $wpdb->cf7dbplugin_submits).

$wpdb only knows about the "built-in" tables when accessing a table name via $wpdb->xxx. To specify access a custom table name, use {$wpdb->prefix}custom_table_name.

The other thing I notice is that, for security purposes, you should never use interpolated variables in an SQL statement passed to any of the $wpdb query methods. Instead, you should use $wpdb->prepare().

Putting these 2 things together results in:

$sql = $wpdb->prepare (
    "SELECT min(TIMESTAMPDIFF($day,`fecha_inscripcion`, $now))
    FROM  {$wpdb->prefix}cf7dbplugin_submits
    WHERE field_name=%s and field_value=%s",
    $fieldname,
    $fieldvalue
    ) ;
$post_count = $wpdb->get_var ($sql) ;
4
  • Hi, thanks for answering my friend, and for your time. Now If I want to prove that the query actually does, should I store a value as a variable in $fieldname and $fieldvalue? for example: $fieldname = '111'... Thanks. – iDuruiz Apr 10 '17 at 19:15
  • @iDuruiz I'm not sure I understand the question in your comment...can you clarify? – Paul 'Sparrow Hawk' Biron Apr 10 '17 at 19:34
  • Hi, sorry, I do not speak English well, I already verified that the query works, but without using the "TIMESTAMPDIFF ..."I am not sure if it is because I am using wrong the TIMESTAMPDIFF, can you explain me how to use this function in the right way? thank you – iDuruiz Apr 10 '17 at 20:18
  • I have solved it, thanks to you, you're the best. Thanks to you, you're the best. thank, thank much. – iDuruiz Apr 10 '17 at 21:05
0

I attach the solution, thank a Paul 'Sparrow Hawk' Biron

global $wpdb;
$day = 'DAY';
$now = 'now()';
$fieldname = "cedula";
$fieldvalue = "1144092";

// set the meta_key to the appropriate custom field meta key

$sql = $wpdb->prepare (
    "SELECT min(TIMESTAMPDIFF($day,fecha_inscripcion, $now))
    FROM  {$wpdb->prefix}cf7dbplugin_submits
    WHERE field_name=%s and field_value=%s",
    $fieldname,
    $fieldvalue
    );
$post_count = $wpdb->get_var($sql) ;

echo "<p>User count is {$post_count}</p>";

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.