Page 1 of 2
Warning: mysql_fetch_assoc(): supplied argument is not a val
Posted: Sun Jan 07, 2007 3:16 am
by oskare100
Hello,
When I try to run this:
Code: Select all
$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;
Code: Select all
$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
Re: Warning: mysql_fetch_assoc(): supplied argument is not a
Posted: Sun Jan 07, 2007 3:36 am
by Christopher
Try something like this to find the SQL error:
Code: Select all
$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 (mysql_errno()) {
echo 'Error: ' . mysql_error();
} else {
$row=mysql_fetch_assoc($result);
echo $row['item_id'];
}
Posted: Sun Jan 07, 2007 3:50 am
by oskare100
Hello,
OK, I did that now and changed the things I could but now I still get the error:
"Error: 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')' at line 1"
When I run this;
Code: Select all
$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` 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)";
$result = mysql_query($sql);
if (mysql_errno()) {
echo 'Error: ' . mysql_error();
} else {
$row=mysql_fetch_assoc($result);
echo $row['item_id'];
}
Do you know what's wrong with it?
Thanks,
/Oskar R
Posted: Sun Jan 07, 2007 4:00 am
by volka
(`identify_pos` OR `identify_pos2`) LIKE '%$item_title%'
(a OR b) will always return a boolean value. It cannot be used to have more than one operand attached to another operation.
(`identify_pos`LIKE '%$item_title%') OR (`identify_pos2`LIKE '%$item_title%')
I'm not sure what you're trying with
$item_title="Templates, test, ebooks";
...
LIKE '%$item_title%'
This will not match
lalalaTemplateslalala, only something like
lalalaTemplates, test, ebookslalala
Posted: Sun Jan 07, 2007 4:01 am
by AKA Panama Jack
Code: Select all
$sql="SELECT * 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`) != $item_title)";
Posted: Sun Jan 07, 2007 5:18 am
by oskare100
feyd | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Hello,
Thanks, that solved that problem, Now when I run this script;
Code: Select all
$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` 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`) != $item_title)";
$result = mysql_query($sql);
if (mysql_errno()) {
echo 'Error: ' . mysql_error();
} else {
$row=mysql_fetch_assoc($result);
echo $row['item_id'];
}
I get the error Error: Unknown column 'Templates' in 'where clause'. What does that mean?
Here is my database structure of the table with the items;
Code: Select all
--
-- 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);
And;
volka wrote:This will not match lalalaTemplateslalala, only something like lalalaTemplates, test, ebookslalala
Volka, what do you mean with that? Can't it match the title if it contains , and is in several words?
Thanks in advance,
/Oskar R
feyd | Please use Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Posted: Sun Jan 07, 2007 5:26 am
by volka
oskare100 wrote:volka wrote:This will not match lalalaTemplateslalala, only something like lalalaTemplates, test, ebookslalala
Volka, what do you mean with that? Can't it match the title if it contains , and is in several words?
It will only match fields that contain whole string
Templates, test, ebooks. It will not match fields that contain
Template or [/i]test[/i]
or [/i]ebooks[/i] .
And there's still the issue with
((`identify_pos` OR `identify_pos2`) LIKE '%$item_title%')
Please explain in detail but without code what you're trying to achieve, i.e. everything you've tried to put into the code must be covered somewhere in your non-code-explaination. A commented example might also be helpful.
Posted: Sun Jan 07, 2007 6:42 am
by oskare100
Hello,
OK, thanks, I'll try to explain what I want it to do. It should be able to identify incomming orders by first checking the price and then find an exact match by matching the title with the identify_ pos and neg values.
To clarify, The title that 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").
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_.
Thanks in advance,,
/Oskar R
Posted: Sun Jan 07, 2007 8:05 am
by volka
AND ((`identify_pos` OR `identify_pos2`) LIKE '%$item_title%')
Let's take the second recordset
(2, 'not 15gb package', '2.95', 'ebooks', 'templates', '15gb', '', 0, 0)
and substitute the values (that's what mysql is doing as well)
AND (('ebooks' OR 'templates') LIKE '%Templates, test, ebooks%')
mysql considers the values true, 'true', 1 and '1' as true. Everything else is false. 'ebooks' is false, 'templates' is false, therefore ('ebooks' OR 'templates') is false. Mysql returns 0 as value for false, i.e. ('ebooks' OR 'templates')
returns 0
AND (0 LIKE '%Templates, test, ebooks%')
LIKE operates on strings, mysql will convert the 0 to a string
AND ('0' LIKE '%Templates, test, ebooks%')
Obviously the string
Templates, test, ebooks is nowhere in the string '0'. This condition is never fulfilled.
You want to compare both fields against a given string. You cannot extract the operator LIKE as you may do with (3a + 4a) = (3+4)a
AND (('ebooks' LIKE '%Templates, test, ebooks%') OR ('templates' LIKE '%Templates, test, ebooks%') )
But that's still wrong, you would need something like
AND (('%ebooks%' LIKE 'Templates, test, ebooks') OR ('%templates%' LIKE 'Templates, test, ebooks') )
Either add the appropriate % characters to your records or use e.g. INSTR, see
http://dev.mysql.com/doc/refman/4.1/en/ ... tions.html
Same with
AND ((`identify_neg` OR `identify_neg2`) NOT LIKE '%$item_title%')
and the purpose of
AND ((`identify_pos` OR `identify_pos2` OR `identify_neg` OR `identify_neg2`) != $item_title)
I do not understand at all.
Posted: Sun Jan 07, 2007 8:18 am
by oskare100
Hello,
Thanks, Well, that didn't sound good. I've started reading the guide you sent me but I must say that I understand far from everything... How would you write the querey then to make it work with what I want to do? Have you any idea of how to make it do what I want to do? Or maybe I shouldn't use a MySQL querey?
Regards,
/Oskar R
Posted: Sun Jan 07, 2007 9:14 am
by feyd
Re: ((`identify_pos` OR `identify_pos2`) LIKE '%$item_title%') and similar
The OR will return a boolean for LIKE to use against the given pattern. More than likely, it won't match. The logic needs to be broken out into individual statements.
This really is about SQL, so moved to Databases.
Posted: Sun Jan 07, 2007 11:34 am
by oskare100
Hello,
OK, so I should use something like;
Code: Select all
AND ((`identify_pos` LIKE '%$item_title%') OR (`identify_pos2` LIKE '%$item_title%'))
instead of
Code: Select all
AND ((`identify_pos` OR `identify_pos2`) LIKE %$item_title%)
then? Or how should I do it?
Thanks,
/Oskar R
Posted: Sun Jan 07, 2007 11:36 am
by feyd
That is correct.
Posted: Sun Jan 07, 2007 11:44 am
by oskare100
Hello,
Now I've changed the code to;
Code: Select all
$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;
Code: Select all
--
-- 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
Posted: Sun Jan 07, 2007 11:59 am
by timvw
Your $sql would expand to: SELECT * FROM `items` WHERE `price` = 2.95 AND ..
You need to place quotes around the 2.95...