0

Wasn't sure whether to put this with PHP or Mysql

I'm having trouble with the below code, it only does the inner while loop once, while still cycling past it every time.
It should update the "favourite" integer column in each "link" by checking if it's in the favourites of each "user".

Favourites in users database entries look like .1.4.8. - hence the ".".___."."

$links=mysql_query("SELECT * FROM links WHERE id>0");
$users=mysql_query("SELECT * FROM users WHERE userid>0");
while($link=mysql_fetch_array($links)){
    $working=0;
    
    while ($user=mysql_fetch_array($users)){
        echo "checking ",$user[6]," for .",$link[0],".<br />";
        if (substr_count($user[6],".".$link[0].".")>0){
            echo "found .",$link[0],". in ",$user[6],"<br />";
            $working+=1;
        }
    }
    
    echo "setting ",$link[0]," to ",$working,'<br />';
    mysql_query("UPDATE links SET favourites=$working WHERE id=$link[0]",$con);
}

gives results:

checking .3.4.2.1. for .1.
found .1. in .3.4.2.1.
checking .19.18.9.7.3. for .1.
setting 1 to 1
setting 2 to 0
setting 3 to 0
setting 4 to 0

It seems to ignore the inner while loop after it's used it once.

Anyone have any ideas what's going on?
Cheers.

Edited by Pinchanzee: n/a

2
Contributors
2
Replies
3
Views
7 Years
Discussion Span
Last Post by Pinchanzee
1

You have to reset your query cursor. After walking through the result with mysql_fetch_array once there are no more records to be retrieved.
Insert mysql_data_seek($users,0) at the end of the inner loop.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.