sql is works in phpmyadmin but not working in php with special case

gratsami · December 7, 2014

Hi all,

I'v table called vehicles with
id (int auto_increment) and
plate_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

Barand · 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?"

Barand · 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>

hansford · December 7, 2014

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

gratsami · 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?

gratsami · 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,

mac_gyver · 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.

Zane · 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;

Sign In

sql is works in phpmyadmin but not working in php with special case

Recommended Posts

gratsami

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

hansford

Link to comment

Share on other sites

gratsami

Link to comment

Share on other sites

gratsami

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

Zane

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information