CODEWITHSUNDEEP
phpmysqlwordpress
aeemk
aeemk

Reputation: 387

MYSQL php: query multiple rows and return value if "WHERE IN" id not found

I am developing a website on wordpress that manages events.

The issue is that I need to get some event title names from their corresponding IDs in a single query. I am trying to get multiple rows out of a single MYSQL query, which works fine using "WHERE IN ({$IDs})": 

$events_implode = implode(',',$events);
$events_titles = $wpdb->get_results("SELECT `title` FROM `wp_events` WHERE `id` in ({$events_implode}) ORDER BY FIELD(id,{$events_implode}",ARRAY_A);

However, I need it to return a value/string if one of the $IDs was not found in the query instead of returning nothing.

Example: If $events_implode is like: 318,185,180,377, and the events with IDs 180 & 185 do not exist (being deleted for instance), I get back the events titles for 318 & 377 only:

Array
(
    [0] => Array
        (
            [title] => Title 1
        )

    [1] => Array
        (
            [title] => Title 4
        )

)

while I need it to return something like: 

Array
(
    [0] => Array
        (
            [title] => Title 1
        )

    [3] => Array
        (
            [title] => Title 4
        )

)

I tried IFNULL:

$events_titles = $wpdb->get_results("SELECT IFNULL( (SELECT `title` FROM `wp_events` WHERE `id` in ({$events_implode}) ORDER BY FIELD(id,{$events_implode}) ),'not found')",ARRAY_A);

but since the query returns more than one row, I get the error message:

"mysqli_query(): (21000/1242): Subquery returns more than 1 row"

Is there a solution to that? Many thanks!

Upvotes: 4

Views: 446

Answers (3)

trincot
trincot

Reputation: 350242

You could achieve this by also querying the id and not just the title:

SELECT id, title FROM ... etc

Then your result array will look like this (for the example data)

array(
    0 => array("id" => 318, "title" => "title for 318"),
    1 => array("id" => 377, "title" => "title for 377")
)

But this can be converted to what you need, with the help of the $events array you already have:

foreach($event_titles as $row) {
    $hash[array_search($row['id'], $events)] = $row['title'];
};

$events_titles = $hash;

Now the array looks like this (for the example data):

array(
    0 => array("title" => "title for 318"),
    3 => array("title" => "title for 377")
)

NB: That conversion can also be done without explicit loop:

$ids = array_intersect($events, array_column($event_titles, 'id'));
$event_titles = array_combine(array_keys($ids), array_column($event_titles, 'title'));

Alternative

The above is probably the best solution for your case, but you could also build your query dynamically to achieve this:

$subsql = implode(
    ' UNION ALL ', 
    array_map(function ($event) { return "SELECT $event AS id"; }, $events)
); 

$sql = "SELECT    wp_events.title 
        FROM      ($subsql) AS eid
        LEFT JOIN wp_events ON wp_events.id = eid.id
        ORDER BY  eid.id";

$events_titles = $wpdb->get_results($sql, ARRAY_A);

The value for $subsql will be like this (for example data):

SELECT 318 AS id UNION ALL
SELECT 185 AS id UNION ALL
SELECT 180 AS id UNION ALL
SELECT 377 AS id

The value of $events_titles will be an array that has one entry for each event, whether or not it matched. So for the example you gave, you would get this result:

array(
    0 => array("title" => "title for 318"),
    1 => array("title" => null), // because no match with 185
    2 => array("title" => null), // because no match with 180
    3 => array("title" => "title for 377")
)

So you can now just test for null. If additionally you would like to remove the null entries without changing the indexes (0 ... 3) in the array, then do this:

$event_titles = array_filter($event_titles, function ($title) { return $title; });

This will give you (in the example):

array(
    0 => array("title" => "title for 318"),
    3 => array("title" => "title for 377")
)

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

Using in clause seems no possible but you can build a dinamic query using union form dual 

"SELECT  t.index, a.`title` 
 FROM `wp_events` as a 
 INNER JOIN ( select  1 as index , 318 as id
             from dual 
             union 
             select 2, 185
             from dual
             union 
             select 3, 180
             from dual
             union 
             select 4, 377
             from dual                 

             ) t  on t.id = b.id
ORDER BY t.index"

Upvotes: 1

Christian
Christian

Reputation: 199

No, there is no way. The returning array from the mysql-query has nothing to do with the implode-array you gave to the query. There might be options to achieve that (creating a temp-table, fill it with keys, left join from that table to the events-table), but please don't to that as it is bad practice and nut KISS (keep it simple, stupid).

The simple question is, why do you want to do that?

Upvotes: 0

Related Questions