> Try to design your tables so, that it would be impossible to enter
> dublicate primary keys. And don't let users (if it's CGi application) to
> set or play with your primary keys (well, we could talk more about this
> one).
> If you used AUTO_INCREMENT, or UNIQUE, I bet you wouldn't have such
> problem.
That is the case, and that is why I get the error.
This is a typical setting:
CREATE TABLE staticStrings (
auto mediumint(4) unsigned DEFAULT '0' NOT NULL auto_increment,
ruri varchar(255) DEFAULT '0' NOT NULL,
staticString mediumtext NOT NULL,
PRIMARY KEY (auto),
UNIQUE ruri (ruri)
);
You see, ruri is unique. We get an error on that key.
This is the error:
Error: Duplicate entry 'static/75e' for key 2,
query:
Invalid SQL: INSERT INTO staticStrings
(ruri, staticString)
VALUES ('static/75e', '<!doctype html public \"-//w3c//dtd html 4.0
etc.
What's this for?
I use this to prevent calling the database on dynamic content
every time when no change happened to the query because the
database has not changed.
This way, I could improve response time. Typical time usage:
0.6480 dynamic generation of page from database
0.0056 static copy in table
so that is significant. If the database changes, the static
copies are dropped and rebuilt the first time they are called.
The code for this typically is:
if (checkStaticContent($trigger)) return ;
which means that in case the condition is met and the static
content is found we dont generate the page dynamically.
Else we open a buffer, generate the page, close the buffer and
store it in the table, then output the buffer.
This could be improved if the buffer would be stored in a file
which raises security questions (well, set up a cron job for
this).
I get this kind of error on another table which records sessions:
CREATE TABLE sessData (
sid varchar(6) NOT NULL,
varsVal mediumtext NOT NULL,
datum datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
PRIMARY KEY (sid),
KEY datum (datum)
);
sid is, of course, the sessionID, varsVal is the data, datum is
needed to delete sessions when they are defined to expire. So the
structure is pretty simple.
sid has to be unique, but it should not be possible to guess it.
Also I didn't want to get a long key, so I rolled my own
function:
function getSessionID(){
global $sessionID;
if ($sessionID) return;
$test = 1;
//start, if we have no sessionID (i.e. from a Cookie)
$db = newDB($db);// get a PHPLIB class instance
while($test){
mt_srand((double)microtime()*1000000);//seed
$sessionID = substr(md5(uniqid(mt_rand())), 0, 4);
//generate a random key, md5 and chop it
$q = "SELECT * FROM sessData
WHERE sid = '$sessionID'";
$db->query($q);//se if we have it already
$test = $db->nf();
if (!$test){//no, grab it
$datum = date("Y-m-d H:i:s");
$q = "INSERT INTO sessData
(sid, varsVal, datum)
VALUES ('$sessionID', 'dummySession', '$datum')";
$db->query($q);
if ($db->Errno) getSessionID();
//if error, try again, just in case
}//else it is taken, get next
}
}
Of course, nobody can play with the tables. They are served by
the program exclusively.
--
Herzlich
Werner Stuerenburg
_________________________________________________
ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen
Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409
http://pferdezeitung.de
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
