MSSQL PHP Query

[ramsay]

Hi,

I want to use a PHP web page to display some data from SIMS. I've got as far as establishing a connection to the SQL database and I can return some information from it using some examples I've found on the Internet (nothing useful though).

I think the string I want is something like:

mssql_query('SELECT * FROM [sims].[something].[something]');

But I can't seem to get this working. I've tried specifying various things to relate to what I see when I log in to the SQL manager on the SIMS server, but no luck so far - just keep getting "Invalid object name".

Something like this maybe? - But this doesn't work
mssql_query('SELECT * FROM [sims].[stud_reg_group].[base_group_id]');

The main thing I want to do is display parent e-mail addresses for various year groups. For example I will use a checkbox to select year 7 and 10, then submit the HTML/PHP form and it will display a list of parent e-mail addresses. Above I was just using the registration group as an easy example.

Any help would be greatly appreciated!

Thank you

[MattMitchell]

OK, where to start?...

1)
First of all, the SQL you're using has the wrong syntax. Standard select queries normally look like this:

SELECT field1, field2, field3, ...
FROM table1 [AS alias1], table2 [AS alias2],...
WHERE conditions...;

Using "*" instead of the fieldlists will just return all columns from the query. If the table(s) you're selecting from has/have a lot of columns, this may not be such a good plan.

Your query needs to read like this instead:

SELECT base_group_id FROM sims.stud_reg_group;

i.e. "SELECT field FROM database.table;", as opposed to "SELECT * FROM database.table.field;" which is not quite right!


2)
Looking at the content of your post, it seems reasonable to assume that you're not entirely comfortable writing SQL queries - do you really want to mess with your school's data in this way, without knowing what the potential outcomes are?

There are other mechanisms for getting this kind of data out of the system, notably the reporting mechanisms in SIMS, which will also export data as CSV/XML in order to let you process them with another program. I'd suggest, very strongly, that this might be a better way to do some of this!

3)
I'd love to be proved wrong on this one, so if anyone can confirm otherwise I'd be delighted: as I understand it, unless you're a SIMS SQL Partner, Capita do not support direct access to the SQL database.

That means that if there is a problem with your data, and there's even a whisper of suspicion that it may be your fault it's happened, you'll get charged a lot of $$$ to fix it.

Also, (and I don't have the licence here), but "received wisdom" on the forum is that the SIMS licence specifically prohibits accessing the database. This includes read-only access - there are quite a lot of threads bemoaing this.

There are some very valid reasons for this. First of all, if Capita change the database schema, or the /meanings/ of various field values, any code you write that accesses the database will cease to work. As the direct access isn't supported, that would mean you're on your own if a change from Capita broke your functionality.

Also, looking at some of the patches Capita have run on our database, there's a certain amount of metadata in the database; i.e. not only can changing a field value change a head of house into a registration tutor, changing a value elsewhere can change where SIMS "looks" to find registration tutors. Or whatever - you get the idea!

The patches that we've had also check the database schema number, and if the version is too old or too recent, then the patch won't work as expected. The same would apply to any code that you write to access the database direct.


If there is a way to access the database with Capita's blessing, on a read-only basis, I'd love to know about it, but I suspect it's an sql-partner-or-nothing scenario!

Hope some of this helps somewhere...

Matt

[theriver]

Hi there.

Just to add in some extra info on MSSQL SQL syntax:

when you're selecting from a table, you can say either:

select [fields] from [table]

...which assumes that you are connected to a particular database as a particular user, OR you can be quite specific and include the database name too, as you've attempted to do.
However, if you do this you also need to specify the schema name of the owner of the table:

select [fields] from [database].[schemaowner].[table]

I don't know how SIMS is set up, but you may well find that the schema is dbo, hence something like:

select * from sims.dbo.stud_reg_group

Having said all that I'd second everything MattMitchell said - from what I've read on these forums direct access to the SIMS database is not to be attempted.

Other threads here have referenced CommandReporter and it would be preferable to tinker with that and use your PHP to parse the output.