kris1988edwards

Good morning, I am trying to return specific values depending on the value of the WJCStatusID, for example if WJCStatusID = < 4 but > 2 return "In production" SQL QUERY: SELECT tblWJCItem.AddedDescription, tblWJC.WJCPrefix + Convert(Varchar(10),tblWJC.WJCNo) AS OurRef, tblWJCItem.MaterialName, tblStockFamily.StockFamily, tblWJCItem.WeightToSend, tblWJC.DateCreated, tblWJC.WJCStatusID FROM tblWJC INNER JOIN tblWJCItem ON tblWJC.WJCID = tblWJCItem.WJCID INNER JOIN tblStockFamily ON tblWJCItem.ProductFamilyID = tblStockFamily.StockFamilyID IF (tblWJC.WJCStatusID) < 2 THEN 'Pre Production' ELSE IF (tblWJC.WJCStatusID) < 4 THEN 'In Production' ELSE IF (tblWJC.WJCStatusID) > 4 THEN 'Ready To Ship' ELSE 'Awaiting Lab Results'; I am quite new to the world of mssql so I maybe doing something wrong which is quite simply to fix. Can any body help?

When I run the SQL directly on the server it runs and returns results fine its just when it goes into the php script

Hello, I am trying to run a report as per the below mssql query: $query = "SELECT tblWJCItem.AddedDescription, tblWJC.WJCPrefix, tblWJC.WJCNo, tblWJCItem.MaterialName, tblStockFamily.StockFamily, tblWJCItem.WeightToSend, tblWJC.DateCreated, tblWJC.WJCStatusID FROM tblWJC INNER JOIN tblCustomer ON tblWJC.CustomerID = tblCustomer.CustomerID INNER JOIN tblWJCStockStatus ON tblWJC.WJCStockStatusID = tblWJCStockStatus.WJCStockStatusID INNER JOIN tblStockStatus ON tblWJC.WJCID = tblStockStatus.WJCID LEFT OUTER JOIN tblWJCProductLine ON tblWJC.WJCID = tblWJCProductLine.WJCID LEFT OUTER JOIN tblWJCStockItem ON tblWJCProductLine.WJCProductLineID = tblWJCStockItem.tblWJCStockItem INNER JOIN tblStockFamily ON tblWJCItem.ProductFamilyID = tblStockFamily.StockFamilyID WHERE tblCustomer.CustomerName = 'NAME' AND tblWJCStockStatus.WJCStockStatus <> 'Stock Usage Confirmed' ORDER BY tblWJC.WJCID"; and then fetch the results by the following php code: $result = sqlsrv_query($conn, $query); while($row = sqlsrv_fetch_object($result)){ echo $row->AddedDescription .",".$row->WJCPrefix .",".$row->WJCNo.",".$row->MaterialName.",".$row->StockFamily.",".$row->WeightToSend.",".$row->DateCreated->format('d-m-Y').",".$row->WJCStatusID ."<br />"; } The connection to the database does get established but no results come through, i've got error reporting on in my php and the following message gets given: Warning: sqlsrv_fetch_object() expects parameter 1 to be resource, boolean given in C:\inetpub\wwwroot\kris\abs\test\rollsroyce.php on line 31 Line 31 is the while($row = sqlsrv_fetch_object($result)) Can anyone help with sorting this out its really starting to annoy me now. .

@barand - no it doesn't seem to work I've added in error reporting and getting the following: Notice: Undefined variable: sqlsrv_query() expects parameter 1 to be resource Connection to the database and everything is working correctly.

Good morning, I am doing a small project including google charts API but I am having trouble getting my results into the json format that is required. Google's documentation states the json format should be: { "cols": [ {"id":"","label":"Topping","pattern":"","type":"string"}, {"id":"","label":"Slices","pattern":"","type":"number"} ], "rows": [ {"c":[{"v":"Mushrooms","f":null},{"v":3,"f":null}]}, {"c":[{"v":"Onions","f":null},{"v":1,"f":null}]}, {"c":[{"v":"Olives","f":null},{"v":1,"f":null}]}, {"c":[{"v":"Zucchini","f":null},{"v":1,"f":null}]}, {"c":[{"v":"Pepperoni","f":null},{"v":2,"f":null}]} ] } the query works perfectly fine thats not the problem but getting it into the above format is proving hard than I expected. The below is what I am trying to do to populate the json data in the correct format. function graphdata() { $array['cols'][] = array('type' => 'string'); $array['cols'][] = array('type' => 'string'); $array['cols'][] = array('type' => 'string'); $result = sqlsrv_query($conn, $query); while($row = sqlsrv_fetch_object($result)){ $array['rows'][] = array('c' => array( array('v'=>'$row->DateCreated->format('d-m-Y')'), array('v'=>$row->UnitPrice)) ); } return $array; } print json_encode(graphdata()); I am using MSSQL and not the normal mysql hence the above code, i am a starter when it comes to php so I may be doing something basic totally wrong but I cannot seem to get it to work. Thanks

Hi, Not found any problems with the javascript any ideas?

No I haven't jcbones ... I will try this out and report back if i have any issues or can't find the problem

Good afternoon, I am working on a project that gives the user a data table and a google chart (using the api) based on what the user selects for a <select> <option>. Index.PHP code: <form> <select name="users" onchange="showUser(this.value);drawChart();"> <option value=""> Select a Metal: </option> <?php //connection details $query = "SELECT TOP(31) tblMetalPrice.MetalSourceID, tblMetalSource.MetalSourceName from tblMetalPrice INNER JOIN tblMetalSource ON tblMetalPrice.MetalSourceID=tblMetalSource.MetalSourceID ORDER BY tblMetalPrice.DateCreated DESC "; $result = sqlsrv_query( $conn, $query); while( $row = sqlsrv_fetch_object ($result)) { echo "<option value='".$row->MetalSourceID ."'>". $row->MetalSourceName ."</option>"; } sqlsrv_close( $conn); ?> </select> </form> <div id="chart_div"></div> <div id="txtHint"><b>Past metal information will be generated below.</b></div> this works fine and generates the list in the select option dropdown Script to get table contents: <script> function showUser(str) { if (str=="") { document.getElementById("txtHint").innerHTML=""; return; } if (window.XMLHttpRequest) { // code for IE7+, Firefox, Chrome, Opera, Safari xmlhttp=new XMLHttpRequest(); } else { // code for IE6, IE5 xmlhttp=new ActiveXObject("Microsoft.XMLHTTP"); } xmlhttp.onreadystatechange=function() { if (xmlhttp.readyState==4 && xmlhttp.status==200) { document.getElementById("txtHint").innerHTML=xmlhttp.responseText; } } xmlhttp.open("GET","scripts/gettabledata001.php?q="+str,true); xmlhttp.send(); } </script> this also works fine and generates the table contents based on 'q' value from the select dropdown. Google API script: <script type="text/javascript"> // Load the Visualization API and the piechart,table package. google.load('visualization', '1', {'packages':['corechart']}); google.setOnLoadCallback(drawChart()); function drawChart() { var jsonData = $.ajax({ url: "scripts/getgraphdata.php", dataType:"json", data: "q="+num, async: false }).responseText; // Instantiate and draw our pie chart, passing in some options. var chart = new google.visualization.LineChart(document.getElementById('chart_div')); chart.draw(data, {width: 400, height: 240}); } </script> getgraphdata.php script: $q = intval($_GET['q']); ini_set('display_errors',1); ini_set('display_startup_errors',1); error_reporting(-1); $query ="SELECT TOP(30) tblMetalPrice.MetalSourceID, tblMetalPrice.DateCreated, tblMetalPrice.UnitPrice, tblMetalPrice.HighUnitPrice, tblMetalSource.MetalSourceName FROM tblMetalPrice INNER JOIN tblMetalSource ON tblMetalPrice.MetalSourceID = tblMetalSource.MetalSourceID WHERE tblMetalPrice.MetalSourceID = '".$q."' ORDER BY tblMetalPrice.DateCreated DESC"; $result = sqlsrv_query($conn, $query); echo "{ \"cols\": [ {\"id\":\"\",\"label\":\"Date\",\"pattern\":\"\",\"type\":\"string\"}, {\"id\":\"\",\"label\":\"Unit Price\",\"pattern\":\"\",\"type\":\"number\"} ], \"rows\": [ "; $total_rows = sqlsrv_num_rows($result); $row_num = 0; while($row = sqlsrv_fetch_object($result)){ $row_num++; if ($row_num == $total_rows){ echo "{\"c\":[{\"v\":\"" . $row->DateCreated->format('d-m-Y') ."\",\"f\":null},{\"v\":" . $row->UnitPrice . ",\"f\":null}]}"; } else { echo "{\"c\":[{\"v\":\"" . $row->DateCreated->format('d-m-Y') ."\",\"f\":null},{\"v\":" . $row->UnitPrice . ",\"f\":null}]}, "; } } echo " ] }"; sqlsrv_close( $conn); When ever i try these they don't work the getgraphdata.php scripts runs fine the issue I believe but may be totally wrong may be done to the google api script that should generate the chart but doesn't. Can anyone help here I've been trying to sort this for a few days now and losing confidence in myself rapidly. Thanks Kris

Good morning, I am trying to convert a mssql query into json format so that I can then later pass this through google's visualisation api. The query and encoding seems to be working but the encode returns NULL. I have checked the normal gotcha's of making sure its utf8 encoded and that I have used a version of PHP that has the encode (using php 5.3.19). Can any one help me with getting the encode to work. PHP CODE: <?php //connection and database details (known to be working) if( $conn ) { echo "Connection established.<br><br>"; }else{ echo "Connection could not be established.<br />"; die( print_r( sqlsrv_errors(), true)); } $query = "SELECT * FROM tblProducts"; $result = sqlsrv_query( $conn, $query); while( $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_NUMERIC) ); $arr = array($result); $encodedarray = array_map(utf8_encode, $arr); echo json_encode($encodedarray); sqlsrv_close( $conn); ?> When I run this code on the server it comes back with: Connection established. [null] Has anyone got any ideas of getting this to work? Thanks Kris

Hi all, I am trying to do a query on a database that takes a variable from a html drop down box. I've tried so many different forums and can't find the answers. HTML PAGE: <html> <head> <script> function showUser(str) { if (str=="") { document.getElementById("txtHint").innerHTML=""; return; } if (window.XMLHttpRequest) { // code for IE7+, Firefox, Chrome, Opera, Safari xmlhttp=new XMLHttpRequest(); } else { // code for IE6, IE5 xmlhttp=new ActiveXObject("Microsoft.XMLHTTP"); } xmlhttp.onreadystatechange=function() { if (xmlhttp.readyState==4 && xmlhttp.status==200) { document.getElementById("txtHint").innerHTML=xmlhttp.responseText; } } xmlhttp.open("GET","getuser.php?q="+str,true); xmlhttp.send(); } </script> </head> <body> <form> <select name="users" onchange="showUser(this.value)"> <option value="">Select ID:</option> <option value="1">Boston</option> <option value="2">NYC</option> <option value="3">UK</option> <option value="4">Paris</option> </select> </form> <br> <div id="txtHint"><b>Info will be listed here</b></div> </body> </html> PHP CODE: <?php $q = intval($_GET['q']); //connection details // db details $query ="SELECT * FROM tblExample WHERE id = '".$q."'"; $result = sqlsrv_query( $conn, $query); echo "<table> <tr> <th> Name</th> <th> Address</th> <th> Department</th> <th> Salary</th> <th> Bonus</th> </tr>"; while( $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ARRAY) ) { echo "<tr><td>".$row[Name]."</td><td>".$row[Address]."</td><td>".$row[Department]."</td><td>".$row[salary]."</td><td>".$row[bonus]."</td></tr>"; } echo "</table>" sqlsrv_close( $conn); This doesn't seem to work, is there any way some can help with sorting this. I want to basically select a load of data that matches the query. There doesn't seem to be loads of help for this.