PHP 'IN' query returns only 1 row

Question

Can anyone explain me why the last query returns always 1 row. it should return more than 1 because there're a lot of records in the database!

Sorry for my bad english

            $query=mysql_query("SELECT book_id FROM ".DB_PREF."books_cats_list WHERE cat_id='".$cat."'");
            if($row=mysql_num_rows($query))
            {

                //fetching all books from $cat category
                for($i=0; $fetch=mysql_fetch_assoc($query); $i++)
                {
                    $records[$i]=$fetch['book_id'];
                }

                //Joining all records in a string for next query
                $records=implode(",",$records);

                //returning num rows if there're book_id records in $records array
                $query=mysql_query("SELECT * FROM ".DB_PREF."books WHERE book_id IN ('".$records."')");
                $rows=mysql_num_rows($query);
                echo $rows;

You should use mysql_fetch_assoc() to read the second query results, just like on the first one. — MarcinJuraszek
– MarcinJuraszek, Commented Apr 13, 2012 at 16:42

gen_Eric · Accepted Answer · 2012-04-13 18:04:20Z

3

Your query is going to look like this:

SELECT * FROM books WHERE book_id IN ('2,3,4,5')

Note how inside the IN, it's all one string. This one string will be converted to an int. This happens by stopping at the 1st non-number character. So, the query becomes:

SELECT * FROM books WHERE book_id IN (2)

Try to remove the single quotes inside the IN.

NOTE: If your values aren't ints, try changing the the implode to: implode("','",$records), and keep the quotes inside the IN.

edited Apr 13, 2012 at 18:04

answered Apr 13, 2012 at 16:43

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

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

Comments

gen_Eric · Accepted Answer · 2012-04-13 17:59:42Z

1

At a quick glance I suggest the for loop:

for($i=0; $fetch=mysql_fetch_assoc($query); $i++)

Should be a while loop:

while($fetch=mysql_fetch_assoc($query))

I expect it is only doing one record with that for loop code.

I suggest however you do a left join select

SELECT * FROM books_cats_list as cat
left join books as book on cat.book_id = book.book_id
WHERE cat.cat_id='$cat'

This will be far more optimal in terms of database performance I expect.

edited Apr 13, 2012 at 17:59

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

answered Apr 13, 2012 at 16:49

jonathanKingston

1,41810 silver badges16 bronze badges

Collectives™ on Stack Overflow

PHP 'IN' query returns only 1 row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related