PHP Ajax MySQL Problem.

DNG

Hello,

I'm tring to create a lookup via PHP AJAX. I keep getting the following error message, can any1 help me please.

"
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /test/getuser.php on line 25
"
I have 2 files and 1 mysql database.
Index.html File

<html>
<head>
<script type="text/javascript">
function showUser(str)
{
if (str=="")
  {
  document.getElementById("txtHint").innerHTML="";
  return;
  } 
document.write(str);
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 (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","getuser.php?q="+str,true);
xmlhttp.send();
}

</script>
</head>
<body>

<form>
<select name="make" onchange="showUser(this.value)">
<option value="">Select a Vehicle:</option>
<option value="1">Acura</option>
<option value="2">Alfa Romeo</option>
<option value="3">Aston Martin</option>
<option value="4">Honda</option>
</select>
</form>
<br />
<div id="txtHint"><b>Vehicle info will be listed here.</b></div>

</body>
</html>

getuser.php file

<?php
$q=$_GET["make"];

$con = mysql_connect('localhost', 'username', 'password');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("database_name", $con);

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

$result = mysql_query($sql);

echo "<table border='1'>
<tr>
<th>Make</th>
<th>Model</th>
<th>Support</th>
<th>Comment</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['Make'] . "</td>";
  echo "<td>" . $row['Model'] . "</td>";
  echo "<td>" . $row['Support'] . "</td>";
  echo "<td>" . $row['Comment'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

MySQL Database File


-- 
-- Setup for table `vehicle`
-- 

CREATE TABLE `vehicle` (
  `Make` varchar(50) NOT NULL,
  `Model` varchar(50) NOT NULL,
  `Support` varchar(50) NOT NULL,
  `Comment` varchar(250) NOT NULL,
  PRIMARY KEY  (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `vehicle`
-- 

INSERT INTO `vehicle` VALUES (1, 'Acura', 'V1', '1', 'COMMENT1');
INSERT INTO `vehicle` VALUES (2, 'Acura', 'V2', '2', 'COMMENT1');
INSERT INTO `vehicle` VALUES (3, 'Acura', 'MDX 2005', '2', 'COMMENT1');
INSERT INTO `vehicle` VALUES (4, 'Acura', 'MDX 2007', '2', 'COMMENT1');
INSERT INTO `vehicle` VALUES (5, 'Acura', 'RSX', '2', 'COMMENT1');
INSERT INTO `vehicle` VALUES (6, 'Acura', 'TL -2007', '2', 'COMMENT1');
INSERT INTO `vehicle` VALUES (7, 'Acura', 'TL TYPE S 2007-', '2', 'COMMENT1');
INSERT INTO `vehicle` VALUES (8, 'Acura', 'TSX', '2', 'COMMENT1');
INSERT INTO `vehicle` VALUES (9, 'Alfa Romeo', '145 HC05', '2', 'COMMENT1');
INSERT INTO `vehicle` VALUES (10, 'Alfa Romeo', '147 93C86', '3', 'COMMENT1');
INSERT INTO `vehicle` VALUES (11, 'Aston Martin', 'VANQUISH', '3', 'COMMENT1');
INSERT INTO `vehicle` VALUES (12, 'Aston Martin', 'OTHER', '3', 'COMMENT1');

Can any1 tell me what I'm dong wrong.

Thanks
DNG

bradgrafelman

When posting PHP code, please use the board's [noparse]

..

[/noparse] bbcode tags as they make your code much easier to read and analyze.

As for your issue, you need to debug the SQL errors that are occurring. If [man]mysql_query/man returns boolean FALSE for a SELECT query, then you need to examine the SQL error message (using [man]mysql_error/man, for example) to find the cause of the problem.

Also, user-supplied data should never be placed directly into a SQL query string else your code will be vulnerable to SQL injection attacks and/or just plain SQL errors. Instead, you must first sanitize it with a function such as [man]mysql_real_escape_string/man (for string data).

paulnaj

Given that the ajax call is:

xmlhttp.open("GET","getuser.php?q="+str,true);

... in getuser.php you should have:

$q=$_GET["q"];
//... not $q=$_GET["make"];

DNG

paulnaj;10958778 wrote:
Given that the ajax call is:
xmlhttp.open("GET","getuser.php?q="+str,true);
... in getuser.php you should have:
$q=$_GET["q"];
//... not $q=$_GET["make"];

That still did not fix the problem...

paulnaj

Did you try what bradgrafelman suggested?

joperalez

in your index.html you have :
xmlhttp.open("GET","getuser.php?q="+str,true);

in your .php file you have:
$q=$_GET["make"];

If you want "make" to work, your going to have to change getuser.php?q="+str to getuser.php?make="+str

or follow paul's suggestion. It follows the same logic.

But not only that!

$sql="SELECT * FROM vehicle WHERE id = '".$q."'"; <--- where is your 'id' column?

this query won't work on that database. you will have to add it or change id to some other column you have in your vehicle table.