mysql

MySQL - Before Insert Trigger Syntax?

With MySQL 5.1.43. I am trying to create a trigger like this: USE `databaseA`; DELIMITER $$ CREATE DEFINER=`root`@`localhost` trigger triggerName BEFORE INSERT ON tableA FOR EACH ROW BEGIN IF(convert(new.subTime, signed) > '600') THEN SET new.fieldA = new.fieldB; END IF; END$$ When I insert a record the fieldA field does not upda...

How do I test the speed of a mySQL query?

I have a select and query like below... $sql = "SELECT * FROM notifications WHERE to_id='".$userid."' AND (alert_read != '1' OR user_read != '1') ORDER BY alert_time DESC"; $result = mysql_query($sql); how do I test how long the query took to run? ...

MySQL command line issue - ambiguous option --s

Hi I'm getting the error below everytime I run a mysql or mysqladmin command - even mysql --help!! ambiguous option '--s' (set-variable, shutdown_timeout) (The daemon is fine) Any ideas? Thanks Daniel ...

'dynamic' SQL join possible?

Hi, I have a table Action with a (part of the) structure like this: Action: ActionTypeID, ActionConnectionID ... ActionTypeID refers to types 1-5 which correspond to different tables (1=Pro, 2=Project etc.) ActionConnectionID is the primaryKey in the corresponding table; ie. ActionTypeID=1, ActionConnectionID=43 -> would point...

Do I need to use C3P0 pooling library in my (grails) web application?

Hi, I am not familiar at all with connection pooling library. I've just discovered it through this blog article) and I am not sure that I should use one in my web application based on grails/hibernate/mysql. So my question is simple : in which situations would you suggest to integrate a connection pooling library into a grails applica...

Can shared Host/MySQl application support load of 500 users accessing the site at the same time

I have a new project and need some advice about managing the application’s load. The application will have over 500 users with about 70% of them logging on between 5 & 7pm each night. The users will pull down about 50 records, update the records and the save the changes back to the data base. Estimate each record will be updated in a...

Best way to track impressions/clicks in a bespoke advertisement system?

I've been asked to create a bespoke advertisement system despite suggesting open source alternatives such as OpenX and DoubleClick for Publishers (the former Google Ad Manager). I've got the basics of the system set up, i.e. uploading creatives, creating positions and a mechanism to place creatives within positions; however, the area I'...

MySQL join two tables and add column values

My knowledge of MySQL is not very in depth. If I have two tables that for example look like this: Table1 Date v1 v2 v3 05/01/2010 26 abc 45 05/02/2010 31 def 25 05/03/2010 50 ghi 46 Table2 Date v1 v2 v3 05/01/2010 42 jkl 15 05/02/2010 28 mno 14 05/03/2010 12 pqr 64 How can I join them in a query by their dat...

MySQL: how to extract multiple text files from my MySQL table

I need to extract data from my MySQL database into multiple text files. I have a table with 4 columns: UserID, UserName, Tag, Score. I need to create a text file for each Tag, with the userID, the userName and score (ordered by score). For example Tag1.txt 234922 John 35 234294 David 205 392423 Patrick 21 Tag2.txt 234922 John 35 234...

MySQL SELECT INTO OUTFILE to a different server?

I have a shell script on server a. The script spits out a csv file to a local directory. The problem is the database server is on server b. How do I use select * into outfile in a setup like this? I get Can't create/write to file '/home/username/test.csv/' (Errcode: 2) Solved load data infile by using 'LOCAL' keyword. Is there somet...

MySQL Count and sort rows

What is the best way to count and sort a column of data in mysql and output the results as a html list? Example Data: **Type** Train Car Car Car Bus Bus Output should be sorted with largest count item first: - car(3) - bus(2) - train (1) Also, is this bad practice to do on large table? ...

Left Join on Associative Table

I have three tables Prospect -- holds prospect information id name projectID Sample data for Prospect id | name | projectID 1 | p1 | 1 2 | p2 | 1 3 | p3 | 1 4 | p4 | 2 5 | p5 | 2 6 | p6 | 2 Conjoint -- holds conjoint information id title projectID Sample data id | title | projectID 1 | color | 1 2...

MySQL: how to keep top 1000 rows for each value of a column

hi, I need some help with this query: I want to delete the following rows from my table. The table has 4 columns: UserID, UserName, Tag, Score. I want to keep only the top 1000 users with highest score, for each Tag. So I need to order by score, and to keep the first 1000 users for each tag. Thanks ...

Why i get everytime the error-message that i've already sent the headers

Hey, i've another question about web-programming. I programmed a login script, but everytime when i try to login it says that i've send the header informations already. Here are the 2 files: <?php if($_GET['logout'] == 1) { setcookie('authorized', 1, time()-3600); } ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitiona...

How to read author_name from current table (comments) if id_author is set to zero or from users table if id_author is bigger then 0(MySQL command)

I have a comment form like this. -form with 1 field (comments) if user is login or -form with 3 fields (name, email, comment) if user is not login (like visitor) After user is submitting the comment depending by status (login or not) write inside the comments table the data like this example: - if user is login write com_id_author=3 ...

MySQL: Question about SELECT WHERE AND/OR

I'm trying to write a query that returns the same result from three different events, but I think I am doing it wrong. I can run my query against one event ID and it works. How can I select all three? Here's what I have so far: SELECT * FROM `Registrations` WHERE `Role` = "Attendee" AND `RegistrationStatus_ID` = "1" AND `DigSignature` ...

MySQL get row closest to NOW()

I have a table with User data such as name, address etc and another table which has a paragraph of text about the user. The reason that they are separate is because we need to record all the old about data. So if the user changes their paragraph - the old one should still be stored. Each bit of about data has a primary key aboutMeID. Wha...

Insert a row and avoiding race condition (PHP/MySQL)

I'm working on a multiplayer game which has a lobby-like area where players select "sectors" to enter. The lobby gateway is powered by PHP, while actual gameplay is handled by one or more Java servers. The datastore is MySQL. The happy path: A player chooses a sector and tells the lobby he'd like to enter. The lobby checks whether this ...

PHP MYSQL SIMPLE SEARCH ISSUE

Hi I have a table with the following structure: id int(11), name varchar(250) I have lots of records in the table but when I am trying to find a particluar record which has the following value on the name field: Lorem ipsum d\'olor sit amet The query is simply returning a blank recordset. I am not being able to figure out this weird...

mysql datetime performance

Hi, since in mysql datetime field is represented as string (i'm not sure how it works internally), wouldn't be from performance point of view faster to store date as unix timestamp? I don't need to use any mysql native date functions, i'm going for performance of sorting and selecting data. if there was an index for that column, would ...