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.

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.

That fixed it! Many thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.