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

need to add "sizes" to shopping cart
Hey guys, I am trying to figure out a way to add a "size" selector on to this bit of code.

php web service error
hey guys,
I'm working on a project requires the use of web services. I've been trying a few tutor

How to insert random unique values in 2 columns ?
I have table with 2 columns

Create Table code_for_code (
first_code varchar2(10) uni

Which practice of iteration through containers is preferred
In the "real world" what kind of loop do most people use to iterate through a container like a vecto

grouping within a foreach?
hello all,

I currently have a list of products spit out from my db via a foreach loop. Right

update post issues
I am trying to create an update to a post function, while the update does occur, the page routing an

PHP hyperlinks generator - HELP plz
Hi

I need some help to get this done using php:


1 - I have few hyperlinks say 500

remove innitial

and

tags
i am using tiny_mce as a text editor for my CMS.
buy now the problem is it add <p>

Working with popups and such in a class
I've recently started building my applications completely enclosed in classes. I like the fact that

query based on 2 conditions
I want to select if the doc_type is either s OR f but this doesn't work:

Code: [Select]$query