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

[oskare100]

Hello,
When I try to run this:
[code=php:0]$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'];[/code]

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

And then I tried this;
[code=php:0]$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'];[/code]

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

[hvle]

it meant you have error(s) on your sql, and I see a few, not sure it will fix the problem, but i'm sure it is error:

like %$item_title% is not syntaxticly correct, you need single quote:
like '%$item_title%'

[oskare100]

Hello,
Now I've changed the code to;
[code=php:0]$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'];
}[/code]

It doesn't echo anything..

Here is the table structure;
[code]--
-- 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); [/code]

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

Thanks in advance,
Best Regards
Oskar R

[hvle]

You forgot to include wild card characters.
Change your $sql to exactly like this:

[code]
$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%'))";
[/code]

I would recommend you to read the basic of PHP from the manual, because you obviously missed out the very basic of it.

[oskare100]

Hello,
OK, now I've really tried to fix this but I couldn't come longer than this;

[code=php:0]$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");

$trimmed = trim($item_title, " \t.");
$keywords = explode(",", $trimmed);
echo $keywords;
echo $keywords[0];
echo $keywords[1];
echo $keywords[2];
echo $keywords[3];[/code]

And that echos this;
[code]ArrayTemplates test ebooks[/code]

So I guess that's good? Because it was from the beginning "Templates, test, ebooks" but now "Templates" "test" "ebooks" and from what I understand I needed to do that for the LIKE function to function as it should.

The thing I can figure out is how to get a working MySQL querey of that, I've tried but it doesn't work... Here is one, among many others, that I've tried with;
[code]"SELECT * FROM `items` WHERE ((`identify_pos` LIKE '%".$keywords[0]."%' OR '%".$keywords[1]."%' OR '%".$keywords[2]."%'OR '%".$keywords[3]."%' OR '%".$keywords[4]."%' OR '%".$keywords[5]."%') AND (`identify_pos2` LIKE '%".$keywords[0]."%' OR '%".$keywords[1]."%' OR '%".$keywords[2]."%'OR '%".$keywords[3]."%' OR '%".$keywords[4]."%' OR '%".$keywords[5]."%')) AND ((`identify_neg` NOT LIKE '%".$keywords[0]."%' OR '%".$keywords[1]."%' OR '%".$keywords[2]."%'OR '%".$keywords[3]."%' OR '%".$keywords[4]."%' OR '%".$keywords[5]."%') AND (`identify_neg2` NOT LIKE '%".$keywords[0]."%' OR '%".$keywords[1]."%' OR '%".$keywords[2]."%'OR '%".$keywords[3]."%' OR '%".$keywords[4]."%' OR '%".$keywords[5]."%'))"[/code]

So could you please help me with the querey? Or at least tell me what to do? I would ge really greatful if you could.. Thanks in advance,
Best Regards
Oskar R

[hvle]

You wanted to match 1 or more keywords with identify_post column.
You can use this code below:
[code]
"SELECT * FROM `items` WHERE `identify_pos` LIKE '%{$keywords[0]%}' OR `identify_pos`
LIKE '%{$keywords[1]%}' OR `identify_pos` LIKE '%{$keywords[2]%}' OR `identify_pos` LIKE '%{$keywords[0]%}'"
[/code]
yes, you have to list all conditions one by one.
This is definitely not the way you want.

You need to enable full text search for `identify_pos` column in your database.  Then in the query, use Match ..Against.

[oskare100]

Hello,
OK, but can I do something like NOT MATCH.. AGAINST..? Because I need to get just one result.

Best Regards
Oskar R

[hvle]

yes, you can use the code I provided, it would be very inefficient but it works

[oskare100]

Hello,
OK, do you mean this code then;
[code]"SELECT * FROM `items` WHERE `identify_pos` LIKE '%{$keywords[0]%}' OR `identify_pos`
LIKE '%{$keywords[1]%}' OR `identify_pos` LIKE '%{$keywords[2]%}' OR `identify_pos` LIKE '%{$keywords[0]%}'"[/code]

And not the one with match against?

Thanks,
/Oskar R

[oskare100]

Hello,
OK, when I run this code now without several of the identify or the identify_negs..;
[code=php:0]$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");

$trimmed = trim($item_title, " \t.");
$keywords = explode(",", $trimmed);
echo $keywords[0];
echo $keywords[1];
echo $keywords[2];
echo $keywords[3];

$sql="SELECT * FROM `items` WHERE `identify_pos` LIKE '%{$keywords[0]}%' OR `identify_pos` LIKE '%{$keywords[1]}%' OR `identify_pos` LIKE '%{$keywords[2]}%' OR `identify_pos` LIKE '%{$keywords[0]}%'";
$result = mysql_query($sql);

    $row=mysql_fetch_assoc($result);
echo $row['item_id'];
echo $row[0];
echo $result[0];[/code]

I do only get this output/it echos only this;
Templates test ebooks (the keywords that is echoed in the beginning of the script)
but not the item_id I want

Please help,
Thanks in advance,
/Oskar R