0

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 Nov 26 '14 at 18:23
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

  • 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 Nov 26 '14 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 Nov 26 '14 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 Nov 26 '14 at 18:38
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.

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.