...

View Full Version : SQL Parameter 1 Resource Problems



spellmaker90
12-02-2011, 01:34 AM
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.

BubikolRamios
12-02-2011, 02:10 AM
perhaps, change
Sum([OnHand])
to
Sum(OnHand)

as I have no idea what [] would do there

Old Pedant
12-02-2011, 02:42 AM
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

Sum(`OnHand`)

DO not confuse the back tick ` with the apostrophe '

The back tick ` is usually on the same key as the tilde ~

spellmaker90
12-02-2011, 02:53 AM
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.

Old Pedant
12-02-2011, 03:44 AM
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.

************


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

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:


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?

spellmaker90
12-02-2011, 04:23 AM
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!

Old Pedant
12-02-2011, 04:48 AM
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.


$result = mysql_query("CALL `Franklin Residents`");

Presto!

spellmaker90
12-02-2011, 06:09 AM
Well, than this was a learning experience in two ways!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum