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

Fetching array then reversing it
Hi, I have a simple problem, I'll try to explain it as best I can:
News entries in my database ar

add 20 000 to the rowcnt
Hi ...
I need to add 20 000 to the row count but just cant find a way to do this i am new to php

Referential Integrity problem when inserting into 2 tables at same time
Hello Everyone,

Im relativity new to PHP and MySQL and i have come up against a problem. i h

Help! refer to a friend script with captcha code
Hi guys, I am posting on here in desperate need for some help with an ongoing search I have been doi

How to separate records and ORDER BY
Let say i have 100 records and displayed in one page, then i want to get the records from 50-100 and

Dynamic Data + Sql Server 2005 Enterprise?
Hi! I have just started to learn ASP.NET, and it looks like it is quite a lot to learn. Im not reall

Members Only
Hi all, for my website i have a members area only which on members can veiw, but at the moment anyon

single page with referrer to show correct page
I have a simple website with a couple of different pages. I would like to be able to in a div tag w

Auto-populating dropdowns and multiple forms.
Here's what I have so far:

First drop down = select a state (works)
This populates the sec

ALV List Display to point to another report on Double Click
Hi,

I want my ALV List Display to point to another report on Double Click on its line ite