mysql_fetch_array error

jawshoouh · #1 (**permalink**) 06-03-09, 16:41

Hello everyone!

This is my first post here...and I'm not sure if this should be going into a PHP subforum or an SQL subforum, but I'm pretty sure it's a PHP error...anyway:

I've been following the tutorial on this site: PHP Example AJAX and MySQL

I keep getting this error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/content/j/a/w/jawshoouh/html/vad/getuser.php on line 31

From a test.html page, here:

Code:

<head>
<title>test</title>
<script type="text/javascript" src="selectuser.js"></script>
</head>
<body>

<form>
<span class="headingText">Select a Name:</span>
<select name="Names" class="formText" onchange="showUser(this.value)">
  <option value="1">1</option>
  <option value="2">2</option>
  <option value="3">3</option>
</select>
</form>
<br />
<div class="contentText" id="vadText">Info will be listed here.</div>

</body>

And here's the selectuser.js file:

Code:

var xmlhttp;

function showUser(str)
{
xmlhttp=GetXmlHttpObject();
if (xmlhttp==null)
  {
  alert ("Browser does not support HTTP Request");
  return;
  }
var url="getuser.php";
url=url+"?q="+str;
url=url+"&sid="+Math.random();
xmlhttp.onreadystatechange=stateChanged;
xmlhttp.open("GET",url,true);
xmlhttp.send(null);
}

function stateChanged()
{
if (xmlhttp.readyState==4)
{
document.getElementById("vadText").innerHTML=xmlhttp.responseText;
}
}

function GetXmlHttpObject()
{
if (window.XMLHttpRequest)
  {
  // code for IE7+, Firefox, Chrome, Opera, Safari
  return new XMLHttpRequest();
  }
if (window.ActiveXObject)
  {
  // code for IE6, IE5
  return new ActiveXObject("Microsoft.XMLHTTP");
  }
return null;
}

And the getuser.php file:

Code:

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

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

mysql_select_db("listing", $con);

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

$result = mysql_query($sql);

echo "<table border='1'>
<tr>
<th>Name</th>
<th>RunBy</th>
<th>Director</th>
<th>Address</th>
<th>City</th>
<th>District</th>
<th>Province</th>
<th>Telephone</th>
<th>LatLong</th>
<th>GeoNotes</th>
<th>Comments</th>
</tr>";

while($row = mysql_fetch_array($result))              <<<< LINE 31
  {
  echo "<tr>";
  echo "<td>" . $row['Name'] . "</td>";
  echo "<td>" . $row['RunBy'] . "</td>";
  echo "<td>" . $row['Director'] . "</td>";
  echo "<td>" . $row['Address'] . "</td>";
  echo "<td>" . $row['City'] . "</td>";
  echo "<td>" . $row['District'] . "</td>";
  echo "<td>" . $row['Province'] . "</td>";
  echo "<td>" . $row['Telephone'] . "</td>";
  echo "<td>" . $row['LatLong'] . "</td>";
  echo "<td>" . $row['GeoNotes'] . "</td>";
  echo "<td>" . $row['Comments'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

And ideas?
Thanks,
Josh

mike_bike_kite · #2 (**permalink**) 06-03-09, 17:11

You shouldn't put your username and password into your example and there's a better way of writing the initial mysql connection stuff:

Code:

$con = mysql_pconnect('localhost', '***', '****')
       or die( "Could not connect: " . mysql_error());
mysql_select_db("listing", $con) 
       or die( "Could not find database: " . mysql_error());

Probably won't fix your issue (unless connecting to the db was at fault) but it's a better and faster way of writing it.

jawshoouh · #3 (**permalink**) 06-03-09, 17:32

Thanks mike_bike_kite...

Those were bogus usernames/passwords, I'm a total n00b at this, but not THAT much of one LOL!

And I can connect to the DB fine...I was able to via the SQL console in myphpadmin.

jawshoouh · #4 (**permalink**) 06-03-09, 17:35

BTW just tried your consolidation suggestion and got this:

Parse error: parse error, unexpected T_LOGICAL_OR in /home/content/j/a/w/jawshoouh/html/vad/getuser.php on line 5

mike_bike_kite · #5 (**permalink**) 06-03-09, 18:43

Quote:

Originally Posted by jawshoouh

And I can connect to the DB fine...I was able to via the SQL console in myphpadmin.

Your code doesn't check that it;s connecting to the database so it could be failing their.

Sorry if misstyped something in code - google the correct syntax but it is the right way to do it. Might not be the solution to your problem though.

I'd suggest just righting a noddy php program that connects to the database and selects some simple data value out. Then include the select statement you gave above and pull that data out using just the barest of the code. Ignore all the other stuff as it simply confuses the matter. If things work then start adding the other bits back in until it fails - you've found you're problem.

jawshoouh · #6 (**permalink**) 06-04-09, 02:14

I took out the echo command that returned verification that connection to the database and to the table within the DB...both appeared to be fine.

But I'll start where you suggested...thanks again, mike_bike_kite!

jawshoouh · #7 (**permalink**) 06-04-09, 04:31

OK there's definitely something hanky going on, maybe it's my host? From that tutorial above, I created a test table in my database called "test", created three new documents just to test out their example, and I still get the aforementioned error.

test.html document:

Code:

<html>
<head>
<script type="text/javascript" src="selectuser.js"></script>
</head>
<body>

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

</body>
</html>

selectuser.js document:

Code:

var xmlhttp;

function showUser(str)
{
xmlhttp=GetXmlHttpObject();
if (xmlhttp==null)
  {
  alert ("Browser does not support HTTP Request");
  return;
  }
var url="getuser.php";
url=url+"?q="+str;
url=url+"&sid="+Math.random();
xmlhttp.onreadystatechange=stateChanged;
xmlhttp.open("GET",url,true);
xmlhttp.send(null);
}

function stateChanged()
{
if (xmlhttp.readyState==4)
{
document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
}
}

function GetXmlHttpObject()
{
if (window.XMLHttpRequest)
  {
  // code for IE7+, Firefox, Chrome, Opera, Safari
  return new XMLHttpRequest();
  }
if (window.ActiveXObject)
  {
  // code for IE6, IE5
  return new ActiveXObject("Microsoft.XMLHTTP");
  }
return null;
}

getuser.php file:

PHP Code:


			
<?php

$q=$_GET["q"];



$con = mysql_connect('localhost', 'dbusername', 'dbpassword');

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }



mysql_select_db("test", $con);



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



$result = mysql_query($sql);



echo "<table border='1'>

<tr>

<th>Firstname</th>

<th>Lastname</th>

<th>Age</th>

<th>Hometown</th>

<th>Job</th>

</tr>";



while($row = mysql_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>";



mysql_close($con);

?>

Loaded test.html, selected a name from the dropdown menu, and got it displays a table with two rows, top one is the headings, bottom is blank, but first entry in the first cell is Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/content/j/a/w/jawshoouh/html/vad/getuser1.php on line 25.

And then I decided to just do a connect.php file to verify that I was at least connecting:

PHP Code:


			
<?php

mysql_connect('hostname', 'DBNAME', 'DBPASS') or die(mysql_error());

echo "Connected to MySQL<br />";

mysql_select_db("listing") or die(mysql_error());

echo "Connected to Database";

?>

And I get this:

Code:

Connected to MySQL
Access denied for user 'DBNAME'@'%' to database 'listing'

jawshoouh · #8 (**permalink**) 06-04-09, 04:34

On that last part, I've been Googling GRANT permissions but I don't have root access...and this is giving me a headache LOL! Is that something I have to take up with my host?

mike_bike_kite · #9 (**permalink**) 06-04-09, 04:50

So it was a problem connecting to the database

You should use the or die( "" ) on all your mysql commands. At the moment it's missing when you run the select statement to. When it comes to testing it's not a bad idea to print out the sql and try it by connecting to the database directly and simply running it by hand. That way you get a clearer idea of where the problem lies.

You could first try connecting using the same user and password that you used to create the database. Then read this page for full info on accounts etc.

Mike

jawshoouh · #10 (**permalink**) 06-04-09, 12:35

Thanks Mike...I'm assuming I need to create a trouble ticket with my host, as they don't grant me any sort of shell access. And I'll fiddle with the code again and start adding the die( "" ) commands.

mike_bike_kite · #11 (**permalink**) 06-04-09, 12:52

I'm not a DBA (in fact I know nothing at all about such stuff) but I'd of thought you could use the same user that created the database in the first place. Does the phpadmin (?) tool have a wizard or something for adding privileges?

jawshoouh · #12 (**permalink**) 06-04-09, 13:41

I thought so, too, but Googling that error generates a lot of "answers" stating that the privileges aren't correct, and that a GRANT permission needs to be used. I know nothing about it, either...in fact, this is the first time I've ever tried to make a site using MySQL and PHP LOL! And the myphpadmin site I use with my databases has nothing for editing privileges for any database users whatsoever...I think that some hosts give you that control, and some keep it...mine apparently keeps it.

mike_bike_kite · #13 (**permalink**) 06-04-09, 14:29

Log in to mysql as the user that created the database. Then at the mysql prompt type:

GRANT ALL ON listing.* to DBUN@localhost ;

I got this info from here

jawshoouh · #14 (**permalink**) 06-04-09, 15:36

I'll give it a go later, I'm at work and can't do it behind our firewall.

jawshoouh · #15 (**permalink**) 06-05-09, 04:08

OK so my host got back to me...it was a connection syntax issue...had to do it like this for this host:

PHP Code:


			
$hostname="hostname";
$username="username";
$password="password";
$dbname="dbname";
$usertable="tablename";

mysql_connect($hostname,$username, $password) or die ("<html><script language='JavaScript'>alert('Unable to connect to database! Please try again later.'),history.go(-1)</script></html>");
mysql_select_db($dbname);

Thanks for the help and suggestions, Mike!

Internet Services

Web Development

Digital Marketing

Consumer Tech