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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
oskare100
Forum Commoner
Posts: 80
Joined: Sun Oct 29, 2006 5:47 am

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

Post 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]
Last edited by oskare100 on Thu Jan 04, 2007 4:27 pm, edited 3 times in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
oskare100
Forum Commoner
Posts: 80
Joined: Sun Oct 29, 2006 5:47 am

Post 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
oskare100
Forum Commoner
Posts: 80
Joined: Sun Oct 29, 2006 5:47 am

Post 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
Post Reply