Coding Thread, MSSQL PHP Query in Coding and Web Development; Hi,
I want to use a PHP web page to display some data from SIMS. I've got as far as ...
2nd December 2009, 10:16 AM #1
- Rep Power
MSSQL PHP Query
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!
3rd December 2009, 09:49 AM #2
OK, where to start?...
First of all, the SQL you're using has the wrong syntax. Standard select queries normally look like this:
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.
SELECT field1, field2, field3, ...
FROM table1 [AS alias1], table2 [AS alias2],...
Your query needs to read like this instead:
i.e. "SELECT field FROM database.table;", as opposed to "SELECT * FROM database.table.field;" which is not quite right!
SELECT base_group_id FROM sims.stud_reg_group;
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!
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...
3rd December 2009, 12:55 PM #3
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.
By localzuk in forum Windows Server 2000/2003
Last Post: 25th September 2009, 10:03 AM
By BaccyNet in forum Windows
Last Post: 16th June 2009, 09:28 PM
By interele in forum MIS Systems
Last Post: 25th November 2008, 07:35 PM
By AlexB in forum MIS Systems
Last Post: 7th November 2007, 10:20 AM
By markwilliamson2001 in forum Web Development
Last Post: 5th October 2007, 09:43 AM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)