[FIXED] Showing me a syntax error while I am trying to using the SELECT COUNT(*) function to count table rows with conditions?

Issue

I am using this code>>>

$recipe_counting    =   $wpdb->get_var( 

            "SELECT COUNT(*)
            FROM '" . $wpdb->prefix . "recipe_ratings'
            WHERE recipe_id ='" . $post_ID . "' AND user_ip='" . $user_IP . "'"

         );

In browser developer tools "Network" tab, it's showing this error when I try to check my output.

<div id="error"><p class="wpdberror"><strong>WordPress database error:</strong> [You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near &#039;&#039;pr4_recipe_ratings&#039;
            where recipe_id =&#039;104&#039; and user_ip=&#039;::1&#039;&#039; at line 2]<br /><code>SELECT COUNT(*)
            from &#039;pr4_recipe_ratings&#039;
            where recipe_id =&#039;104&#039; and user_ip=&#039;::1&#039;</code></p></div>{"status":2}

Solution

You might try to remove the ' that encloses your tablename since the DB engine couldunderstand that you make the SELECT against a string.

The mariadb documentation for the SELECT statement (https://mariadb.com/kb/en/select/) mentions to use the backstick caracter ` to quote tablenames, not single quote.

You can quote column names using backticks. If you are qualifying column >names with table names, quote each part separately as >`tbl_name`.`col_name`.

Your query becomes the following SELECT COUNT(*) from pr4_recipe_ratings where recipe_id ='104' and user_ip='::1'

Answered By - MichelR