How to write PHP code inside MySQL query

Question

I am confused how to create loop inside MySQL query.

I have this query:

<?php
    $arr = explode(',', $features);
    $query_spicy = "
    SELECT wposts.*             
        FROM $wpdb->posts wposts
    INNER
        JOIN ( SELECT post_id
               FROM $wpdb->postmeta wpostmeta
                    WHERE ('; 
                                ( wpostmeta.meta_key = 'features' AND wpostmeta.meta_value LIKE '%test1%' )
                            OR 
                                ( wpostmeta.meta_key = 'features'AND wpostmeta.meta_value LIKE '%test2%' )
                            OR 
                                ( wpostmeta.meta_key = 'features'AND wpostmeta.meta_value LIKE '%test3%' )
                            OR
                                ( wpostmeta.meta_key = 'features'AND wpostmeta.meta_value LIKE '%test4%' )
                            OR
                                ( wpostmeta.meta_key = 'features'AND wpostmeta.meta_value LIKE '%test5%' )
                    ')
                    GROUP BY post_id
            ) AS t ON t.post_id = wposts.ID WHERE wposts.post_status = 'publish' AND wposts.post_type = 'book' ORDER BY wposts.post_date DESC";
?>

And I want generate bellow part dynamic with for loop:

( wpostmeta.meta_key = 'features' AND wpostmeta.meta_value LIKE '%test1%' ) OR 
( wpostmeta.meta_key = 'features' AND wpostmeta.meta_value LIKE '%test2%' ) OR 
( wpostmeta.meta_key = 'features' AND wpostmeta.meta_value LIKE '%test3%' ) OR
( wpostmeta.meta_key = 'features' AND wpostmeta.meta_value LIKE '%test4%' ) OR
( wpostmeta.meta_key = 'features' AND wpostmeta.meta_value LIKE '%test5%' )

Note: test1, test2, test3, test4 and test5, I am getting from this $arr.

I hope you understand my question and I will thankful if you help me.

Thanks :)

Why do you want to do this? It's certainly possible to achieve exactly what you're asking for, but I feel like there's probably a better way to do what you want than this. — Hecksa
– Hecksa, Commented Mar 5, 2014 at 9:22

Viktor Todorov · Accepted Answer · 2014-03-05 09:23:20Z

2

try something like

$arr = explode(',', $features);
$conditions = array();

foreach($arr as $feature){
    $condition[] = "( wpostmeta.meta_key = 'features' AND wpostmeta.meta_value LIKE '{$feature}' )";
}
$query = "SELECT ...". join("OR",$conditions);

But you can simply do something like wpostmeta.meta_key = 'feature' and wpostmet IN($features)

I think second solution is better and faster.

answered Mar 5, 2014 at 9:23

Viktor Todorov

742 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mr.Happy Over a year ago

Hi thanks you your answer but can you guide me with full explanation. Thanks.

Viktor Todorov Over a year ago

First here is more info for mysql in statements: w3schools.com/sql/sql_like.asp Ok I assume you already have a list of features, concatenated with a comma (dont forget to add ' to them). So you will need to create a query that looks like this: ...continue after while

Viktor Todorov Over a year ago

SELECT post_id FROM $wpdb->postmeta wpostmeta WHERE wpostmeta.meta_key = 'features' AND meta_value IN ($feature) You can also see INSTR function dev.mysql.com/doc/refman/5.0/en/…

dev4092 · Accepted Answer · 2014-03-05 09:24:40Z

0

Try this

wpostmeta.meta_key = ".$features." AND wpostmeta.meta_value LIKE '%test1%'

answered Mar 5, 2014 at 9:24

dev4092

2,9111 gold badge18 silver badges15 bronze badges

Comments

Peon · Accepted Answer · 2014-03-05 09:25:47Z

0

<?php
    $arr = explode( ',', $features );

    $query_spicy = "
    SELECT wposts.*             
        FROM {$wpdb->posts wposts}
    INNER
        JOIN ( SELECT post_id
               FROM {$wpdb->postmeta wpostmeta}
                    WHERE (";

    $tmp = array();
    foreach( $arr as $value )
    {
        $tmp[] = "( wpostmeta.meta_key = 'features' AND wpostmeta.meta_value LIKE '%{$value}%' )";
    }

    $query_spicy .= implode( ' OR ', $tmp );
    $query_spicy .= ")
                    GROUP BY post_id
            ) AS t ON t.post_id = wposts.ID WHERE wposts.post_status = 'publish' AND wposts.post_type = 'book' ORDER BY wposts.post_date DESC";
?>

answered Mar 5, 2014 at 9:25

Peon

8,0417 gold badges63 silver badges104 bronze badges

Comments

Robert Seddon-Smith · Accepted Answer · 2014-03-05 09:25:36Z

-1

FWIIW:

Just set an array to contain the data you need to insert then use foreach to loop through it.

$sql="SELECT wposts.*             
        FROM $wpdb->posts wposts
    INNER
        JOIN ( SELECT post_id
               FROM $wpdb->postmeta wpostmeta
                    WHERE ('; ";
foreach($arrayoftestdata as $test)
    {
    $sql.="( wpostmeta.meta_key = 'features' AND wpostmeta.meta_value LIKE '%$test%' ) OR ";
    }

$sql.="')
                    GROUP BY post_id
            ) AS t ON t.post_id = wposts.ID WHERE wposts.post_status = 'publish' AND wposts.post_type = 'book' ORDER BY wposts.post_date DESC";

answered Mar 5, 2014 at 9:25

Robert Seddon-Smith

9771 gold badge10 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to write PHP code inside MySQL query

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related