View Full Version : First check the price, then check the title and see if contains..

01-04-2007, 11:53 AM
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`)

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

01-04-2007, 10:53 PM
Just wanted to add that the problem isn't solved... I still need all help I can get with it..

01-05-2007, 05:32 PM
Hello again,
Can this be done with only PHP and "ordinary" MySQL queries or more advanced MySQL queries? Any ideas?

Best Regards
Oskar R

01-05-2007, 06:50 PM
Certainly be a big help if you could post a sample row of the data.

01-05-2007, 07:32 PM
you should be posting this in the mysql thread. this isn't a php question. Please post in the appropriate forums.

01-06-2007, 11:53 PM
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..

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

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

$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.

/Oskar R

01-08-2007, 09:45 AM
OK, now I've really tried to fix this but I couldn't come longer than this;

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

$trimmed = trim($item_title, " \t.");
$keywords = explode(",", $trimmed);
echo $keywords;
echo $keywords[0];
echo $keywords[1];
echo $keywords[2];
echo $keywords[3];

And that echos this;

ArrayTemplates test ebooks

So I guess that's good? Because it was from the beginning "Templates, test, ebooks" but now "Templates" "test" "ebooks" and from what I understand I needed to do that for the LIKE function to function as it should.

The thing I can figure out is how to get a working MySQL querey of that, I've tried but it doesn't work... Here is one, among many others, that I've tried with;

"SELECT * FROM `items` WHERE ((`identify_pos` LIKE '%".$keywords[0]."%' OR '%".$keywords[1]."%' OR '%".$keywords[2]."%'OR '%".$keywords[3]."%' OR '%".$keywords[4]."%' OR '%".$keywords[5]."%') AND (`identify_pos2` LIKE '%".$keywords[0]."%' OR '%".$keywords[1]."%' OR '%".$keywords[2]."%'OR '%".$keywords[3]."%' OR '%".$keywords[4]."%' OR '%".$keywords[5]."%')) AND ((`identify_neg` NOT LIKE '%".$keywords[0]."%' OR '%".$keywords[1]."%' OR '%".$keywords[2]."%'OR '%".$keywords[3]."%' OR '%".$keywords[4]."%' OR '%".$keywords[5]."%') AND (`identify_neg2` NOT LIKE '%".$keywords[0]."%' OR '%".$keywords[1]."%' OR '%".$keywords[2]."%'OR '%".$keywords[3]."%' OR '%".$keywords[4]."%' OR '%".$keywords[5]."%'))"

So could you please help me with the querey? Or at least tell me what to do? I would ge really greatful if you could.. Thanks in advance,
Best Regards
Oskar R