oskare100 Posted January 7, 2007 Share Posted January 7, 2007 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 1Warning: 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 Quote Link to comment Share on other sites More sharing options...
hvle Posted January 7, 2007 Share Posted January 7, 2007 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%' Quote Link to comment Share on other sites More sharing options...
oskare100 Posted January 7, 2007 Author Share Posted January 7, 2007 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 RegardsOskar R Quote Link to comment Share on other sites More sharing options...
hvle Posted January 8, 2007 Share Posted January 8, 2007 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. Quote Link to comment Share on other sites More sharing options...
oskare100 Posted January 8, 2007 Author Share Posted January 8, 2007 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 RegardsOskar R Quote Link to comment Share on other sites More sharing options...
hvle Posted January 8, 2007 Share Posted January 8, 2007 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. Quote Link to comment Share on other sites More sharing options...
oskare100 Posted January 8, 2007 Author Share Posted January 8, 2007 Hello,OK, but can I do something like NOT MATCH.. AGAINST..? Because I need to get just one result.Best RegardsOskar R Quote Link to comment Share on other sites More sharing options...
hvle Posted January 8, 2007 Share Posted January 8, 2007 yes, you can use the code I provided, it would be very inefficient but it works Quote Link to comment Share on other sites More sharing options...
oskare100 Posted January 8, 2007 Author Share Posted January 8, 2007 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 Quote Link to comment Share on other sites More sharing options...
oskare100 Posted January 8, 2007 Author Share Posted January 8, 2007 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 wantPlease help,Thanks in advance,/Oskar R Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.