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

[Oskare100]

Hello,
I need help with a part of my script that should, 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=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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.

Really thanks in advance to anyone who can help me with this,
/Oskar R

[Oskare100]

Just wanted to add that the problem isn't solved... I still need all help I can get with it..

[liquorvicar]

$data_matches = array();
$sql = "SELECT * FROM `items` WHERE `price`=$item_price";
$query = mysql_query($sql);
while ($result=mysql_fetch_assoc($query)) {
    if (strstr($item_title,$result['identify_pos'])!==false &&
        strstr($item_title,$result['identify_pos2'])!==false &&
        strstr($item_title,$result['identify_neg'])===false &&
        strstr($item_title,$result['identify_neg2'])===false) {
        $data_matches[] = $result;
    }
}

$data_matches will then contain any rows that have matched your criteria. You can choose what to do based on how many entries there are in that array.

[Oskare100]

Hello,
Ok, great, really thanks for your help.

To make sure that only one row is found, can I use something like this?;

$count=mysql_num_rows($result);
if($count==1){
// continue with the script...
}
else {
// Error, echo all returned items and send to me..
}

And one more thing, I need the item_id of the returned row. How can I select that from the $result?

And again, thanks for your time. I'll try it as soon as I get the server online.
Best Regards
Oskar R

[laserlight]

Read the PHP Manual on [man]mysql_fetch_assoc/man. You would use $result['item_id'].

[Oskare100]

Hello,
I can't get that working, with the mysql_fetch_assoc(). I've among many other things tried $row = mysql_fetch_assoc($result['item_id']); but it seams like it doesn't work at all, PHP just gives me the error message "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in.." .

So, How should I write it to make it work??

Thanks,
/Oskar R

[laserlight]

I can't get that working, with the mysql_fetch_assoc(). I've among many other things tried $row = mysql_fetch_assoc($result['item_id']); but it seams like it doesn't work at all, PHP just gives me the error message "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in.." .

Have you read the PHP Manual concerning mysql_fetch_assoc()?

[Oskare100]

Yes, but since I'm not that good at PHP I might have missed something.. And I didn't understand everything either.. Could you please give me some more information about how to do it?

Thanks
/Oskar R

[Oskare100]

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=InnoDB 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