+ Post New Thread
Results 1 to 3 of 3
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 ...
  1. #1

    Join Date
    Oct 2009
    Posts
    46
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    MSSQL PHP Query

    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

  2. #2

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    244
    Thank Post
    23
    Thanked 36 Times in 27 Posts
    Rep Power
    20
    OK, where to start?...

    1)
    First of all, the SQL you're using has the wrong syntax. Standard select queries normally look like this:
    Code:
    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:
    Code:
    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

  3. #3

    Join Date
    Apr 2006
    Posts
    388
    Thank Post
    23
    Thanked 95 Times in 61 Posts
    Rep Power
    44
    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.

SHARE:
+ Post New Thread

Similar Threads

  1. MSSQL Timeout
    By localzuk in forum Windows Server 2000/2003
    Replies: 4
    Last Post: 25th September 2009, 09:03 AM
  2. Print Manager Plus + MSSQL 05
    By BaccyNet in forum Windows
    Replies: 0
    Last Post: 16th June 2009, 08:28 PM
  3. SIMS and MSSQL
    By interele in forum MIS Systems
    Replies: 2
    Last Post: 25th November 2008, 06:35 PM
  4. SIMS on MSSQL 2005
    By AlexB in forum MIS Systems
    Replies: 9
    Last Post: 7th November 2007, 09:20 AM
  5. Query of existing query data in PHP
    By markwilliamson2001 in forum Web Development
    Replies: 5
    Last Post: 5th October 2007, 08:43 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •