Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resour...

Hello,
When I try to run this:
$sql="SELECT *, COUNT(*) AS i FROM `items` WHERE `price` = $item_amount AND ((`identify_pos` OR `identify_pos2`) LIKE %$item_title%) AND ((`identify_neg` OR `identify_neg2`) NOT LIKE %$item_title%) AND ((`identify_pos` OR `identify_pos2` OR `identify_neg` OR `identify_neg2`) NOT = $item_title) AND `i` = 1";
$result = mysql_query($sql);
$row=mysql_fetch_assoc($result);
echo $row['item_id'];

I get this error: "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in".

And then I tried this;
$sql="SELECT *, COUNT(*) AS i FROM `items` WHERE `price` = $item_amount AND ((`identify_pos` OR `identify_pos2`) LIKE %$item_title%) AND ((`identify_neg` OR `identify_neg2`) NOT LIKE %$item_title%) AND ((`identify_pos` OR `identify_pos2` OR `identify_neg` OR `identify_neg2`) NOT = $item_title) AND `i` = 1";
$result = mysql_query($sql);
if ($row = mysql_fetch_row($result)) {
return $row;
} else {
print (mysql_error());
}
$row=mysql_fetch_assoc($result);
echo $row['item_id'];

And got this error; "Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in.. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%Templates, test, ebooks%) AND ((`identify_neg` OR `identify_neg2`) NOT LIKE %Te' at line 1
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in..."

Could you please take a look at the errors and tell me what's wrong with my code? Thanks in advance,
/Oskar R

The error you mention in the title of your thread is only happening because your query failed.

Your query failed because, well, there's a syntax error. :)

Since you have imbedded variables in the query, you should echo the query text when there's an error so you can either rule out the variables as the problem, or identify the variables as the problem. (One common problem I find is the variables are empty, which screws up the wording of the query.)

So make your error reporting look like

print ("query: ".$sql." error text: ".mysql_error());


and run it again to see if you can spot the problem that way.

Firstly, your string variables need to be in quotes ('s)
second,

AND ((`identify_pos` OR `identify_pos2`) LIKE %$item_title%)

doesn't look sensible to me, you probably need

AND ((`identify_pos` LIKE '%$item_title%') OR (`identify_pos2` LIKE '%$item_title%'))


(and in the other places it's used)

Hello,
Now I've changed the code to;
$item_amount="2.95";
$item_title="Templates, test, ebooks";

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM `items` WHERE `price` = ".$item_amount." AND ((`identify_pos` LIKE '".$item_title."') AND (`identify_pos2` LIKE '".$item_title."')) AND ((`identify_neg` NOT LIKE '".$item_title."') AND (`identify_neg2` NOT LIKE '".$item_title."'))";
$result = mysql_query($sql);
if (mysql_errno()) {
echo 'Error: ' . mysql_error();
} else {
$row=mysql_fetch_assoc($result);
echo $row['item_id'];
}

It doesn't echo anything..

Here is the table structure;
--
-- Table structure for table `items`
--

CREATE TABLE `items` (
`item_id` int(11) NOT NULL AUTO_INCREMENT,
`item_name` varchar(100) NOT NULL DEFAULT '',
`price` varchar(30) NOT NULL DEFAULT '',
`identify_pos` varchar(50) NOT NULL DEFAULT '',
`identify_pos2` varchar(50) NOT NULL DEFAULT '',
`identify_neg` varchar(50) NOT NULL DEFAULT '',
`identify_neg2` varchar(50) NOT NULL DEFAULT '',
`file_id` int(10) NOT NULL DEFAULT '0',
`pack_id` int(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `items`
--

INSERT INTO `items` (`item_id`, `item_name`, `price`, `identify_pos`, `identify_pos2`, `identify_neg`, `identify_neg2`, `file_id`, `pack_id`) VALUES
(1, '15GB package', '2.95', '15gb', 'templates', 'test', '', 0, 0),
(2, 'not 15gb package', '2.95', 'ebooks', 'templates', '15gb', '', 0, 0);

But it still doesn't return anything! Please help me, what is wrong with it??

Thanks in advance,
Best Regards
Oskar R

you had ORs in your first query, and my post contained ORs as well, but there are none in your change...
think about what conditions you actually want for your query, and go from there. I'm not going to try and guess what you mean, as your query doesn't seem to make a lot of sense...

Hello,
OK, I'll try to explain what I want the script to do and I hope that you have time to see what I should use. Thanks in advance. It should be able to identify incomming orders by first checking the price and then find one match by matching the title with the identify_ pos and neg values.

The title should contain "identify_pos" and "identify_pos2" but not "identify_neg" or "identify_neg2" (keywords that the title should or shouldn't contain). The incomming transaction contains item_title and item_price
- item_price -> select all items with that price in the database, if only one then that's the correct one : )
- item_title -> if several rows has the same price, then find a matching row by match the item_title with the keywords in the tows ("identify_pos" and "identify_pos2" but not "identify_neg" or "identify_neg2").

( CAN take this LATER: If the script finds one match, then continue. If the scripts finds several rows where the price = $item_amount and the title matches the identify_ values then it should report it/do something else. Also, if it isn't a problem it would be good if the identify_ also can be empty so if just one identify_pos contains text then ignore the other identify_. )

Really thanks for your help,
/Oskar R

Besides what I already suggested (which you have yet to do), I'd recommend getting the query to work like you want it to outside of the PHP environment first (using phpMyAdmin's adhoc query window, for example) and once you have it working in that situation, then bring it into your page.