Count rows in while loop

Question

I am trying to while loop information into a SELECT statement, then COUNT the results. I have tried at least 10 different "solutions" and none works. I only get 0, 1, or nothing. Here's my most recent attempt:

$query35 = "SELECT * FROM movies WHERE userID = $memberID";
$result35 = $db->query($query35);

while ($row35 = $result35->fetchAll(PDO::FETCH_ASSOC)) {

    $movie = $row35['movie'];

    $query36 = "SELECT COUNT(*) AS similar FROM movies WHERE userID = '$profileID' && movie = '$movie'";
    $result36 = $db->query($query36);
    $row36->fetchObject;

    $similar = $row36['similar'];

    echo $similar;

}

`$row35` is an array of all results from `$query35`. It is not a row. If you want a row, use fetch not fetchAll. — kainaw, Apr 22 '15 at 18:38
You should use prepared statements. If a user wrote a movie title (presuming thats what movie is) you could be injected by putting that value directly back into a query. Or better yet an actual example `Schindler's List `.. (not an injection example just a case where this would break) — chris85, Apr 22 '15 at 18:41
This query pattern suffers form the N+1 problem and you should rewrite it to not make 1 query per movie http://stackoverflow.com/questions/97197/what-is-the-n1-selects-issue — Hugo Tunius, Apr 22 '15 at 18:48
I don't see var `$profileID` declared anywhere, I guess you're getting empty results on your second query. — Pedro Lobito, Apr 22 '15 at 18:59
Why do a nested query/loop. Just do it in 1 query - `SELECT COUNT(*) AS similar FROM movies WHERE userID IN($memberID,$profileID) GROUP BY movie HAVING similar = 2` — Sean, Apr 22 '15 at 19:07
I am trying to compare current user with profile they're viewing. $profileID is the profile they're using and $memberID is logged in user. — Donald Faulknor, Apr 22 '15 at 19:16
@kainaw as I have mentioned, I've tried many solutions, including fetchAll. — Donald Faulknor, Apr 22 '15 at 19:16
@PedroLobito $profileID and $memberID our properly declared in another page. For all intense purposes, assume they exist. I know that isn't where my problem is. I can echo them fine and get the id numbers. — Donald Faulknor, Apr 22 '15 at 19:19
@DonaldFaulknor You can cut and paste random code from others, you will not likely get what you want. You must understand what you are doing. fetchAll works one and only one time. It returns an array indexed 0, 1, 2, 3... So, your attempt to access $row35['movie']; will be null because movie is not an index in the series 0, 1, 2, 3... So, you only hit on movies named ''. So, fetchAll clearly will not produce anything useful as your code is written. You need to change to fetch and then see what results you get for $row35['movie']. Then, work on the next query. — kainaw, Apr 22 '15 at 19:40
I wrote the code myself. This is simply one of many solutions I tried for my problem. Regular php was so much easier when you could just count the rows and name it using `AS newName`. Old simple way that doesn't work anymore... `$query = "SELECT COUNT(*) AS count FROM movies WHERE userID = '$userID'"; $result = mysql_query($query); $count = $row['count']; echo $count;` — Donald Faulknor, Apr 23 '15 at 01:32
And I have tried `fetch`. I can echo the query fine. If I echo $movie, it shows me all the correct instances and queries when I use fetch. I have gotten the queries right in several of my attempts. Still isn't counting them — Donald Faulknor, Apr 23 '15 at 01:40

Samuel Hawksby-Robinson · Answer 1 · 2015-04-22T18:55:58.890

0

If all you are looking to do is count the number of times your loop is run per script execution, then it is fairly simple to do. See below:

$count = 0;

while($row35 = $result35->fetch(PDO::FETCH_ASSOC)){
    //Do all your loop stuff.
    $count++;
}

var_dump($count);

Important to note that your $count variable needs to be declared outside of your loop.

Also you either need to use fetchAll with a foreach loop, or use fetch with a while loop, but don't mix them.

Also a tip on good practice. Try to avoid as much as possible executing any kind of database querying with a loop, you can run into serious performance issues down the line as your loops get bigger.

edited Apr 22 '15 at 18:55

answered Apr 22 '15 at 18:51

Samuel Hawksby-Robinson

2,652
4
24
25

but `fetchAll` returns just once. isn't it? – Alex Apr 22 '15 at 18:53
Ah yes, I didn't spot that. While fetch or foreach fetchAll, not any other combination. – Samuel Hawksby-Robinson Apr 22 '15 at 18:58

score 0 · Answer 2 · answered Apr 22 '15 at 18:54

0

$row36->fetchObject;

Seems null object, I think it should be

$row36 = $result36->fetchObject();

answered Apr 22 '15 at 18:54

zul

15
5

Is there someone I can pay to write a code that works? I've been working on this for 18 straight hours. I've been through 75 solutions and none work. – Donald Faulknor Apr 23 '15 at 03:23
@DonaldFaulknor could you please put more specific requirements, such as database scheme, the scenario and result you're expected, etc – zul Apr 23 '15 at 03:37
I am trying to create a compatibility feature on my website where it determines how many different interests you have in common. This is just one table called movies where I am trying to return the number of results where the logged in member and the member of the profile the person is viewing have in common. – Donald Faulknor Apr 23 '15 at 04:21
I'm trying to develop the site with as little money as possible but I'm seriously almost ready to go to odesk.com and pay someone to do it for me. I can figure most things out myself, but this is the biggest headache of all. So far my biggest challenge. – Donald Faulknor Apr 23 '15 at 04:23

Alex · Answer 3 · 2015-04-23T12:48:23.570

0

Not sure what are you doing. But at least try:

$query35 = "SELECT * FROM movies WHERE userID = $memberID";
$result35 = $db->query($query35);
if ($row35 = $result35->fetchAll(PDO::FETCH_ASSOC)) 
foreach ($row35 as $row) {

    print_r($row);

}

or maybe

$query35 = "SELECT * FROM movies WHERE userID = $memberID";
$result35 = $db->query($query35);
while ($row = $result35->fetch(PDO::FETCH_ASSOC)) {

    print_r($row);

    $movie = $row['movie'];

    $query36 = "SELECT COUNT(*) AS similar FROM movies WHERE userID = '$profileID' && movie = '$movie'";
    $result36 = $db->query($query36);
    $obj  = $result36->fetchObject();

    $similar = $obj->similar;

    echo $similar;

}

edited Apr 23 '15 at 12:48

answered Apr 22 '15 at 18:55

Alex

16,739
1
28
51

1

Which question are you answering ? this one ?! – Pedro Lobito Apr 22 '15 at 18:57
@PedroLobito I tried your "solution" and it found `fetch_assoc()` to be an undefined function. – Donald Faulknor Apr 23 '15 at 04:25
@DonaldFaulknor ohh, my bad, try fixed update if you still need it – Alex Apr 23 '15 at 12:49
@PedroLobito I'm not where I can at the moment. But if all you changed was fetch(PDO::FETCH_ASSOC)... don't worry. Already tried that. The result for that was that it counted each result individually, therefore all results were one and looped 3 times. If only I could add up all the ones and get $similar to equal 3. – Donald Faulknor Apr 23 '15 at 20:44

Count rows in while loop

3 Answers3