Link to home
Start Free TrialLog in
Avatar of BR
BRFlag for Türkiye

asked on

PHP - AJAX and MySQL it works only if the value is a number

Dear Experts, I use w3school sample code which works perfectly fine. However it only works if the value is a number.

it works like this <option value="1">Peter Griffin</option>
it doesn't work when you use it like <option value="Peter Griffin">Peter Griffin</option>

I modify the SQL for sure. it works onyl if the value is a number
I'd like to use this code with strings too.

w3school sample PHP - AJAX and MySQL code

<html>
<head>
<script>
function showUser(str) {
    if (str == "") {
        document.getElementById("txtHint").innerHTML = "";
        return;
    } else { 
        if (window.XMLHttpRequest) {
            // code for IE7+, Firefox, Chrome, Opera, Safari
            xmlhttp = new XMLHttpRequest();
        } else {
            // code for IE6, IE5
            xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
        }
        xmlhttp.onreadystatechange = function() {
            if (this.readyState == 4 && this.status == 200) {
                document.getElementById("txtHint").innerHTML = this.responseText;
            }
        };
        xmlhttp.open("GET","getuser.php?q="+str,true);
        xmlhttp.send();
    }
}
</script>
</head>
<body>

<form>
<select name="users" onchange="showUser(this.value)">
  <option value="">Select a person:</option>
  <option value="1">Peter Griffin</option>
  <option value="2">Lois Griffin</option>
  <option value="3">Joseph Swanson</option>
  <option value="4">Glenn Quagmire</option>
  </select>
</form>
<br>
<div id="txtHint"><b>Person info will be listed here...</b></div>

</body>
</html>

Open in new window


php code is

<!DOCTYPE html>
<html>
<head>
<style>
table {
    width: 100%;
    border-collapse: collapse;
}

table, td, th {
    border: 1px solid black;
    padding: 5px;
}

th {text-align: left;}
</style>
</head>
<body>

<?php
$q = intval($_GET['q']);

$con = mysqli_connect('localhost','peter','abc123','my_db');
if (!$con) {
    die('Could not connect: ' . mysqli_error($con));
}

mysqli_select_db($con,"ajax_demo");
$sql="SELECT * FROM user WHERE id = '".$q."'";
$result = mysqli_query($con,$sql);

echo "<table>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
<th>Hometown</th>
<th>Job</th>
</tr>";
while($row = mysqli_fetch_array($result)) {
    echo "<tr>";
    echo "<td>" . $row['FirstName'] . "</td>";
    echo "<td>" . $row['LastName'] . "</td>";
    echo "<td>" . $row['Age'] . "</td>";
    echo "<td>" . $row['Hometown'] . "</td>";
    echo "<td>" . $row['Job'] . "</td>";
    echo "</tr>";
}
echo "</table>";
mysqli_close($con);
?>
</body>
</html>

Open in new window

Avatar of David Jones
David Jones

What is that SQL row id defined as? Int most likely since it is an ID...

You are probably okay with something like this for learning and ease:
$sql="SELECT * FROM user WHERE id = $q";

Also.. You should pull specific's instead of everything (*) unless you specifically need everything, for example:

$sql="SELECT user.firstname, user.lastname FROM user WHERE id = $q";
or
$sql="SELECT u.firstname, u.lastname FROM user u WHERE id = $q";

Hope you're using PDO method to prepare your statements, doesn't appear to be, but gotta start somewhere to learn ;-)
Avatar of Ryan Chong
it doesn't work when you use it like <option value="Peter Griffin">Peter Griffin</option>

in your php code: getuser.php?q=xxxxxx

since your original code is querying for the id:

$sql="SELECT * FROM user WHERE id = '".$q."'";

then if you want to query based on the user's name, the change the id to the name field accordingly, like:

$sql="SELECT * FROM user WHERE user_fullname = '".$q."'";
Avatar of BR

ASKER

Dear David Jones,
I change the sql statement,
I select another column instead of id column, it works if the value is number, but it doesn't work if the value is like "Peter Parker"

By the way, I use mysqli method,
is PDO safer or advanced than mysqli method?

I mostly use SQL like $sql="SELECT * FROM user WHERE id = '$q';
My tables have not more than 3000 rows and I use where clause most of the time.

Do you recommend me to use always the column names?
Try this ;)

All well expect this statement:

$q = intval($_GET['q']);

Open in new window


When you are sending string value it trying to return int value hence in case of string input it returning 0

So, simply remove this intval function
Look at Ryan Chongs answer also, you could query based on the name if you want to use the string but right now you are querying against id which is most likely defined in your SQL server as an int.

Yes PDO is safer
Avatar of BR

ASKER

Dear Mukesh Yadav,
You are very good. That was the answer I was looking for.

But I think I use it with number value, I can add type column to my database

Because when I remove $q = intval($_GET['q']); it doesn't work with numbers.

if you have a better sample, could you please share with me?
SOLUTION
Avatar of Mukesh Yadav
Mukesh Yadav
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of BR

ASKER

thank you all
You are welcome.
Avatar of BR

ASKER

Dear Julian,
I'm working on your sample,
but I couldn't see the db.config.php file on your sample

I assume you have the database connection on that file, right?
The db.config.php file just stores the values for
$host
$user
$password
$database

These are specific to your environment and sensitive to mine so they are not included in the sample.

The file basically looks like this
<?php
$host = 'localhost';
$user = 'user';
$password = 'password';
$database = 'database';

Open in new window