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 8 of 8
  1. #1
    New to the CF scene
    Join Date
    Dec 2011
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts

    SQL Parameter 1 Resource Problems

    I keep trying to fix this error message, but I can't seem to figure it out.

    Warning: mysql_fetch_field() expects parameter 1 to be resource, boolean given in E:\xampp\htdocs\Gameshop\dbLib.php on line 61.

    It comes when running this sql code:

    INSERT INTO storedQuery VALUES(
    null,
    'Q8 - Sum Basic Inventory Total',
    'SELECT Sum([OnHand]) AS ItemsOnHand
    FROM Inventory'
    );

    Since I'm realitively new to this, I don't know what else to give you guys....
    Sorry.

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    perhaps, change
    Sum([OnHand])
    to
    Sum(OnHand)

    as I have no idea what [] would do there
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • Users who have thanked BubikolRamios for this post:

    spellmaker90 (12-02-2011)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    Access and SQL Server allow you to use [...] around non-standard field and table names in the same way the MySQL uses the back ticks `...`

    For MySQL, you would either do as Bubikol said or use
    Code:
        Sum(`OnHand`)
    DO not confuse the back tick ` with the apostrophe '

    The back tick ` is usually on the same key as the tilde ~
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    spellmaker90 (12-02-2011)

  • #4
    New to the CF scene
    Join Date
    Dec 2011
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Well, all the ones that had that error message that had the brackets I fixed, but I'm still running into these two that are also giving me errors:

    INSERT INTO storedQuery VALUES (
    null,
    'Q1 - Franklin Residents',
    'SELECT Customers.CustomerNum, Customers.FirstName, Customers.LastName, Customers.City
    FROM Customers
    WHERE Customers.City = Franklin'
    );

    and

    INSERT INTO storedQuery VALUES(
    null,
    'Q12 - Price of all Inventory',
    'SELECT Inventory.Price,
    FROM Inventory INNER JOIN Sales ON Inventory.GameNum = Sales.GameNum
    WHERE Sales.GameNum = Inventory.GameNum'
    );

    I'm also getting it on a edit table prompt was written; but only on one table. I don't know if you could help me with that too, but it's giving me the same error.
    Last edited by spellmaker90; 12-02-2011 at 02:19 AM.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    Code:
    INSERT INTO storedQuery VALUES (
        null,
        'Q1 - Franklin Residents',
        'SELECT CustomerNum, FirstName, LastName, City FROM Customers WHERE City = \'Franklin\' '
    );
    It's not wrong to prefix a field name with the table name (you used "Customers." throughout), but it's not needed when there is only one table in the FROM clause.

    Anyway the real problem is that MySQL thought you were trying to find a record where the field City is equal to the *FIELD* Franklin

    Any string that doesn't have apostrophes around it is assumed to be a table or field name.

    Because you are using apostrophes in the INSERT statement, you need to "escape" them in the data you are inserting. You do that by using \' to represent an embedded apostrophe.

    ************
    Code:
    INSERT INTO storedQuery VALUES(
        null,
        'Q12 - Price of all Inventory',
        'SELECT Inventory.Price,
         FROM Inventory INNER JOIN Sales ON Inventory.GameNum = Sales.GameNum
         WHERE Sales.GameNum = Inventory.GameNum'
    );
    This one has two mistakes in syntax and a bigger mistake in logic.

    For syntax, you have a COMMA after Inventory.Price but you have not other fields there. So either you need to be SELECTing more fields or you need to omit the comma.

    You are also doing a JOIN ... ON and then *REPEATING* the same condition in your WHERE clause. Make up your mind: Either use ON or WHERE, but don't repeat the condition.

    For logic: The query makes no sense. It is going to simply get you every single value of Inventory.Price where an inventory item matches a Sales item.

    (1) How will you tell what Price goes with what product???
    (2) This will get you the same Price multiple times if the GameNum appears in multiple Sales.

    None of this makes sense, especially given the title of the query, which is Price of ALL Inventory

    Maybe you need to figure out what you really want here, throw out that query, and write it again.

    ********

    FINALLY... None of this stuff you are doing really seems to me to have a purpose.

    If you want to have "stored queries", then why in the world are you storing them in yet another DB table? MySQL supports stored queries *NATIVELY* so you wouldn't need to do this kind of junk.

    Since your second query there makes no sense, let's use your first one as an example.

    The MySQL way to do this would be
    Code:
    CREATE VIEW `Franklin Residents`
    AS 
    SELECT CustomerNum, FirstName, LastName, City
    FROM Customers WHERE City = 'Franklin';
    That would store that query *in the database* as, indeed, a named query.

    And so you could later do SELECT * FROM `Franklin Residents`

    As an alternative, you could create a STORED PROCEDURE, thus:
    Code:
    delimiter //
    CREATE PROCEDURE `Franklin Residents`
    BEGIN
       SELECT CustomerNum, FirstName, LastName, City
       FROM Customers WHERE City = 'Franklin';
    END
    //
    delimiter ;
    (The stuff in red there is only used if you are doing this from a MySQL command line client.)

    And now you could invoke that stored procedure via CALL `Franklin Residents`

    Is there some *real* reason you are doing all this ad hoc, instead of using what is built in to MySQL?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    spellmaker90 (12-02-2011)

  • #6
    New to the CF scene
    Join Date
    Dec 2011
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks a million (again)...

    I'm using it this way becuase I plan to incorporate it into php format, a build a friendly interface for a non-computer literate person to use in their business.

    I'm more proficient in Java and C++ coding than sql or php, so it's being a learning experience for me too.

    That, and I'm getting a jumpstart for some more computer science classes that I'll take next spring in php and html.

    Thanks again!

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    Quote Originally Posted by spellmaker90 View Post
    I'm using it this way becuase I plan to incorporate it into php format, a build a friendly interface for a non-computer literate person to use in their business.
    But you can *still* do that using MySQL VIEWs and STORED PROCEDUREs and in the process gain both more power (especially with stored procedures...which can essentially become complete "packages" of code in and of themselves) and more security and less coding in PHP.

    PHP can execute stored procedures, you know.
    Code:
    $result = mysql_query("CALL `Franklin Residents`");
    Presto!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    New to the CF scene
    Join Date
    Dec 2011
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Well, than this was a learning experience in two ways!


  •  

    Posting Permissions

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