Not a valid MySQl Resource

[JVassie]

The error message:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ..../functionlist.php on line 1267

The code it refers to is this function:

function gettreatmentid($first, $last, $date){
         global $conn;
         $q = "SELECT treatmentid FROM treatments WHERE firstname = '$first' && lastname = '$last' && date = '$date'";
         $result = mysql_query($q,$conn);
         $row = mysql_fetch_array($result, MYSQL_NUM);
         return $row[0];
}

What i want it to do is pull the treatmentid from the treatments table, where it match the first name, last name and the date. I have many other queries liek this, but i think the problem is because treatmentid field is of integer type?

Anyone know how to work around this problem?

Most appreciated

[bradgrafelman]

When debugging MySQL queries, you should always find out what MySQL is telling you. Normally, I debug SQL queries like so:

$result = mysql_query($q) or die('MySQL error: ' . mysql_error() . "<hr>Query: $q");

In your case, however, the problem is that '&&' doesn't exist as an operator in MySQL; instead, you should use 'AND'.

Also, do the variables passed to the function have the potential of coming from user-supplied data? If so, your script may be vulnerable to SQL injection attacks. User-supplied data should never be placed directly into a MySQL query; it should first be sanitized by a function such as [man]mysql_real_escape_string/man.

[JVassie]

Thanks for the reply, I had changed the query to this ad it removed the error, but still didnt actually add the data to the database 😕

function gettreatmentid($first, $last, $date){
         global $conn;
         $q = "SELECT treatmentid FROM treatments WHERE firstname = '$first' && lastname = '$last' && date = '$date'";
         $result = mysql_query($q,$conn);
         return $result;
}

And about the SQL injections, i have them on all my other form inputs, this isnt placed live though until it works 🙂

Should i still try replacing && with AND?

Edit: though i would add the fnction that actually inserts the data into the database:

function addfeedback($username, $treatmentid, $date, $firstname, $lastname, $general24, $general3, $general1, $specific24, $specific3, $specific1, $explanation, $relevant, $didithelp, $enjoyable, $notes, $opinion){
   global $conn;
   $q = sprintf("INSERT INTO feedback VALUES ('', '$username', '$treatmentid', $date', '$firstname', '$lastname', '$general24', '$general3', '$general1', '$specific24', '$specific3', '$specific1', '$explanation', '$relevant', '$didithelp', '$enjoyable', '$notes', '$opinion')",
   mysql_real_escape_string($username, $conn),
   mysql_real_escape_string($treatmentid, $conn),
   mysql_real_escape_string($date, $conn),
   mysql_real_escape_string($firstname, $conn),
   mysql_real_escape_string($lastname, $conn),
   mysql_real_escape_string($general24, $conn),
   mysql_real_escape_string($general3, $conn),
   mysql_real_escape_string($general1, $conn),
   mysql_real_escape_string($specific24, $conn),
   mysql_real_escape_string($specific3, $conn),
   mysql_real_escape_string($specific1, $conn),
   mysql_real_escape_string($explanation, $conn),
   mysql_real_escape_string($relevant, $conn),
   mysql_real_escape_string($didithelp, $conn),
   mysql_real_escape_string($enjoyable, $conn),
   mysql_real_escape_string($notes, $conn),
   mysql_real_escape_string($opinion, $conn));
}

[bradgrafelman]

JVassie wrote:

I had changed the query to this

I don't see any change in the query... I only see that you removed the [man]mysql_fetch_array/man function and are now only returning a MySQL resource ID (not any actual data).

JVassie wrote:

Should i still try replacing && with AND?

Well... yeah. Like I said, '&&' doesn't exist in MySQL.

[JVassie]

bradgrafelman wrote:

I don't see any change in the query... I only see that you removed the [man]mysql_fetch_array/man function and are now only returning a MySQL resource ID (not any actual data).

Well... yeah. Like I said, '&&' doesn't exist in MySQL.

Yes thats what i meant by changing the query, and it somehow got rid of the error.

As for the &&, the MySQL reference says it exists:

Link

Whats the best function to use to return the result of an integer type field from a database?

[bradgrafelman]

D'oh, didn't even realize '&&' was equivalent to 'AND'... not sure why I had that misconception.

JVassie wrote:

Yes thats what i meant by changing the query, and it somehow got rid of the error.

Like I said, you didn't "change" the query at all, nor did you get rid of the error. All you did was get rid of the function that actually tries to retrieve data from MySQL.

JVassie wrote:

Whats the best function to use to return the result of an integer type field from a database?

They all work the same regardless of column type. If you're only retrieving one column from one row, however, you could simply use [man]mysql_result/man.

Since the '&&' doesn't appear to be the issue, debug the query as I mentioned above and let us know what the MySQL error message is.

[JVassie]

MySQL error: Unknown column 'lastname' in 'where clause'Query: SELECT treatmentid FROM treatments WHERE firstname = 'xxx' AND lastname = 'xxx' AND date = '-01-01'

I spotted the mistake i had made, in that table, lastname should be surname. Upon fixing the error, the next one i get is:

Warning: mysql_real_escape_string() expects parameter 1 to be string, resource given in /home/chilte/public_html/secure/functionlist.php on line 30

Corresponding code is:

function addfeedback($username, $treatmentid, $date, $firstname, $lastname, $general24, $general3, $general1, $specific24, $specific3, $specific1, $explanation, $relevant, $didithelp, $enjoyable, $notes, $opinion){
   global $conn;
   $q = sprintf("INSERT INTO feedback VALUES ('', '$username', '$treatmentid', $date', '$firstname', '$lastname', '$general24', '$general3', '$general1', '$specific24', '$specific3', '$specific1', '$explanation', '$relevant', '$didithelp', '$enjoyable', '$notes', '$opinion')",
   mysql_real_escape_string($username, $conn),
   mysql_real_escape_string($treatmentid, $conn),
   mysql_real_escape_string($date, $conn),
   mysql_real_escape_string($firstname, $conn),
   mysql_real_escape_string($lastname, $conn),
   mysql_real_escape_string($general24, $conn),
   mysql_real_escape_string($general3, $conn),
   mysql_real_escape_string($general1, $conn),
   mysql_real_escape_string($specific24, $conn),
   mysql_real_escape_string($specific3, $conn),
   mysql_real_escape_string($specific1, $conn),
   mysql_real_escape_string($explanation, $conn),
   mysql_real_escape_string($relevant, $conn),
   mysql_real_escape_string($didithelp, $conn),
   mysql_real_escape_string($enjoyable, $conn),
   mysql_real_escape_string($notes, $conn),
   mysql_real_escape_string($opinion, $conn));
}

Line 30 being the escape string for treatment id.

echoing out the results of $treatmentid gives

Resource id #10 

Rather than the integer I was expecting.

[bradgrafelman]

Where's the line where you define $treatmentid, and any other lines that may modify this variable?

Also note that your use of [man]sprintf/man is in correct. At present, the data returned by all of those calls to mysql_real_escape_string() is simply being discarded. An example of how to use sprintf:

$var = "Bob";
$time = "morning";
$num = 10;
sprintf("Hey there, %s! Isn't it a beautiful %s? I like %i foobars!!", $var, $time, $num);

More information about how this function is used can be found in the manual (linked above).

[JVassie]

$treatmentid=gettreatmentid($firstname, $lastname, $date);

All of the variables in the function when echo'd out display correctly, so i'm not sure where the problem is. 😕

Also, i still don't understand about sprintf, what do the %s and %i represent?

EDIT: Saw this in another post, would this be the correct way of using the escape_string function?

$username = mysql_real_escape_string($_post['username']);
$password = mysql_real_escape_string($_post['password']);
$pass_conf = mysql_real_escape_string($_post['pass_conf']);
$email = mysql_real_escape_string($_post['email']);
$ip = mysql_real_escape_string($_post['ip']);

Thanks for your continued help
James

[bradgrafelman]

JVassie wrote:

Saw this in another post, would this be the correct way of using the escape_string function?

Yes, that would be correct, provided you use $POST instead of $post. You could then use the $username, etc. variables in a SQL query.

The way I usually do it, however, is like so:

$query = sprintf("SELECT foo FROM bar WHERE foobar='%s' OR foobar='%s'",
    mysql_real_escape_string($_POST['foobar1']),
    mysql_real_escape_string($_POST['foobar2'])
);

That way there's no need to create extra variables.

JVassie wrote:

Also, i still don't understand about sprintf, what do the %s and %i represent?

Those are "type specifiers" that tell the function what type the given argument is. Since my query above had two instances of %s, sprintf() knows that it should look for two other parameters passed to it that will correspond to these type specifiers. It then inserts the variables, in order, at those places.

It's basically a shorthand way of doing concatenation, e.g. this:

$string = 'hi' . $var1 . '! Your age is ' . $age . ' years old!';

could be turned into:

$string = sprintf('hi %s! Your age is %i years old!',
    $var1,
    $age
);