CODEWITHSUNDEEP
phpmysql
Mahmoud Samy
Mahmoud Samy

Reputation: 183

MySQL SELECT WHERE id IN() get num_rows

I use this code to show category from database 

$select_newscats = $mysqli->query("SELECT * FROM news_cats 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'];

//here is my data

}

i have in news table row for categoies it's name is cats and i insert data inside it like that 1,5,6,8

and i use this code to count news inside each cat 

$select_newsnum = $mysqli->query("SELECT id FROM news where $id_newscats  IN (cats)");
$rows_newsnum   = $select_newsnum->fetch_array(MYSQL_ASSOC);
$num_newsnum    = $select_newsnum->num_rows;

it gets only first value for example if i have this values 1,5,6,8 it gets only 1 the first value

Upvotes: 0

Views: 496

Answers (2)

Nick Marden
Nick Marden

Reputation: 116

$select_newsnum = $mysqli->query("SELECT id FROM news where $id_newscats  IN (cats)");
$rows_newsnum   = $select_newsnum->fetch_array(MYSQL_ASSOC);
$num_newsnum    = $select_newsnum->num_rows;

Here $rows_newsnum will only return the first row in the database.

Fetch Array only returns one row at a time, and then moves the row pointer forward. For example the following code on the dataset you provided (1, 5, 6, 8).

$rows_newsnum = $select_newsnum->fetch_array(MYSQL_ASSOC);
echo $rows_newsnum["id"]; // Will print 1
$rows_newsnum = $select_newsnum->fetch_array(MYSQL_ASSOC);
echo $rows_newsnum["id"]; // Will print 5

What you need to do is move it into a while loop (like your first example) to get to access each row, like below:

while($row = $select_newsnum->fetch_array(MYSQL_ASSOC)) {
    echo $rows_newsnum["id"] . ", ";
}

// Will produce:
// 1, 5, 6, 8,

If you just want to get a count

Your existing code should work fine. And you can omit the call to fetch_array.

$num_newsnum = $select_newsnum->num_rows;
echo $num_newsnum; // Will display 4

Read more about fetch_array on the PHP documentation: http://php.net/manual/en/mysqli-result.fetch-array.php

Hope this helps.

Upvotes: 0

Shadow
Shadow

Reputation: 34231

I would do the counting in a single sql call, not with php logic and separate sql calls. For this I would join the 2 tables using left join and use a join condition instead of an in clause:

SELECT nc.id, nc.title, nc.ord_show, nc.icon, nc.king, nc.description, count(n.id) as newsnum
FROM news_cats nc
LEFT JOIN news n ON nc.id=n.cats
GROUP BY nc.id, nc.title, nc.ord_show, nc.icon, nc.king, nc.description
ORDER BY nc.ord_show asc

Obviously, make sure that the join condition is the right one. When you loop through the resultset, the number of news per category will be in the newsnum field.

Upvotes: 1

Related Questions