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

    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resour...

    Hello,
    When I try to run this:
    PHP Code:
    $sql="SELECT *, COUNT(*) AS i FROM `items` WHERE `price` = $item_amount AND ((`identify_pos` OR `identify_pos2`) LIKE %$item_title%) AND ((`identify_neg` OR `identify_neg2`) NOT LIKE %$item_title%) AND ((`identify_pos` OR `identify_pos2` OR `identify_neg` OR `identify_neg2`) NOT = $item_title) AND `i` = 1";
    $result mysql_query($sql);
    $row=mysql_fetch_assoc($result);
     echo 
    $row['item_id']; 
    I get this error: "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in".

    And then I tried this;
    PHP Code:
    $sql="SELECT *, COUNT(*) AS i FROM `items` WHERE `price` = $item_amount AND ((`identify_pos` OR `identify_pos2`) LIKE %$item_title%) AND ((`identify_neg` OR `identify_neg2`) NOT LIKE %$item_title%) AND ((`identify_pos` OR `identify_pos2` OR `identify_neg` OR `identify_neg2`) NOT = $item_title) AND `i` = 1";
    $result mysql_query($sql);
    if (
    $row mysql_fetch_row($result)) {
               return 
    $row;
            } else {
               print (
    mysql_error());
            }
    $row=mysql_fetch_assoc($result);
     echo 
    $row['item_id']; 
    And got this error; "Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in.. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%Templates, test, ebooks%) AND ((`identify_neg` OR `identify_neg2`) NOT LIKE %Te' at line 1
    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in..."

    Could you please take a look at the errors and tell me what's wrong with my code? Thanks in advance,
    /Oskar R

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    The error you mention in the title of your thread is only happening because your query failed.

    Your query failed because, well, there's a syntax error.

    Since you have imbedded variables in the query, you should echo the query text when there's an error so you can either rule out the variables as the problem, or identify the variables as the problem. (One common problem I find is the variables are empty, which screws up the wording of the query.)

    So make your error reporting look like
    PHP Code:
    print ("query: ".$sql." error text: ".mysql_error()); 
    and run it again to see if you can spot the problem that way.

  • #3
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    Firstly, your string variables need to be in quotes ('s)
    second,
    Code:
    AND ((`identify_pos` OR `identify_pos2`) LIKE %$item_title%)
    doesn't look sensible to me, you probably need
    Code:
    AND ((`identify_pos` LIKE '%$item_title%') OR (`identify_pos2` LIKE '%$item_title%'))
    (and in the other places it's used)

  • #4
    New Coder
    Join Date
    Dec 2006
    Posts
    57
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hello,
    Now I've changed the code to;
    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");

    $sql="SELECT * FROM `items` WHERE `price` = ".$item_amount." AND ((`identify_pos` LIKE '".$item_title."') AND (`identify_pos2` LIKE '".$item_title."')) AND ((`identify_neg` NOT LIKE '".$item_title."') AND (`identify_neg2` NOT LIKE '".$item_title."'))";
    $result mysql_query($sql);
    if (
    mysql_errno()) {
          echo 
    'Error: ' mysql_error();
    } else {
         
    $row=mysql_fetch_assoc($result);
         echo 
    $row['item_id'];

    It doesn't echo anything..

    Here is the table structure;
    Code:
    -- 
    -- Table structure for table `items`
    -- 
    
    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_id` int(10) NOT NULL DEFAULT '0',
      `pack_id` int(10) NOT NULL DEFAULT '0',
      PRIMARY KEY  (`item_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
    
    -- 
    -- Dumping data for table `items`
    -- 
    
    INSERT INTO `items` (`item_id`, `item_name`, `price`, `identify_pos`, `identify_pos2`, `identify_neg`, `identify_neg2`, `file_id`, `pack_id`) VALUES
    (1, '15GB package', '2.95', '15gb', 'templates', 'test', '', 0, 0),
    (2, 'not 15gb package', '2.95', 'ebooks', 'templates', '15gb', '', 0, 0);
    But it still doesn't return anything! Please help me, what is wrong with it??

    Thanks in advance,
    Best Regards
    Oskar R

  • #5
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    you had ORs in your first query, and my post contained ORs as well, but there are none in your change...
    think about what conditions you actually want for your query, and go from there. I'm not going to try and guess what you mean, as your query doesn't seem to make a lot of sense...

  • #6
    New Coder
    Join Date
    Dec 2006
    Posts
    57
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hello,
    OK, I'll try to explain what I want the script to do and I hope that you have time to see what I should use. Thanks in advance. It should be able to identify incomming orders by first checking the price and then find one match by matching the title with the identify_ pos and neg values.

    The title should contain "identify_pos" and "identify_pos2" but not "identify_neg" or "identify_neg2" (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").

    ( CAN take this LATER: 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 for your help,
    /Oskar R

  • #7
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Besides what I already suggested (which you have yet to do), I'd recommend getting the query to work like you want it to outside of the PHP environment first (using phpMyAdmin's adhoc query window, for example) and once you have it working in that situation, then bring it into your page.


  •  

    Posting Permissions

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