Hi, i decided to do a member status indicator for my script, such as brb, on phone, away, that sort of thing and so i made a small table to hold the status.
It only has two fields, mem_id and status. i didnt originally have a primary key but after doing some reading on this option i figured i needed one so i made the mem_id the unique primary key.
I know there are concerns regarding server stress for lack of a better word when using this option on larger tables, but since this table is so small, im wondering if it is efficient to use this option.
The mod is built and it works, but i did want to ask because i trust the feedback here.
Here is my query.
PHP Code:
//update the status table
$query = "REPLACE INTO member_status VALUES ('$userid', '$status')";
$retvalu=mysql_query($query,$link) or die(mysql_error());
It should be fine. Using INSERT...ON DUPLICATE KEY is probably slightly faster, but as you said: It's a small table. Yes, you *MUST* have a primary key. The docs point out that without one REPLACE is converted into a simple INSERT.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Old Pedant may know offhand as well; I believe that INSERT. . . ON DUPLICATE KEY actually issues an update (minus no key which makes sense of course), while the REPLACE issues a DELETE. . . INSERT. This is a big difference if you were to use enforced cascade delete foreign keys. For example a simple query of REPLACE INTO mytable (col1, col2) SELECT col1, col2 FROM mytable (I know its a terrible example); if mytable has cascade delete to mytable2, doing the above would effectively give you mytable in the exact same state as originally created, but mytable2 has now been truncated.
//update the status table
$query = "INSERT INTO member_status(mem_id, status)
VALUES ('$userid', '$status')
ON DUPLICATE KEY UPDATE status = '$status' ";
$retvalu=mysql_query($query,$link) or die(mysql_error());
Last edited by durangod; 10-11-2012 at 07:55 AM..
Reason: removed single quotes from field names, oops lol
MySQL is in loose mode by default. You can set it into strict mode which forces your datatypes to match. Its also a good habit to get into; I believe that MySQL is the only dbms that actually does implicit datatype conversions.
Using the wrong datatype means that MySQL has to convert it before using it. This has tremendous overhead and you can see it on batch inserts.
Thanks, i have to go thru the files anyway and fix 65 occurances of
PHP Code:
SELECT *, field, field, field
to proper format, so ill do that at the same time and go thru and any int VALUES in the INSERT querys i will make sure they are without quotes at all...
Before you post, read our: 
