6

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
  • Have you tried runnning this query directly in the phpmyadmin? – TBI Infotech Sep 22 '14 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 '14 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 – TBI Infotech Sep 22 '14 at 5:49
  • 1
    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 '16 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 '16 at 20:03
2

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>";
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 '20 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 – Debbie Kurth Apr 12 '20 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 '20 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
;

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.