PHP Ajax MySQL Problem.

DNG · 07-22-2010, 11:07 AM

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

Code:

<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

Code:

<?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

Code:

-- 
-- 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=MyISAM 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*** · 07-22-2010, 11:13 AM

When posting PHP code, please use the board's [php]..[/php] 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 mysql_query() returns boolean FALSE for a SELECT query, then you need to examine the SQL error message (using mysql_error(), 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 mysql_real_escape_string() (for string data).

paulnaj · 07-22-2010, 11:14 AM

Given that the ajax call is:

Code:

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

... in getuser.php you should have:

PHP Code:


			
$q=$_GET["q"];

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

DNG · 07-22-2010, 11:41 AM

Quote:

Originally Posted by paulnaj

Given that the ajax call is:

Code:

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

... in getuser.php you should have:

PHP Code:


			
$q=$_GET["q"];

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

That still did not fix the problem...

paulnaj · 07-22-2010, 11:46 AM

Did you try what bradgrafelman suggested?