Hw does a Query really work in PHP?

[doubledee]

Can someone help me understand exactly how queries work in PHP?

 

Here is some code of mine...

// Build query.
$q2 = 'SELECT m.first_name, m.username, m.photo_name, m.photo_label, m.logged_in, m.last_activity,
							c.created_on, c.body, c.status
		FROM member AS m
		INNER JOIN comment AS c
		ON m.id = c.member_id
		WHERE c.status="Approved" AND c.article_id=?
		ORDER BY c.created_on';

// Prepare statement.
$stmt2 = mysqli_prepare($dbc, $q2);

// Bind variable to query.
mysqli_stmt_bind_param($stmt2, 'i', $articleID);

// Execute query.
mysqli_stmt_execute($stmt2);

// Store results.
mysqli_stmt_store_result($stmt2);

// Check # of Records Returned.
if (mysqli_stmt_num_rows($stmt2)>=1){
	// Comment(s) Found.
	$commentExists = TRUE;

	// Bind result-set to variables.
	mysqli_stmt_bind_result($stmt2, $firstName, $username, $photoName, $photoLabel, $loggedIn, $lastActivity,
																	$createdOn, $comments, $status);
}else{
	// Comments Not Found.
	$commentExists = FALSE;
}//End of FIND COMMENT RECORD

 

 

Questions:

 

1.) When I run/execute that query what happens?

 

Is the entire "Results Set" created?

 

2.) If so, where is it stored?

 

3.) What happens when I bind the results-set to variables?

 

Does that actually assign values to the variables or just create a "link"?

 

4.) What happens here...

		while (mysqli_stmt_fetch($stmt2)){

 

 

Debbie

 

[requinix]

First, understand that mysqli doesn't really do a whole lot of work. Everything comes from the MySQL C library. So most of the answers to your questions are along the lines of "the code calls the appropriate function from MySQL's C library". Besides that,

 

1. The query is prepared (which involves it being parsed for validity), you bind a value to the one parameter, the query and bound values are sent to the MySQL server, the server does whatever it does, and handle to the result gets passed back to the library. Your code then instructs that the resultset gets stored in memory, you bind variables to columns, each row is (re)read, and the variables are updated. mysqli itself only does a bit of that.

2. In memory.

3. It binds stuff internally in pretty much the same way the PHP code binds stuff. It does not set values (which it does via references) until you begin (re)reading the resultset.

4. The code calls the appropriate function from MySQL's C library and does some other stuff.

[Zane]

Think of it like a movie rental store.  The actual database is.. well, the movie store itself.  SQL is the cashier who helps you find a certain movie.  PHP is YOU, the customer, albeit a very annoying one in this analogy.  You can ask the cashier (SQL) to find you any movie that you want, but unless you actually rent the movie, the location of this movie is lost... and since this hypothetical customer has no SQL memory, he/she must ask the cashier(SQL) again and again and again where every single movie that he/she wants .. is.

 

It isn't until the customer(PHP) actually says, "I think I'll pick that one up..and hold it in my hands while I find others" that memory is stored.  Well, I guess it wouldn't be stored into memory until said movies were actually rented, but the customer could steal them.  Either way, the customer selecting the movies is no different that when you assign variables for result sets in PHP.