[RESOLVED] problem with query

[Kurtismonger]

I have to be doing something stupid here:

This is a function that is called to look up production totals for a particular part number. For some reason I am not getting a result.
I can run this query manually and I get the result I expect.

function prodLookup($Part) {
	$prodQuery = "SELECT SUM(Production.QuantProd) AS Production
			FROM Production
			WHERE 	Production.ProdDate >= '$startDate'
				AND Production.ProdDate <= '$endDate'
				AND PartNo = '$Part'";

$result = mysql_query($prodQuery);
return $result;
}

[YAOMK]

it looks like you may have a problem connecting to your database, are you defining the link for your connection and selecting the respective db accordingly? try to echo any possible mysql erros using mysql_error().

[Kurtismonger]

Well I'm not sure, I use this exact query elsewhere in the same script to pull the same numbers, although I pop the results in an array and loop through to add up all the numbers. I'm just trying to add them to table with other results now so I am looking them up individually.
I added mysql_errno and mysql_error on my connection, but all that was returned was 0

[YAOMK]

So you are connecting fine and there are no errors. Could it be that you are passing numbers as strings in your query?

so instead of:

$prodQuery = "SELECT SUM(Production.QuantProd) AS Production
            FROM Production
            WHERE     Production.ProdDate >= '$startDate'
                AND Production.ProdDate <= '$endDate'
                AND PartNo = '$Part'"; 

do:

$prodQuery = "SELECT SUM(Production.QuantProd) AS Production
            FROM Production
            WHERE     Production.ProdDate >= $startDate
                AND Production.ProdDate <= $endDate
                AND PartNo = '$Part'";

[Kurtismonger]

Now I'm getting "Resource id #7' with the number incrementing for each row.

If I remove the single quotes from around $Part in the WHERE clause I get the following error:
1054: Unknown column 'A20133976' in 'where clause' on one of the rows. Something's wonky, as that is one of the part numbers. Although the full part number should be 'A20133976-10'

[Kurtismonger]

Just for laughs, here is the query that works fine from another part of my script.

$sql = "SELECT Production.PartNo AS Part,
		SUM(Production.QuantProd) AS Production
		FROM Production
		WHERE $deptString
			AND Production.ProdDate >= '$startDate'
			AND Production.ProdDate <= '$endDate'
		GROUP BY Part";
$result = mysql_query($sql);

[YAOMK]

have you escaped $Part? Also if the data type for that column is not numeric, you should use quotes around it.

[Kurtismonger]

I think I want to pass all those values as strings. That is what I do in all the other queries I'm using and they work correctly. I tried removing single quote on them all anyway but got a 1064 error after >= and <=.

[Kurtismonger]

YAOMK wrote:

have you escaped $Part? Also if the data type for that column is not numeric, you should use quotes around it.

The data is not always numeric so I'm using single quotes (single correct?). Part numbers examples include:
123
A123
A123-45
Not sure what you mean by escape $Part.

[YAOMK]

so that the "-" gets inserted in the database by running mysql_real_escape_string($part). When I refer to data-type I'm referring to the ones your columns where defined as, i.e. INT, VARCHAR, CHAR etc.....

[Kurtismonger]

YAOMK wrote:

so that the "-" gets inserted in the database by running mysql_real_escape_string($part). When I refer to data-type I'm referring to the ones your columns where defined as, i.e. INT, VARCHAR, CHAR etc.....

Hopefully I'm not being thickheaded here. I'm not inserting into the database, the values are already in the table. I'm just trying to find a match. I tried mysql_real_escape... but no difference. PartNo column is type VARCHAR.

[YAOMK]

No problem, I understand it can be frustrating, I been there many times myself. The reason why in my mind I think data types would make a difference is that even tho you are not inserting a thing you are calling on the values on those columns when you are comparing them. I believe $part should stay within quotes but $startDate and $endDate are a different story. By the way getting errors is better than getting nothing at all, so don't fear then and use the to your advantage.

[Kurtismonger]

YAOMK wrote:

No problem, I understand it can be frustrating, I been there many times myself. The reason why in my mind I think data types would make a difference is that even tho you are not inserting a thing you are calling on the values on those columns when you are comparing them. I believe $part should stay within quotes but $startDate and $endDate are a different story. By the way getting errors is better than getting nothing at all, so don't fear then and use the to your advantage.

The dates are in the format 2007-11-27. Like I said - I use similar queries elsewhere without a problem. If I remove the quotes from around the dates this is the error I get :
1064: 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 'AND Production.ProdDate <=' at line 5

[YAOMK]

but before you got this right?

Now I'm getting "Resource id #7' with the number incrementing for each row.

If I remove the single quotes from around $Part in the WHERE clause I get the following error:
1054: Unknown column 'A20133976' in 'where clause' on one of the rows. Something's wonky, as that is one of the part numbers. Although the full part number should be 'A20133976-10'

[Kurtismonger]

YAOMK wrote:

but before you got this right?

That was removing the quotes from $Part. The above error happens when I unquote the date variables

[Kurtismonger]

I'm not sure something else isn't wrong with my query. Even if I hard code the part number my query result is empty.
If I return the contents of $Part all the part numbers are there.

function prodLookup($Part) {
	$dbConnect = mysql_connect("localhost", "user", "pass");
	mysql_select_db("production", $dbConnect);

$Part = mysql_real_escape_string($Part);
$ProdQty = "SELECT SUM(Production.QuantProd)
		FROM Production
		WHERE Production.PartNo = '$Part'
			AND Production.ProdDate >= '$startDate'
			AND Production.ProdDate <= '$endDate'";

$result4 = mysql_query($ProdQty, $dbConnect);
echo mysql_errno($dbConnect). ": ". mysql_error($dbConnect)."<BR />";
//	$result4 = $Part; //if I uncomment, I can see the correct part numbers returned from the function call
	return $result4;
}

[YAOMK]

when you hard-coded $part did you use an alphanumeric number with a dash "-"? And you made sure there were values that satisfied your 3 conditions?

I can run this query manually and I get the result I expect.

This line from your original post is what puzzles me.

[Kurtismonger]

YAOMK wrote:

when you hard-coded $part did you use an alphanumeric number with a dash "-"? And you made sure there were values that satisfied your 3 conditions?

This line from your original post is what puzzles me.

Yes, I used a alphanumeric with dash. And the values do satisfy my conditions.

What I meant was if I go to my SQL client and enter:
SELECT SUM(Production.QuantProd)
FROM Production
WHERE Production.PartNo = "A10133986-10"
AND production.proddate >= "2007-9-1"
AND production.proddate <= "2007-9-30"

The result is exactly what I am looking for.

When I hard code the part number and dates in my script it returns
resource id #7 for each line with the number incrementing?

[Kurtismonger]

Okay, so I discovered one of my problems was that I wasn't passing the date variables into the function. I've now corrected that. I also discovered that while I was using mysql_query I wasn't doing anything with the results. For my other queries I pump them into an array and then use a loop to iterate through. In this case I know there will only be one value so I try using mysql_result but it still comes up empty. I then tried mysql_fetch_row, but all that appears in the result is "Array".

Here is the revised function:

function prodLookup($Part, $startDate, $endDate) {
	$Part = mysql_real_escape_string($Part);

if (!($link = @ mysql_connect("localhost", "guest", "pass")))
	die("Cannot connect");

if (!(mysql_select_db("production", $link)))
	die("select db failed");

$ProdQty = "SELECT PartNo
		FROM Production
		WHERE PartNo = '$Part'
			AND Production.ProdDate >= '$startDate'
			AND Production.ProdDate <= '$endDate'";

if (!($result = @ mysql_query($ProdQty, $link)))
	die("query failed");

$row = mysql_result($result);
echo mysql_errno($link). ": ". mysql_error($link)."<BR />";
//	$result4 = $Part;
	return $row;
}

[Kurtismonger]

Sometimes I just feel too stupid to live. Besides the other issues I identified in the last post I was not specifying the field in my mysql_result()
On the positive side this is my first real function so I guess I had to earn it through blood, sweat and tears. My head hurts.

YAOMK thanks for your assistance in helping me work through my idiocy. At least I learned some error handling.

Here is the final code for anyone that cares:

function prodLookup($Part, $startDate, $endDate) {
	$Part = mysql_real_escape_string($Part);
	if (!($link = @ mysql_connect("localhost", "guest", "pass")))
		die("Cannot connect");

if (!(mysql_select_db("production", $link)))
	die("select db failed");

$ProdQty = "SELECT SUM(quantprod) AS Qty
		FROM Production
		WHERE PartNo = '$Part'
			AND Production.ProdDate >= '$startDate'
			AND Production.ProdDate <= '$endDate'";

if (!($result = @ mysql_query($ProdQty, $link)))
	die("query failed");

$row = mysql_result($result, "Qty");  //Insert the Qty field you dolt!
return $row;
}