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

how to remove in php string display
My code is $message="We're looking at developing our website."; $body= "My mess

Multiple Options for a Single Page
For this example I want to use the Handlers option which is under Fed Admin and all the related codi

How can I get my program to read a larger array ??
Hi:

I have this program that reads from an array to do calculations. The array are just t

PHP login form help (Done Most of It)
Hi i am having a problem, when i try logging in it is always saying "Invalid Login" im not

Time-based image rotation script
I'm trying to write a PHP script that rotates an image based on what time of day it is. I want the

for loop without same $i digits?
Hi,

Can some guru please advise how can I accomplish this with a simple for loop ?

I

Generate multilayered array from string.
ok so i have a string that looks like this:
Code: [Select]blog:edit_all,delete_all|users:edit_all

How can I Compare two xml documents?
Hi all,

I am doing a POC for my project and I am using XE database 10g version.

problem getting my contact form working
Yeah I know this is a pretty basic problem, but it's been a while since I've worked with PHP and I'm

Just wondering what you think about my site
I am new to phpfreaks i just seen this form and thought it would be cool to see what other thought a