First check the price, then check the title and see if contains..

Hello,
The answer I got when I asked this question a while ago was that any half decent query should do it but I forgoted the main problem, that it should both match the price first and then all of the identify_. Sorry to ask a similar question but I really couldn't sove it with just that answer.

I want the script, when finnished, to be able to identify incomming orders by first checking the title and then, where price is = the price, check the identify to find a match.

Here is the database structure;

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_name` varchar(100) NOT NULL default '',
  `file_pack` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Code (markup):

First the script needs to select all rows where "price" = $item_amount. Then it should check the $item_title and search all rows where the price is $amount and find a match where the $item_title contains "identify_pos" and "identify_pos2" but not "identify_neg" or "identify_neg2".

To clarify, It is the title that should contain "identify_pos" and "identify_pos2" but not "identify_neg" or "identify_neg2" and the identify_ isn't the complete title, just 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 to anyone who can help me with this,
/Oskar R

 

hi, how do you get the $link_title in your page? please check with this, i didnt tried, if not working then please tell me.....

"SELECT * FROM tablename WHERE price = $item_amount AND (identify_pos or identify_pos2 LIKE '%$item_title%' AND identify_neg or identify_neg2 NOT LIKE '%$item_title%')  

PHP:

 

Hello,
I didn't get it working as I hoped it would,
Here is what I inserted in the database;

-- 
-- 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_name` varchar(100) NOT NULL default '',
  `file_pack` varchar(30) NOT NULL default '',
  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_name`, `file_pack`) VALUES 
(1, '15GB package', '2.95', '15gb', 'templates', 'test', '', 'test.zip', ''),
(2, 'not 15gb package', '2.95', 'ebooks', '', '15gb', '', 'test2.zip', '');

Code (markup):

Then I used this PHP code;

// Normaly comes from Paypal's API
$item_amount="2.95";
$item_title="Templates, ebooks, graphics, test, clip art, logos! Resell!";

// 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");

$sql2="SELECT item_id FROM items WHERE price = $item_amount AND (identify_pos and identify_pos2 LIKE '%$item_title%' AND identify_neg and identify_neg2 NOT LIKE '%$item_title%')";
$result2=mysql_query($sql2);
$found_item=mysql_fetch_array($result2);

echo $found_item[0];

?>

PHP:

First I tried with the title "15GB templates, ebooks and so on!" and it worked, it selected row 1. But when I changed the title to "Templates, test, ebooks" it didn't select row 2 but still row 1. So what can the problem be with that?

Really thanks for your help, I really appreciate it,
Best Regards
Oskar R

 

because you are didnot using any loops to list all your results change here

$result2=mysql_query($sql2);
$found_item=mysql_fetch_array($result2);
echo $found_item[0];

PHP:

Use this code to list all the item_id found by the query

$result2 = mysql_query($sql2);
while ($row = mysql_fetch_array($result2))
{
      $found_item = $row['item_id'];
       echo $found_item;
}

PHP:

 

Hello,
OKEY, I'll change that, but it returned row 1 and it shouldn't have done that at all because the word "test" was in the title and that word was among the identify_neg of row 1.

Thanks,
/Oskar

 

then how it return, because in the code we said, title in "identify_pos" and "identify_pos2" but not "identify_neg" or "identify_neg2". So that its never return the words in the "identify_neg" or "identify_neg2"......

Always do simple things for faster and correct results...i didnt understand why you using identify_neg....

 

Hello,
Ignore this post...

/Oskar

 

Hello,
OK, I now used this code instead;

$item_price="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");


$sql2="SELECT item_id FROM items WHERE price = $item_amount AND (identify_pos and identify_pos2 LIKE '%$item_title%' AND identify_neg and identify_neg2 NOT LIKE '%$item_title%')";
$result2 = mysql_query($sql2);
while ($row = mysql_fetch_array($result2))
{
      $found_item = $row['item_id'];
       echo $found_item;
}

PHP:

But now I got this error from PHP: "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in..".

Did I missunderstand your post or is it something else that is wrong with the code? Please tell me..

Thanks in advance,
/Oskar R

 

Hello,
I need help with building the MySQL querey/PHP code. I've now given up on the above code... First, how should I use PHP/MySQL? Should MySQL do the matching or should a PHP code do it?

Thanks in advance,
/Oskar R