PHP MySQL Problem

Manat

PHP Warning, supplied argument is not a valied mysql result resource.

What does the above mean, it was in my server logs?

The below code is not working and causing the above error:
Please can you help me out with my snippet.

<?php
$contest="roundTable";
$result = mysql_query("SELECT * FROM contests WHERE ContestName = $contest",$db);

printf("Contestant ID: %s \n", mysql_result($result,0,"Contestant_ID"));
printf("Age: %s \n", mysql_result($result,0,"Age"));
printf("First Name: %s \n", mysql_result($result,0,"Fname"));

Does anyone know why my code doesn't work?

Can anyone show me how I could do this:

Contestand ID First Name Last Name
$contestant_ID $fname $lname
$contestant_ID $fname $lname
$contestant_ID $fname $lname
$contestant_ID $fname $lname
$contestant_ID $fname $lname

So it would list all the entries that conforms to the SQL criteria.

Any help would be much appreciated!

steved

The error message means that your query failed to return any results into your $result variable. Probably because your query is failing.

Assuming you already have established your connection to the database (your code doesn't show it), try putting single quotes around the $contest variable like:

$result = mysql_query("SELECT * FROM contests WHERE ContestName = '$contest' ",$db);

os2al

My query works fine when I use one table with about 800 records. But when I query another table with 4,000 or so records, the query doesn't work and I get this error message:

"Supplied argument is not a valid MySQL result resource" etc. with a line number.

As I said, the tables are exactly the same except for the size. Does anyone know why this error might appear querying one table and not the other?

Everything else is the same except for the table being queried, of course. I understand the query is failing somehow but the reason escapes me, since the tables differ only in size.

Thanks,

steved

Well, without seeing the table definitions and the queries you are using, it is pretty tough to debug the problem.

If the tables are defined the same, they must be in different databases, or is it the columns that are the same and the table names are different?

os2al

Sorry about that. The columns are identicial in two different tables (different names, same structure), one with 800 records and the other with about 4,000. The table here is "Organization."

Here is the code that searches in the 800 record table, a fairly simple seach for the name of an organization:

<?
if (!$searchtype || !$searchterm)
{
echo "You have not entered search details. Please go back and try again.";
exit;
}

$searchtype = addslashes($searchtype);
$searchterm = addslashes($searchterm);

@ $db = mysql_pconnect("localhost", "database", "password");

if (!$db)
{
echo "Error: Could not connect to database. Please try again later.";
exit;
}

mysql_select_db("database");
$query = "select * from Organization where ".$searchtype." like '%".$searchterm."%'";
$result = mysql_query($query);

$num_results = mysql_num_rows($result);

echo "Organizations found: ".$num_results."";

for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);
echo "".($i+1).". Organization Name: ";
echo stripslashes($row["Org_Name"]);
echo " -- Org Address: ";
echo stripslashes($row["Org_Address"]);

(... etc ... with a bunch of other rows following along the same lines ... )

In the manual it mentions that sometimes for large data sets it's better to use "mysql_fetch_row," although I don't know how to apply it and I don't know if it would apply here. Perhaps you could help me out with that, if you think that is a possible solution.

os2al

[[ I reprint the following lines below from the manual about fetch. Perhaps you could adapt it for me, if it applies. I'm not too long in the tooth:

(query = "SELECT * FROM table_name";
$query_result_handle = mysql_query ($query)
or die ('The query failed! table_name must be a valid table name that exists in the database specified in mysql_select_db');

make sure that we recieved some data from our query

$num_of_rows = mysql_num_rows ($query_result_handle)
or die ("The query: '$query' did not return any data");

print "The query: '$query' returned $num_of_rows rows of data.
";

use mysql_fetch_row to retrieve the results

for ($count = 1; $row = mysql_fetch_row ($query_result_handle); ++$count)
{
print "
Fetching row #$count from the query.
";
print "Value stored at the first index position of \$row: '$row[0]'
";
print "Value stored at the second index position of \$row: '$row[1]'
";
print "Value stored at the third index position of \$row: '$row[2]'
";
print "Value stored at the fourth index position of \$row: '$row[3]'
";
print "Value stored at the fifth index position of \$row: '$row[4]'
";
print "Value stored at the sixth index position of \$row: '$row[5]'
";
print '...
';
} ...etc. ]]

steved

4,000 rows shouldn't bog down MySql. Especially since you are not selecting everything, but only those that match your criteria.

I am curious as to why you are doing the addslashes to your $searchtype variable. It should be a single column name and shouldn't need it.

Maybe a place to start would be to print out the value of $query just before you run it to see if you can spot anything wrong with it. If you have a way to do it, you can then run the query manually to see if the error message is any more helpful.

Manat

Can you guys make a seperate thread for this?

I started this thread and it no longer relates to me, yet I still get emails when someone replies.

Thx

os2al

Sorry if the wrong people get this post. As for the last answer, I appreciate the help, but I think you meant stripslashes, which strips out extraneous junk in the search, or so I figure.

It's hard to make the points over again so I'll just say that this search works fine at 800 records, but with 4,000 it doesn't -- just a different table name, everything else the same. I get the aforementioned error message. Somehow the query doesn't work with the same code, for whatever reason, and I'd like to find out what it is if anyone has another idea perhaps of how to search the records.

Thanks again for the suggestions, though.

os2al

steved

No, I meant addslashes....................

$searchtype = addslashes($searchtype);
$searchterm = addslashes($searchterm);

os2al

Yes, you're right. That is for formatting strings for storage, for escaping characters entered into the database, which are "slashed" out on the way back. Thanks for noticing but it doesn't solve my dilemma.

The question remains: Does anyone know another way to query the database because the one above doesn't seem to work for some reason on larger data sets?