ncncnc

Thanks a lot.

Am I right in think that setting Name as a Unique Constraint could solve this?

BTW I'm sorry I just realised that my code is more like Pseudo-code. I typed it quickly from memory, but I'm sure you see what I'm trying to do. I don't believe I can use the DUPLICATE command in SQL Server.

Hi I should have been more clear I'm not using mySQL I'm using SQL Server. INSERT INTO products VALUES ($nameNew, $newID, $newPrice) ON DUPLICATE IGNORE Is it as simple as this? I'm unable to test because I'm away from my workstation. Thanks.

Hi, I'm making a website that uses data stored in SQL. One of my pages allows the user to enter a new product into a product table. I was wondering if somebody could help me with some validation or Query that will not allow the user to enter a product name that already exists in the table? $nameNew = $_POST['new]; $nameID = $_POST['newID]; $namePrice = $_POST['newPrice]; INSERT INTO products VALUES ($nameNew, $newID, $newPrice) $describeQuery = "SELECT ID, Name, Price FROM Products"; $results = sqlsrv_query($conn, $describeQuery); echo '<table border="1" BORDERCOLOR=Black>'; echo '<tr><th bgcolor = "LightBlue">Name</th><th bgcolor = "LightBlue" >ID</th> <th bgcolor = "LightBlue" >Price</th></tr>'; while($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) { echo '<tr>'; echo '<td >' .$row['Name'].'</td>'; echo '<td>' .$row['ID'].'</td>'; echo '<td>' .$row['Price'].'</td>'; echo '</tr>'; } echo '</table>'; sqlsrv_close($conn); Thanks

Hi all, I've made a webpage that displays data taken from SQL. I'm trying to display two seperatre tables side by sie. I can currently display them side by side but one is below and to the side of the other. I wan't them right next to each other. Can somebody help? echo '<table border="1" BORDERCOLOR=Black style=width:300px >'; echo '<tr><th bgcolor = "LightBlue">Name</th><th bgcolor = "LightBlue">Sales Volume</th><th bgcolor = "LightBlue">Year</th></tr>'; while($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) { echo '<tr>'; echo '<td >' .$row['NAME'].'</td>'; echo '<td>' .$row['SalesVolume'].'</td>'; echo '<td>' .$row['Year'].'</td>'; echo '</tr>'; } echo '</table>'; echo '<table border="2" BORDERCOLOR=Black style=float:left;margin-left:400px; width:300px>'; echo '<tr><th bgcolor = "Black">ID</th><th bgcolor = "Black" >Name</th> <th bgcolor = "Black" >Price</th></tr>'; while($row = sqlsrv_fetch_array($results2, SQLSRV_FETCH_ASSOC)) { echo '<tr>'; echo '<td >' .$row['ID'].'</td>'; echo '<td>' .$row['Name'].'</td>'; echo '<td>£' .$row['Price'].'</td>'; echo '</tr>'; } echo '</table>'; sqlsrv_close($conn); ?> I've been trying to position the table using margins.

I want it to update the databse with the new data the user has inputted from the form.

Hi all, I'm trying to make a webpage that allows the user to enter new sales figures for a certain product in a chosen month of a chosen year. I thought my code was correct but it doesn't really do anything. It just displays the table but it's not updated. Can anybody spot an error? $describeQuery = "SELECT p.ID, p.NAME, dt.[Year], dt.[Month], dt.SalesVolume from Products p join (select ProductCode, sum(SalesVolume) as SalesVolume, [Month], [Year] from MonthlySales where [Year] = '$desiredYear' AND [Month] = '$desiredMonth' group by ProductCode, [Year], [Month])dt on dt.ProductCode = p.ID"; $editQuery = "UPDATE MonthlySales SET SalesVolume = '$newSales' WHERE Month ='$desiredMonth' AND Name = '$desiredProduct' AND Year = '$desiredYear'"; $results = sqlsrv_query($conn, $describeQuery); $resultsx = sqlsrv_query($conn, $editQuery); echo '<table border="1" BORDERCOLOR=Black>'; echo '<tr><th bgcolor = "LightBlue">Name</th><th bgcolor = "LightBlue" >ID</th> <th bgcolor = "LightBlue" >Sales</th><th bgcolor = "LightBlue" >Month</th> <th bgcolor = "LightBlue" >Year</th> </tr>'; while($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) { echo '<tr>'; echo '<td >' .$row['NAME'].'</td>'; echo '<td>' .$row['ID'].'</td>'; echo '<td>' .$row['SalesVolume'].'</td>'; echo '<td>' .$row['Month'].'</td>'; echo '<td>' .$row['Year'].'</td>'; echo '</tr>'; } echo '</table>'; while($row = sqlsrv_fetch_array($resultsx, SQLSRV_FETCH_ASSOC)) { } sqlsrv_close($conn);

Of course. Thanks for the help.

What I'm trying to do is make a webpage that dyanically updates the name of a product which is displayed in a table. The user enters the name of an existing product into a form, then enters what they want the name to be changed to. I want the table to update and show the new updated data. My current code just prints out the product list and doesn't change the name after the user has filled in the form.

In that case I don't know what the problem is because I'm getting no error messages.

Hi all, I'm trying to make a query that allows the user to edit the name of a entry in SQL. I believe I have the corect Syntax but I'm not sure if I have the code set up correctly. I am using 2 queries when I think it is possible with one. The first query is used to Pint out results for a certain year and the second to update the names. $desiredYear = $_POST['year']; $nameEdit = $_POST['edit']; $origName = $_POST['orig']; echo ' The year you have chosen is '.$desiredYear; $describeQuery = "SELECT ID, Name, (SELECT SUM(SalesVolume) as SalesVolume FROM MonthlySales WHERE ProductCode=Products.ID AND Year = '$desiredYear') AS num_sales FROM Products"; $editQuery = "UPDATE Products SET Name = '$nameEdit' WHERE Name ='$origName'"; $results = sqlsrv_query($conn, $describeQuery); echo '<table border="1" BORDERCOLOR=Black>'; echo '<tr><th bgcolor = "LightBlue">Name</th><th bgcolor = "LightBlue" >ID</th> <th bgcolor = "LightBlue" >Sales</th></tr>'; while($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) { echo '<tr>'; echo '<td >' .$row['Name'].'</td>'; echo '<td>' .$row['ID'].'</td>'; echo '<td>' .$row['num_sales'].'</td>'; echo '</tr>'; } echo '</table>'; sqlsrv_close($conn); Can somebody help me with this? Thank you

Thanks a lot, It's working now.

Array ( [0] => Array ( [0] => 42S22 [sqlSTATE] => 42S22 [1] => 207 => 207 [2] => [Microsoft] [sql Server]Invalid column name 'prod_id'. [message] => [Microsoft][sql Server Native Client 10.0][sql Server]Invalid column name 'prod_id'. ) )

Nope, I'm still getting the same error. Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in C:\RDEUsers\NET\400792\4.php on line 31 I have a feeling it's something simple like the wrong type of brackets or something, but I've tried a few things and it doesn't like any of them.

Hello agian, I've had chance to test this code and it doesn't like it. I'm getting this: Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in C:\RDEUsers\NET\400792\3.php on line 37 This must be something to do with the query right? $describeQuery = "SELECT ID AS prod_id, Name, (SELECT COUNT(*) FROM MonthlySales WHERE ProductCode=prod_id AND Year = 1990) AS num_salesFROM Products"; $results = sqlsrv_query($conn, $describeQuery); echo '<table border="1" BORDERCOLOR=Black>'; while($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) { echo '<tr>'; echo '<td >' .$row['Name'].'</td>'; echo '<td>' .$row['ProductCode'].'</td>'; echo '<td>' .$row['Year'].'</td>'; echo '</tr>'; } echo '</table>'; sqlsrv_close($conn); Any ideas?

Thanks a lot! It works now.

Thanks a lot for the reply. It's on the right track but now when I try and filter it it get this error message: Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in C:\RDEUsers\NET\400792\1.php on line 51 I know this means there's shomething wrong with my query. The code I'm trying is $describeQuery="select ProductCode , SUM(SalesVolume) as SalesVolume, Year from MonthlySales GROUP BY ProductCode, where year = 1990"; Any ideas?

Hi all, I'm working on a webpage that dynamically shows sales of products over a number of years. It's going well apart from the data isn't showing as I want it to. The data is showing the number of sales per month in a given year. What I want to do is add up all sales of the same product within a year to give one row for each product for each year. The below screenshot shows what my current output is. I want to be able to have one row showing sales all year rather than month by month. <?php error_reporting(E_ERROR | E_PARSE); $desiredProduct = $_POST['product']; $server = 'SQL2008'; $connectionInfo = array( "Database"=>"rde_400792"); $conn = sqlsrv_connect($server,$connectionInfo); $describeQuery="select ProductCode , SalesVolume, Year, Month from MonthlySales where ProductCode = $desiredProduct"; $results = sqlsrv_query($conn, $describeQuery); echo '<table border="1" BORDERCOLOR=Black>'; echo '<tr><th bgcolor = "LightBlue">Product Code</th><th bgcolor = "LightBlue" >Sales Volume</th><th bgcolor = "LightBlue">Month</th><th bgcolor = "LightBlue">Year</th></tr>'; while($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) { echo '<tr>'; echo '<td >' .$row['ProductCode'].'</td>'; echo '<td>' .$row['SalesVolume'].'</td>'; echo '<td >' .$row['Month'].'</td>'; echo '<td>' .$row['Year'].'</td>'; echo '</tr>'; } echo '</table>'; sqlsrv_close($conn); Can somebody help me?

Thanks a lot. I'll give this a go and get back to you. Is this based on the fact that relationships have been set up in SQL? or will it work regardless? I assume this is the same formula for any combination of things from tables?

Hi Thanks for the reply. I'm unable to access my databases as I am at work, but I can tell you the headers from each table. Sales table: ID, ProductCode, Month, Year, SalesVolume. Product Table: ID, Name, Price. Product code in Sales and ID in Product are the same thing. So on my website I want to run a query that shows the name of a product (from product) and what it has sold in a certain year (from sales). If this information is not sufficient for you, let me know and I will have to wait until I next get the opportunity to access my code.

Hi all, I'm currently working on a webpage that dynamically shows tables of sales for products. I'm using SQL to store 2 tables, one containing product info (name, price etc.) and one containing sales for each product. One on of my web pages I want to show the product name and sales for a single year. This would involve ripping the product name from my product table and the sales and year from my sales table. Is this possible in one query? $Query= "select name from productTable and sales, year from salesTable where year = $desiredyear; Obviously I know this is wrong because it isn't working but am I even on the right lines? Thank you.

Thanks for explaining that, I had no idea. I'll give it a go tomorrow and let you know how it goes. Can you see any other reason why this wouldn't work?

Thanks a lot for the replies guys. Year is a four-byte signed integer. This is what I was told to make it in the tutorial. Do you mean to make it look like this? $describeQuery="select year, force, homicide from crime where year = $desiredyear "; I'm unable to test this now because I'm at work, but could it really be that simple?