Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Dec 2006
    Posts
    57
    Thanks
    2
    Thanked 0 Times in 0 Posts

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

    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;
    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 ;
    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
    Last edited by oskare100; 01-04-2007 at 11:27 PM.

  • #2
    New Coder
    Join Date
    Dec 2006
    Posts
    57
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Just wanted to add that the problem isn't solved... I still need all help I can get with it..

  • #3
    New Coder
    Join Date
    Dec 2006
    Posts
    57
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hello again,
    Can this be done with only PHP and "ordinary" MySQL queries or more advanced MySQL queries? Any ideas?

    Best Regards
    Oskar R

  • #4
    New Coder
    Join Date
    Dec 2006
    Location
    Denver, Colorado
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Certainly be a big help if you could post a sample row of the data.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    you should be posting this in the mysql thread. this isn't a php question. Please post in the appropriate forums.

  • #6
    New Coder
    Join Date
    Dec 2006
    Posts
    57
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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..

    PHP Code:
     $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)

    PHP Code:
    $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)

    PHP Code:
    $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

  • #7
    New Coder
    Join Date
    Dec 2006
    Posts
    57
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hello,
    OK, now I've really tried to fix this but I couldn't come longer than this;

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

    $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;
    Code:
    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;
    Code:
    "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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •