Query failed issue with php script but works fine in mssql manager!
Posted on
16th Feb 2014 07:03 pm by
admin
hi i have the script below which copies data from one table to another but will only insert new data update current data or delete old data from tempproducts to products then it will delete the tempproducts from the db however i keep getting this error:
Warning: mssql_query() [function.mssql-query]: Query failed in E:UpdateProducts.php on line 33
updateproducts.php
Code: [Select]<?php
include('../../otherscripts/functions.php');
$log = new Logging();
// create DB connection
$host = "localhost";
$user = "user";
$pass = "pass";
$mydb = "db";
$db = mssql_connect($host,$user,$pass);
//Select Database
mssql_select_db($mydb);
// delete all old data
$sql0 = "SELECT * FROM tempproduct";
$sql1 = "INSERT INTO products SELECT * FROM tempproduct WHERE manf_part_no NOT IN (SELECT manf_part_no FROM products) AND supp_id NOT IN (SELECT supp_id FROM products)";
$sql2 = "DELETE FROM products WHERE manf_part_no NOT IN (SELECT manf_part_no FROM tempproduct) AND supp_id NOT IN (SELECT supp_id FROM tempproduct)";
$sql3 = "UPDATE p1 SET p1.avail_qty = t1.avail_qty, p1.cost_price = t1.cost_price, p1.rrp = t1.rrp, p1.date_added = t1.date_added, p1.description = t1.description FROM Products p1 INNER JOIN tempproduct t1 ON (p1.manf_part_no = t1.manf_part_no AND p1.supp_id = t1.supp_id)";
$sql4 = "TRUNCATE TABLE tempproduct";
//If tempproduct is empty done Execute Commands if it is full then execute commands
$query = mssql_query($sql0) or die($log->lwrite('Failed to select for count from db'));
$rowcount = mssql_num_rows($query);
if($rowcount == 0){
$log->lwrite('Teh tempproduct am emptyish');
}
else{
mssql_query($sql1) or die($log->lwrite('Failed to insert to db'.$sql1));
mssql_query($sql2) or die($log->lwrite('Failed to Delete from db'));
mssql_query($sql3) or die($log->lwrite('Failed to Update db'));
mssql_query($sql4) or die ($log->lwrite('Failed to TRUNCATE db'));
}
?>
if i run $sql1 command in the sql manager it runs fine and no errors occur?
Your Answer:
Login to answer
303 
20 
Other forums
BI in Upstream Production operations
Appreciate if you can assist in the following areas:
1) Examples of life before and after BI i
comparing tables across databases sql refinement ideas required
Hi all
Via pl/sql I need to ensure that data between tables in different databases match
Create a form of 2 numbers input and find the greatest.
Hi, everybody.
I have a homework in my training of php, which ask you to make a form that ask
Best way to cross matching large datasets
Hi,
Im running a script where am I cross matching about 200 000 data sets with each other. Ea
update 2 columns by doing inner 2-column query
Hi,
is something like this possible?
update contract_all set col1,col2 =
(
php sessions,logouts & the bloomin back button!
Hi All,
I've got a cms that members can log into. When they logout, the session is destroyed,
private constructor
Hello,
Can we create a constructor as private? If yes, what is the use of it? If no, why can'
MySQL issue
I am taking sentences through a form on page. Then checking some condition and trying to insert them
Passing an array of values From VB6.0 to Oracle PL/SQL Function
Basically we are using VB6.0.
When we try to pass an array from VB6.0 to an Oracle PL/SQL
array_map() probably obvious mistake
The code below is part of a class to escape strings, but should also accept an array, using array_ma
