PHP mysqli: store_result() function
mysqli_store_result function / mysqli::store_result
The mysqli_store_result function / mysqli::store_result — transfers a result set from the last query.
Object oriented style
mysqli_result mysqli::store_result ([ int $option ] )
Procedural style
mysqli_result mysqli_store_result ( mysqli $link [, int $option ] )
| Name | Description | Required/Optional | |
|---|---|---|---|
| link | A link identifier returned by mysqli_connect() or mysqli_init() | Required for procedural style only and Optional for Object oriented style | |
| option | The option that you want to set. It can be one of the following values: | Required for procedural style only and Optional for Object oriented style | |
| Valid options | |||
| Name | Description | ||
| MYSQLI_STORE_RESULT_COPY_DATA | Copy results from the internal mysqlnd buffer into the PHP variables fetched. By default, mysqlnd will use a reference logic to avoid copying and duplicating results held in memory. For certain result sets, for example, result sets with many small rows, the copy approach can reduce the overall memory usage because PHP variables holding results may be released earlier (available with mysqlnd only, since PHP 5.6.0) | ||
Usage: Procedural style
mysqli_stmt_init(connection);
Return value:
Returns a buffered result object or FALSE if an error occurred..
Version: PHP 5, PHP 7
$city="Paris"; // Create a prepared statement $stmt=mysqli_stmt_init($con); if (mysqli_stmt_prepare($stmt,"SELECT District FROM city WHERE Name=?")) < // Bind parameters mysqli_stmt_bind_param($stmt,"s",$city); // Execute query mysqli_stmt_execute($stmt); // Bind result variables mysqli_stmt_bind_result($stmt,$district); // Fetch value mysqli_stmt_fetch($stmt); printf("%s is in district %s",$city,$district); // Close statement mysqli_stmt_close($stmt); >mysqli_close($con); ?> Previous: stmt_init
Next: thread_id
Follow us on Facebook and Twitter for latest update.
PHP: Tips of the Day
How do I expire a PHP session after 30 minutes?
You should implement a session timeout of your own. Both options mentioned by others (session.gc_maxlifetime and session.cookie_lifetime) are not reliable. I’ll explain the reasons for that.
session.gc_maxlifetime
session.gc_maxlifetime specifies the number of seconds after which data will be seen as ‘garbage’ and cleaned up. Garbage collection occurs during session start.
But the garbage collector is only started with a probability of session.gc_probability divided by session.gc_divisor. And using the default values for those options (1 and 100 respectively), the chance is only at 1%.
Well, you could simply adjust these values so that the garbage collector is started more often. But when the garbage collector is started, it will check the validity for every registered session. And that is cost-intensive.
Furthermore, when using PHP’s default session.save_handler files, the session data is stored in files in a path specified in session.save_path. With that session handler, the age of the session data is calculated on the file’s last modification date and not the last access date:
Note: If you are using the default file-based session handler, your filesystem must keep track of access times (atime). Windows FAT does not so you will have to come up with another way to handle garbage collecting your session if you are stuck with a FAT filesystem or any other filesystem where atime tracking is not available. Since PHP 4.2.3 it has used mtime (modified date) instead of atime. So, you won’t have problems with filesystems where atime tracking is not available.
So it additionally might occur that a session data file is deleted while the session itself is still considered as valid because the session data was not updated recently.
session.cookie_lifetime
session.cookie_lifetime specifies the lifetime of the cookie in seconds which is sent to the browser. [�]
Yes, that’s right. This only affects the cookie lifetime and the session itself may still be valid. But it’s the server’s task to invalidate a session, not the client. So this doesn’t help anything. In fact, having session.cookie_lifetime set to 0 would make the session�s cookie a real session cookie that is only valid until the browser is closed.
Conclusion / best solution:
The best solution is to implement a session timeout of your own. Use a simple time stamp that denotes the time of the last activity (i.e. request) and update it with every request:
if (isset($_SESSION['LAST_ACTIVITY']) && (time() - $_SESSION['LAST_ACTIVITY'] > 1800)) < // last request was more than 30 minutes ago session_unset(); // unset $_SESSION variable for the run-time session_destroy(); // destroy session data in storage >$_SESSION['LAST_ACTIVITY'] = time(); // update last activity time stamp
Updating the session data with every request also changes the session file’s modification date so that the session is not removed by the garbage collector prematurely.
You can also use an additional time stamp to regenerate the session ID periodically to avoid attacks on sessions like session fixation:
if (!isset($_SESSION['CREATED'])) < $_SESSION['CREATED'] = time(); >else if (time() - $_SESSION['CREATED'] > 1800) < // session started more than 30 minutes ago session_regenerate_id(true); // change session ID for the current session and invalidate old session ID $_SESSION['CREATED'] = time(); // update creation time >
- session.gc_maxlifetime should be at least equal to the lifetime of this custom expiration handler (1800 in this example);
- if you want to expire the session after 30 minutes of activity instead of after 30 minutes since start, you’ll also need to use setcookie with an expire of time()+60*30 to keep the session cookie active.
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook
mysqli_store_result
Transfers the result set from the last query on the database connection represented by the mysql parameter to be used with the mysqli_data_seek() function.
Parameters
Procedural style only: A mysqli object returned by mysqli_connect() or mysqli_init()
The option that you want to set. As of PHP 8.1, this parameter has no effect. It can be one of the following values:
| Name | Description |
|---|---|
| MYSQLI_STORE_RESULT_COPY_DATA | Copy results from the internal mysqlnd buffer into the PHP variables fetched. By default, mysqlnd will use a reference logic to avoid copying and duplicating results held in memory. For certain result sets, for example, result sets with many small rows, the copy approach can reduce the overall memory usage because PHP variables holding results may be released earlier (available with mysqlnd only) |
Return Values
Returns a buffered result object or false if an error occurred.
Note:
mysqli_store_result() returns false in case the query didn’t return a result set (if the query was, for example an INSERT statement). This function also returns false if the reading of the result set failed. You can check if you have got an error by checking if mysqli_error() doesn’t return an empty string, if mysqli_errno() returns a non zero value, or if mysqli_field_count() returns a non zero value. Also possible reason for this function returning false after successful call to mysqli_query() can be too large result set (memory for it cannot be allocated). If mysqli_field_count() returns a non-zero value, the statement should have produced a non-empty result set.
Errors/Exceptions
If mysqli error reporting is enabled ( MYSQLI_REPORT_ERROR ) and the requested operation fails, a warning is generated. If, in addition, the mode is set to MYSQLI_REPORT_STRICT , a mysqli_sql_exception is thrown instead.
Examples
Notes
Note:
Although it is always good practice to free the memory used by the result of a query using the mysqli_free_result() function, when transferring large result sets using the mysqli_store_result() this becomes particularly important.
See Also
User Contributed Notes 5 notes
After reading through original notes and example above as well as wading through the documentation, I finally got a loop to work with two stored procedures.
Using the results of the first one as a parameter for the second one. Easier to do this way than a huge modified sequence of Inner Join queries.
// Connect to server and database
$mysqli = new mysqli ( » $dbServer » , » $dbUser » , » $dbPass » , » $dbName » );
// Open First Stored Procedure using MYSQLI_STORE_RESULT to retain for looping
$resultPicks = $mysqli -> query ( «CALL $proc ( $searchDate , $maxRSI , $incRSI , $minMACD , $minVol , $minTrades , $minClose , $maxClose )» , MYSQLI_STORE_RESULT );
// process one row at a time from first SP
while( $picksRow = $resultPicks -> fetch_array ( MYSQLI_ASSOC )) // Get Parameter for next SP
$symbol = $picksRow [ ‘Symbol’ ];
// Free stored results
clearStoredResults ( $mysqli );
// Execute second SP using value from first as a parameter (MYSQLI_USE_RESULT and free result right away)
$resultData = $mysqli -> query ( «CALL prcGetLastMACDDatesBelowZero(‘ $symbol ‘, $searchDate )» , MYSQLI_USE_RESULT );
$dataRow = $resultData -> fetch_array ( MYSQLI_ASSOC );
// Dump result from both related queries
echo »
$symbol . Num Dates: » . $dataRow [ ‘NumDates’ ];
// Free results from second SP
$resultData -> free ();
// Free results from first SP
$resultPicks -> free ();
// close connections
$mysqli -> close ();
What Does mysqli_store_result() Actually Do?
The Question is Where it transfers the result set? Actually I was getting the error «Commands out of sync; you can’t run this command now» after executing mysqli_multi_query But When I used the following method, the Error gone.
mysqli_multi_query($connection,$query); do < mysqli_store_result($connection); >while(mysqli_next_result($connection)); Now, Should I use this mysqli_store_result($connection) and mysqli_next_result($connection) after each mysqli_query or just after mysqli_multi_query Because I have read in PHP Manaul that
«Although it is always good practice to free the memory used by the result of a query using the mysqli_free_result() function, when transferring large result sets using the mysqli_store_result() this becomes particularly important.»
Source: PHP: mysqli_store_result One More Question Arises When I executed the above mentioned mysqli_multi_query($connection,$query); I put a statement echo ‘storing result
‘ like below
do < echo 'storing result
mysqli_store_result($connection); > while(mysqli_next_result($connection)); storing result storing result storing result storing result It means there were four result sets that were transferred. I can’t understand this situation. One Last Question. Does the above mentioned do while process will effect the performance?