mysql

MySQL: how to write a query for a relationship that is based on a range, not a foreign key?

I have three tables in MySQL that are related, yet not technically linked to each other by a foreign key. They are: users, levels, and classes. The users table has a karma column, a numeric type. Based on this karma number, I want to know the user's level, which I retrieve from the levels table. This is not a hard relationship, since a...

mysql union question

mysql> select job_desc_title from postings where id=194582; +-----------------------------+ | job_desc_title | +-----------------------------+ | Speech/Language Pathologist | +-----------------------------+ 1 row in set (0.00 sec) mysql> select email_address,first_name,last_name,home_phone_area,home_phone_num from accou...

Why does this SQL INSERT statement return a syntax error?

Ok I have a very simple mysql database but when i try to run this query via mysql-admin i get weird errors INSERT INTO customreports (study, type, mode, select, description) VALUES ('1', '2', '3', '4', '5'); Error: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for t...

MySQL: query the top n aggregations

I have a table which counts occurrences of one specific action by different users on different objects: CREATE TABLE `Actions` ( `object_id` int(10) unsigned NOT NULL, `user_id` int(10) unsigned NOT NULL, `actionTime` datetime ); Every time a user performs this action, a row is inserted. I can count how many actions were p...

displaying all rows in a calculation?

I have a MySQL table of correlation data that I need to extract. I am running this query to find the correlation between x and y given the id; however, I need to run x.id=1 against y.id=2, 3, 4, 5... Currently, I am just running the below command multiple times for each pairing. Is there a way to speed up the query so that I can just run...

Subsonic MySQL Version

I understand that Subsonic 2.2 requires the MySQL.data 5.2.3 dll. I'm trying to integrate a subsonic DAL for a VB project in Visual Studio. The project already uses a lot of datasets. Because of this, Visual Studio requires that I have the MySQL NET connector software installed, which automatically throws its own MySQL dll deep into ...

Best Spring/Hibernate configuration for never changing tables

I'm currently using Spring+Hibernate+MySQL for a project I'm working on. I realized that a have quite a few tables which never change. They are static, no insert or updates can ever occur on these tables, and so can be considered immutable. All accesses to these tables are via lazy loading (can be made eager) of entity collections and...

Can Hibernate work with MySQL's "ON DUPLICATE KEY UPDATE" syntax?

MySQL supports an "INSERT ... ON DUPLICATE KEY UPDATE ..." syntax that allows you to "blindly" insert into the database, and fall back to updating the existing record if one exists. This is helpful when you want quick transaction isolation and the values you want to update to depend on values already in the database. As a contrived exa...

strange problem with mysql stored function

It's just for test, but pretty strange: DELIMITER | DROP FUNCTION IF EXISTS get_idiscussion_ask| CREATE FUNCTION get_idiscussion_ask(iask_id INT UNSIGNED) RETURNS TEXT DETERMINISTIC BEGIN DECLARE done INT DEFAULT 0; DECLARE body varchar(600); DECLARE created DATETIME; DECLARE anonymous TINYINT(1); DECLARE screen_name va...

What is causing this MySQLSyntaxError exception?

Hi all, i written this query (in java class) to select some information from the MySQL database and view it on jsp page... SELECT instructor.name FROM instructor,section,teach WHERE teach.student_id='3'AND teach.section = section.number AND section.instructor_id= instructor.ID but there is exception was occur! javax.serv...

MySql Group by

Hi all so I have the following table, 't1' id r_id o_id count 1 2 100 1 2 3 100 1 3 5 100 1 4 2 101 2 5 3 101 2 6 4 101 2 What I'm trying to do is, for a given list of r_id's, return the r_id and o_id where the count...

I increment numerical value if there is same record , or insert wants to do 0 if there is not it

Please help me. select * from hoge where id=xxx; If there is a data, I do it to do it, but though I implement this step, there is if in a program, and insert hoge set data=0 is troublesome if update hoge set data=data+1, a result are 0 lines. May not you realize this procedure by a blow by SQL? replace hoge select id, data+1 as da...

MySQL Conditional Insert

I am having a difficult time forming a conditional INSERT I have x_table with columns (instance, user, item) where instance ID is unique. I want to insert a new row only if the user already does not have a given item. For example trying to insert instance=919191 user=123 item=456 Insert into x_table (instance, user, item) values (919...

Website tactics: A question about how many SQL queries are enough

I've recently begun to unveil and slowly roll out a homemade CMS. The site allows a lot of customization with movement towards internationalization and customization onto a level that doesn't require source code. This is a personal project, and the entire intent was to see how far I can push my own programming limits (the question of dis...

Complicated MySQL query for newsletter queue

Hey everyone, I'm back and looking forward to more of your brilliance. I have two tables: newsletters — each row contains a 'id', 'subject', 'body' & 'from' headers for an email newsletter_queue — each row contains an 'id', 'email' address, 'date' added to queue and the 'newsletterid' My goal is to develop a MySQL query that can pull...

varchar vs. mediumtext

Hello, I have a website that has approx 1000 different strings in a mysql database which are used to display all of the text on my website. The reason the string are stored in a database is because I'm supporting 2 different languages and need to be able to display the different languages based on the users preference (english and chin...

mysql: How to free space

I have a mysql DB configured with the innoDB storage engine. I dropped the database (drop database <dbname>) but the disk space hasn't be liberated. At /var/lib/mysql there are some "big" files called ib_logfile0, ib_logfile1 and ibdata1 where the last one is really big. I pressume that these files are in fact the original DB. How can...

PHP - MySQL Query not working

<mx:RemoteObject id="zendAMF" destination="zend" showBusyCursor="true" source="test_class" > <mx:method name="doLogin" result="onSayHelloResult(event)"> <mx:arguments> <username> {username.text} </username> <password> {password.text} </password> </mx:arguments> </mx:method> </mx:RemoteObject> This is my Flex co...

Flex Remoting problem with PHP as Back end.

My PHP Code returns always NO regardless there is a username and password. Flex/MXML code: <mx:RemoteObject id="zendAMF" destination="zend" showBusyCursor="true" source="test_class" > <mx:method name="doLogin" result="onSayHelloResult(event)"> <mx:arguments> <username> {username.text} </username> <pass...

Regular expressions in JDBC

I have a java-application using JDBC for database interaction. I want to do a search based on a series of regular-expressions, however the application should be generic, we do not know if the database-engine will by mysql,oracle,sql server etc, but we are pretty sure it's gonna be either mysql or oracle. Will regular-expressions limit m...