0

I can't figure out why this is so difficult. I have a SQL query that results in a column of numeric results, i.e.:

23
45
67
54
34
78
56

Using a PHP/MySQL query, I want to simply generate a comma separated list of these values that can be displayed or used elsewhere. This is the code I am using

 <?php


global $wpdb;

$sql = <<<SQL
SELECT FK_T_L_INCENTIVES_ID FROM lighting_incentives.WAYPOINT_USER_PICKED WHERE WP_RECOMMENDED = 1 AND FK_USER_ID = 31
SQL;

if (!$sql) { // add this check.
    die('Invalid query: ' . mysql_error());
}

$resultset = array();
$rebates = $wpdb->get_results( $sql, ARRAY_A );
foreach($rebates as $data) {
    $resultset[] = $data;
}
$comma_separated = implode(",", $resultset);

print $comma_separated;
?>

For some reason I keep getting the error 

mysql_fetch_array() expects parameter 1 to be resource

Any help would be much appreciated!

qwerty123
  • 350
  • 1
  • 6
  • 20

5 Answers5

3

this can be done in SQL if you would like... all you need to do is on the column that returns the numbers do this GROUP_CONCAT(column_name) and it'll put all in one row with it comma separated

removes the need to write multiple lines of php code

John Ruddell
  • 25,283
  • 6
  • 57
  • 86
1

Change

$resultset=array();
$rebates = $wpdb->get_results( $sql );
while ($data = mysql_fetch_array($rebates,MYSQL_NUM)) {
    $resultset[] = $data;
}

to

$resultset = array();
$rebates = $wpdb->get_results( $sql, ARRAY_A );
foreach($rebates as $data) {
    $resultset[] = $data;
}

More info on how to use get_results()

Machavity
  • 30,841
  • 27
  • 92
  • 100
  • Thank you for your help! After replacing the code you suggested, I now get this error `Catchable fatal error: Object of class stdClass could not be converted to string` – qwerty123 Jun 11 '14 at 20:27
  • Added a second argument to get you an array – Machavity Jun 11 '14 at 20:39
  • Thank you for your help! I have updated the current version of my code above. Now when the page loads I get `Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array ` Do you have any additional insight into how I can change each of these "array" strings to the number I desire? – qwerty123 Jun 11 '14 at 20:48
  • @MattShirk that is the correct response, you are just printing the whole array.. try changing your print to something like `print_r ($comma_separated);` if you want to see the info in the array – John Ruddell Jun 11 '14 at 20:55
  • @JohnRuddell Thanks for your help, however after adding `print_r ($comma_separated); ` I still receive the same response `Array,Array,Array,Array,Array,Array,Array, etc` – qwerty123 Jun 11 '14 at 21:15
  • probably because its making an array of arrays.. did you try my answer? might be a lot easier – John Ruddell Jun 11 '14 at 21:34
1

Try this:

$resultset = array();
$rebates = $wpdb->get_results( $sql );
foreach($rebates as $data) {
$resultset[] = (array) $data;
}

$comma_separated = implode(",", $resultset);

echo  $comma_separated;
Hackerman
  • 12,139
  • 2
  • 34
  • 45
0

$wpdb does not return a MySQL, please try:

$table_ids = $wpdb->get_col( "SELECT id FROM table" );

Source: http://codex.wordpress.org/Class_Reference/wpdb

gbrunacci
  • 13
  • 3
0

@JohnRuddell @Machavity

Thanks for your help. Here is code that finally worked.

<?php


global $wpdb;
$user_ID = get_current_user_id();
$sql = <<<SQL
SELECT FK_T_L_INCENTIVES_ID FROM lighting_incentives.WAYPOINT_USER_PICKED WHERE WP_RECOMMENDED = 1 AND FK_USER_ID = $user_ID
SQL;

if (!$sql) { // add this check.
    die('Invalid query: ' . mysql_error());
}

$resultset = array();
$rebates = $wpdb->get_results( $sql, ARRAY_A );
foreach($rebates as $data) {
     echo $data['FK_T_L_INCENTIVES_ID']. ",";
}

?>
qwerty123
  • 350
  • 1
  • 6
  • 20