Jump to content

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


gratsami

Recommended Posts

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

 

Link to comment
Share on other sites

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>
Link to comment
Share on other sites

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 by hansford
Link to comment
Share on other sites

 

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, 

Link to comment
Share on other sites

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;
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.