CODEWITHSUNDEEP
phpmysql
Mahmoud Samy
Mahmoud Samy

Reputation: 183

select from database where id in Field

I have a Database for news i use multi cats in each news so i have a filed in database called cats and i insert data inside it like that 1,5,8,2 this data is the cats id.

And i use this code to select cats from database

$select_newscats = $mysqli->query("SELECT * FROM news_cats where show_home = '1' and  kind = 1 or kind = 2 order by ord_show asc");

while ($rows_newscats = $select_newscats->fetch_array(MYSQL_ASSOC)){

    $id_newscats          = $rows_newscats ['id'];
    $title_newscats       = $rows_newscats ['title'];
    $ord_show_newscats    = $rows_newscats ['ord_show'];
    $icon_newscats        = $rows_newscats ['icon'];
    $kind_newscats        = $rows_newscats ['kind'];
    $description_newscats = $rows_newscats ['description'];

}

i want to select news from database where this $id_newscats inside cats filed in news table so i traied this code to get news data

$select_newscats = $mysqli->query("SELECT * FROM news_cats where show_home = '1' and  kind = 1 or kind = 2 order by ord_show asc");

while ($rows_newscats = $select_newscats->fetch_array(MYSQL_ASSOC)){

    $id_newscats          = $rows_newscats ['id'];
    $title_newscats       = $rows_newscats ['title'];
    $ord_show_newscats    = $rows_newscats ['ord_show'];
    $icon_newscats        = $rows_newscats ['icon'];
    $kind_newscats        = $rows_newscats ['kind'];
    $description_newscats = $rows_newscats ['description'];


    $select_news = $mysqli->query("SELECT * FROM news where $id_newscats  IN (cats)");
    while ($rows_news = $select_news->fetch_array(MYSQL_ASSOC)){

        $id_new          = $rows_news ['id'];
        $title_news       = $rows_news ['title'];

        echo "<div>{$title_news}</div>";
    }
}

but i have a problem with this code it select only first one for example if i have this cats id's 5,2,7 it gives me only news from this id 5 and ignore 2,7 so how can i fix that and select all news where this id $id_newscats in cats Field Thanks.

Upvotes: 0

Views: 227

Answers (4)

Mahmoud Samy
Mahmoud Samy

Reputation: 183

i got this

change this

$select_news = $mysqli->query("SELECT * FROM news where $id_newscats  IN (cats)");

into this 

$select_news = $mysqli->query("SELECT * FROM news where FIND_IN_SET('$id_newscats', cats) ");

Upvotes: 0

RiggsFolly
RiggsFolly

Reputation: 94642

You are not building an IN list in the outer while loop! You get ONE row and then in the inner loop you only have ONE id to search with so there is no need for the IN syntax. Which is wrong anyway

Your outer query also look Wrong, I think you need some brackets round the OR.

$select_newscats = $mysqli->query("SELECT * 
                                   FROM news_cats 
                                   WHERE show_home = '1' 
                                    and  ( kind = 1 or kind = 2 ) 
                                   ORDER BY ord_show asc");

while ($rows_newscats = $select_newscats->fetch_array(MYSQL_ASSOC)){

    $id_newscats          = $rows_newscats ['id'];
    $title_newscats       = $rows_newscats ['title'];
    $ord_show_newscats    = $rows_newscats ['ord_show'];
    $icon_newscats        = $rows_newscats ['icon'];
    $kind_newscats        = $rows_newscats ['kind'];
    $description_newscats = $rows_newscats ['description'];

    // amended query, you only have one $id_newscats
    //  each time round this outer loop
    $select_news = $mysqli->query("SELECT * 
                                   FROM news 
                                   WHERE id = $id_newscats");

    while ($rows_news = $select_news->fetch_array(MYSQL_ASSOC)){

        $id_new          = $rows_news ['id'];
        $title_news       = $rows_news ['title'];

        // no need for {} round scalar variables
        //echo "<div>{$title_news}</div>";
        echo "<div>$title_news</div>";

        // or you could just dothis and dont bother 
        // creating an unecessary scalar variable
        //echo "<div>{$rows_news['title']}</div>";
    }
}

Upvotes: 0

Eldho NewAge
Eldho NewAge

Reputation: 1333

Looks like there is both syntax(1) and Logical(2) error.

  1. SELECT * FROM news where $id_newscats IN (cats) is wrong .

    This is the correct syntax.
    $select_news = $mysqli->query("SELECT * FROM news where cats IN ($id_newscats)")

  2. Use paranthesis in your Query.

    SELECT * FROM news_cats where show_home = '1' and (kind = 1 or kind = order by ord_show asc"

Upvotes: 1

Maximilian Riegler
Maximilian Riegler

Reputation: 23506

You have your IN query in the wrong order.

$select_news = $mysqli->query("SELECT * FROM news where cats IN ($id_newscats)")

Upvotes: 0

Related Questions