geno11x11

Update: I commented out all the prepared statement lines and substituted a legacy query and all search results are working correctly. That leads me to believe the problem may lie in the prepared statement query -- perhaps the CONCAT function is causing it. So I am open to all thoughts and suggestions on how to fix the unpredictable prepared statement query quandry...

I pasted your routine starting with the for loop to the last curly bracket into my code -- The search string remained empty until I entered three characters or larger (the logical result of the substr($nameSearch, 2, $i) statement). Results "Failed" through all iterations. Then var_dump($variables) displayed a large array of values.

The $nameSearch value comes from operator input - an input form. The only validation was to limit the field length. There are no extra characters in $nameSearch, but there is definitely white space within the queried field "Name". Since the legacy query using MySQL Workbench worked without any special processing, I assumed it would work the same with PHP. What do you suggest, and please provide a code example of validation/filtering/trimming.

The references should have been removed (since corrected) - that was from a code example found online. Although it worked, I later learned more about references and that they are no longer needed. I follow everything except the following snippet: for ($i = 1; $i < 12; $i++) { $strSearchText = substr($nameSearch, 2, $i); echo 'Searching for "' . $strSearchText . '"<br/>'; $stmt = $mysqli->prepare($sql); if (false === $stmt) { die('Query Prep Failed' . $mysqli->error . $mysqli->errno); } Please explain the count to 12 and the function of this routine.

I realize this is more elaborate and error prone than it had to be, but I already had the code written with prepared statements and I didn't want to start new with legacy queries. Let me know if this is not sufficient. Thanks, GK $nameSearch = "SomethingInYourDatabase-setupforString"; $mysqli = new mysqli($serverName, $userName, $password, $database) or die('There was a problem connecting to the database'); if ($mysqli->connect_errno) {echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;} $sql="SELECT * FROM Table WHERE Name LIKE CONCAT('%',?,'%')" $stmt = $mysqli->prepare($sql); if (false === $stmt) {die('Query Prep Failed' . $mysqli->error . $mysqli->errno);} $stmt->bind_param('s',$nameSearch); //string $stmt->execute(); if (false===$stmt) {die('execute() failed: '.$stmt->error);} $stmt->store_result(); $rowCount = $stmt->num_rows;echo "<b>Rowcount: </b>",$rowCount; $fieldList = array(); $meta = $stmt->result_metadata(); print_r($meta); while ( $field = $meta->fetch_field() ) { $fieldList[] = $field->name; } $x=call_user_func_array(array($stmt, 'bind_result'), &$fieldList); if (false === $x) {die('bind_param()error :' . $mysqli->error);} while ( $stmt->fetch() ) { $assocRow=$library->buildAssocRow(&$mysqli,&$stmt,&$fieldList,&$colNames); } var_dump($assocRow);

character_set_cleint = utf8 character_set_database = utf8 collationt_database = utf8_unicode_ci

Running more searches, I have found that in one case, a 4-character name has some peculiar search characteristics: The name is gbks: It will appear with a search of any matching single letter, and a 2-character ks, No other 2, 3, or 4 character combination works. An 8-character name can be searched as far as 7-characters before it fails -- the more characters used the narrower the filter so fewer other records appear.. Very strange... any ideas?

I originally started there and it returned no records -- some research turned up the CONCAT() expression which got me to where I am so far... But I tried your suggestion nevertheless, and no records were returned -- I also checked the php online manual for the proper syntax and, although your format is shown as an example, it does not work with my code. No errors thrown, just no results...

Hello all, I have the following working query: $sql="SELECT * FROM Table WHERE Name LIKE CONCAT('%',?,'%')" $library->connectDB(&$mysqli,&$stmt); if ($mysqli->connect_errno) {echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;} $stmt = $mysqli->prepare($sql); if (false === $stmt) {die('Query Prep Failed' . $mysqli->error . $mysqli->errno);} $stmt->bind_param('s',$nameSearch); //string $stmt->execute(); if (false===$stmt) {die('execute() failed: '.$stmt->error);} $stmt->store_result(); If I am looking for a value such as "Flashlight" I can search with partial matching characters (wildcards) for "flash", "light" or any matching character such as "F", "f", "L", etc. and get the desired results. My problem arises when I search with the entire word: Using "Flashlight" or "flashlight" (i.e., $nameSearch = "Flashlight"), the query returns zero records. MySQL documentation says this should work. What is wrong with the query or the code? Additionally, using mySQL Workbench, I had success with the following query (can't get prepared statements to work with this utility): SELECT Name FROM Table WHERE Name LIKE '%flashlight%';

kicken, Your explanation and code example just solved my problem. I did not realize that the data type string must be part of the bind_parameter array for call_user_func_array(). I thought the data types were passed as a string outside of the array, which is why it threw the error. I used array_unshift() to add the string and it flew! The sample data was properly UPDATED to the database, and all is well! I will apply the change to my ADD/DELETE modules and the last weeks of frustration will have come to a close. My sincere thanks to you and mac_gyver for your thoughtful analysis and suggestions.

mac_gyver, Thanks so much for clearing up the issue of result_bindings for written data. There is a huge amount of conflicting information out there... I was following an online example (grossly in error) which showed result bindings for UPDATE, but noticed multiple comments elsewhere stating that bindings aren't used -- Now things make more sense, and I think the proverbial light is shining a bit brighter! Answering your question about call_user_func_array, I pared down the code to simplify the snippet as much as possible. I threw a few static values in there but the real code is dynamic and the fields used will vary. In the context of call_user_func_array and their related variations, can you give an explanation what callbacks do and how they make the field list variable? It is working for my SELECT query, but I copied an online example and have no idea what it does or how it works. Thanks,

I am working on converting legacy queries to mysqli prepared statements. My SELECT queries are working fine, but ADD/DELETE/UPDATE are giving me problems. I have found many online examples showing bind statements for ADD/DELETE/SELECT, including the use of call_user_func_array() but others claim that bindings are not necessary when writing records (as opposed to reading them). So I am confused about this issue, and when I use bind statements I get the following error message: Warning: mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement in /srv/www/htdocs/PNS/Complex/writeRec5.php on line 177 bind_param()error : The following are snippets of code using dummy values to simplify the code for testing along with output. I reaIize that call_user_func_array() is not necessary in this simple example, but I will need it for the project. I don't see the error, and I have compared it to many online forum examples which claim to be functioning. Does anything stand out? $prepareString = "Address=?,Permit_Rate=?"; echo "<b>PrepareString: </b>",$prepareString,"<br /><br />"; $Address="123 Somewhere Street";$Permit_Rate=25; $sql="UPDATE table1 SET " . $prepareString . " WHERE Key_ID = " . $keyid; echo $sql,"<br /><br />"; Output: PrepareString: Address=?,Permit_Rate=? UPDATE table1 SET Address=?,Permit_Rate=? WHERE Key_ID = 2664 $stmt = $mysqli->prepare($sql); if (false === $stmt) {die('Query Prep Failed' . $mysqli->error);} $dataTypes="si";$bindParams=array(&$Address,&$Permit_Rate); echo "<b>Length of dataTypes: </b> ",strlen($dataTypes),": <b>Contents of dataTypes: </b>",$dataTypes,"<br />"; echo "<b>Count of bindParams: </b>",count($bindParams)," <b>Contents of bindParams: </B>"; var_dump($bindParams); echo "<br /><br />"; Output: Length of dataTypes: 2: Contents of dataTypes: si Count of bindParams: 2 Contents of bindParams: array(2) { [0]=> &string(20) "123 Somewhere Street" [1]=> &int(25) } $x = call_user_func_array(array($stmt, 'bind_result'), &$bindParams); if (false === $x) {die('bind_param()error :' . $mysqli->error);} $stmt->execute();$stmt->close(); Output: Warning: mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement in /srv/www/htdocs/PNS/Complex/writeRec5.php on line 177 bind_param()error :

cpd: Ok, I've got the picture. I will start by modifying my original query and cutting out the unnecessary fields, although that will mean an additional query down the road; I wonder how it will net out. I expect there are algorithms that break out the efficiency of every processing step, but who has time for that? It might be a terrific add-on for an editor though - something of an efficiency grading or processing score at the end of a module. Without a definitive answer to the pause/stall question, the second step will be to eliminate the large array and revert back to pulling data from the database. Using prepared statements and substituting the bound parameters for the needed fields, processing efficiency is said to be high; I was impressed by the speed at which large volumes of data were processed. Finally, I'll dump the nested loops and return to recursiveIteratorIterator(). Based on the efficiency equations you provided, it appears that less is more -- I am guessing that the canned functions are not just easier to utilize, but are optimized for efficiency as well. Thanks for your time and advice!

cpd: Yes, I am using OOP. Please expand your answer to question 2. I am confused by two statements: Passing a mass data structure containing all your data definitely is not more efficient than retrieving the data you need, when you need it. Assuming you're using an object-oriented style, you should fetch your data and and create an object to contain the data, then pass the object around. As I read them, these statements seem to contradict. But perhaps your reference to "the mass data structure" means the array. If so, It implies there is something else (the object you referred to) available to hold mass data and I am unaware of it. A reference or code example would be very helpful.

I may have gotten carried away trying to make my project ultra efficient... Originally, I wrote my application to repeatedly grab the same data set from mySQL with each php module. The data is used in a variety of ways, so I get the full mySQL data record and then chop & process it within the php module rather than modifying the query over and over again. I thought it would be great to get data once, stuff it all into a multidimensional array, and transfer the array from module to module to increase speed (RAM is faster than drive access, right?) As I extracted full data sets with recursiveIteratorIterator(), I realized that once I obtained the needed data subset I could break the loop because the balance of the data was unneeded -- so I switched to nested foreach loops to grab data from the array and added a break when the data subset was extracted. So now I am on the third version of the same code and I am finding that var_dump($array) has a lengthy pause after about 150 records. When I display the data using echo or print_r, the nested foreach loops stop at about the point of the var_dump pause. The array size of my current query is 102KB. Questions: I like the idea of cutting the processing when the data search has done it's job before the end of record, but am I really saving time or resources? Is it really a better idea to create a large array and pass it between modules or is grabbing the data from mySQL (perhaps cached?) a better choice? What is causing the pause in var_dump? Am I running out of RAM, perhaps going to virtual ram on my computer? Is there a way to assign memory to the array? Why is the nested foreach commands stopping output prematurely? In terms of efficiency balanced by practicality, what is the best approach? Code - This version displays the full record without breaks: foreach ($rows as $key => $row) { foreach ($row as something => $else) { foreach ($else as $k => $V) { echo "row(",$key,") "," field[",$something,"] ",$k ,": ", $v, "<br/>"; } } }