Firefog Firefog - 6 months ago 45
PHP Question

Wordpress $wpdb Custom query not working

Retrieving data from wpdb and returning as JSON: I am trying to populate my custom table data into a input field here is an example. I did everything but the PHP query not retrieving data from table.

Here is My WORDPRESS Version Not Working

<?php
global $wpdb;
$db_table_name = $wpdb->prefix . 'sonali_data';
$key=$_GET['key'];
$array = array();
$result = $wpdb->get_results ( "SELECT * FROM $db_table_name WHERE name LIKE '%{$key}%'");

while($row=mysql_fetch_assoc($result)){
$array[] = $row['name'];
}
echo json_encode($array);
?>


Here is My PHP Version works fine same as example

<?php
$key=$_GET['key'];
$array = array();
$con=mysql_connect("localhost","root","password");
$db=mysql_select_db("search",$con);
$query=mysql_query("select * from ajax_example where name LIKE '%{$key}%'");
while($row=mysql_fetch_assoc($query))
{
$array[] = $row['name'];
}
echo json_encode($array);
?>

Answer

When you are using wpdb it is already doing the sorting and putting it into array for you so you don't need mysql_fetch_assoc - also you should use wpdb prepare to sanitize the strings, especially when using $_GET as you are vulnerable to sql injection otherwise - revised code:

 global $wpdb;
 $db_table_name = $wpdb->prefix . 'sonali_data';
$holder = "%s";
 $key=$_GET['key'];
 $array = array();
 $result = $wpdb->get_results ( $wpdb->prepare("SELECT * FROM $db_table_name WHERE name LIKE '%{$holder}%'",$key),ARRAY_A);

 foreach($result as $res) {
        $array[] = $res['name'];
  }
 echo json_encode($array);

The ARRAY_A tells wpdb to sort the data into an associative array, the default is to sort it into an OBJECT in which case you would have had to do $array[] = $res->name

One more thing, you should use wp_send_json if you are sending back to a wp_ajax hook: so instead of echo json_encode($array); you do wp_send_json($array);