gratsami Posted December 7, 2014 Share Posted December 7, 2014 Hi all, I'v table called vehicles withid (int auto_increment) andplate_number (varchar 150). the plate_number have records like (a a a 1 1 1 1) (b b b 2 2 2 2), three chars and four numbers spliced by spaces. I'v php search page with url like this: http://localhost/vehicles/show.php?plate_number=f%20f%20f%205%205%205%205 search about plate_numbet = f f f 5 5 5 5 my php code: // pdo connection. // first sql test $sql = "SELECT * FROM vehicles WHERE plate_number LIKE '%".$_GET['plate_number']."%'"; // second sql test // $sql = "SELECT * FROM vehicles WHERE plate_number = '".$_GET['plate_number']."'"; $db->query($sql); if($db->rowcount() > 0){ // print all results... }else{ echo "There are no results."; } The query result is: There are no results.. But, when I copy the sql statements into phpmyadmin it is works fine. (there is a result). like this: SELECT * FROM vehicles WHERE plate_number LIKE '%f f f 5 5 5 5%'; OR SELECT * FROM vehicles WHERE plate_number = 'f f f 5 5 5 5'; Also i used string functions (htmlspecialchars, rawurldecode, ... ), still not work. any suggestion to solve this issue? Thanks to all Quote Link to comment https://forums.phpfreaks.com/topic/292944-sql-is-works-in-phpmyadmin-but-not-working-in-php-with-special-case/ Share on other sites More sharing options...
Barand Posted December 7, 2014 Share Posted December 7, 2014 the plate_number have records like (a a a 1 1 1 1) (b b b 2 2 2 2), three chars and four numbers spliced by spaces. The question that immediately springs to mind is "Why the hell would anyone do that?" Quote Link to comment https://forums.phpfreaks.com/topic/292944-sql-is-works-in-phpmyadmin-but-not-working-in-php-with-special-case/#findComment-1498845 Share on other sites More sharing options...
Barand Posted December 7, 2014 Share Posted December 7, 2014 Having just Googled "PDO rowCount" there appears to be issues on unbuffered result sets always returning a zero count. It certainly worked ok with mysqli $mysqli = new mysqli(HOST,USERNAME,PASSWORD,'test'); $plate = isset($_GET['plate']) ? $_GET['plate'] : ''; $sql = "SELECT * FROM vehicles WHERE platenumber = ?"; $stmt = $mysqli->prepare($sql); $stmt->bind_param('s', $plate); $stmt->execute(); $res = $stmt->get_result(); if ($res->num_rows) { $row = $res->fetch_row(); echo $row[0] . ' | ' . $row[1]; } else echo "No results"; ?> <html> <head> <style type='text/css'> td {text-align: center;} </style> </head> <body> <form> Plate <input type='text' name='plate' value=''> <input type='submit' name='btnSub' value='Submit'> </form> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/292944-sql-is-works-in-phpmyadmin-but-not-working-in-php-with-special-case/#findComment-1498846 Share on other sites More sharing options...
hansford Posted December 7, 2014 Share Posted December 7, 2014 (edited) I don't rely on rowcount() because it can be hit or miss depending on your database. It takes two queries, but at least you know if something was actually returned or not. The first query you get the count. SELECT COUNT(*) After executing this query you can use fetchColumn() to see if any results were returned. If so, then you can execute your regular query. Edited December 7, 2014 by hansford Quote Link to comment https://forums.phpfreaks.com/topic/292944-sql-is-works-in-phpmyadmin-but-not-working-in-php-with-special-case/#findComment-1498908 Share on other sites More sharing options...
gratsami Posted December 8, 2014 Author Share Posted December 8, 2014 The question that immediately springs to mind is "Why the hell would anyone do that?" Because this is the rule in my country for vehicles plate numbers (three chars and four numbers spliced by spaces). Do you have any solution? Quote Link to comment https://forums.phpfreaks.com/topic/292944-sql-is-works-in-phpmyadmin-but-not-working-in-php-with-special-case/#findComment-1498972 Share on other sites More sharing options...
gratsami Posted December 8, 2014 Author Share Posted December 8, 2014 Having just Googled "PDO rowCount" there appears to be issues on unbuffered result sets always returning a zero count. It certainly worked ok with mysqli $mysqli = new mysqli(HOST,USERNAME,PASSWORD,'test'); $plate = isset($_GET['plate']) ? $_GET['plate'] : ''; $sql = "SELECT * FROM vehicles WHERE platenumber = ?"; $stmt = $mysqli->prepare($sql); $stmt->bind_param('s', $plate); $stmt->execute(); $res = $stmt->get_result(); if ($res->num_rows) { $row = $res->fetch_row(); echo $row[0] . ' | ' . $row[1]; } else echo "No results"; ?> <html> <head> <style type='text/css'> td {text-align: center;} </style> </head> <body> <form> Plate <input type='text' name='plate' value=''> <input type='submit' name='btnSub' value='Submit'> </form> </body> </html> I don't rely on rowcount() because it can be hit or miss depending on your database. It takes two queries, but at least you know if something was actually returned or not. The first query you get the count. SELECT COUNT(*) After executing this query you can use fetchColumn() to see if any results were returned. If so, then you can execute your regular query. First of all, Thanks for answers... For (rowCount()) I'v class For PDO and (rowCount()) is a function inside the class. when i select all records without where query / or with another where query it is work fine in my php code. but searching about plate_number isn't retrieve the records in php code. it is give 0 result. mmm, I want to ask can i use function inside the sql statement ONLY like str_replace to convert it from (f f f 5 5 5 5) to (f-f-f-5-5-5-5) and do the search on this style? regards, Quote Link to comment https://forums.phpfreaks.com/topic/292944-sql-is-works-in-phpmyadmin-but-not-working-in-php-with-special-case/#findComment-1498973 Share on other sites More sharing options...
mac_gyver Posted December 8, 2014 Share Posted December 8, 2014 For (rowCount()) I'v class For PDO and (rowCount()) is a function inside the class. if by that you mean you are using a second class on top of the PDO class, the problem is likely in the code of that class. Quote Link to comment https://forums.phpfreaks.com/topic/292944-sql-is-works-in-phpmyadmin-but-not-working-in-php-with-special-case/#findComment-1498997 Share on other sites More sharing options...
Zane Posted December 8, 2014 Share Posted December 8, 2014 The question that immediately springs to mind is "Why the hell would anyone do that?" Yes, why? I would ditch the spaces, until you actually want to display the data.. otherwise, it's just a pain in the ass. You can query without the hassle of spaces SELECT * FROM vehicles WHERE plate_number LIKE '%f35%' To display the plate number with the spaces, just convert the string to an array and implode it by string. $str = implode( " ", str_split('fff3333') ); echo $str; Quote Link to comment https://forums.phpfreaks.com/topic/292944-sql-is-works-in-phpmyadmin-but-not-working-in-php-with-special-case/#findComment-1499000 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.