Jump to content

PHP MSSQL Help!


zduchene

Recommended Posts

I need the help of the form.  I am using PHP and connecting to MSSQL database :(.

 

I wrote a MSSQL query and tested it on the MSSQL Query Analyzer program where it worked great, did exactly what I wanted it to.  I integrated the query into a php script and I am getting:

 

PHP Warning: mssql_query() [function.mssql-query]: message: Line 1: Incorrect syntax near '.'. (severity 15) in C:\webapps\htdocs\ji\ji2.php on line 7 PHP Warning: mssql_query() [function.mssql-query]: Query failed in C:\webapps\htdocs\ji\ji2.php on line 7

 

Here is the php script along with the query:

 

$q = 'SELECT "estimate"."job number", "DEBTOR"."NAMES", "ESTIMATE"."details1", "debtor"."salesrep", "salesrep"."name" AS "csr", "estimate"."due date", "estimate"."order no", "estimate"."qty1", "estimate"."qty2", "estimate"."qty3", "estimate"."qty4" 
FROM "LIVEDATA_Dosrun"."dbo"."ESTIMATE", "LIVEDATA_Dosrun"."dbo"."DEBTOR", "LIVEDATA_Dosrun"."dbo"."SALESREP", "LIVEDATA_Dosrun"."dbo"."INVOICE" 
WHERE "ESTIMATE"."JOB NUMBER" = 53373 AND "INVOICE"."JOB NO" = "ESTIMATE"."JOB NUMBER" AND "INVOICE"."CSR RECNUM" = "SALESREP"."DATAFLEX RECNUM ONE" AND "DEBTOR"."AC NO" = "ESTIMATE"."DEBTOR"';
$result = mssql_query($q);

 

Any help is appreciated!

Link to comment
Share on other sites

  • 2 weeks later...

Looking over your code there is a few things that I can say is wrong with the way you are doing things. I will admit that I don't do a whole lot with MS SQL mixed with PHP cause MS SQL sucks.

 

But here is what I saw:

1. some columns in your query have spaces instead of underscores "_" that will cause issues

2. you are selecting your database in the query.

 

Here is what I would suggest:

1. alter your table so there are no spaces in column names

2. run this to connect

  $conn = mssql_connect('server', 'username', 'password');

  mssql_select_db('LIVEDATA_Dosrun', $conn);

3. now run your query (this is your same query just edited to be easier to ready and follow)

  $q = "SELECT e.job_number, d.names, e.details1,

d.salesrep, s.name csr, e.due_date,

e.order_no, e.qty1, e.qty2, e.qty3, e.qty4

  FROM estimate e, debtor d, salesrep s, invoice i

  WHERE e.job_number = '53373'

      AND i.job_no = e.job_number

      AND i.csr_recnum = s.dataflex_recnum_one

      AND d.ac_no = e.debtor";

4. now you can run your result

  $result = mssql_query($q);

 

If I can be of assistance more let me know. I am always happy to help where I can.

Link to comment
Share on other sites

^^ agree with u ...

 

give a try :)

 

i am using an odbc connection to mssql with PHP and that works like a dream. Very fast.

use the sql tools to make your query, create an SP and then run the SP from your PHP code.

i have some examples if your interested.

 

the query indeed needs to be tuned to speed it up, not using tablenames for instance but aliases. tbldata t1 etc.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.