Edit report at http://bugs.php.net/bug.php?id=52196&edit=1
ID: 52196 Updated by: and...@php.net Reported by: lanpioneer at 126 dot com Summary: MysqlSTMT could not effectively support dynamic SQL with Prepare Statement -Status: Assigned +Status: Feedback Type: Bug Package: MySQL related Operating System: Linux PHP Version: 5.3.2 Assigned To: mysql Previous Comments: ------------------------------------------------------------------------ [2010-07-06 13:20:29] and...@php.net Hi, I needed to do some changes to the attached script. MySQL knows utf8 but not utf-8. Because I use CLI for testing I added some \n-s in the printfs. Also, `set @count` is not a valid SQL statement, returns an error, should be `set @count=NULL`. After these changes, here is my output on the console (it seems right?): > php failedpagedrecoreds.php.patch.txt <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>title</title> </head> <body> <table border="1"> <tr> <td>Name</td> <td>Size</td> <td>Desp</td> </tr> <li>name=BoostWEB</li> <li>name=Clever Terminal</li> <li>name=ä¸æ论å</li> <li>name=WinGate</li> <li>name=Copernic 99</li> <tr><td>BoostWEB</td><td>304KB</td><td>æä½³åCache,æµè§ç½é¡µé度快</td></tr> <tr><td>Clever Terminal</td><td>655KB</td><td>é常好çTelNet软件</td></tr> <tr><td>ä¸æ论å</td><td>655KB</td><td>建ç«BBSç好软件</td></tr> <tr><td>WinGate</td><td>2523KB</td><td>å¤å°çµèå ±ç¨ä¸ä¸ªMODENä¸ç½ç软件</td></tr> <tr><td>Copernic 99</td><td>2404KB</td><td>Searchå·¥å ·</td></tr> </table> </body> </html> ------------------------------------------------------------------------ [2010-06-28 14:33:54] lanpioneer at 126 dot com I used mysqlnd and Linux version is Linux version 2.6.9-78.ELsmp (brewbuil...@hs20-bc2-3.build.redhat.com) Also, i tried to test it on windows but it still like that. ------------------------------------------------------------------------ [2010-06-28 12:57:33] and...@php.net Hi, can you give us more data: - Do you use mysqlnd or libmysql? (--with-mysqli=mysqlnd or --with-mysqli) - What is the server version? - Can you provide us with an SQL dump which includes the some data in the `software` table, as well as the SP you have already provided - And then a minimal script, which shows the problem. Thank you! Andrey ------------------------------------------------------------------------ [2010-06-27 06:45:05] lanpioneer at 126 dot com Description: ------------ Hi, I found this problem but i could not find any suggestion in google, so I think that was probable a bug in Mysqli_STMT: first,I create a dynamic SQL Procedure example: CREATE UP_Get_PagedSoftware( IN VI_PageSize INT, IN VI_PageNow INT, OUT OV_ROWS INT ) BEGIN DECLARE UV_BeginRow INT DEFAULT 0; DECLARE UV_dynamicSQL VARCHAR(1000); SET UV_BeginRow = (VI_PageNow-1)*VI_PageSize; SELECT COUNT(id) INTO OV_ROWS FROM software; SET UV_dynamicSQL = CONCAT_WS(' ','SELECT Name,Size,Desp FROM software LIMIT',UV_BeginRow,',',VI_PageSize); SET @dynamicSQL = UV_dynamicSQL; PREPARE pager_stmt FROM @dynamicSQL; EXECUTE pager_stmt; DEALLOCATE PREPARE pager_stmt; END I directly called this procedure in Mysql Command Line that was OK, But I called this procedure in PHP page, the code is below: $softlist = array(); if($this->link){ $this->link->query("set names 'utf8'"); $this->link->query("SET @count"); $stmt = $this->link->stmt_init(); $stmt = $this->link->prepare('CALL UP_Get_PagedSoftware(?,?,@count)'); if($stmt){ $stmt->bind_param('ii',$this->pagesize,$currentpage); $stmt->execute(); $stmt->store_result(); if($this->link->more_results()){ $this->link->next_result(); $rs = $this->link->query('SELECT @count'); list($count) = $rs->fetch_array(MYSQLI_NUM); $this->pagecount=(int)$count; $rs->free(); } $stmt->bind_result($name,$size,$desp); while($stmt->fetch()){ $softlist[]=array($name,$size,$desp); } $stmt->free_result(); $stmt->close(); } } return $softlist; just like this, but the recoreds returned look like this:'ostWEB304KB$æä½³åCache,æµè§ç½é¡µé度快Clever Terminal655KBé常好çTelNet软件 ä¸æ论å' it seemed the Mysqli_STMT returned all records according to start and end index and allocate into one filed ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/bug.php?id=52196&edit=1