April 11, 2012 at 5:51 am
Hi Guys,
I am currently working on a website that deals with sales of products. For one of my pages for the website I need it to be able to change the current sales information for a specific product.
The top part of the following code selects the specific product however I cannot get the update query to work.
Can anybody help please?
Thank you
$describeQuery = "SELECT p.ID, p.NAME, dt.[Year], dt.[Month], dt.SalesVolume FROM Products p
join
(select ProductCode, sum(SalesVolume) as SalesVolume, [Year], [Month] from MonthlySales
group by ProductCode, [Year], [Month])dt
on dt.ProductCode = p.ID WHERE [NAME] = '$desiredProduct' AND [Year] = '$desiredYear' AND [Month] = '$desiredMonth'";
$editQuery = "UPDATE MonthlySales SET SalesVolume = '$NewSales' WHERE ID = '$desiredProduct' AND Year = '$desiredYear' AND Month = '$desiredMonth'";
$query = sqlsrv_query($link, $describeQuery);
April 11, 2012 at 6:21 am
I can't see how the $Variables are being generated within the code, but the basic UPDATE syntax looks right. So what do you mean by "does not work"?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 11, 2012 at 6:37 am
As in the new sales figures are not being updated when I enter them. It is not doing anything at all. Im pretty certain im on the right lines i think it will just be something simple.
April 11, 2012 at 7:01 am
Not seeing where the $NewSales is coming from, maybe that's it. It's the values passed or the WHERE clause. One or the other. The T-SQL syntax looks proper.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 15, 2012 at 1:22 pm
Have you run a profiler trace on this when your trying to update your DB?
***The first step is always the hardest *******
April 15, 2012 at 1:55 pm
Is this PHP with a MySQL backend?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2012 at 6:18 am
The Update Syntax seems fine.
Have you tested the Select Query??....If the Select Query works fine then I think that $desiredProduct, $desiredYear and $desiredMonth would not be the ones causing problems as they being used in the Select Query as well.
What is left is $NewSales....but I won't doubt it to cause problems because I think its coming from where all the other above mentioned variables are coming and I assume that all the variables are being passed correctly to the query.
The next part I don't understand is the "$query = sqlsrv_query($link, $describeQuery);" part. You'll have to elaborate a little more on it. Because if I go by common logic.....I would ask you that you have "$describeQuery" here but you don't have "$editQuery".
That makes me think that the Update might not be working because of the absence of "$editQuery".
These are all just assumptions because that's how PHP or whichever language that is appears to SQL people. So, we could provide better solutions if you could explain what every part of the code is doing.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply