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

do while...woes
I'm sure this is childsplay but it is stumping me, everytime i try what I think would make this work

Page Reload
I am having issues getting a page to reload after I submit data to a database. This form should inp

Warning: session_start() headers already sent error - Driving me Nuts!
I am trying my sister in laws site and I keep getting an error with my coding. I am more of a design

How To Make More Than One Redirection with PHP on the same page?
Hey im trying to do a direction page where it open differently link direction pages every time som

Display thumbnails as square while retaining aspect ratio
I am trying to figure out a way to make an image display as a square, for example 80x80 pixels, when

Error querying database.
I get the above error when trying to insert some values to a datatable.
Here's the code :

Strange array issue, never happened before.
mysql_fetch_array returns 1 array per call. Generally that's why it is inserted into a while statem

Fetching META TAGS through
Hello everybody
I want to fetch meta tags of a domain.
It will be done from following code of

DOMDocument parsing
Hello, I am parsing an xml file from an API which I have converted into a DOMDocument in php. This i

weird problem
last time, i did post a topic concerning why my page sometime will load as blank page when using IE.