7

As title implies, I cannot seem to get $wpdb->get_results to give results. Running the "raw" query is successful, but this seems to be failing for some strange unknown reason.

    global $wpdb;


    $sql = "SELECT *
            FROM something aki, 
                 somethingelse akb, 
                 somethingelseelse ac 
            WHERE aki.keyID = akb.keyID 
              AND akb.someID = ac.someID 
              AND aki.type LIKE '%thing%'";

    $corps = $wpdb->get_results($sql, OBJECT);

    var_dump($corps);
9
  • 1
    Have you tried runnning this query directly in the phpmyadmin? Sep 22, 2014 at 5:15
  • 1
    Yes, phpmyadmin and mysql shell directly. It works in both. Yet no results (empty array) in $wpdb->get_results
    – 1Up
    Sep 22, 2014 at 5:40
  • 1
    Not able to figure out the exact issue. But you can debug it by using it without second parameter OBJECT and using simple query and then adding conditions. You will get to know the issue Sep 22, 2014 at 5:49
  • 2
    Arrrr... I just used $wpdb->show_errors( true ) before the query and I got the error back. It seems that WP started encoding the ' character inside queries suddenly and out of the blue... Might be the reason of your problems too. Might want to feed '%thing%' as a parameter to get_reults.
    – NoOne
    Feb 27, 2016 at 19:44
  • 1
    Eventually, my problem turned out to be that I should use $wpdb->posts instead of wp_posts inside the query. I guess that somehow, for no apparent reason, the DB tables on our server got renamed? WTF?!?!?!
    – NoOne
    Feb 27, 2016 at 20:03

5 Answers 5

3

Use $wpdb->show_errors( true ) before the query and see what error comes back.

My problem (because I've experienced the same thing) was that I should use $wpdb->posts instead of wp_posts inside the query. The prefix of the tables can change from WP installation to installation or even in the same installation depending on the time (e.g. an admin can change the prefix whenever he likes). So, one should write the query like that:

$query = "select <stuff here> from $wpdb->posts where <stuff here>";
1

I was troubleshooting my script that produced an empty array in WordPress with a seemingly healthy MySQL string. It turned out that, unlike the person who asked the question, I used $wpdb->prepare(), but the solution I found may still be useful to someone who comes across this Q&A (as I have).

This string, AND aki.type LIKE '%thing%'", contains percentage wilcards (%) that indicate that we're looking for aki.type cell that contains the thing as part of its value.

$wpdb->prepare converts them into hashes, and the MySQL server gets something like this: AND aki.type LIKE '{399038939300d2c307c53b29a166ee90101da7b2aba7978a898694010bf1bfe6}thing{399038939300d2c307c53b29a166ee90101da7b2aba7978a898694010bf1bfe6}'. Naturally, one would get no results!

The wpdb::prepare page reads, 'Literal percentage signs (%) in the query string must be written as %%. Percentage wildcards (for example, to use in LIKE syntax) must be passed via a substitution argument containing the complete LIKE string, these cannot be inserted directly in the query string. Also see wpdb::esc_like().'

So, the LIKE part must be prepared like this (example from the wpdb::esc_like() page):

$wild = '%';
$find = 'only 43% of planets';
$like = $wild . $wpdb->esc_like( $find ) . $wild;
$sql  = $wpdb->prepare( "SELECT * FROM $wpdb->posts WHERE post_content LIKE %s", $like );

Where $wpdb->esc_like() should go before $wpdb->prepare().

And if one wants to replace percentage wildcard placeholders manually, they should use $wpdb->remove_placeholder_escape() after $wpdb->prepare().

5
  • I am posting this here because the query in the question is very similar to what I had, while it wasn't immediately obviuos that in my case the problem was in $wpdb->prepare(). I believe someone else may come to this page with the same problem.
    – Artem
    Jun 14 at 7:24
  • Your issue is sort of similar, but not the same as the original question. I suggest opening a new question and then answering it (answering your own question is encouraged). Then you can mark your answer as the accepted answer, and people having issues with $wpdb->prepare() (as opposed to $wpdb->get_results()) can more easily find your question and answer.
    – Pat J
    Jun 14 at 15:08
  • Wilco Pat J. Do you think I should leave this answer here or delete it?
    – Artem
    Jun 14 at 15:55
  • I'd say leave it. If an admin feels it's inappropriate they can remove it.
    – Pat J
    Jun 14 at 17:44
0

May be a bit late, but you are missing the "prepare" call necessary in wordpress:

$Table_Name    = $wpdb->prefix.'Your Table Name';
$sql_query     = $wpdb->prepare("SELECT * FROM $Table_Name WHERE aki.keyID=%d AND akb.someID=%d AND aki.type like %s", akb.keyID, ac.someID, ''%thing%');
$result        = $wpdb->query( $sql_query ); 

Multiple results can be found in the array.

3
  • He mentioned that he had the table name wrong above. The $wpdb->prepare() call isn;t required for a query that is entirely static and not user-originated so isn't likely to be helpful here. In any case, your prepare() line above contains code that is not PHP.
    – Brian C
    Apr 11, 2020 at 4:31
  • For numeric searchs no...but for mySQL string searches, require a different set of parameter passing that the prepare call does for you. Otherwise you have to insert the '%s%' around the string. If you don't the mysql search fails Apr 12, 2020 at 21:49
  • Correct, but you misread. “Static” means unchanging, ie a query based entirely on fixed text, within variables inserted. The query shown was fixed test (aka “static”) so using prepare adds nothing. However it’s good practice to use prepare() when interpolating values into a query (aka dynamic or non-static). Cheers.
    – Brian C
    Apr 13, 2020 at 7:04
0

I had the same issue a, a good sql statement, no errors, but no results using wpdb.

The fix for me was a matter of having the correct collation/character encoding (e.g. COLLATE 'utf8_general_ci') for my table.

I had a query that worked, the table was replaced with a dump from an other server (and older server) and my query stopped working. It returned NULL or 0 even thought running the query in the database returned the proper results.

I create the tables both ways (a couple times because I did not believe it), ran the same data insert script, and did not change my PHP/wpdb code.

When the table was created with this this, my query did NOT work.

CREATE TABLE IF NOT EXISTS `vf_user` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `email` varchar(50) NOT NULL,
  `full_name` varchar(25) NOT NULL,
  `password` varchar(25) NOT NULL,
  `role` enum('user','tester','admin') NOT NULL DEFAULT 'user',
  `phone` varchar(25) NOT NULL,
  `last_modified` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `user_activation_key` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `email` (`email`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=325 DEFAULT CHARSET=utf8 COMMENT='users table';

When it was created with this, my query did return results

CREATE TABLE `vf_user` (
    `id` MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(50) NOT NULL COLLATE 'utf8_general_ci',
    `full_name` VARCHAR(25) NOT NULL COLLATE 'utf8_general_ci',
    `password` VARCHAR(25) NOT NULL COLLATE 'utf8_general_ci',
    `role` ENUM('user','tester','admin') NOT NULL DEFAULT 'user' COLLATE 'utf8_general_ci',
    `phone` VARCHAR(25) NOT NULL COLLATE 'utf8_general_ci',
    `last_modified` TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    `user_activation_key` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `email` (`email`) USING BTREE
)
COMMENT='users with access to the virtual festival'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=325
;
0

Wpdb->get_results function from wordpress returns the result if successful otherwise it will return null. There can be many reasons if a query get failed.Refer in-depth article on debugging get_results() returning empty results here

Although you can use functions like wpdb->show_error() to check what was the last error after executing the sql query. sometimes this error returns empty then try to use wpdb->last_query to check the final query that get formed.

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.