0

When i try to search for a available id with numbers, it echos correctly.

HOWEVER, if there is a single letter inside, like this: 5325252T, It wont find it in the database. I have a column with type: longtext

How can I get around this? I never noticed this problem before and now I'm in a hurry to fix it...

Btw, If i echo all the tables for rusp_9_cf7dbplugin_submits, it also shows those ids with letters inside. Really weird.

    // Create connection
$conn = new mysqli($hostname, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT field_value FROM rusp_9_cf7dbplugin_submits WHERE field_value = 5325252T"; // If i remove the T, It will find the id and echo it in a table, but if the T is there, it wont find the id at all...
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table><tr><th>ID</th><th>Name</th></tr>";
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>".$row["field_value"]."</td><td>".$row["field_value"]." ".$row["field_value"]."</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}
$conn->close();
Adam Norbäcker
  • 91
  • 1
  • 10
  • 1
    *field_value = 5325252T* -> **field_value = '5325252T'** ? – Alex Oct 18 '16 at 15:17
  • 2
    @Alex I think you mean `"... field_value = '5325252T' "` – Juan Carlos Oropeza Oct 18 '16 at 15:18
  • @JuanCarlosOropeza since somebody upvoted your comment I did change, but I see no difference http://sqlfiddle.com/#!9/fe686/4 Could you give any explanation why single quote is better than double quotes? – Alex Oct 18 '16 at 15:22
  • It's correct, Thanks guys :) – Adam Norbäcker Oct 18 '16 at 15:23
  • Sorry @Alex not sure sure why `"` work maybe they are equivalent in MySql, My reaction is because in Postgres `"Name"` mean a field name where `'something'` mean is a string, so I always use `'something'` notation. – Juan Carlos Oropeza Oct 18 '16 at 15:28
  • @Alex check this out http://stackoverflow.com/questions/3446216/what-is-the-difference-between-single-quoted-and-double-quoted-strings-in-php – Adam Norbäcker Oct 18 '16 at 15:39
  • @AdamNorbäcker I know difference between single and double quotes in php. I did ask why Juan recommended to change double to single in mysql query. and finally - there is no difference. – Alex Oct 18 '16 at 15:52
  • @Alex add this line `SET SQL_MODE=ANSI_QUOTES;` to your fiddle for example – Your Common Sense Oct 18 '16 at 15:58

1 Answers1

1

Just enclose the field_value value in single inverted commas ' since adding a character makes the SQL engine interpret that value as a number where as it is a string literal, whereas if its just numbers then it interprets it as an integer.

Your code becomes...

...
$sql = "SELECT field_value FROM rusp_9_cf7dbplugin_submits WHERE field_value = '5325252T'"; // If i remove the T, It will find the id and echo it in a table, but if the T is there, it wont find the id at all...
...
Mohd Abdul Mujib
  • 13,071
  • 8
  • 64
  • 88