Re: [PHP-DB] ROugh idea of speed

2002-11-09 Thread Thomas Lamy
Steve Vernon [mailto:steve;extremewattage.co.uk] wrote:

 Hiya,
 Just wondering what is the rough idea of speed of a 
 server like this is
 holding a database with millions of records. I know its 
 difficult, depends
 on the data stored etc.
 
 Its basically storing an index int and about 5 or so char 
 field (50
 long). In total I want to store 500 million records. Accessed 
 using PHP.
   a.. 2x Intel Pentium III 1260 CPU or higher
   b.. 1 GB RAM
   c.. 60 GB hard drive
   d.. 20 GB traffic/month
   e.. RedHat LInux 7.2
 Ive read that its better to store the data in different 
 databases on the
 same server?
 
 Can someone please give me a rough idea of the speed and how many
 servers needed, my client wants to know how much it will cost 
 to host the
 site.
 
  Anyone have any experience with holding a lot in MySQL? 
 Any idea of
 speed would be great.
 
One of my clients is running a special interest portal, with roughly 200-300
parallel users in peak times, and about 1000 SQL queries/sec (of which about
90% are SELECTs). For stability and scalability reasons, we have an LVS
(http://linuxvirtualserver.org/) cluster for http, and 2 mysql servers
(Athlon 1800+, 512 MB, SCSI-RAID) with active/active replication, having a
loadaverage of about 0.10 at peak times.
Our database is about 1 Gig now, storing authentication- and user data
(including user-uploadable pictures, which make 600 MB by now).

We had many problems with mysql running on the same machines apache was
running (freezes and deadlocks with 1 httpd consuming 400+ MB and
loadaverage 100+), for yet unknown reasons (kernel = 2.4.16 comes to
mind...), which were all solved moving to the above configuration.

I am really confident with the current setup, which is running for half a
year now.  But keep in mind that - most of the times - you get far more
performance by optimizing your database (and queries) than by upgrading
hardware (the --log-slow-queries and --log-long-format options to mysqld are
your friend).

Thomas

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




Re: [PHP-DB] Idea as to why this query won't work as expected?

2002-11-05 Thread Thomas Lamy
Dave Smith [mailto:DavidSmith;byu.net] wrote:
 
 I can never remember whether SQL is left-to-right, right-to-left, or 
 some other deviant. Using parens is a sure way to guarantee that your 
 statements are processed in the order you desire.
 
 --Dave
 
It's easy if you remember: AND is equal to multiplication, OR is equal to
addition. Multiplications are done before additions, so ANDs are done before
ORs.

Thomas

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




Re: [PHP-DB] catalog system

2002-10-21 Thread Thomas Lamy
Sorry, didn't want to be that offensive. It just comes through from time to
time.

Thomas

Sparks [mailto:alex;paychoice.com] wrote:
 
 Yes, I've been trying to learn what I need.  Yes, I've surfed the
 newbie/wanna be places.  I just asked if anyone knew of one, 
 not asking to
 build it for me.  Thought I'd try and learn from one and make my own.
 
 If someone will point me to the Nubee-forum, I will gladly go.
 
 Wannabe
 
 
 Thomas Lamy [EMAIL PROTECTED] wrote in message
 news:656F04F343FC25409463829A15B5FDDC08AFC4;netwake-nt.netwake.de...
  Doesn't seem so. Wannabes/Leechers never die. They haven't really
 understood
  what this media ought to be.
 
  Most of the time it's faster (and wiser) to switch brain on 
 for at least
 ten
  minutes, rather than surfing beginner's guides for hours. 
 Otherwise get
  yourself some training day(s).
  Really.
 
 
  [No, this was not off-topic]
 
  Thomas
 
  PS: I (and many others on the list) _like_ to help people 
 with problems
 when
  their stuck. But from time to time this list tends to be a 
 nubee-forum.  I
  take time from my projects to read this list (and answer 
 sometimes), just
 to
  give back to the community what I got - fast help when _I_ 
 got stuck.
 
 
 
  Aaron Wolski [mailto:aaronjw;martekbiz.com] wrote:
  
   Build you own.
  
   Does no one want to 'work' for their projects these days?
  
   *shrug*
  
   Aaron
  
   -Original Message-
   From: Sparks [mailto:alex;paychoice.com]
   Sent: Monday, October 21, 2002 9:29 AM
   To: [EMAIL PROTECTED]
   Subject: [PHP-DB] catalog system
  
  
   Greetings,
  
   I'm looking for a cataloging system.  I've found some but
   they all deal
   with e-commerce, which I don't want.  I'm trying to 
 catalog items by
   country, then by catagories, then by items.  I've been to all the
   regular download sites but no luck :(
  
   any help would be grateful
  
   sparks
  
  
  
   --
   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 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




Re: [PHP-DB] easier way to do this? (time interval)

2002-10-11 Thread Thomas Lamy

Hi,

convert your date to UNIX_TIMESTAMP, which is number of seconds since
1.1.1970, and you can use simple math, as in

  $query=mysql_query(
SELECT dtg 
FROM techs 
WHERE 
  tech='$user'
AND
  UNIX_TIMESTAMP(dtg)  UNIX_TIMESTAMP(NOW()) - 86400 
AND
  HOUR(dtg)=7
  );
... where 86400 = 24 hours (in seconds)

See http://www.mysql.com/doc/en/Date_and_time_functions.html#IDX1302


Thomas


 -Ursprüngliche Nachricht-
 Von: Thoenen, Peter Mr. EPS
 [mailto:[EMAIL PROTECTED]]
 Gesendet: Freitag, 11. Oktober 2002 06:34
 An: [EMAIL PROTECTED]
 Betreff: [PHP-DB] easier way to do this? (time interval)
 
 
 Hello,
 
 Curious if there is an easier way to do this (using just SQL 
 and not PHP).
 SQL seems powerful enough to do this but can't think of the 
 synatx.  Note, I
 am using MySQL so no sub-selects (or other useful items).  
 Basically trying
 to pull all records for a 24 hour period but instead of -2400,
 0700-0700 (next day).
 
 if (date(H)7){
 
   $query=mysql_query(
 SELECT dtg 
 FROM techs 
 WHERE 
   tech='$user'
 AND
   DAYOFMONTH(NOW())=DAYOFMONTH(dtg)
 AND
   HOUR(dtg)=7
   );
 
 } else {
 
   $query=mysql_query(
 SELECT dtg 
 FROM techs 
 WHERE 
   tech='$user' 
 AND 
 (
   (DAYOFMONTH(NOW())=DAYOFMONTH(dtg) AND HOUR(dtg)7)
   OR
   ((DAYOFMONTH(NOW())-1)=DAYOFMONTH(dtg) AND HOUR(dtg)=7)
 )
   );
 
 }
 
 Cheers,
 
 -peter
 

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




Re: [PHP-DB] Error query : mysql_result

2002-10-10 Thread Thomas Lamy



Burgess [mailto:[EMAIL PROTECTED]] wrote:
 Hi
 
 I have used the mysql_result function to return some specific 
 information
 from database.  The information is returned as it should but 
 I keep getting
 this error message:
 
 Warning: Unable to jump to row 0 on MySQL result index 4 in
 /u1.bath/s31/eh842/public_html/SCR/register/process.php on line 140
 
 My script, in part, reads as below:
 
133   // check to see if company exists in db
134  
135 $CompanyQuery = mysql_query(SELECT coid FROM co_details 
136  WHERE co_name =
137  '$company' AND co_city = '$city' AND co_country = '$co_country');
138  
139  $CompanyExist = mysql_num_rows($CompanyQuery);
140  $company_id= mysql_result($CompanyQuery,0,0);
141  
142  if ($CompanyExist  0) {
143  
144//if company exists add workplace details
145 $confirmkey =  md5(uniqid(rand()));
146$signup_id = signup_details($username, $title, $fname, 
 []
 
I have inserted the line numers where I think they are right, please correct
me...

This seems to be a common trivial error: in 139, you get the number of rows
found, but if there are none, you try to retrieve data in line 140, where
you get the error message. Exchange line 140 with 142, and the error message
will go away.

And please, the next time you ask here, post only code snippets (e.g.
beginning of function to 2-5 lines from where you get the error), and
include line numbers. Thanks.

Thomas

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




AW: [PHP-DB] OT...where do I go for this......?

2002-10-10 Thread Thomas Lamy

Have you checked apache's config file for:

AddType application/x-httpd-php .php
AddType application/x-httpd-php .php4
AddType application/x-httpd-php-source  .phps

?

IIRC Apache denies POST to file types not registered for... Also it may be
possible that your OS upgrade has overwritten your (old, maybe handcrafted)
httpd.conf.


Thomas


Michael Cortes [mailto:[EMAIL PROTECTED]] wrote:
 Gesendet: Donnerstag, 10. Oktober 2002 18:52
 An: [EMAIL PROTECTED]
 Betreff: RE: [PHP-DB] OT...where do I go for this..?
 
 
 I just checked and register_globals are on in php.ini.  I 
 have also previously been in the 
 httpd.conf in my previous attempts to fix this problem.   I 
 have googled my fingers off and all 
 fixes have mentioned the conf file for apache.  I have played 
 with options ExecCGI as well as 
 other options as per google search results.
 
 BTW... I am trying to use the POST method in my php scripts.
 
 
 
 On 10 Oct 2002 at 10:20, Naved, Masroor wrote:
 
  Michael,
  
  The easist way of fixing this is to turn register_globals on. It is
  defaulted to off since a few versions ago, I believe. 
 However, there are
  some security concerns you should be aware of with this 
 option. Google
  around for some discuss about this.
 
 On 10 Oct 2002 at 12:09, John W. Holmes wrote:
 
  That would be an Apache configuration issue. Somewhere in 
 http.conf, I
  think, you can choose whether or not to allow GET/POST operations.
  
  -Original Message-
  
  I am working on a simple inventory program and am running 
 into the following
  messageMethod 
  Not Allowed
  The requested method POST is not allowed for the
  URL/cafe_inventory/choose.php. Apache/1.3.23 Server at 
 (domain.net) Port 80
  
  If anyone can think of a mailing list, chat room, or even 
 paid tech support
  to help me with this 
  problem, I would greatly appreciate it.
 
 
 Michael Cortes
 Fort LeBoeuf School District
 34 East 9th Street
 PO Box 810
 Waterford PA 16411-0810
 814.796.4795
 Fax1 814.796.3358
 Fax2 978-389-1258
 
 
 -- 
 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




Re: [PHP-DB] update and join?

2002-10-10 Thread Thomas Lamy

Cory Hicks [mailto:[EMAIL PROTECTED]] wrote:
 Hello to all!
 
 Quick questionis it possible to do an update query w/ a 
 join yet? If not, what is your preferred method? Would you 
 take care of it on the PHP side?
No, it's not possible, at least not with MySQL 3.x. MySQL 4.1, currently in
development, will feature nested subqueries and multi-table-updates.
 
 I need to update a table w/ data from another table if 
 certain conditions are true, i.e the fields in the table to 
 be joined are NULL
 
I have to do that a couple of times, and always do it this way:
Build a SELECT statement, selecting all the data you will need in the
updates, and the primary key for the table which needs updates.
Fetch all the data in a nested array, like this:
  $a = array(primary key 1 = array (col1=data1,col3=data3),
 primary key 2 = array (col2=data2,col3=data3),
 );
and then, do the updates in a neat foreach loop:
  foreach ($a as $pkey=$data) {
[ build update stmt from $data array ]
dbquery ($stmt)
  }

Hope this helps.

Thomas

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




AW: [PHP-DB] error_reporting()

2002-09-30 Thread Thomas Lamy

Do you have
display_errors = On
in your php.ini ?

Ryan Jameson (USA) wrote:
 
 I do the following 
 
 ?PHP
 error_reporting(E_ALL);
 echo error_reporting();
 ?
 
 and it says 2047 and still reports no errors. What overrides 
 my setting?
 
 Thanks...
  Ryan

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




AW: [PHP-DB] getting mysql_fetch_row into array

2002-09-23 Thread Thomas Lamy

Hi,

it would have been easier to help you if you included some code snippet...

Use something like this:

$res = mysql_query (SELECT COUNT(deptid) FROM maintenance GROUP BY
deptid);
$deptcount = array();   // reset the array
while ($row = mysql_fetch_row ($res)) {
  $deptcount[] = $row[0];
}


Thomas

 -Ursprüngliche Nachricht-
 Von: LSC Exhibits Department [mailto:[EMAIL PROTECTED]]
 Gesendet: Montag, 23. September 2002 19:17
 An: '[EMAIL PROTECTED]'
 Betreff: [PHP-DB] getting mysql_fetch_row into array
 
 
 Going through brain-lock here. From this query Select 
 count(deptid) from
 maintenance group by deptid ,I get 11 rows. What I need to 
 do is get an
 array like $a=array(315,11,43,67,415,32,25,63,93,46,76) from 
 this query, so
 that 
 $a[0]=315
 $a[1]=11
 $a[2]=43 
 and so on. The best I can get is ;
 $a[0][0]=315
 $a[0][1]=11
 $a[0][2]=43 and so on. Does anyone have any ideas???
 
 John Coder
   
 
 -- 
 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




AW: [PHP-DB] Large files using phpMyAdmin

2002-09-22 Thread Thomas Lamy

Hi,

1. Problem (Uploads)
Have a look at your php.ini, max_upload_size is 2 MB per default, you may
wish to increase this. Not sure if phpMyAdmin has another parameter to
configure this...

2. Problem (pics messed up)
Seems to be some magic quotes problem. In your php.ini, check
magic_qoutes_gpc and magic_quotes. Can't tell how you designed picture
uploads, but if you did it the right way (my opinion, no flames :-), both
need to be Off.


Thomas


 Von: Steve Vernon [mailto:[EMAIL PROTECTED]]
 Gesendet: Sonntag, 22. September 2002 20:00
 An: [EMAIL PROTECTED]
 Betreff: [PHP-DB] Large files using phpMyAdmin
 
 
 Hiya,
 So on the net I ask for a dump of my database using 
 myPHPadmin, get a
 2mb sql file. Put it in my local copy of myphpadmin and it 
 wont accept it.
 If I remove some of the lines then it works so I suppose its 
 something to do
 with the size. Can this be fixed? Is it to do with timeouts?
 
 The other problem is that I store pictures in the 
 database, about 1.8mb
 worth. They look fine on the net but when i download they are 
 messed up!!!
 They used to till I formatted my hard disk, what have I done 
 wrong? Please!
 
 Just a quick note I have not got a command line access to 
 the online
 server only using myphpadmin and I have tried getting a dump 
 using IE and
 Netscape  to see if it was IE messing the file up!
 
 Thanks a lot,
 
 Steve
 
 
 
 
 -- 
 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




AW: [PHP-DB] strange behavior

2002-09-12 Thread Thomas Lamy

Hi,
this isn't essantially DB-related
It's a feature called transparent session id (activated at compile time with
--enable-trans-sid). No need for ?=session_name().'='.session_id()? in
most cases, as it is automatically added by PHP. And for the few times PHP
can not add session information, you may use the SID define, which is
automatically defined to session_name().session_id() if the user has turned
off cookies.

Read: http://php.net/session

Thomas

 -Ursprüngliche Nachricht-
 Von: Martin Adler [mailto:[EMAIL PROTECTED]]
 Gesendet: Donnerstag, 12. September 2002 14:34
 An: [EMAIL PROTECTED]
 Betreff: [PHP-DB] strange behavior
 
 
 Hi,
 
 i start a session on a entrypage with a form,
 and i wonder about the output to the browser.
 There appeared a input-field of the type hidden
 with the session-name as name and the
 session-id as value.
 How can I switch this PHP-behavior off?
 
 CODE
 td
   form name=focusedform
 action=index.php??=session_name().'='.session_id()? method=post
   input type=text name=auth_username size=22 maxlength=15
 /td
 
 
 OUTPUT
 ...
 td
   form name=focusedform
 action=index.php?PHPSESSID=5ac37e23f46a3c2c1388201e3f4a951f
 method=postinput type=hidden name=PHPSESSID
 value=5ac37e23f46a3c2c1388201e3f4a951f /
   input type=text name=auth_username size=22 
 maxlength=15
 /td
 ...
 
 
 greet
 Martin
 
 
 
 
 -- 
 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




AW: [PHP-DB] Pictures+MySQL+PHP

2002-01-27 Thread Thomas Lamy



 -Ursprüngliche Nachricht-
 Von: Michael Waples [mailto:[EMAIL PROTECTED]]
 Gesendet: Samstag, 26. Januar 2002 09:54
 An: [EMAIL PROTECTED]
 Betreff: Re: [PHP-DB] Pictures+MySQL+PHP
 
 
 Ck Raju wrote:
  
   that I changed my mind. Gurhan OzenStoring images in the 
 database is not a
   good idea , just store the path of Gurhan Ozenthe images 
 in the database
   and keep your images in your hard disk... Gurhan Ozen
  
  Since everything is on hard-disk, I personally feel, the 
 image can be stored
  anywhere. BLOB should be easier when doing a mysqldump, or 
 when replication
  is needed.
  
  Anywhere else go in for storing images separately.
  Raju
 I prefer storing images out of the dbserver to save on server 
 load. You
 can serve those images with a http server like thttpd, boa etc which
 will serve images quicker and with a lighter load.
 If you need replication just use rsync to move your images around.
 
 But if server load isn't an issue a database is as good as any place.

I did a community software where users can upload pics all over the
application. It is run on LVS cluster, so nobody can say to which of the
cluster members the pic is uploaded to. NFS, or unattended rsync/scp has
been denied by security policy, so the only way was to store the pics in the
DB (mysql).
I also did some performance measures, and, as all the pic requests had to go
thru php (for member verification), there was practically no performance
drawback; in fact, storing the pics in database was a bit quicker than
getting them through the filesystem when the number of pics went above
100,000 :-)
And now it is very easy to do full backups, and no problem with db/fs
getting out of sync.
Only one thing has given me a hard time: The first time I stored all 5
instances of an image (thumb plus 4 other resolutions) in the same db table,
but mysql choked on it when it became about 200 mb (the whole server did not
respond or respond veeery slow). I split the whole thing into 5 tables, and
now it runs like a charm.

Thomas  

--
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] Retrieving Rows - Can I Change The Order?

2001-07-28 Thread Thomas Lamy

 Von: Caleb Walker [mailto:[EMAIL PROTECTED]]
 Gesendet: Samstag, 28. Juli 2001 08:26
 An: Dave Watkinson; PHP-MySQL List
 Betreff: Re: [PHP-DB] Retrieving Rows - Can I Change The Order?
 
 
 
  Now then, I have changed a column in the table, and the 
 only way I found to
  change a data type is to delete the offending column and 
 then add it again.
 
  When I do the select now the new column, which was at 
 $row[3] is now always
  $row[39]. I don't mind this, because I know where it is, 
 but is there any
  way I can make it appear at $row[3] again? I'm sure you can 
 imagine when
  there are quite a few pages using this code it's easier to 
 reorder the
  columns in the select than to change all my if() codes!
 
You should use mysql_fetch_array() instead of mysql_fetch_row(), so you can
refer to your columns by name, as in:
$q = mysql_query(select id,name from address;);
$a = mysql_fetch_array ($q);
== now you can use $a['id'] and $a['name'] to get the field's values.

I found it always good practice to name all affected columns in a query, so
you have no pain  if your columns get mixed by db alters.


Thomas


-- 
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]




AW: [PHP-DB] Variable passing

2001-04-24 Thread Thomas Lamy

 Johannes Janson [mailto:[EMAIL PROTECTED]] wrote:
 
 You can output the passed varibles from the previous page
 in an input type=hidden  in the form of the following page.
 
 Johannes
 
But be sure to urlencode() your data before putting in the hidden fields,
and decode them when you enter the next page.

Thomas
 
 Brinzoi Constantin Aurelian [EMAIL PROTECTED] schrieb im 
 Newsbeitrag
 [EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
  Hi !
 
  Here is the problem: I got 8 pages, shown one after 
 another. Each have 2
  buttons (one for continue and one for abort). When I click 
 to Continue I
  go to the next page. On the last page I have to submit ALL 
 variables from
  these 8 pages to the database.
 
  My test shown that only the variables from the last page 
 are filled-in and
  otherones are empty.
 
  I have tried with another php page that includes all 8 -
  include(page...). Worthless!
 
  What can I do?
 
  TIA,
 
  Constantin Brinzoi
  Dept. Sondaje
  IRECSON
 
 
  --
  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]




AW: [PHP-DB] syntax error - -- agghhh!

2001-04-24 Thread Thomas Lamy

 Marc S. Bragg [mailto:[EMAIL PROTECTED]] wrote:

 
 Hi
 
 Anyone have any idea of what the syntax error in this statement is?
 
 else if (($op == ds)  ($action == sub)  ($password == 
 $passnog)
  ($password) 
 (!eregi(^([a-z0-9_]|\\-|\\.)+@(([a-z0-9_]|\\-)+\\.)+[a-z]{2,4
 }$,$email))
 {
 
 It worked fine until I added the tricky part:
 
 
 (!eregi(^([a-z0-9_]|\\-|\\.)+@(([a-z0-9_]|\\-)+\\.)+[a-z]{2,4
 }$,$email)
 
You are missing a ) to close the if expression

Thomas

-- 
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]




AW: [PHP-DB] Concurrent update to database (PostgreSQL or MySQL) ??

2001-04-18 Thread Thomas Lamy

Another way is to keep another unique value inside the table to be updated
and remember it.
When needed, I add a second unique column to the table (in my case a
char(64)) which is filled with the current timestamp and some md5 checksum.
I select this value before the update, pass it along with the HTML form,
and, before updating, I re-select the row to be updated and compare the
keys. If the comparision fails, the user is presented a warning message,
else I do the update (the user's data and a new generated stamp-value) with
the primary key _and_ the original stamp in the where clause. Then I check
if my new stamp made it to the table, or present another warning.

A sample:

Table atable:
id int not null primary key
stamp  char(64) not null unique
avalue int

select id,stamp,avalue from atable where id=1   (select data for update)
build html form with "id" and "stamp" as hidden values

select stamp from atable where id=the_id
if stamp(form) != stamp(db)
  error (e.g. start from beginning)
else
  construct new_stamp
  update atable set avalue=new,stamp=new_stamp where id=the_id AND
stamp=old_stamp
  select stamp from atable where id=the_id
  if stamp(db) != new_stamp
error
  endif
endif

It's a bit of work, but it had never let me down.

Thomas



id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
  Is what I use in the WHERE clause to update data
ts CHAR(32) NOT NULL UNIQUE
  I 
 -Ursprngliche Nachricht-
 Von: Doug Semig [mailto:[EMAIL PROTECTED]]
 Gesendet: Mittwoch, 18. April 2001 20:48
 An: [EMAIL PROTECTED]
 Betreff: Re: [PHP-DB] Concurrent update to database (PostgreSQL or
 MySQL) ??
 
 
 As others have mentioned, this is a tricky thing to do.
 
 What others haven't mentioned is a way to do it.
 
 Let's say you just showed the update form to two users.  It 
 contains the
 fields that they are allowed to update and the values in 
 those fields had
 been retreived from the database.
 
 Both users see a form like this:
 
 Today's High Temperature
 
 City: Detroit
 Temp (degrees F): 47
 
( Submit )
 
 The city field is not available for update (presumably the editor/user
 selected it off of a previous menu), and their task is to 
 update today's
 high temperature for Detroit.  One of the editors has 
 information that the
 high temperature reached 49 degrees F, and the other editor 
 has information
 that the high temperature has reached 50.  (Perhaps one of 
 the editors is
 overworked and hadn't gotten to enter the new high, or the 
 temperature is
 changing very quickly.)
 
 So one of the people enter 50 and the other enters 49.  Both 
 hit submit.
 
 What needs to be done right at that moment to protect it from 
 changing the
 value to 50 degrees (which is the correct, most recent, data) and then
 immediately changing the value to 49 degrees (which is now 
 out of date)?
 
 Basically, you have to pass the original value to the script 
 that is the
 ACTION of the HTML form.  This way, you have the original 
 value that may
 have been updated and the new value.
 
 The first thing the HTML form's ACTION script has to do is 
 get the record
 from the database again.  (Use SELECT ... FOR UPDATE if 
 available, so the
 RDBMS might lock the row.)  If the value you get from the 
 SELECT is the
 same as the original value, go ahead and run the UPDATE to 
 change the value
 to the new value.  If not, then generate and display a screen 
 telling the
 editor that the value has been updated by someone else...you 
 can show the
 value and ask if they want to proceed with the update, but 
 that's all up to
 you and/or your interface designers.
 
 This is all similar to how folks design old fashioned screen-oriented
 database systems (like with CICS).
 
 Good luck,
 Doug
 
 At 01:47 PM 4/18/01 +0200, Nicolas Guilhot wrote:
 Hi,
 
 How can I avoid two or more users to update the same record 
 at the same time
 ?
 
 Ideally, I would like to lock the record when a user open it for
 modification. So if another user try to open the same record 
 he'll be warned
 or get record values as read only. Is this possible and how 
 to do it with
 PHP ?? How can I know that the user that has locked the 
 record has finished
 with it, if he never commits his changes ? Is there an FAQ 
 about this ?
 
 Regards,
 
 Nicolas
 
 
 
 
 -- 
 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]