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: <?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

Other forums

Creating web pages by php
Hello again,
I was just wondering us there a way to use a php script to create a new web page. I

Deleting pointers froms vector?
Hi. I have a vector filled with pointers and I want to delete the pointers. I could do a loop and do

Problem with php's rename function
So, i'm having a problem with the php rename function. Basically my script 'delete_user.php' attempt

php ajax - two drop down list with a submit button
i have created a form with 2 drop-down list and a submit button. I would like to know how to pass th

Price in PR
Dear Experts,
Why we have to enter the price in the PR, and why there is no free goods indicato

Help with Password Encoding/Decoding?
Trying to design a "change password" tool. On my signup code I'm using base64_encode, now

how to export excel file in same server
My first post - php newbie, so appreciate your support.

I'm currently using headers to save w

Comparing two dates
Hi Guys
I have two dates I want to compare. Below is my current implementation. The problem with

Get word number x from string?
How can I use a function to loop through a string, and "have a look at" every word in the

C problem struct and string
Hi guys,

This is my first post and I'm hoping the experts here can help me solve my problem.<