mysql

Use content of fieldnames in query..

Hi, i have three mysql tables: Table 456 id | binder | property1 1 | b | hello 2 | b | goodbye 3 | a | bonjour Table binder id | binder | tableid1 | tableid2 1 | a | 23 | 456 2 | b | 21 | 456 3 | c | 45 | 42 Table 21 id | property1 | data.. 1 | goodbye | data about goodbye.. 2 | ciao | data about ciao.. So first i want to select in...

Get Template::Plugin::Date to accept MySQL dates as well as datetimes

I'm using the date plugin for Template::Toolkit (Template::Plugin::Date), it works well with datetimes (yyyy-mm-dd hh:mm:ss) pulled straight out of MySQL, but it will not work with dates (yyyy-mm-dd). What's the simplest way to get date.format to accept dates (without modifying the sql query)? Thanks. ...

MYSQL - datetime to seconds

Hello, I wasn't able to find out (googling, reading mysql reference manual) how to get value of DATETIME in seconds in MySQL. I dont mean to extract seconds from datetime, but to convert it into seconds. Thanx ...

Is UTF-8 enough for all common languages?

I just wanted to develop a translation app in a Django projects which enables registered users with certain permissions to translate every single message it appears in latest version. My question is, what character set should I use for database tables in this translation app? Looks like some european language characters cannot be stored...

mysql reference result from subquery

this is what i am doing update t1 set x=a,y=b where a and b are obtained from (select query here) i know the select query the select query returns multiple results which are the same when i use group by or distinct query execution slows down considerably a and b are forward references so mysql reports an error i want to set a equal t...

PHP/MySQL Swap places in database + JavaScript (jQuery)

I'm currently developing a website which stores bookmarks in a MySQL database using PHP and jQuery. The MySQL for bookmarks looks like this (CSV format): id,userid,link_count,url,title,description,tags,shareid,fav,date "1";"1";"0";"img/test/google.png";"Google";"Best. Search Engine. Ever.";"google, search, engine";"7nbsp";"0";"12675789...

How to self-update PHP+MySQL CMS?

I'm writing a CMS on PHP+MySQL. I want it to be self-updatable (throw one click in admin panel). What are the best practices? How to compare current version of cms and a version of the update (application itself and database). Should it just download zip archive, upzip it and overwrite files? (but what to do with files that are no longer...

Can I lock tables in an IF statement (or a stored proc) in MySQL?

This (from body of a stored proc) is throwing a syntax error: IF (name = in_name) SET out_id = temp; ELSE LOCK TABLE People WRITE; INSERT INTO People (Name) VALUES (in_name); UNLOCK TABLE; SELECT LAST_INSERT_ID() INTO out_id END IF do I have to lock any tables I need a...

dynamically drawing polylines on googlemaps using php/mysql

Hi. I am new to the googlemaps API. I have written a small app for my mobile phone that periodically updates its location to an SQL databse. I would like to display this information on a googlemap in my browser. Ideally i'd like to then poll the database periodically and if any new co-ords have arrived, add them to the line. Best way...

In MySQL, what is the most effective query design for joining large tables with many to many relationships between the join predicates?

In our application, we collect data on automotive engine performance -- basically source data on engine performance based on the engine type, the vehicle running it and the engine design. Currently, the basis for new row inserts is an engine on-off period; we monitor performance variables based on a change in engine state from active to...

How do I make all the finders on the model ignorecase?

I have a model with several attributes, among them title and artist. The case of title and artist should be ignored in all the Active Record finders. Basically, if title or artist are present in the :conditions (or dynamically i.e. find_all_by_artist), then the WHERE artist = :artist should become WHERE UPPER(artist) = UPPER(:artist) o...

How to count all rows when using SELECT with LIMIT in MySQL query?

I've got a mysql query like this: SELECT A.ID, A.NAME, B.ID, B.NAME FROM table1 A JOIN table2 B ON ( A.ID = B.TABLE1_ID ) WHERE cond1, cond2, ..., condN LIMIT 10 I've got many where clauses in query. How to improve this query to get also full row count? I don't want to use one more request without LIMIT. ...

Stop 2 identical queries from executing almost simultaneously?

I have developed an AJAX based game where there is a bug caused (very remote, but in volume it happens at least once per hour) where for some reason two requests get sent to the processing page almost simultaneously (the last one I tracked, the requests were a difference of .0001 ms). There is a check right before the query is executed ...

mysql select two prior to and one after NOW()

Hi I have a table with a column event_time. How can I select two rows right before NOW() and the next one after NOW(), ordered by event_time? Is is possible with a single query? ...

MySQL Query to Count Unique Domains from Email Address field

I'd like to get a better idea of what domains my customers are using. I could easily do this in PHP by explodeing each address and counting the domain that way. But I'm wondering if there's a way to get this information with just a plain MySQL query? This is what sample output would look like: gmail.com | 3942 yahoo.com | 3852 hotmai...

Speeding up inner-joins and subqueries while restricting row size and table membership

I'm developing an rss feed reader that uses a bayesian filter to filter out boring blog posts. The Stream table is meant to act as a FIFO buffer from which the webapp will consume 'entries'. I use it to store the temporary relationship between entries, users and bayesian filter classifications. After a user marks an entry as read, it w...

Given a Date "03/13/2010", using that in a MYSQL Where Clause?

I would like to pass a MYSQL query via Coldfusion the following date: 03/13/2010 So the query filters against it like so: SELECT * FROM myTable WHERE dateAdded before or on 03/13/2010 I'd also like to be able to take 2 dates as ranges, from: 01/11/2000, to: 03/13/2010 SELECT * FROMT myTable WHERE dateAdded is ON or Between 01/11/2000...

trying to connect mysql with vb.net

I've found this at connection strings.com http://connectionstrings.com/mysql Do I need to download connector-net from this site: http://dev.mysql.com/downloads/connector/net/ I recycled the code that I used in connecting vb.net with ms sql: Imports system.data.sqlclient idnum = TextBox1.Text lname = TextBox2.Text fname = Text...

warning in python with MySQLdb

hi, when I use MySQLdb get this message: /var/lib/python-support/python2.6/MySQLdb/__init__.py:34: DeprecationWarning: the sets module is deprecated from sets import ImmutableSet I try filter the warning with import warnings warnings.filterwarnings("ignore", message="the sets module is deprecated from sets import ImmutableS...

Collaborative filtering in MySQL ?

Hi I'm trying to develop a site that recommends items(fx. books) to users based on their preferences. So far, I've read O'Reilly's "Collective Intelligence" and numerous other online articles. They all, however, seem to deal with single instances of recommendation, for example if you like book A then you might like book B. What I'm tr...