The method employed by the company I work for is to maintain a version
controlled set of scripts, which can be used to create/update a database
(many of these scripts are objects such as UDFs, Stored Procedures and so
forth, which represent the 'software' layer within the database).
A bespoke
This is certainly the first step.
Also, if you want to insert only those rows which are not already present,
you can use something akin to:
INSERT INTO table1
SELECT * FROM table2 t2
LEFT JOIN table1 t1 ON (unique row identifiers -- whatever these are for
your data)
WHERE t1.XYZ IS NULL
Essentially, I think, you are asking about organizing hierarchical data.
This is something which I always find tricky to wrap my head around, but,
one approach I seen used successfully is to use (initially) two tables:
1. A table such as 'Places' which contains the actual data (or Locations,
any
Hi Martin,
Speaking more from a general standpoint (I mostly work with MS SQL, but my
home projects are MySQL - these days there is precious little time for
those, though)..
Assuming you have appropriate indexes on tables y and y2:
1. Truncate WILL be faster than delete, as it is a non-logged
There are a number of approaches which you could take to this.
One option would be to 'pre-assign' rows to a server - so that each server
has a distinct set of rows to work through.
Another would be to use transactions to handle this, so that only one set of
updates actually occur at a time
Hmm.
Bit Odd. However, I suspect the problem is that your 'where' isn't explicit
enough:
Where ( month(date) = month(now()) ) or ( month(date) = month(now())-1 )
Bear in mind that if month(now()) = 1 you will be looking for records in
month 0!
A better way to do this might be:
WHERE
As others have said, you can use 'IN'. You could also use UNION (although I
don't think I would, personally, for quite what you want!):
SELECT word FROM word_table WHERE id = 1
UNION
SELECT word FROM word_table WHERE id = 2
Etc. Assuming your version of MySQL supports the UNION operator!
To me, this is entirely a matter of personal choice - and the important
thing is to pick a standard and stick to it. :)
I usually end up with a table called 'People' for arguments sake, which will
have an abstract PK (auto increment int) called PeopleID (I always use the
table name). I also
I do something similar in my PHP applications - using an object wrapper to
the SQL connection. That way, when an error occurs, the object automatically
outputs the query, along with any error which was returned.
Cheers,
Matt
-Original Message-
From: Richard Bryson [mailto:[EMAIL
As Jeremy says - it depends totally on what you want to do.
If you have tables where there is no logical, unique way to identify that
column (or the only way to do so is via a column you do not want to use for
this purpose), then assigning a separate ID column as a PK makes sense.
E.g: If you
Hi all.
Another hiccup along the happy road with MySQL 5.0!
The last bit of a stored procedure I have just put together does this:
-- insert cluster row
INSERT INTO clusters (Name) VALUES (sName);
SELECT LAST_INSERT_ID() INTO iNewClusterID;
-- insert map row
INSERT INTO map (X, Y) VALUES
,
Matt
-Original Message-
From: Matt Chatterley [mailto:[EMAIL PROTECTED]
Sent: 23 April 2004 02:08
To: 'MySQL List'
Subject: LAST_INSERT_ID() and Stored Procs
Hi all.
Another hiccup along the happy road with MySQL 5.0!
The last bit of a stored procedure I have just put together
I suspect you want 'IS NULL' rather than '= NULL'. :)
I always find it best to think of NULL as undefined value rather than no
value - which is why you need to check for it especially (using IS rather
than = or other operators).
Cheers,
Matt
-Original Message-
From: Yonah Russ
it installed on Windows at the moment - because it's more
convenient to debug (and restart)!
Cheers,
Matt
-Original Message-
From: [EMAIL PROTECTED] [mailto:mysql-list-
[EMAIL PROTECTED]
Sent: 15 April 2004 20:29
To: Matt Chatterley
Subject: Re: Slow Connection from PHP to MySQL 5.0
do
Hi Folks.
I've seen similar questions asked both here, and via a google search - but I
haven't found an answer which has helped yet - so heres hoping!!
I run a Linux webserver which has PHP 4 installed, and am currently
prototyping a design using MySQL 5.0-alpha on Windows 2K professional. The
As I discovered recently, thanks to another user on this list, there is at
least one situation where you WILL need to also create a KEY index on a
PRIMARY KEY column -
If you have a composite primary key such as (col1, col2) and you wish to
place a foreign key on col2, you will ALSO have to add a
Yep. Theres no reason at all why this sort of thing won't work for MSSQL
too. Use SQL Enterprise Manager to generate a create script for all objects
in the database, and also tell it to script referential integrity (FKs,
etc).
Then add anything MySQL specific, such as Type=InnoDB (which you will
Hmm.
SELECT o.*
FROM orders o
INNER JOIN orderitems oi ON o.orderid = oi.orderid
INNER JOIN products p ON p.productid = oi.productid
AND p.productparentid 2
WHERE o.orderstatus =2
Not sure why you are checking for a NULL ordered in orderitems? That would
suggest you get back only items
Hi folks.
I've just submitted the following as a 'bug' via the MySQL website, and was
wondering if anyone out there had experienced the same problem?
It only seems to cause a crash when a nested IF is put into a stored
procedure, so the work-around is obvious - don't nest 'em!
As posted to
Hmm.
You might be best off using the FIND_IN_SET function (check the manual at
mysql.com for more information).
In short, if you replace all spaces in your string to search with commas,
you should be able to do something like:
SELECT * FROM xyz WHERE FIND_IN_SET(test_column, your string here)
I'm not 100% sure as to what you are trying to do, however, the relationship
you describe could, I believe, be modeled as:
Candles (candleid, description, price)
Waxes (waxid, name/description)
Candle_Waxes (candleid, waxid)
Thus one table holds the description and price of each candle, another
Morning :)
1. You sure can, e.g:
CREATE PROCEDURE `user_authenticate`(IN sUserName VARCHAR(25), IN sPassword
CHAR(32), OUT sUserCookie CHAR(32))
BEGIN
DECLARE iUserID INT;
DECLARE iLogID INT;
SELECT MD5(CONCAT(UserID,NOW())) INTO sUserCookie FROM users WHERE UserName
= sUserName AND Password
To select the contents of both into one table, you most likely want to use
the 'UNION' operator:
SELECT * FROM desktops
UNION
SELECT * FROM laptops
If you create the computers table before hand (you can see how you would
create either of the others with SHOW CREATE tablename), then you can just
Hi folks. I have a problem creating a foreign key constraint which I just
don't seem to be able to figure out.
There are three tables, for which the 'show create' output is given below:
CREATE TABLE `users` (
`UserID` int(11) unsigned NOT NULL auto_increment,
`ContactID` int(10) unsigned NOT
One option would be to 'union' the two queries (assuming the columns are the
same type and length), allowing you to run one query string:
Select serial from blacklist where serial = x
Union
Select serial from seriallist where serial = x
Would return 1 or 2 rows, depending on whether rows are
Hi all,
I hope I've chosen a suitable list for this post. If not, please feel free
to redirect me! :)
By day, I am a SQL Server (and .NET) programmer. By night, my current
project (a space-based trading/strategy game to be delivered on the web,
should anyone be interested in contacting me
The only idea which presents itself (be warned, this is ugly) is to maintain
a set of tables which hold the same data, partitioned out by privilege, and
to grant access on those tables to appropriate users.
These tables could be maintained (or recreated) from the source data at
regular intervals.
Can you roughly outline the schema of the two tables?
If the key value doesn't have to match from the input to the final result,
you can always insert into a list of fields, skipping the auto_increment/key
column, and they will continue to be generated..
Assuming you have two tables with id_col,
the temporary table back
into the original.
I don't like that at all, though. It seems messy. Hopefully someone here can
come up with a better idea!
Cheers,
Matt
-Original Message-
From: Eric J. Janus [mailto:[EMAIL PROTECTED]
Sent: 29 March 2004 20:12
To: Matt Chatterley; 'MySQL'
Subject: RE
Hmm.
Taking a stab in the dark here, but..
If it's purely a 'hit counter' type affair, and you're updating a single
row, perhaps performance could be gained by instead inserting into a table
(presumably this can be done with single-row level locking - at least in
InnoDB?), and running a regular
Very straightforward if the new column is NULLable OR has a deterministic
value, e.g.
INSERT INTO newtable (col1, col2)
SELECT col1, 1 FROM oldtable WHERE ID = xxx
Or if NULLable:
INSERT INTO newtable (col1)
SELECT col1 FROM oldtable WHERE ID = xxx
Equally you could populate 'col2' from an
The not very useful answer would be: It depends on what you are trying to
achieve.
More usefully (I hope):
I work with a complex web application which is also strongly modularized.
This system is generally delivered using several databases, residing on
different servers, to allow us to cope with
Hi.
Bit off-topic (ish) I know, however:
You are missing a semi-colon in the 'forum snippet'.
The line:
$conn = mysql_connect(localhost, forums,
forumuser, somepass)
or die(mysql_error())
should be:
$conn = mysql_connect(localhost, forums,
forumuser, somepass)
or
Making the assumption that you are running a version of MySQL which supports
subqueries, I believe you could use:
SELECT Login, TimeStamp, IP
FROMSessions S
INNER JOIN (
SELECT MAX(TimeStamp) TimeStamp, Login
FROMSessions
Had my brain been in gear, I would have typed the 'AND Latest.TimeStamp =
S.TimeStamp' which you will also need on that join..
Cheers,
Matt
-Original Message-
From: Matt Chatterley [mailto:[EMAIL PROTECTED]
Sent: 20 March 2004 19:51
To: 'motorpsychkill'; 'mysql'
Subject: RE
Possibly veering off topic, but I have a strong urge to comment on this, and
shall!
I am a M$ .NET developer (primarily ASP.NET with SQL Server), and have
recently embarked on a project at home, and wished to apply the same sort of
principles that I use at work - for example, keeping all 'system
This reply has two purposes.
Firstly, a small suggestion (modest compared to the others which will
follow, no doubt!) - check out mysql_escape_string() - this may be useful to
you.
Secondly, a further question:
In addition to protecting against SQL Injection, has anyone here
experimented with
37 matches
Mail list logo
