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

help countdown timers
hello every one,

I'd like to know how to insert many countdowns in the same page.
The dura

Selecting an "empty" date formated field
How do you select an "empty" date field? I've tried the few ways I can think.
SQL> select

Validation
Hello

I know this is a really vag question.

I need to have a input field on a form th

Using loop to count number of entries
I'm writing a program that must ask user to type in numbers. After each entry, the program has to re

Javascript or not?
How many people prefer javascript/ajax sites? How many prefer the good old fashion straight php sit

Tree Menu
Hi guys,

Can anyone help me making a tree navigation system? I have a site where I'm allowin

using variables in another page
I have a test database set up on localhost. I have a form that I can type a name into, hit the butto

multipart emiail forms
Hi All,

I am new to the boards and I've been working on a form (which is rather massive, imo

WORDPRESS - Multiple Loops Only Going to One Post?
I have various pages with multiple loops and i need them to be individual to all other categories.

frames get header location..
i have two frames, one top, one bottom. how do i do a form on the bottom frame that gives me the ur