Page 1 of 1

First check the price, then check the title and see if conta

Posted: Wed Jan 03, 2007 8:51 am
by oskare100
feyd | Please use

Code: Select all

,

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,
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;
[syntax="sql"]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 ;
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


feyd | Please use[/syntax]

Code: Select all

,

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: Wed Jan 03, 2007 9:06 am
by feyd
Any half decent select query should be able to pull out the information you wish.

On a side note, please start using the syntax highlighting tags.

Posted: Thu Jan 04, 2007 12:44 pm
by oskare100
Hello,
I've edited the question to reflect what I need the script to do now.. The server is using PHP4 but I hope that shouldn't be a problem.

Best Regards
Oskar R

Posted: Sat Jan 06, 2007 4:47 pm
by 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: Select all

$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;
    }
}
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: Select all

$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;
}
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: Select all

$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);
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