Make a SQL query with wpdb in WordPress

Question

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

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

Answer 1

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) ;

Answer 2

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>";