I wonder how mysql_query/mysqli_query and mysql_fetch_*/mysqli_fetch_* functions really work. Where is result of mysql_query call stored? Is it MySQL server or PHP client? When the transmission of data occurs?
It's a cooperation between PHP and the MySQL server. mysql*_query sends a query to the server, which prompts it to sift through its data and assemble a result set. This result set now needs to be transferred over to PHP, one row at a time.
By default, mysql*_query uses a buffered query, which means it's transferring the data little by little over to PHP as soon as it can. When calling mysql*_fetch*, you read a row of this data one by one. You can also use unbuffered queries, e.g. using mysql_unbuffered_query. This does not transfer data in the background, it only does so when you explicitly call one of the *fetch* functions. This uses less memory on the PHP side, but is slower and in turn requires the SQL server to hold onto the data longer than it would have otherwise.