Those of you who are new to PHP programming and are using MySQL may run into many problems in getting started. Of course most of us wish to gather some data from the database and view it in a browser. Some times we run into problems where this just won’t work. One of the most frequent problems I have seen is the “Not a valid result resource” error provided by MySQL. Thankfully I can tell you how to work through it to get what you want done.
Basically when you run a query on the MySQL server, the result is returned. That’s fairly obvious, right? So let’s look at some queries:
SELECT * FROM `table_name` ORDER BY column_title DESC;
SELECT column_title, column_title, column_title, column_title
FROM `table_name` ORDER BY column_title DESC;
SELECT column_title FROM `table_name` ORDER BY column_title DESC;
The first query will select ALL column data in the table referenced. The second will only grab the column data specified. And the third is the most basic query of all–it will select only the one columns data in the table. Now, with that, if you limit the third query to one row to be returned, the result will be the data you’re after. So let’s say we have a table with the following setup:
Table Name: members
| id | Username | Password |
+——+—————-+—————-+
| 1 | patterson_b | password1 |
| 2 | killer_bee | pbcup |
+——+—————-+—————-+
Let’s say we want to grab the password of the first user in the table. We would run the following query:
$query = “SELECT Password FROM `members` ORDER BY id ASC LIMIT 1”;
$result = mysql_query($query);
echo $result;
The code above will run a query on the database and grab the first password in the table. When we echo the variable $result, we will get “password1.” Let’s look at another snippet of code:
Code: $query = “SELECT * FROM `members` ORDER BY id DESC”; $result = mysql_query($query); echo $result;
What we’re doing there is running the query on the database and getting some data back as a result, and then we print the result of the query.
Now, what we should see (if the query is working properly) is something like:
Resource ID #?? where “??” is equal to an integer. If that is not the output you get, then you have to add a die statement to the query to see what your query error is. So your code could look like:
$query = “SELECT * FROM `members` ORDER BY id DESC”;
$result = mysql_query($query) or die(mysql_error());
The mysql_error() will spit out the exact error that mysql encounters. If you would like even more information, you can use mysql_errno() to give you the error number that corresponds to the error. This is helpful for googling for answers to your problems.
If you get an actual result, not a resource ID or anything like that, but something that you expect, then you need to select more than one column and more than one row. Here’s some examples of working & non-working code.
// Basic Connection settings
$conn = @mysql_connect(‘localhost’, ‘user’, ‘pass’);
if($conn)
{
mysql_select_db(‘test_database’, $conn);
}
else
{
echo “Couldn’t connect to database!!”;
}
$query1 = “SELECT * FROM `test_table` ORDER BY id DESC”;
$result1 = mysql_query($query1);
echo $result1.'<hr>’;
$query2 = “SELECT id FROM `test_table` ORDER BY id DESC LIMIT 1”;
$result2 = mysql_query($query2);
echo $result2.'<hr>’;
$query3 = “SELECT id, name FROM `test_table` LIMIT 0,2”;
$result3 = mysql_query($query3);
echo $result3;
That will output:
Resource ID#?? ————– 1 ————– Resource ID#??
Note: The “??” will be replaced with a numerical value. It is dependant upon other factors and can not be determined.
