Custom Wordpress Table wpdb

Question

running into issues querying a custom table and getting results.

As of Wordpress 3.9, I can no longer run mysql_query(), and have to use $wpdb->query();

So keeping that in mind:

Old Code:

<?php
if (!empty($_GET['msg'])) {

global $wpdb;
$vuser_id = $_GET['id'];
$sql = "SELECT * FROM wp_quiz WHERE quiz_id='$vuser_id'";
$query = mysql_query($sql);
$result = mysql_fetch_assoc($query);

$total = $result['total_count'];

Updating it what I think would be proper:

<?php
if (!empty($_GET['msg'])) {

    global $wpdb;
    $vuser_id = $_GET['id'];
    $sql = "SELECT * FROM wp_quiz WHERE quiz_id='$vuser_id'";
    $query = $wpdb->query($sql);
    $result = mysql_fetch_assoc($query);

    $total = $result['total_count'];

and var_dumping $result gives me NULL

and I'm now getting the error:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, integer given in on line 16

I've tried using $wpbd->get_row($sql); $wpdb->get_results($sql); all come back null.

If I exit( $sql ); I can see that the query is being ran correctly SELECT * FROM wp_quiz WHERE quiz_id='5925' and if I run this directly into phpmyadmin, it gives me the correct results.

I'm not sure what I'm doing wrong.

You shouldn't be calling the mysql_ functions in WordPress, use $wpdb, or in general, as the mysql extension is deprecated, use PDO or Mysqli instead if wpdb isn't available — Tom J Nowell
– Tom J Nowell ♦, Commented Nov 26, 2014 at 18:23

Pat J · Accepted Answer · 2014-11-26 19:20:35Z

2

First off, you should not be handing an untrusted input (in this case, $_GET['id'] to your database. Always SQL-escape the query and validate/sanitize the data. (In the code snippet below, it's SQL-escaped using $wpdb->prepare() for escaping and int typecasting to sanitize to integer value).

Secondly, the $wpdb object provides more than just the query() function. In your case, it sounds like you're trying to get a set of rows, so you can use the get_results() function:

if ( ! empty( $_GET['msg'] ) ) {

    global $wpdb;
    $vuser_id = (int) $_GET['id'];
    $result = $wpdb->get_results( 
                   $wpdb->prepare( 
                       "SELECT * FROM {$wpdb->prefix}quiz WHERE quiz_id=%d", 
                       $vuser_id 
                    ) 
    );

    var_dump( $result );

}

The var_dump() is in there so you can examine exactly what $wpdb->get_results() returned. By default it'll be an array of objects.

Reference

wpdb class on the WordPress Codex

edited Nov 26, 2014 at 19:20

answered Nov 26, 2014 at 18:14

Pat J

12.6k2 gold badges29 silver badges36 bronze badges

1

I was writing almost the same answer but you was faster. I want to make a correction. The prepare method doesn't sanitize the data; this method scapes the query string against sql-injection attacks. Sanitize the data is a different thing, for exmpale, typecasting $_GET['id'] to integer value.

cybmeta
– cybmeta

2014-11-26 18:17:05 +00:00
Commented Nov 26, 2014 at 18:17
1

I'd also add that you shouldn't hardcode the wp_ prefix, not all sites use wp_ and it breaks multisite support. Use $wpdb->prefix instead. Also @cybmeta it's not a race to answer first, it's a race to answer best =]

Tom J Nowell
– Tom J Nowell ♦

2014-11-26 18:24:13 +00:00
Commented Nov 26, 2014 at 18:24
Thank you. I had forgot to make sure it was an integer, rookie mistake. I ended up using $wpdb->get_row as an array instead of using $wpdb->get_results.

Brandon Shutter
– Brandon Shutter

2014-11-26 18:38:10 +00:00
Commented Nov 26, 2014 at 18:38

Add a comment |

Brandon Shutter · Accepted Answer · 2014-11-26 18:37:18Z

0

<?php
if (!empty($_GET['msg'])) {

    global $wpdb;
    $user_id = (int) $_GET['id'];
    $sql = $wpdb->prepare("SELECT * FROM wp_quiz WHERE quiz_id='%d'", $user_id);
    $result = $wpdb->get_row($sql, ARRAY_A);
    var_dump($user_id);
    var_dump($result);

    $total = $result['total_count'];

Ended up figuring it out after I posted the question.

answered Nov 26, 2014 at 18:37

Brandon Shutter

33 bronze badges

Add a comment |

Stack Exchange Network

Custom Wordpress Table wpdb

2 Answers 2

Reference

Your Answer

Hot Network Questions

Custom Wordpress Table wpdb

2 Answers 2

Reference

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions