BR
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
php code is
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>
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>
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."'";
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?
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:
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
All well expect this statement:
$q = intval($_GET['q']);
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
Yes PDO is safer
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you all
You are welcome.
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?
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
$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';
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 ;-)