(mutters incomprehensibly)First, why do you fetch all records from the Logs table if you are only interested in the one with host = ???? This is a terrible waste. Even if there was not an index on the column, it would still be much much quicker to ask the database to find the one row instead of forcing it to send you all.

Second, function calls are not evaluated within doublequoted strings. Anyway you should NEVER interpolate a variable into some SQL you are gonna execute unless you either quote the variable or make sure it only contains safe characters. If you compute a number or test that the variable only contains a number you are safe, but guess what happens if you blindly interpolate a variable into a SQL like that and the variable contains a singlequote. Search the Net for "SQL injection attack". It's generaly prefered to use placeholders.

It's not the best idea to use the INSERT INTO TableName VALUES (...). You should always specify the columns into which you are inserting the data. Imagine you add a column to the table later or reorder the columns. Booooooom. If you are lucky. If unlucky it'll still "work" and only several weeks later you find out that the data are bogus, because the columns were mixed up.

Even if you did use a sensible SELECT statement, used placeholders and specified the columns to insert into there would still be a bug in your code. What if two instances of your script are fired at the same time? With the same host? The first one checks whether there is a row for that host, but before it'll be able to insert a row, the second script gets the CPU, runs the same check, also finds out that there is no record for the host and then both scripts insert a new row for the host. Not too good I guess.

So what you should do is to set the database so that there is an index on the "host" column and the database doesn't allow duplicates in this column. And then use code that will try to UPDATE the row where "host = ?", if the update does not affect any rows it will try to INSERT new row with that "host" and if this fails with the "Cannot insert duplicate value ..." error, UPDATE the row where "host = ?".

You might also use transactions (if your database supports them), but that would require table level locks (to make sure no rows are inserted between the UPDATE and the possible INSERT) and that might degrade the performance.

Also ... if your database supports that you'd better create a stored procedure that would do the UPDATE, INSERT, UPDATE for you. That'd be more efficient than to contact the database up to three times and have it compile the SQL and build the execution plan for each visit.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.