mysql

3rd <day_of_week> of the Month - MySQL

I'm working on a recurrence application for events. I have a date range of say, January 1 2010 to December 31 2011. I want to return all of the 3rd Thursdays (arbitrary) of the each month, efficiently. I could do this pretty trivially in code, the caveat is that it must be done in a stored procedure. Ultimately I'd want something lik...

Does "select for update" prevent other connections inserting when the row is not present

Hi all, I'm interested in whether a select for update query will lock a non-existent row. e.g. Table FooBar with two columns, foo and bar, foo has a unique index Issue query select bar from FooBar where foo = ? for update If query returns zero rows Issue query insert into FooBar (foo, bar) values (?, ?) Now is it possible that t...

Am I saving myself from sql injections?

Am I doing this right? Will this help avoid sql injections? $deleteid = htmlspecialchars(strip_tags(mysql_real_escape_string($_POST['listid']))); mysql_send("DELETE FROM stage where listid='$deleteid'"); ...

How decently track the database structure modification by SVN?

The main problem is the versioning of the database structure. The standard mysqldump and pg_dump utilities do not produce very well suited files for versioning. The dump commands produces the dump files with autoincrement values, TOC entries and so on. Since these objects is subject to continuous changes it always produces the huge di...

PHP form will not post.

I have just implemented mysql_real_escape_string() and now my script won't write to the DB. Everything worked fine before adding mysql_real_escape_string(): Any ideas?? $name = mysql_real_escape_string($_POST['name']); $description = mysql_real_escape_string($_POST['description']); $custid = mysql_real_escape_string($_SESSION['custome...

Hibernate tuning for high rate of inserts and selects per second

Hello, We have a data acquisition application with two primary modules interfacing with DB (via Hibernate) - one for writing the collected data into DB and one for reading/presenting the collected data from DB. Average rate of inserts is 150-200 per second, average rate of selects is 50-80 per second. Performance requirements for both...

Should I use a subquery?

I have two tables, one that stores the current price, and one that stores the historical price of items. I want to create a query that pulls the current price, and the difference between the current price and the most recent historical price. In the historical table, I have the start and end times of the price, so I can just select the...

Database Relationship or friend table design for a social networking site

Hello Im in the midst of creating a social networking site and I would like to know how I would go about creating the relationships between users. Many sites say that I should create a relationship/friend table, but Im looking into the future and believe that this would be ineffective. This idea could be as popular as facebook and I wan...

Mysql query with user-defined-function - why function was called twice?

I have a 1:1 relation between tables Entity and Contact (that corresponds to object 's inherirance). fn_match(id) is UDF which returns boolean and returns true if record matches some special criteria (additional queries inside function). That's a query: select * from Entity a inner join Contact b on a.id = b.id where fn_match(a.i) It ...

A mysql query that gives the most recent revision for each entity

I have my data split across two tables. One is the entity table where it describes the basics of the entity, and the revision table where the data of an entity resides. If I know the entity I can query for the most recent version: SELECT * FROM `entities` JOIN (SELECT * FROM `revisions` WHERE `entity_fk` = ? ...

Large amounts of static data to populate a webpage - store in a database, or static?

I have a large amount (several gigabytes worth) of archival data that I want to make available to users and search engines through a web interface. Most of the data will rarely change, so I'm debating the best way to store and deliver the data. I would like to ensure that the data loads quickly and efficiently so it can easily be viewed...

HTTPService & MySQL & PHP

I can't seem to post needed information to my database, here's what I've got: <mx:HTTPService id="someService" url="http://name.domain/postPHP.php" method="POST"> <s:request xmlns=""> <name>{name.text}</name> <score>{score.text}</score> </s:request> </mx:HTTPService> And of course a button to send(); P...

MySQL Multiple databases schemas to update => performance

Hi you all, I have thousands of databases schema to update (add a column, update some rows and add 3 rows in 2 different tables). I have 2 different approaches to do so: 1) Put the name of each schema before table names # database A01be91a86 UPDATE A01be91a86.ACTION set CODE_QUALIFICATION.... ALTER table A01e02a697.IMPRESSION add co...

mysql permissions needed to do a ALTER TABLE in database

grant LOCK TABLES, SELECT,ALTER,INSERT,CREATE ON `databasetoupgrade%`.* to 'someuser'@'localhost'; those are the privileges I gave a users that needs to be able to ALTER a table (add columns, ...) the mysql documentation states that alter, insert, create is needed, but even with lock tables and select permissions, I still get the erro...

using php variable in mysql LIKE

Hi! I want to write a mysql query something like this: select * from books where title like '$title_'; The $title is a php variable. when i run the above query, it throws an error saying '$title_ variable not found' How can I achieve this? Thanks.. ...

Connection pool query results jumping between live and old data?

I'm developing a Java SE application with a MySQL database. It uses a connection pool of size 10 as there are many screens. Each screen has a Thread that will update some JTables about every 10 seconds. My connection pooling code is taken from http://java.sun.com/developer/onlineTraining/Programming/JDCBook/conpool.html with a few method...

What is the difference between ON and WHERE in mysql JOINs?

Possible Duplicates: Difference between and and where in joins In MySQL queries, why use join instead of where? What is the difference between SELECT * from T1 JOIN T2 ON T1.X = T2.X AND T1.Y = T2.Y and SELECT * from T1 JOIN T2 ON T1.X = T2.X WHERE T1.Y = T2.Y ? ...

MySQL stored procedure fails when called from R

This procedure works from the MySQL commandline both remotely and on localhost and it works when called from PHP. In all cases the grants are adequate: CREATE PROCEDURE `myDB`.`lee_expout` (IN e int, IN g int) BEGIN select lm.groupname, lee.location, starttime, dark, inadist,smldist,lardist,emptydur,inadur,smldur,lardur,emptyct,entct...

Which is the best way to perform this query? Maybe UNION

I have these four tables described bellow. Basically I have feeds with entries relationed with categories, and each category can be a main category or not (flag named as "principal"). Also each feed can be a partner feed or not (flag named as "parceiro"). I want to select all feed entries from partrners feeds, so I have this: SELECT `e...

Coldfusion problem with mySQL binary logging?

I just tried adding binary logging to my mySQL "my.ini" file with the intention of being able to do incremental backups. However, I immediately provoked an error Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT' CF 9.01, mySQL 5.1, on an Win XP developm...