Solved

PHP & MSSQL - Problem with basic query not working...

Posted on 2010-08-19
8
238 Views
Last Modified: 2013-12-13
I have a problem while trying to run a SQL query within PHP - i'm sure its worked previously but i just cant get it to go anymore..!

What i'm trying to do is to copy one table to another, with the results filtered - but for testing purposes only, i've reduced the code down to simply copy one table to another, but it still isnt working!

It seems to do something though - before running this code i have been manually dropping the table, after the code has then been run the table 'SugJobs2' DOES exist, but with no data.

The table 'SugJobs' contains 6592 rows of data (so i'm not copying from an empty table!)

If i run the same query within Management Studio it runs without any problems and the data is in the new table.

Anyone have any ideas where i am going wrong?

Thanks
<?php



// MSSQL Server

$SQLSERV = "SERVERNAME";

// MSSQL Username

$SQLUSER = "USERNAME";

// MSSQL Password

$SQLPASS = "PASSWORD";



$link = mssql_connect($SQLSERV, $SQLUSER, $SQLPASS);



if (!$link || !mssql_select_db('CoA', $link)) {

    die('Unable to connect or select database!');

}



$query1 = mssql_query("

SELECT * INTO SugJobs2

	FROM SugJobs

");



$query1;



?>

Open in new window

0
Comment
Question by:Bergstr
  • 5
  • 2
8 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33473553
Just for kicks, try this
<?php



// MSSQL Server

$SQLSERV = "SERVERNAME";

// MSSQL Username

$SQLUSER = "USERNAME";

// MSSQL Password

$SQLPASS = "PASSWORD";



$link = mssql_connect($SQLSERV, $SQLUSER, $SQLPASS);



if (!$link || !mssql_select_db('CoA', $link)) {

    die('Unable to connect or select database!');

}



$query1 = mssql_query("SELECT * INTO CoA..SugJobs2 FROM CoA..SugJobs");



//$query1;

$count = mssql_query('SELECT count(*) FROM CoA..SugJobs');

$row = mssql_fetch_array($count);

echo $row[0];

mssql_free_result($count);



echo "<br>";



$count = mssql_query('SELECT count(*) FROM CoA..SugJobs2');

$row = mssql_fetch_array($count);

echo $row[0];

mssql_free_result($count);

?>

Open in new window

0
 
LVL 3

Expert Comment

by:LDH
ID: 33473634
Does the provided user has proper rights to the other table?
0
 

Author Comment

by:Bergstr
ID: 33474036
Tried the above code and the output is:

6592
0

Just checked the rights by logging into Management Studio with the same username / pass as im using in PHP, and running the same SELECT INTO code - works fine..!

SELECT * INTO CoA..SugJobs2 FROM CoA..SugJobs

Output:
(6592 row(s) affected)
0
 

Author Comment

by:Bergstr
ID: 33474086
Confused! it seems to be working all of a sudden, but with no changes...

After running the "SELECT * INTO CoA..SugJobs2 FROM CoA..SugJobs" line in management studio, i removed the "SELECT INTO" line from the php page and re-run it just to confirm it connected to the SugJobs2 table and was reading the same number of lines from both tables - it worked and the result was

6592
6592

(as expected)

i then dropped the table within MS, and refreshed the php page, worked!
double checked the tables in MS and it seems to be displaying everything now...

Just going to run a few more queries in php to make sure everything is going ok and will report back :)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Bergstr
ID: 33474311
Getting there.... i've now changed my code back to the original query, with a bit more detail - i can get it to work with everything in there apart from one particular WHERE statement...

Full Code is listed below, but the problem line is:

RTRIM(LTRIM(C.WorkCentres)) IN('WC1', 'WC2', 'WC3') AND

if i remove the above line, it works ok...

Any idea how i can get that line to work?

Thanks
SELECT A.* INTO SugJobs2

	FROM (

SELECT	B.*

	FROM	MiniSugJobs AS B



LEFT OUTER JOIN ProdWC AS C

	ON	B.StockCode=C.StockCode 

	

WHERE 

RTRIM(LTRIM(C.WorkCentres)) IN('WC1', 'WC2', 'WC3') AND

B.JobStartDate < '18-AUG-2010'



) AS A

Open in new window

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33474495
I don't know what you mean by "work" but do you mean

SELECT A.* INTO SugJobs2
      FROM (
SELECT      B.*
      FROM      MiniSugJobs AS B
LEFT OUTER JOIN ProdWC AS C
      ON      B.StockCode=C.StockCode  AND RTRIM(LTRIM(C.WorkCentres)) IN('WC1', 'WC2', 'WC3')
WHERE
      B.JobStartDate < '18-AUG-2010'
) AS A

But you might as well drop the left join since it plays no part in select b.*
Otherwise, if you are filtering on C, you might want to change the LEFT to INNER join.
All it means is that there really are no matches for [C.WorkCentres IN('WC1', 'WC2', 'WC3')]
0
 

Author Comment

by:Bergstr
ID: 33475084
all seems to be working now - the correct data is being transferred :)
0
 

Author Comment

by:Bergstr
ID: 33475097
Thanks everyone thats helped me out! :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now