[PHP-DB] RE: PHP4 on Windows using MSSQL
Sorry made a typo in my last email - I mean't to say wouldn't recommend it rather than would (in reference to using MSSQL from Linux). Hope you get the issue sorted Cheers Ollie -Original Message- From: Kevin Gordon [mailto:[EMAIL PROTECTED] Sent: 26 February 2003 08:23 To: Oliver Cronk Cc: PHP Mail Subject: RE: PHP4 on Windows using MSSQL Thanks Ollie. I will check Tcp or Named Pipes. I have found a Linux Journal article dated 14 Feb 2003 using a FreeTDS driver and ODBC to MSSQL and will try that. Cheers, Kevin On Tue, 2003-02-25 at 23:25, Oliver Cronk wrote: Yes, but I have never personally used Linux to connect to MSSQL so I can't really help you on that one. Although I hear that several people have done this I would really recommend it (as Linux isn't a supported client O/S for MSSQL). It is *fairly* straightforward on Windows - the hardest bit is making sure you have all the correct DLL's in the right places etc etc. I use MySQL/ Postgres these days so I can't really remember all the stuff I had to do to get MSSQL working with PHP but I will have a think and let you know if I think of anything that might be useful. After having another quick look at your error message at the bottom of your original email it might be worth looking at your MSSQL Server connection config dialogs - make sure that TCP/IP has been enabled (looks like you might just have named pipes). Regards Ollie Cronk -Original Message- From: Kevin Gordon [mailto:[EMAIL PROTECTED] Sent: 25 February 2003 08:05 To: Oliver Cronk Cc: PHP Mail Subject: RE: PHP4 on Windows using MSSQL Thanks Ollie I guess this means using PHP4 MSSQL functions in Windows and PHP4 Sybase functions in Linux? On Tue, 2003-02-25 at 04:47, Oliver Cronk wrote: The MSSQL module for PHP DOES NOT USE ODBC. It uses the C library direct style of connecting to MSSQL. Use your enterprise manager to configure the appropriate users and permissions and you should be fine. Ollie Cronk www.cronky.net -Original Message- From: Kevin Gordon [mailto:[EMAIL PROTECTED] Sent: 24 February 2003 07:09 To: PHP Mail Subject: PHP4 on Windows using MSSQL Hi Everyone, From a Linux session: ~ # telnet 10.1.1.1 1433 Trying 10.1.1.1... Connected to 10.1.1.1. Escape character is '^]'. Connection closed by foreign host. From within NT4 Primary Domain Server: SQL Server Query Analyzer using the user login password applied in PHP (and created in MSSQL) Queries processed ok. In PHP must I use NT authenticated login / password? Both a PHP client on Linux and Windows receive the same error message: Warning: SQL error: [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server not found., SQL state 08001 in SQLConnect in E:\Inetpub\wwwroot\php\ODBC_Driver.php on line 174 The PHP.ini file on NT4 has: ** [MSSQL] ; Allow or prevent persistent links. mssql.allow_persistent = On ; Maximum number of persistent links. -1 means no limit. mssql.max_persistent = -1 ; Maximum number of links (persistent+non persistent). -1 means no limit. mssql.max_links = -1 ; Minimum error severity to display. mssql.min_error_severity = 10 ; Minimum message severity to display. mssql.min_message_severity = 10 ; Compatability mode with old versions of PHP 3.0. mssql.compatability_mode = Off ; Valid range 0 - 2147483647. Default = 4096. ;mssql.textlimit = 4096 ; Valid range 0 - 2147483647. Default = 4096. ;mssql.textsize = 4096 ; Limits the number of records in each batch. 0 = all records in one batch. ;mssql.batchsize = 0 ; Use NT authentication when connecting to the server mssql.secure_connection = Off ; Specify max number of processes. Default = 25 ;mssql.max_procs = 25 ntwdblib.dll is in the correct Windows directory. Using a System DNS for ODBC. Should I be using a File DNS? Can I open up NT4 PDS to telenet to it? Any thoughts, comments? Many thanks, Kevin Gordon New Zealand -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE: PHP4 on Windows using MSSQL
Yes, but I have never personally used Linux to connect to MSSQL so I can't really help you on that one. Although I hear that several people have done this I would really recommend it (as Linux isn't a supported client O/S for MSSQL). It is *fairly* straightforward on Windows - the hardest bit is making sure you have all the correct DLL's in the right places etc etc. I use MySQL/ Postgres these days so I can't really remember all the stuff I had to do to get MSSQL working with PHP but I will have a think and let you know if I think of anything that might be useful. After having another quick look at your error message at the bottom of your original email it might be worth looking at your MSSQL Server connection config dialogs - make sure that TCP/IP has been enabled (looks like you might just have named pipes). Regards Ollie Cronk -Original Message- From: Kevin Gordon [mailto:[EMAIL PROTECTED] Sent: 25 February 2003 08:05 To: Oliver Cronk Cc: PHP Mail Subject: RE: PHP4 on Windows using MSSQL Thanks Ollie I guess this means using PHP4 MSSQL functions in Windows and PHP4 Sybase functions in Linux? On Tue, 2003-02-25 at 04:47, Oliver Cronk wrote: The MSSQL module for PHP DOES NOT USE ODBC. It uses the C library direct style of connecting to MSSQL. Use your enterprise manager to configure the appropriate users and permissions and you should be fine. Ollie Cronk www.cronky.net -Original Message- From: Kevin Gordon [mailto:[EMAIL PROTECTED] Sent: 24 February 2003 07:09 To: PHP Mail Subject: PHP4 on Windows using MSSQL Hi Everyone, From a Linux session: ~ # telnet 10.1.1.1 1433 Trying 10.1.1.1... Connected to 10.1.1.1. Escape character is '^]'. Connection closed by foreign host. From within NT4 Primary Domain Server: SQL Server Query Analyzer using the user login password applied in PHP (and created in MSSQL) Queries processed ok. In PHP must I use NT authenticated login / password? Both a PHP client on Linux and Windows receive the same error message: Warning: SQL error: [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server not found., SQL state 08001 in SQLConnect in E:\Inetpub\wwwroot\php\ODBC_Driver.php on line 174 The PHP.ini file on NT4 has: ** [MSSQL] ; Allow or prevent persistent links. mssql.allow_persistent = On ; Maximum number of persistent links. -1 means no limit. mssql.max_persistent = -1 ; Maximum number of links (persistent+non persistent). -1 means no limit. mssql.max_links = -1 ; Minimum error severity to display. mssql.min_error_severity = 10 ; Minimum message severity to display. mssql.min_message_severity = 10 ; Compatability mode with old versions of PHP 3.0. mssql.compatability_mode = Off ; Valid range 0 - 2147483647. Default = 4096. ;mssql.textlimit = 4096 ; Valid range 0 - 2147483647. Default = 4096. ;mssql.textsize = 4096 ; Limits the number of records in each batch. 0 = all records in one batch. ;mssql.batchsize = 0 ; Use NT authentication when connecting to the server mssql.secure_connection = Off ; Specify max number of processes. Default = 25 ;mssql.max_procs = 25 ntwdblib.dll is in the correct Windows directory. Using a System DNS for ODBC. Should I be using a File DNS? Can I open up NT4 PDS to telenet to it? Any thoughts, comments? Many thanks, Kevin Gordon New Zealand -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE: PHP4 on Windows using MSSQL
The MSSQL module for PHP DOES NOT USE ODBC. It uses the C library direct style of connecting to MSSQL. Use your enterprise manager to configure the appropriate users and permissions and you should be fine. Ollie Cronk www.cronky.net -Original Message- From: Kevin Gordon [mailto:[EMAIL PROTECTED] Sent: 24 February 2003 07:09 To: PHP Mail Subject: PHP4 on Windows using MSSQL Hi Everyone, From a Linux session: ~ # telnet 10.1.1.1 1433 Trying 10.1.1.1... Connected to 10.1.1.1. Escape character is '^]'. Connection closed by foreign host. From within NT4 Primary Domain Server: SQL Server Query Analyzer using the user login password applied in PHP (and created in MSSQL) Queries processed ok. In PHP must I use NT authenticated login / password? Both a PHP client on Linux and Windows receive the same error message: Warning: SQL error: [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server not found., SQL state 08001 in SQLConnect in E:\Inetpub\wwwroot\php\ODBC_Driver.php on line 174 The PHP.ini file on NT4 has: ** [MSSQL] ; Allow or prevent persistent links. mssql.allow_persistent = On ; Maximum number of persistent links. -1 means no limit. mssql.max_persistent = -1 ; Maximum number of links (persistent+non persistent). -1 means no limit. mssql.max_links = -1 ; Minimum error severity to display. mssql.min_error_severity = 10 ; Minimum message severity to display. mssql.min_message_severity = 10 ; Compatability mode with old versions of PHP 3.0. mssql.compatability_mode = Off ; Valid range 0 - 2147483647. Default = 4096. ;mssql.textlimit = 4096 ; Valid range 0 - 2147483647. Default = 4096. ;mssql.textsize = 4096 ; Limits the number of records in each batch. 0 = all records in one batch. ;mssql.batchsize = 0 ; Use NT authentication when connecting to the server mssql.secure_connection = Off ; Specify max number of processes. Default = 25 ;mssql.max_procs = 25 ntwdblib.dll is in the correct Windows directory. Using a System DNS for ODBC. Should I be using a File DNS? Can I open up NT4 PDS to telenet to it? Any thoughts, comments? Many thanks, Kevin Gordon New Zealand -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] RE: mssql problems
Hi Kras - Running PHP as the ISAPI module make sure you have the latest version (you should get the latest version anyway). Do you have problems with php in ISAPI mode with MSSQL permament connections? -- I don't use pconnect with ISAPI but maybe I should thinking about it(!) so I can't help you with that, I noticed a huge performance boost just switching from the CGI so didn't think about optimising the DB connection If I was you I would use 4.1.1 or 4.1.2 as I believe there are a number of fixes for Windows / MS-SQL. Make sure you copy the latest php mssql extension dll over into system32 (or wherever you work from). I ran it in that mode, and it was very fast and was working until timeouted pconnection. After reconnection php isami dumped 'access violation...' . Apart of that I have problems with some libraries in isapi mode, so maybe you have some tips about it? Yes I have had some problems in the previous versions of PHP but 4.1.2 seems very robust and stable. I seem to recall some problems with extensions but I am afraid I can't really remember which ones - I know for sure that GD and MSSQL worked fine but I think the IISadmin and pdf had problems (not a problem for us as we don't use the functionality those modules provide at present). *my config: NT4 srv, SP6a,SRP, IIS4 w the latest patches, PHP4.1.0, libraries: GD, IMAP, PDF,zlib,MSSQL,DOMXML* Hope that helps you further - sorry I can't be of more use but I stop using NT4 a few months ago and I don't seem to be able to lay my hands on the docs for the old server config (if they exist!) at the moment. Cheers Ollie Final Year Computer Science Student Essex Uni, UK ( www.cronky.net ) and E-Government Developer currently working on behalf of Maidstone Borough Council, UK ( www.digitalmaidstone.co.uk ) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE: mssql problems
Obviously the major advantage of using the native MS-SQL extension is performance (IHMO) maybe if you are running a few small apps then you won't notice the connection overhead involved in establishing an odbc connection to MSSQL. I have found the MSSQL extension much faster in testing . In addition using ODBC doesn't allow you to return the amount of rows returned by a select query (but it does return the amount of effected rows by an update or delete). I'm not saying there aren't disadvantages to using the MSSQL ext. - it works on an older interface (DB-LIB) and therefore lacks a few of the current (MSSQL 2000) features only available via ADO or ODBC. But I guess its just horses for courses. Cheers Ollie -Original Message- From: Frank Flynn [mailto:[EMAIL PROTECTED]] Sent: 19 April 2002 22:21 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: mssql problems I had this exact same problem and after a lot of time I switched to an ODBC connection (no mssql_connect... Just use the ODBC commands) and everything has worked flawlessly since. There's probably some right combination and way to use the mssql specific commands but life it too short and the ODBC stuff will do everything you need anyway. It worked first time and every time since for me. Good Luck, Frank On 4/19/02 1:54 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: -Original Message- From: Rob Fraser [mailto:[EMAIL PROTECTED]] Sent: 19 April 2002 08:44 To: [EMAIL PROTECTED] Subject: mssql problems Dear All, I hope somebody can help me. I am new to PHP (I've used ASP for three years:-( ) and I'm loving PHP and trying to get my work to convert but I've coming across a error with mssql_query() explorer its just crashes with a php.exe 'memory could not be read error'. I'm sure it a school boy error from me but I'm just stuck, please help me code is.. === $link = mssql_connect (laptop, sa, elmwood); print (brLINK was $link); $back = mssql_select_db ( elmback,$link); print(brback was $back); $query = SELECT * FROM tblperson; print(brquery was $query); $result = mssql_query($query,$link); //JUST CRASHES ON THIS LINE $r = mssql_rows_affected ( $link); print(brrows affected was $r); $close = mssql_close ($link); print(brclose was $close); = I run IIS4, NT4 (SP6a) and SQL 7 on the same machine as its my development one, out of date maybe but you should see my clothes but PHP was test downloaded a month ago - any ideas from a white knight? best regards Rob -- Frank Flynn Poet, Artist Mystic -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE: troubles with select * from... to a ms-sql-server
Have you tried removing the [] brackets? And what is ReferencesComplete - a standard table, a view or a stored procedure? Ollie -Original Message- From: Hermann Otteneder [mailto:[EMAIL PROTECTED]] Sent: 17 April 2002 16:30 To: [EMAIL PROTECTED] Subject: troubles with select * from... to a ms-sql-server hi, i try to get some data from our ms-sql-server the following statement: $SQL = SELECT * FROM [ReferencesComplete]; causes an error of the php.exe! i tried many various of this statement but nothing helped. ether it came no error and the [$result = mssql_query($SQL,$IDconnection);] variable was false or the php.exe crashed! with mysql it workes ok but with the ms-sql-server not! please give me help - this so what simple - but does not work - i lost so much time... hermann -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE: mssql problems
Hi PHP Is quite cool isn't it As for your problem you might want to try a few things: - Running PHP as the ISAPI module make sure you have the latest version (you should get the latest version anyway). - Removing this line : $r = mssql_rows_affected ( $link); - as there is a mysql function for this but not mssql - it should have read - mssql_rows_affected($result) anyway I believe - it works on the result rather than the connection). Can't really think of any thing else without more info, although I've had bad experiences with trying to run MSSQL / IIS on the same NT4 box (although on 2000 and XP it's fine) - one of my colleages solution was to run Apache for win32 instead of IIS as there appeared to be clashes between SQL and IIS (something we have since rectified). Good luck Ollie -Original Message- From: Rob Fraser [mailto:[EMAIL PROTECTED]] Sent: 19 April 2002 08:44 To: [EMAIL PROTECTED] Subject: mssql problems Dear All, I hope somebody can help me. I am new to PHP (I've used ASP for three years:-( ) and I'm loving PHP and trying to get my work to convert but I've coming across a error with mssql_query() explorer its just crashes with a php.exe 'memory could not be read error'. I'm sure it a school boy error from me but I'm just stuck, please help me code is.. === $link = mssql_connect (laptop, sa, elmwood); print (brLINK was $link); $back = mssql_select_db ( elmback,$link); print(brback was $back); $query = SELECT * FROM tblperson; print(brquery was $query); $result = mssql_query($query,$link); //JUST CRASHES ON THIS LINE $r = mssql_rows_affected ( $link); print(brrows affected was $r); $close = mssql_close ($link); print(brclose was $close); = I run IIS4, NT4 (SP6a) and SQL 7 on the same machine as its my development one, out of date maybe but you should see my clothes but PHP was test downloaded a month ago - any ideas from a white knight? best regards Rob -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] page expires
How about this incorporated somewhere: $s = (SERVER_PROTOCOL == HTTP/1.0) ? Pragma: no-cache : Cache-Control: no-cache, must-revalidate; header($s); This code must be at the start of the script BEFORE anything is output to the browser - as it sends an additional HTTP header (hence the function header()). Hope that helps Ollie -Original Message- From: Jonathan Hilgeman [mailto:[EMAIL PROTECTED]] Sent: 18 April 2002 19:37 To: 'Natividad Castro'; Subject: RE: [PHP-DB] page expires http://php.sitecreative.com/faq.php -Original Message- From: Natividad Castro [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 17, 2002 6:12 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] page expires Hi to all, I have form that users fill out and submit. What I'm trying to do is not to let users to go back to the form after they have submmited, or if they go bak, make the page expires. Can someone please tell me or give an idea on how to do it? Thanks in advanced Nato -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE: MS SQL problem with php
What is the query / code that you are sending to MS-SQL in PHP? -Original Message- From: Daniel Ryhle [mailto:[EMAIL PROTECTED]] Sent: 12 March 2002 21:42 To: [EMAIL PROTECTED] Subject: MS SQL problem with php I run MS SQL 2000 on a win 2k machine and IIS5. On this i have installed php4.11 with php_mssql.dll support. When i do an insert query it works fine. But my problem is with SELECT querys. If i write a select query and run it on a table that is empty it works fine. BUT if i put an entry into the table and run the same query again it just stands and think until it time out. Extremely strange, cause ive programmed some php against mysql on a linux machine and never had any troubles. But this is totally...wacked.. Seems to work fine if i run the same querys directly in ms sql. Is this a bug in php or is it perhaps just a configuration question or somethin?? Extremely glad if someone had any info about this cause i need to get it to work -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Is ODBC now the Native interface for MS-SQL7/2000 ?
After looking at the newsgroup microsoft.public.sqlserver.odbc I came across the follwing piece of information - anyone know if it is true? It would certainly explain a few things (why we can only get 255 chars out of varchars etc). Should I move my code over to ODBC? I have tried but the ODBC driver appears to lack any decent output (-1) for the odbc_num_rows function - which breaks my existing code (it performs checks on the amount of rows). Anyone have any information about this? Should I talk to the developer(s) of the MSSQL extension do you think? IF it is true then the php docs should at least illustrate this point as a warning. [start of ms post] They don't provide one because db-lib is a dead interface and hasn't been upgraded in 5+ years now. fyi - ODBC and OLE-DB *ARE* native interfaces to SQL Server - it is these you should be using. [person who asked the question wrote] Why has Microsoft not provided a new ntwdblib.dll file for SQL Server 2000? Clients, such as Crystal, rely on this being valid for the current database. Crystal provides p2ssql.dll that communicates with ntwdblib.dll. Since ntwdblib.dll was last updated with SQL Server 6.5, the p2ssql.dll driver fails for clients. For example, column names 30 chars in length don't work in Crystal if you use the p2ssql.dll driver (that communicates with the very outdated ntwdblib.dll library). There are several other issues with this as well. As a result, clients such as Crystal must use a more generic ODBC driver (p2sodbc.dll). That works fine, but why doesn't MS provide a current NATIVE driver to work with SQL Server 2000? Where can I get a NEW ntwdblib.dll to install? I installed the latest MDAC and that didn't solve the problem. Thanks. [end of ms post] Thanks for any pointers anyone throws up. Oliver Cronk p.s. this was found under the thread Re: ntwdblib.dll (Native vs. ODBC driver) on microsoft.public.sqlserver.odbc over the last couple of days (and so should still be there if anyone else wants to look at the entire thread). -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbiequestion)
Great, simple solution, which I really should have thought if myself considering I did semaphores, IPC and other process management stuff last year in my Uni course! Cheers Everyone, I'll let you know what solution I use in the end (probably won't know until Easter when my second prototype/ beta / final version will be developed) Ollie p.s. This newsgroup/mailing list is great, much better than the ones I have used in the past - I love the fact that no one has dissed my DB or O/S platform choices! I will continue to be involved where time allows! -Original Message- From: John Lim [mailto:[EMAIL PROTECTED]] Sent: 02 February 2002 20:09 To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbiequestion) Hi, This is a technique I first saw in the O'Reilly book Oracle Design. The idea is to use integers rather than dates, and treat the integers as semaphores (or latches in Oracle terminology). This technique is very portable as it does not require date types. Create an integer column called LOCKCNT in the table. When retrieving your record, remember the last value of LOCKCNT in lastlockcnt. When updating, use UPDATE table SET col1 = ??, ..., LOCKCNT = LOCKCNT + 1 WHERE key = ??? and LOCKCNT = lastlockcnt; If the record has been modified by someone else, the UPDATE will fail. Regards, John Dl Neil [EMAIL PROTECTED] wrote in message 05cf01c1acaa$19f774b0$c200a8c0@jrbrown">news:05cf01c1acaa$19f774b0$c200a8c0@jrbrown... This is good brain-storming guys! Better to use an integer rather than a date field as 2 simultaneous transactions can still occur on the same second; most date time fields are accurate only to the nearest second. Good point, John. However MySQL (AFAIK) does not have any time functions returning values less than one second. So are you proposing to use PHP's microtime function? That being the case, the two components (seconds and micro-secs) would have to be added together. Also the field would either have to be changed to accept a floating-point value, or the combined number multiplied up to be integral microseconds (of the Unix epoch). Are you guys interested in yet another solution? It simular but I think it's even easier if that's an incentive... =always interested in constructive suggestions - I've learned from this conversation - as well as enjoying the challenge/getting the brain cells to all march around in the same direction... -In your table do put a datetime field, I'll call it lastMod. This will be the last updated date and time (you need time here) -In your form have this as a hidden field so it gets submitted with the rest of the data. -When you receive the data to update the record you have I presume the record ID in $id and the last modified date timein $lastMod. Create a new $now with today's date and the current time Your SQL will look like: UPDATE Foo SET col1 = $col1, col2 = $col2, col3 = $col3, lastMod = $now WHERE recordID = $id AND lastMod = $lastMod As you can see the will succeed only if lastMod has not been changed. And if it had been updated it will not fail (it's perfectly valid SQL) but will not update any records. -Now to check if you did update the record... SELECT lastMod FROM Foo WHERE recordID = $id And compare this to your $now value. Or in MS SQL you can check the @@rowcount variable - (that's a MS SQL value) it will be 1 - success or 0 - collision (if it's 1 you've got duplicate ID's). =if using MySQL then MySQL_affected_rows() would be more efficient - no call to the RDMBS. =Unfortunately if this database requires an additional SELECT, then the solution requires a SELECT (to populate the form), and UPDATE (attempt), and a second SELECT to confirm the UPDATE. This is exactly the same 'cost' as earlier suggestions. (ignoring the UPDATE-clash situation which will have the same effect/cost in all cases) This has some disadvantage in that your end used could go through all the effort to update a record only to have their update fail - I suggest putting a nice error message and refresh the values in the form with the current ones. =which is the accepted fate of all of these suggestions - and the inevitable (if very occasional) impact of multi-user systems - always assuming that such a fact is of major interest to the user!!! It has the advantage of avoiding locking, setting flags, rolling back, you only need to compare one field and more work for you - and it will alw ays work. =So given all of the questions above, I wondered why use an (extra) time field at all? =I don't think that the original question mentioned how many fields were being updated by the form interaction (please correct me if I'm wrong), but I think the outline above could be applied reasonably effectively, even if several fields were being
RE: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question)
Thanks for that answer, you filled in some of the blanks for the table / logging solution, but I am now looking at row locking instead of a seperate table (and then doing things similar to what you outlined). The main problem is the darn timeout - how long should it be etc? And if I use row locking and don't unset the locks / use a timeout then the db will get completely locked up if the user doesn't update I would imagine! Darn users!! This script is complicated enough without this!! Cheers Ollie -Original Message- From: Peter Lovatt [mailto:[EMAIL PROTECTED]] Sent: 01 February 2002 08:10 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question) Hi Interesting problem! Systems would be so much easier to build if we didn't have to allow for users :) Two suggestions, depending on how you want the data dealt with. A table of rows in use, with a time stamp and an owner. When user1 opens the record, stamp it with owner and time. If user2 wants to use the record, check when it was 'locked' and apply a timeout based on how long it takes to edit. For example if the record was opened 3 mins ago, and the timeout is 5, the user2 gets a message saying 'Record in use try again in 2 minutes' If it was opened 6 minutes ago set the owner of the locked record to user2, and reset the timestamp. If / when user1 submits, refuse the update, and inform user1, and whatever handing you need after that. If no user2 has tried to open the record, then user1 can still submit, because they still own it, even if there is a timeout. If you are feeling flash maybe a JavaScript timer that pops up 1 minute before timeout and warns user1 to save (update record and reload for more editing)? Probably more hassle than its worth, but you could also take a snapshot of the data, when user1 starts, and if more than one user tries to edit the record, save the updates in a temp table, compare the updated record with the original snapshot, and do some sort of intelligent amalgamation. HTH Peter -Original Message- From: Oliver Cronk [mailto:[EMAIL PROTECTED]] Sent: 31 January 2002 23:09 To: [EMAIL PROTECTED] Subject: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question) Hi there, currently writing an e-CRM system for an intranet using PHP on Win32 and MS-SQL. This system needs to be scalable but more importantly there will be anything up to 400 users (unlikely, but the max amount) using the same records (updating information about customers etc) and I worry that whilst one user has a form open (via one of my PHP scripts) that another user could also be making changes to the same record and if they post it before the other one they could overwite each others changes. For info: database is normalised to 3NF so that side of things should be okay. I have thought of a couple of solutions: Row Locking when a user has a record - and if another user wants to use that record PHP tells them its in use. But if the forst user doesn't make any changes how will the db know to unlock the row and there might be potential deadlock issues. Also I'm not sure of the SQL for row locking (do you use a SELECT with a ROWLOCK hint?). Another idea was to have a log or temp table - that would get written into when ever some opens a record but this has the same issues as the first solution I think. An another idea is T-SQL and transactions but I'm not sure if that will solve the problem (and I've never used T-SQL before - therefore I'm not sure of its capabilities) eg: When the script is started by the first user (to bring up the existing record) perhaps a transaction is started (if they can persist between batches?): $tranname = @tran.$id; $sqlstr = TRANSACTION $tranname SELECT rows from CASES WHERE id = $id GO /* maybe find the date / time from a system table sp_something of the last time the row was modified?? */ START TRANSACTION $tranname GO ; But that probably won't work thinking about it (and looking at the stupid senseless code I have written above) The transcation probably need to be around the update SQL doesn't it? And then do a rollback if it finds another user has updated lately? And then reload the data and send it back to the form for the user to check (then they can update - after checking the other users data?) Anybody have a solution /views on this? Anybody had to fix a similar problem? Or is all this paranoia (will the DB handle this problem on it own? - I very much doubt that last comment!) Any help would be most appreciated, I don't need all of the PHP code just the concepts will do (I have been using PHP/MS-SQL for a while) or some example T-SQL if you think thats the solution I should go for. Thanks very much in advance... Oliver Cronk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL
RE: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question)
Thats the most elegant and probably easiest to implement, now I just need to figure out when to do a roll back I know the commands (COMMIT or ROLLBACK just don't know how to put it in my transaction - is it something like if(@@ERROR) ROLLBACK As I have read the manual (both MSSQL and PHP) but I am non- the wiser how to prevent an uncommited dependancy any quick example code you could provide would be really great or a T-SQL tutorial (I am familar with SQL just not the transaction bit). Thanks Ollie -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: 02 February 2002 15:40 To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; Php-Db ML Subject: Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question) Ollie, Probably easier to repeat the 'read' (from the first population of the user form) - and then do the comparison(s) in PHP - either way it will a SQL query that must be evaluated by PHP before the UPDATE is issued. ie put the interaction in a function and call it from the two locations in the code! That way you don't have essentially the same logic implemented in two places (fatal when you come to modify the code/db). Regards, =dn - Original Message - From: Oliver Cronk [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Php-Db ML [EMAIL PROTECTED] Sent: 01 February 2002 15:23 Subject: Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question) Yes thats an interesting idea, and this compare-and-modify-if-unchanged bit would be implemented via SQL IF statement(s) perhaps? Thanks Ollie Hugh Bothwell [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... Oliver Cronk [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... Thanks for that answer, you filled in some of the blanks for the table / logging solution, but I am now looking at row locking instead of a seperate table (and then doing things similar to what you outlined). Just a thought - if you have the user form echo a copy of the original data back (ie in addition to the modified data), you can compare it to the existing data and warn the user if the data has been changed in the interim. You must make the [compare-and-modify-if-unchanged] atomic, but that's okay, because it's all in the same script anyway - it becomes reasonable to do it as a transaction. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question)
Ah hang on just thought of a flaw in that - in between the second select, compare and update is enough time for another user to slip in - so I will still need some kind of logging. But it brings me nearer the solution! I think the solution is a combination of the 2 - start a transaction do a second select but do the comparision in SQL, then if all ok then start another transaction do the update do the update(s) of the joined tables and then commit both transactions - that way MSSQL will lock the required resources during the transaction (and can't slip in between the 2 sql queries). Or not? Ollie -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: 02 February 2002 15:40 To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; Php-Db ML Subject: Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question) Ollie, Probably easier to repeat the 'read' (from the first population of the user form) - and then do the comparison(s) in PHP - either way it will a SQL query that must be evaluated by PHP before the UPDATE is issued. ie put the interaction in a function and call it from the two locations in the code! That way you don't have essentially the same logic implemented in two places (fatal when you come to modify the code/db). Regards, =dn - Original Message - From: Oliver Cronk [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Php-Db ML [EMAIL PROTECTED] Sent: 01 February 2002 15:23 Subject: Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question) Yes thats an interesting idea, and this compare-and-modify-if-unchanged bit would be implemented via SQL IF statement(s) perhaps? Thanks Ollie Hugh Bothwell [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... Oliver Cronk [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... Thanks for that answer, you filled in some of the blanks for the table / logging solution, but I am now looking at row locking instead of a seperate table (and then doing things similar to what you outlined). Just a thought - if you have the user form echo a copy of the original data back (ie in addition to the modified data), you can compare it to the existing data and warn the user if the data has been changed in the interim. You must make the [compare-and-modify-if-unchanged] atomic, but that's okay, because it's all in the same script anyway - it becomes reasonable to do it as a transaction. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Inserting NULL values does not work
Why not make it a function which also checks and replaces for characters SQL
fails on eg if ' replace with '' in strings etc, and does any sanity /
validation checks at the same time?
Ollie
Rick Emery [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]...
Your problem is that you've included the value of $park in single-quotes.
Therefore, because it contains NULL, i.e., \0, this is translated to '0'
in
the INSERT statement. You must make your test prior to the INSERT
statement
and use the proper $park value.
Or, you can:
if ($park == 'false'){
$park = NULL;
}
else{
$park = \$park\;
};
in you INSERT statement change: '$park',to $park,
-Original Message-
From: Andy [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 01, 2002 9:43 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Inserting NULL values does not work
Hi guys,
I am trying to insert a NULL value into a DB field. The value is not
always
NULL so the statement is given. Unfortunatelly it is inserting 0 instead
of
NULL.
How can I get the thing going with NULL???
Here is the code I am using:
if ($park == 'false'){
$park = NULL;
}
$stmt = INSERT INTO $T1
(
belongs_to,
name,
caption,
country,
province,
city,
glacier,
park,
lake,
mountain,
beach,
comment,
keywords,
date_taken,
date_posted
)
VALUES
(
'$user_id',
'$picture',
'$caption',
'$country',
'$province',
'$city',
'$glacier',
'$park',
'$lake',
'$mountain',
'$beach',
'$comment',
'$keyword',
'$date_taken',
'$date_posted'
);
Cheers Andy
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Inserting NULL values does not work
Like this for example:
http://www.phpbuilder.com/snippet/download.php?type=snippetid=378
-Original Message-
From: Oliver Cronk [mailto:[EMAIL PROTECTED]]
Sent: 01 February 2002 16:01
To: Php-Db ML; [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Inserting NULL values does not work
Why not make it a function which also checks and replaces for characters SQL
fails on eg if ' replace with '' in strings etc, and does any sanity /
validation checks at the same time?
Ollie
Rick Emery [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]...
Your problem is that you've included the value of $park in single-quotes.
Therefore, because it contains NULL, i.e., \0, this is translated to '0'
in
the INSERT statement. You must make your test prior to the INSERT
statement
and use the proper $park value.
Or, you can:
if ($park == 'false'){
$park = NULL;
}
else{
$park = \$park\;
};
in you INSERT statement change: '$park',to $park,
-Original Message-
From: Andy [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 01, 2002 9:43 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Inserting NULL values does not work
Hi guys,
I am trying to insert a NULL value into a DB field. The value is not
always
NULL so the statement is given. Unfortunatelly it is inserting 0 instead
of
NULL.
How can I get the thing going with NULL???
Here is the code I am using:
if ($park == 'false'){
$park = NULL;
}
$stmt = INSERT INTO $T1
(
belongs_to,
name,
caption,
country,
province,
city,
glacier,
park,
lake,
mountain,
beach,
comment,
keywords,
date_taken,
date_posted
)
VALUES
(
'$user_id',
'$picture',
'$caption',
'$country',
'$province',
'$city',
'$glacier',
'$park',
'$lake',
'$mountain',
'$beach',
'$comment',
'$keyword',
'$date_taken',
'$date_posted'
);
Cheers Andy
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Inserting NULL values does not work
I know its not failing on YOUR example but if there is a single quote in the
data eg someones name is O'leary then the SQL will fail surely?
Ollie
-Original Message-
From: Rick Emery [mailto:[EMAIL PROTECTED]]
Sent: 01 February 2002 16:13
To: '[EMAIL PROTECTED]'; Php-Db ML
Subject: RE: [PHP-DB] Inserting NULL values does not work
It's not failing on the single-quotes. MYSQL can use single-quotes.
-Original Message-
From: Oliver Cronk [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 01, 2002 10:01 AM
To: Php-Db ML; [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Inserting NULL values does not work
Why not make it a function which also checks and replaces for characters SQL
fails on eg if ' replace with '' in strings etc, and does any sanity /
validation checks at the same time?
Ollie
Rick Emery [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]...
Your problem is that you've included the value of $park in single-quotes.
Therefore, because it contains NULL, i.e., \0, this is translated to '0'
in
the INSERT statement. You must make your test prior to the INSERT
statement
and use the proper $park value.
Or, you can:
if ($park == 'false'){
$park = NULL;
}
else{
$park = \$park\;
};
in you INSERT statement change: '$park',to $park,
-Original Message-
From: Andy [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 01, 2002 9:43 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Inserting NULL values does not work
Hi guys,
I am trying to insert a NULL value into a DB field. The value is not
always
NULL so the statement is given. Unfortunatelly it is inserting 0 instead
of
NULL.
How can I get the thing going with NULL???
Here is the code I am using:
if ($park == 'false'){
$park = NULL;
}
$stmt = INSERT INTO $T1
(
belongs_to,
name,
caption,
country,
province,
city,
glacier,
park,
lake,
mountain,
beach,
comment,
keywords,
date_taken,
date_posted
)
VALUES
(
'$user_id',
'$picture',
'$caption',
'$country',
'$province',
'$city',
'$glacier',
'$park',
'$lake',
'$mountain',
'$beach',
'$comment',
'$keyword',
'$date_taken',
'$date_posted'
);
Cheers Andy
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Inserting NULL values does not work
right ok yes I see, but surely thats
$querystr = INSERT INTO mytable (name) VALUES(\O'leary\) otherwise the
query string gets truncated by PHP at the values point???
My point simply is that he IS using single quotes (see below) and would
therefore suffer from this problem (hence the reason for suggesting such
additional functionality + consideration to validation of input).
$stmt = INSERT INTO $T1
(
belongs_to,
name,
caption,
country,
province,
city,
glacier,
park,
lake,
mountain,
beach,
comment,
keywords,
date_taken,
date_posted
)
VALUES
(
'$user_id',
'$picture',
'$caption',
'$country',
'$province',
'$city',
'$glacier',
'$park',
'$lake',
'$mountain',
'$beach',
'$comment',
'$keyword',
'$date_taken',
'$date_posted'
);
But I don't want to piss you off and as you will undoubtable have more mysql
experience than me so please don't take this as some kind of points scoring
exercise / competition - I was just suggesting some additional features he
might want to consider at the same point.
Ollie
Rick Emery [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]...
It won't fail if it is enclosed in double-quotes. That is:
INSERT INTO mytable (name) VALUES(O'leary)
is OK.
-Original Message-
From: Oliver Cronk [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 01, 2002 10:41 AM
To: Rick Emery; [EMAIL PROTECTED]; Php-Db ML
Subject: RE: [PHP-DB] Inserting NULL values does not work
I know its not failing on YOUR example but if there is a single quote in
the
data eg someones name is O'leary then the SQL will fail surely?
Ollie
-Original Message-
From: Rick Emery [mailto:[EMAIL PROTECTED]]
Sent: 01 February 2002 16:13
To: '[EMAIL PROTECTED]'; Php-Db ML
Subject: RE: [PHP-DB] Inserting NULL values does not work
It's not failing on the single-quotes. MYSQL can use single-quotes.
-Original Message-
From: Oliver Cronk [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 01, 2002 10:01 AM
To: Php-Db ML; [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Inserting NULL values does not work
Why not make it a function which also checks and replaces for characters
SQL
fails on eg if ' replace with '' in strings etc, and does any sanity /
validation checks at the same time?
Ollie
Rick Emery [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]...
Your problem is that you've included the value of $park in
single-quotes.
Therefore, because it contains NULL, i.e., \0, this is translated to '0'
in
the INSERT statement. You must make your test prior to the INSERT
statement
and use the proper $park value.
Or, you can:
if ($park == 'false'){
$park = NULL;
}
else{
$park = \$park\;
};
in you INSERT statement change: '$park',to $park,
-Original Message-
From: Andy [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 01, 2002 9:43 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Inserting NULL values does not work
Hi guys,
I am trying to insert a NULL value into a DB field. The value is not
always
NULL so the statement is given. Unfortunatelly it is inserting 0 instead
of
NULL.
How can I get the thing going with NULL???
Here is the code I am using:
if ($park == 'false'){
$park = NULL;
}
$stmt = INSERT INTO $T1
(
belongs_to,
name,
caption,
country,
province,
city,
glacier,
park,
lake,
mountain,
beach,
comment,
keywords,
date_taken,
date_posted
)
VALUES
(
'$user_id',
'$picture',
'$caption',
'$country',
'$province',
'$city',
'$glacier',
'$park',
'$lake',
'$mountain',
'$beach',
'$comment',
'$keyword',
'$date_taken',
'$date_posted'
);
Cheers Andy
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question)
Yes indeed I agree thats what I was trying to say (row lock between 2nd select and update during which time a SQL comparision is made (as if its 2 queries (and PHP does the comparision in between) MSSQL will release the locks i believe). Thanks for your help and everyone else's, now I just need to figure out ROLLBACK! Cheers Ollie Dl Neil [EMAIL PROTECTED] wrote in message news:02e301c1ac0c$f8116880$c200a8c0@jrbrown... Ollie, First off, apologies, this is the first mention of MS-SQL (that I have noted). I am not 'up' on the latest versions/facilities offered. Ah hang on just thought of a flaw in that - in between the second select, compare and update is enough time for another user to slip in - so I will still need some kind of logging. =Not too much so. If the entire table is locked (between the second SELECT and the UPDATE), how long will it be locked for? - and is that a major issue within your operating parameters? If it is locked, then there is 'no' time... But it brings me nearer the solution! I think the solution is a combination of the 2 - start a transaction do a second select but do the comparision in SQL, then if all ok then start another transaction do the update do the update(s) of the joined tables and then commit both transactions - that way MSSQL will lock the required resources during the transaction (and can't slip in between the 2 sql queries). =the database transaction should not start until the second SELECT, for the reason stated. Most of the time there will be (I assume the probability is v.low) no difference in the database between SELECTs. The only time the user would be aware that what (s)he thinks of a transaction is not the same as the RDBMS' view! =dn Or not? Ollie -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: 02 February 2002 15:40 To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; Php-Db ML Subject: Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question) Ollie, Probably easier to repeat the 'read' (from the first population of the user form) - and then do the comparison(s) in PHP - either way it will a SQL query that must be evaluated by PHP before the UPDATE is issued. ie put the interaction in a function and call it from the two locations in the code! That way you don't have essentially the same logic implemented in two places (fatal when you come to modify the code/db). Regards, =dn - Original Message - From: Oliver Cronk [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Php-Db ML [EMAIL PROTECTED] Sent: 01 February 2002 15:23 Subject: Re: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question) Yes thats an interesting idea, and this compare-and-modify-if-unchanged bit would be implemented via SQL IF statement(s) perhaps? Thanks Ollie Hugh Bothwell [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... Oliver Cronk [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... Thanks for that answer, you filled in some of the blanks for the table / logging solution, but I am now looking at row locking instead of a seperate table (and then doing things similar to what you outlined). Just a thought - if you have the user form echo a copy of the original data back (ie in addition to the modified data), you can compare it to the existing data and warn the user if the data has been changed in the interim. You must make the [compare-and-modify-if-unchanged] atomic, but that's okay, because it's all in the same script anyway - it becomes reasonable to do it as a transaction. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Ensuring users don't overwrite each other (NOT a newbie question)
Hi there, currently writing an e-CRM system for an intranet using PHP on Win32 and MS-SQL. This system needs to be scalable but more importantly there will be anything up to 400 users (unlikely, but the max amount) using the same records (updating information about customers etc) and I worry that whilst one user has a form open (via one of my PHP scripts) that another user could also be making changes to the same record and if they post it before the other one they could overwite each others changes. For info: database is normalised to 3NF so that side of things should be okay. I have thought of a couple of solutions: Row Locking when a user has a record - and if another user wants to use that record PHP tells them its in use. But if the forst user doesn't make any changes how will the db know to unlock the row and there might be potential deadlock issues. Also I'm not sure of the SQL for row locking (do you use a SELECT with a ROWLOCK hint?). Another idea was to have a log or temp table - that would get written into when ever some opens a record but this has the same issues as the first solution I think. An another idea is T-SQL and transactions but I'm not sure if that will solve the problem (and I've never used T-SQL before - therefore I'm not sure of its capabilities) eg: When the script is started by the first user (to bring up the existing record) perhaps a transaction is started (if they can persist between batches?): $tranname = @tran.$id; $sqlstr = TRANSACTION $tranname SELECT rows from CASES WHERE id = $id GO /* maybe find the date / time from a system table sp_something of the last time the row was modified?? */ START TRANSACTION $tranname GO ; But that probably won't work thinking about it (and looking at the stupid senseless code I have written above) The transcation probably need to be around the update SQL doesn't it? And then do a rollback if it finds another user has updated lately? And then reload the data and send it back to the form for the user to check (then they can update - after checking the other users data?) Anybody have a solution /views on this? Anybody had to fix a similar problem? Or is all this paranoia (will the DB handle this problem on it own? - I very much doubt that last comment!) Any help would be most appreciated, I don't need all of the PHP code just the concepts will do (I have been using PHP/MS-SQL for a while) or some example T-SQL if you think thats the solution I should go for. Thanks very much in advance... Oliver Cronk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Re: MSSQL Query - Unicode data in a Unicode-only collation or ntext data cannot be sent
No worries, there's an IIS-Lockdown Tool available from Microsoft if you want to play around with IIS - and not have to worry about code red etc (you still need to keep it regularly updated patches wise though - I guess thats the advantage of apache). The sample script is fine - thats how I got into MSSQL/PHP many moons ago. Note that it isn't very efficient (you can write a much faster script using mssql_fetch_array() and arrays) as it makes a lot of requests on the db. I think you are better off creating a simple DB in MSSQL enterprise manager (dead easy once you get the hang of it - much better than access IMHO but thats not very hard!). Do you know anything about Transactional SQL? If you do you might be able to help me in return (see my question in another newer thread)! If you want any more help let me know, I have been using this stuff for a while and don't mind sharing info (seeing as I'm probably not going to get to use PHP once I'm a graduate in a few months). Cheers Ollie William Fong [EMAIL PROTECTED] wrote in message news:001501c1aab2$79d83e40$0ac8a8c0@willbox... Hi Ollie, Thank you for your reply. I was mistaken. I am using PHP 4.1.1 loaded as a CGI. I am using Apache because I am from a Solaris/Linux background, and I know how to use it better. I don't feel comfortable enough with IIS to secure it down (with all that NIMDA and CodeRed floating around). Our company is going to implement SQL Server 2000, so I want to try it out. I used the sample PHP script from PHP's Web site to connect. Here is the code: ?php $hostname = localhost; $username = username; $password = password; $dbName = Northwind; MSSQL_CONNECT($hostname,$username,$password) or DIE(DATABASE FAILED TO RESPOND.); mssql_select_db($dbName) or DIE(Table unavailable); $query = SELECT * FROM Employees; $result = MSSQL_QUERY($query); $number = MSSQL_NUM_ROWS($result); $i=0; if ($number == 0) : print No data?; elseif ($number 0) : print Data:; while ($i $number) : $name = mssql_result($result,$i,Name); print $name; print ; $i++; endwhile; endif; ? I modified it to grab information from Northwind's Employee table. I didn't know Unicode was something that was defined. I will try to make a sample database with 'more simple' database fields to see if that works. I just wanted to do a quick test to grab data. I see it'll take me a little longer to learn MSSQL. Thanks! william -- William Fong - [EMAIL PROTECTED] Phone: 626.968.6424 x210 | Fax: 626.968.6877 Wireless #: 805.490.7732| Wireless E-mail: [EMAIL PROTECTED] - Original Message - From: Oliver Cronk [EMAIL PROTECTED] To: Php-Db ML [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, January 31, 2002 3:19 PM Subject: [PHP-DB] Re: MSSQL Query - Unicode data in a Unicode-only collation or ntext data cannot be sent Hi there, PHP4.0.6 on IIS / Windows 2000 works fine with SQL Server 2000 (I have teste this quite throughly) I don't know about unicode fields however as I don't/ haven't used them in any of my databases. I know that varchar fields have a few issues (can't retrieve more that 255 characters, you have to use text fields instead - which aren't queryable). What are you using to connect to MS-SQL? The MSSQL PHP library functions I hope (NOT ODBC its much slower IMO). Why are you using Apache on Win32 may I ask(is it for apache functionality you can't get with IIS?)? Whats the query that you are sending in your test.php script? Cheers Ollie [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... Getting this error: Warning: MS SQL message: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier. (severity 16) in C:\webroot\test.php on line 13 Warning: MS SQL: Query failed in C:\webroot\test.php on line 13 Warning: Supplied argument is not a valid MS SQL-result resource in C:\webroot\test.php on line 15 --- Using Windows 2000 Server with Apache 1.3.23 and PHP4.0 with SQL Server 2000. Any ideas? PHP's site didn't mention anything about SQL Server 2000. Thanks! will -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] re: newbie question: request response
No (not out of the box anyway) BUT the beauty of PHP is its much simpler than JSP or ASP's server.response() server.request() methods (sorry if thats not the exact syntax - I haven't done huge amounts of ASP/JSP work lately), in most cases you simply output the data (i.e. print / echo something;) for the response (maybe in conjuction with an optional http header - the default is text/html but you could send pretty much any MIME type - jpegs, pdfs etc). This means knocking up simple scripts is much easier quicker... The request object is taken care in most cases by just being there as a php variable with the same name as html / http element - i.e. if your html form has an element called bob then when this form is (via http post / get) sent back to a PHP script the variable is available as $bob. How simple! Sometimes you might want to use HTTP_GET_VARS['bob'] but I haven't encountered a time where thats necessary apart from when retrieving server info - auth_user, server_name etc (perhaps someone else could explain other situations when that is required?) Of course you could create request and response classes in PHP if you wanted to but that would remove the simplicity, beauty and speed of the langauge (IMHO). After all when I look at a JSP script I just think what a waste of code - server.response(thismethod(anothermethod(x))) just to output something!! Don't get me wrong I am not bad mouthing Java (in fact I use it too - just not for web programming) its features are there for security (ideal for e-banking), object orientated reasons and J2EE stuff, but with PHP you can really just get to the core of 90% of what web scripts need to do - you just need to return some data in HTML / XML back to the browser as efficiently and quickly as possible. I am bad mouthing ASP though! I think its over-complicated for no good reasons when compared to PHP! In addition you have a severe lack of libary functions (image manipulation, database access (other than the terribly slow ODBC option or the ADO option) XML etc). I presume VB.NET and C# will try to make up for these things by offering the windows forms interfaces and other new stuff. In my view echo something; (interpreted by PHP machine code cgi / isapi module) would appear on the face of things to be a lot quicker than: servlet.response(something); (in java byte code (hmm slow!) interpreted by jsp/servlet/library engine then into machine code) or server.response(something); (code interpreted by class / library in asp.dll or suchlike then into asp engine / windows service then machine code) obviously the string something would be something dynamic like a database result or such like but you get the idea. But I could be wrong! What really impresses me about PHP is that it offer a version of their scripting engine that will run on any almost any platform. This is machine code complied and so in theory outperforms the cross platform Java (byte-code), and maybe asp (visual basic - interpreted) which is also know to be a bit sluggish compared with PHP, but of course only really runs on Win32 (i know there's chillisoft - asp for unix) - but thats not exactly a realistic option. As you can see I have bathered on a bit (opps I have just realised this was a newbie question!), the reason being that I had to make this comparision for a company a while back - they said whats the best web/ intranet servers side technology - I said (after careful consideration of various factors, including such things as how easily existing staff could pick up the technlogy, and value for money) PHP running on Win32/IIS (offers the best solution - in my particular client's case - using a Windows Domain / MS-SQL Databases). What does everyone else think - or I am completely barking up the wrong tree? I expect a lot of Linux rocks use Linux and MYSQL/Postgres responses, but for those coming from a ASP background with IT managers wanting to use MS products whereever possible using PHP on Win32 makes sense. Luke Crouch [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... Does PHP have built-in support for using request and response objects? Thanks, -L -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] RE: drop down list
Something I have created for a project recently (note that I use my own db
wrapper functions - just substitute the dbconn(), dbquery(), dbnumrows() and
dbresult() for your database functions - e.g. mysql_connect() for mysql,
ms_sql_connect() - look them up in the PHP manual however as I am not sure
that those are completely right).
This code works quite well but the current value will be repeated (it will
be selected but it can also be found in the drop down -- fixes anyone?) and
I have tested it quite throughly (although I can't swear its bug proof as
this has only got to alpha stage in my current project). If you want
further explanation of the code / an example then let me know and I will dig
something out for you.
$id = the field name that the id value resides in the table
$name = the name field in the table,
$table is the name of the SQL table
$current is optional (hence is set by default to NULL)
and htmlelname refers to the HTML class name that the dropdown will be
given.
?php
// create a drop down HTML form element from the fields and table specified
function dropdownfromdb($id, $name, $table, $current = NULL, $htmlelname =
select) {
$sqlquery= SELECT $id, $name FROM $table;
$db = dbconn();
$res = dbquery($sqlquery, $db);
$j = dbnumrows($res);
if($j 0) {
print select name=\$htmlelname\\n;
for ($i = 0; $i $j; $i++) {
$val = dbresult($res, $i, 0);
print option ;
if ($current == $val) print selected ;
print value =\$val\\n;
print dbresult($res, $i, 1)./option\n;
}
print /select\n;
}
else print ERROR dropdownfromdb function failed;
}
?
Hope that helps, sorry if this email is a bit scrappy, very tired!
Ollie
P.S. Does anyone know of a better way than DIE() (or my above error message)
to capture errors - eg Java / C++'s exceptions? I couldn't find any mention
of exceptions in the PHP manual.
-Original Message-
From: B.J.Rumsey [mailto:[EMAIL PROTECTED]]
Sent: 30 January 2002 06:30
To: php-db
Subject: drop down list
I have two fields artist_id, artist. How do I put the contents of artist
into a dropdown list.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] arrays and email
It would appear that I should check if anyone else has answered a question
first! And get some more sleep!
Sorry!
Ollie
-Original Message-
From: olinux [mailto:[EMAIL PROTECTED]]
Sent: 29 January 2002 20:05
To: [EMAIL PROTECTED]
Subject: RE: [PHP-DB] arrays and email
The other thing you could do is populate a string of
emails and add the BCC header to your mail() function.
This way everyone's email will not be seen by all
other recipients.
Check out the docs at www.php.net for this one
a better way to build the string would probably be:
while ($row = mysql_fetch_row($result)) {
$real_name = $row[1];
$email_list .= $row[12];
}
Then just pass $email_list in the BCC - be careful as
some ISP's do not allow mass mailings to be sent in
BCC. [i.e. mine is limited to 99 emails and then it
gets spam blocked so i must send through the mailing
list feature]
olinux
--- Gurhan Ozen [EMAIL PROTECTED] wrote:
Hi kevin,
Seems like in your while loop, you are not
populating your list array
correctly with all the emails you have.
Try to have a count value and populate the array
list accordingly such as:
$count = 0;
while ($row = mysql_fetch_row($result))
{
$real_name = $row[1];
$email = $row[12];
$list[$count] = $email;
$count = $count + 1;
}
Hope this helps.
Gurhan
-Original Message-
From: Kevin Ruiz [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 29, 2002 2:14 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] arrays and email
I'm working on an application that will allow
someone to view all attendees
for a specific webinar that my company is hosting.
I want to allow the user
to send one group email to all participants
scheduled for that particular
webinar.
After I connect to my database my code looks like
this:
?
$sql = select * from webusers where
webdate=\$webdate\;
$result = mysql_query($sql) or die(couldn't
generate a list of the
users);
while ($row = mysql_fetch_row($result))
{
$real_name = $row[1];
$email = $row[12];
$list[] = $email;
}
echo form method=\post\
action=\doemailattendees.php\\n;
echo table width=\100%\ border=0
cellspacing=0 cellpadding=0
class=\orange4\\n;
echo tr\n;
echo td
valign=\top\pbTo:/b/p/td\n;
echo td valign=\top\p\n;
foreach ($list as $value)
{
print $value, ;
$to = $value;
}
echo /p/td\n;
echo /tr\n;
echo /table\n;
echo table width=\100%\ border=0
cellspacing=0 cellpadding=0\n;
echo tr\n;
echo td
valign=\top\pbSubject:/b/p/td\n;
echo td valign=\top\pinput
type=\text\
name=\subject\/p/td\n;
echo /tr\n;
echo tr\n;
echo td
valign=\top\pbMessage:/b/p/td\n;
echo td valign=\top\textarea
name=\message\/textarea/td\n;
echo /tr\n;
echo tr\n;
echo td colspan=2 valign=\top\input
type=\submit\
value=\submit\/td\n;
echo /tr\n;
echo /table\n;
echo /form\n;
?
The $to, $subject, $message variables then get
sent to a page that
actually mails the message. The problem I'm having
is that it's only being
sent to the last person in the array. I understand
why this is happening
but don't know enough about arrays to find a
solution. As my code shows I
ambitiously tried setting $to to the entire array
but that doesn't work.
If anyone would be kind enough to help me out I'd
greatly appreciate it.
Thank you.
Kevin
www.worktiviti.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail:
[EMAIL PROTECTED]
For additional commands, e-mail:
[EMAIL PROTECTED]
To contact the list administrators, e-mail:
[EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail:
[EMAIL PROTECTED]
For additional commands, e-mail:
[EMAIL PROTECTED]
To contact the list administrators, e-mail:
[EMAIL PROTECTED]
__
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions!
http://auctions.yahoo.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] RE: mssql_init(), mssql_bind() and mssql_execute() not working in Linux
I believe these additional functions are only available in the CVS version of PHP not the version you can download from the web site. But I could be wrong (someone correct me!) Oliver Cronk -Original Message- From: Duarte Costa [mailto:[EMAIL PROTECTED]] Sent: 23 January 2002 15:35 To: [EMAIL PROTECTED] Subject: mssql_init(), mssql_bind() and mssql_execute() not working in Linux Dear Sirs, I have compiled PHP 4.1.1 in SuSE Linux with Sybase+FreeTDS to use MSSQL_* functions. The mssql_connect(), mssql_close(), mssql_select() ... all works fine. But when I try to use : mssql_init (), mssql_execute(), and mssql_bind() I get an error: Fatal error: Call to undefined function: mssql_init() in /usr/local/httpd/htdocs/PHP/teste_1.php on line 10 How can I have it working ... ? Best Regards Duarte Costa -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
