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

[oskare100]

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;
[CODE]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]

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

[Renlok]

im sorry but i dont really understand what your problem is?

[oskare100]

Hello,
The problem is that I don't know how to make it first check the price and then identify the correct item by matching it against the identify_ values in the different rows where the price is the price of the item.

/Oskar

[Renlok]

just use use mysql_num_rows() on the queries for the chacks so itd be something like this
[code]
<?php
$price = mysql_num_rows(mysql_query("SELECT * FROM items WHERE price='$item_amount'"));
if($price > 0)
{
$title = mysql_query("SELECT * FROM items WHERE item_name='$item_title'");
} else {
echo "error";
}
?>
[/code]
but im still not entirly sure what you mean so this may not be of any help

[oskare100]

Hello,
OK, thanks, but that still doesn't solve the problem with the keywords. " item_name='$item_title'" " would only match the entire title, I want it to search for the keywords "identify_pos" and "identify_pos2" and find a match where both of the keywords are present in the $item_title.

If it's possible it would be good if it also could search for the "identify_neg" and "identify_neg2" and exlude all item_name rows where "identify_neg" or "identify_neg2" are present in the $item_title.

Thanks in advance,
Best Regards
Oskar R

[oskare100]

Hello,
OK, Now I've, with help, got three suggestions about how it can be done.. The problem is that none of them work as they should..

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

When I try to fetch the result of that code with mysql_fetch_array($result['item_id']) I get an error message.. (Don't know how to get the correct result)

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

When I use this code I get this error message; "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in".. (Don't know how to get the correct result)

[code=php:0]$buildquery = "select * from items where price = $item_amount";
if ($identify_pos != "")
{
  $buildquery.= " and instr($item_title, $identify_pos) > 0";
}
if ($identify_pos2 != "")
{
  $buildquery.= " and instr($item_title, $identify_pos2) > 0";
}
if ($identify_neg != "")
{
  $buildquery.= " and instr($item_title, $identify_neg) < 1";
}
if ($identify_neg2 != "")
{
  $buildquery.= " and instr($item_title, $identify_neg2) < 1";
}
$result = mysql_query($buildquery);[/code]

And when I use this code I either get an error message or the same item. (Don't know how to get the correct result)

These codes might work, my problem is that I don't know how to get the "finnish" right (in other words; I don't know how to the the item_id of the correct result). Any help on this would be very appreciated.

Thanks,
/Oskar R