Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4

Thread: MysqlI gotcha..

  1. #1
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,474
    Thanks
    63
    Thanked 537 Times in 524 Posts

    MysqlI gotcha..

    Heads up to those who are switching over to mysqli..

    I've been working on code for a site using this recently. Now in the old true to form style of mysql, I'd been looping through the resultset for my rows and values.

    In mysqli you can do this with mysql_stmt_get_result().. but there is a massive gotcha. You MUST have the mysqlND (mysql native driver) installed.

    If you do not (and this is what I've tripped on this week) you have two options:
    1) Use mysqli_query like the old mysql and use real_escape_string() - rather pointless IMO as you're not really gaining anything over mysql

    2) Bind variables to the retuned values. This has a massive problem though. If you have a table with 25 columns, you've got 25 variables and variable names to come up with and check they're not in use elsewhere in your script. In essence, you're being forced to declare new variables which could overwrite other vars in large code files.

    So, here's a tip for y'all, check (using phpinfo() ) that your target server has mysqlnd installed before writing anymore code that relies on a resultset. If you don't have it, you can't use prepared statements AND a resultset.
    I can't really think of anything to write here now...

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,146
    Thanks
    2
    Thanked 333 Times in 325 Posts
    You can bind the result to an array variable. The following checks if the query is a type that produces a result (even if it contains zero rows), dynamically gets the column/field names and binds an array with one element for each column, fetches the data into the bound array, then, because of the referencing, copies each column value into a final array -

    Code:
    				// query ran without any errors, if a result set type of query, dynamically bind the result
    				if($meta = $stmt->result_metadata()){
    					// result set query - select/show/explain
    					echo 'a result set type of query';
    					$variables = array(); // the 'bind_result' parameters
    					$data = array(); // array to reference to hold the actual fetched data
    					while($field = $meta->fetch_field()){
    						$variables[] = &$data[$field->name]; // parameters to 'bind_result' are references to the data array elements
    					}
           
    					if(!call_user_func_array(array($stmt, 'bind_result'), $variables)){
    						echo "binding results failed: (" . $stmt->errno . ") " . $stmt->error;
    						return false;
    					}      
    
    					$rows = array();
    					$i=0;
    					while($stmt->fetch()){
    						$rows[$i] = array();
    						foreach($data as $k=>$v){
    							$rows[$i][$k] = $v; // you must specifically access the key/value (otherwise you get a reference to the element in $data and they are all the last value fetched)
    						}
    						$i++;
    					}
    					//echo '<pre>',print_r($rows,true),'</pre>';
    					return $rows; // this is part of a general purpose prepared query function
    				}
    This why PDO is becoming so popular.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    tangoforce (11-10-2013)

  • #3
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,146
    Thanks
    2
    Thanked 333 Times in 325 Posts
    edit: somehow the forum double posted the above...
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #4
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,474
    Thanks
    63
    Thanked 537 Times in 524 Posts
    Ignore me.. I got it working.

    Not a bad bit of code once it's working.. it would of course help if I'd remembered to use mysqli_stmt_execute()
    Last edited by tangoforce; 11-10-2013 at 01:39 AM.
    I can't really think of anything to write here now...


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •