mysql

Get last row PER Group

How can I formulate a query for the below task: Let's say you are logged in as user:1 I want to get one row per conversations I've had. For each row I want to get, the "Subject" of the first row within the conversation "DateTime" of the first row "Message" last message of this conversation no matter who wrote it CREATE TABLE messages ...

Every derived table must have its own alias error

I get that error when running the following query: SELECT MAX( DateTime ) FROM ( ( SELECT DateTime FROM Class_Searches ) UNION ALL ( SELECT DateTime FROM Book_Searches ) ) WHERE User_Email = '[email protected]' AND DateTime > NOW( ) - INTERVAL 30 DAY I know I need to add Aliases but I'm not sure where ...

MySQL Query, Subquery optimization, SELECT, JOIN

I have one table with some data and I want select newest data for each type... Table: +----+------+------+---------------------+ | ID | data | type | date | +----+------+------+---------------------+ | 1 | just | 2 | 2010-08-07 14:24:48 | | 2 | some | 2 | 2010-08-07 18:07:32 | | 3 | data | 9 | 2010-08-06 02:5...

How do I select times from MySQL in order based on precedence?

I know that question doesn't make much sense, but here goes: Times Table Authority | Time ------------------------------------- animuson@forums | 45.6758 132075829385895 | 49.7869 qykumsoy@forums | 44.45 439854390263565 | 50.761 user@forums | 44.9 another@auth | 46.123 bingo@nameo | 47.4...

Finding similar number patterns in table

Ok, let's suppose we have members table. There is a field called, let's say, about_member. There will be a string like this 1-1-2-1-2 for everybody. Let's suppose member_1 has this string 1-1-2-2-1 and he searches who has the similar string or as much similar as possible. For example if member_2 has string 1-1-2-2-1 it will be 100% match...

Maximum number of columns / values you can INSERT INTO a table - mysql

Does anybody know what's the maximum number of columns/values you can insert into a table (mysql)? I get an error for... IDK... 20 columns/values? (yes, #_of_col = #no_of_val) INSERT INTO comenzi (a,b,c,d,e,f,...) VALUES (1,2,3,4,5,6,...) ...

MySQL count problem.

I've been trying to count how many times a tag has been entered into the database and display the number of times it has been entered along with the tag into the database just like here on StackOverflow but I can't seem to be able to do it can someone help me? So far I can get the tag but not the count. Thanks in advance for the help!...

What is a good way to format sql of a view from MYSQL

As most of you would know, when you save a view in MYSQL and then retrieve it later, it's all in native MYSQL, looks terrible - no line spaces, indents, plenty of UTF encoding tags littered around the place making your code look like sql soup. Does anyone have a program or recommend a plugin to netbeans that i can use to format this sql...

Basic principles for securing a web-app?

Possible Duplicate: PHP Session Security I've just finished coding the basics of a web-app (the main code) and I've integrated a basic user system. This is my first web-app so I'm wondering what are the things that I should use to secure it? I already know about thing like mysql_real_escape_string() and strip_tags() but what e...

How to order numeric and non-numeric values in MySQL.

I was wondering how can I order numeric and non-numeric values using MySQL. Here is what is outputed. 1 10 1234 2 25 26 a b c d Here is what I want. 1 2 10 25 26 1234 a b c d Here is my MySQL code. SELECT tags.*, COUNT(tag_id) as number_of_tags FROM tags INNER JOIN posts_tags ON tags.id = posts_tags.tag_id GROUP BY tags.tag ORD...

Packaging a PHP-MySQL app to allow easy install

I've developed an open source application in php and mysql. I'd like to give it to the end user to install on their computer and use from their browser without me having to host it for them. But the end users are non-developers so they're unlikely to have what it takes to run the application (php-apache local environment like a developer...

Need an abstract trigger in MySQL 5.1 to update an audit log

I need a way to check for and pass entries into an audit log for any entries in a table that have been changed. It needs to be abstracted away from the table structure. For example: CREATE TRIGGER table1_update BEFORE UPDATE ON table1 FOR EACH ROW BEGIN DECLARE i_column_name varchar(32); DECLARE done INT; DECLARE cursor1 CUR...

Filter rows of an HTML table generated from a database using AJAX

I have an HTML/XML table generated by AJAX that displays limited columns of a database table. I would like to filter rows of the HTML/XML table using columns from the database that aren't part of the HTML table. Example: MySQL tables (ignore bad syntax): TABLE technicians (id, name, email, ...

MySQL (InnoDB) Select from 2 tables and always get empty result set

Hello, I'm a little confused. I'm using MySQL 5+ with InnoDB as my engine. If I run the following statement and the table 'user_temp' is empty I always get an empty result set no matter what's in the 'users' table. SELECT * FROM `users`, `user_temp` If I put something in 'user_temp' I'll get back all the results. Is this suppos...

removing all 0 value rows returns different result?

Hi there. this query SELECT SUM(s.msg_sent_datetime - r.date_received) AS difference returns -743726381625992.000000 but if i add on WHERE (s.msg_sent_datetime - r.date_received) > 0 it returns: 343435413.000000 I thought the 0 value rows would have no effect on a sum? also, what is up with the .000000 ...

MySQL/PHP 5 - getting the ID of the last fetched row

I am trying to create a look-up table from the results which mysql_fetch_assoc returns, and this is the code I have so far: protected function dumpResultAsHash($primaryKey) { if (empty($primaryKey) || !isset($primaryKey)) throw new Exception("Primary key cannot be null or not defined!"); $resultset = array(); while...

splitting one query into four to avoid massive joins?

Hi there, So i have a query that looks like this: SELECT col1, col2, col3 ... FROM action_6_members m LEFT JOIN action_6_5pts f ON f.member_id = m.id LEFT JOIN action_6_10pts t ON t.member_id = m.id LEFT JOIN action_6_weekly w ON w.member_id = m.id WHERE `draw_id` = '1' ORDER BY m.id DESC LIMIT 0, 20; now this is doing a massive join...

FULLTEXT key too long

I get the following error when trying to add a FULLTEXT index to Title, Edition and Author in my MySQL database: Specified key was too long; max key length is 1000 bytes Here are the columns: `Title` varchar(255) NOT NULL, `Edition` varchar(20) default NULL, `Authors` varchar(255) default NULL, None of them are unique.. Even th...

FULLTEXT search on a very small column

I have a table Books in my MySQL database which has the columns Title (varchar(255)) and Edition varchar(20)). Example values for these are "Introduction to Microeconomics" and "4". I want to let users search for Books based on Title and Edition. So, for example they could enter "Microeconomics 4" and it would get the proper result. My ...

Is there MySQL example database like in Oracle?

Sometime ago, when I installed Oracle on Ubuntu, there was a sample database. Database contained Employees, Departments, ... Now I wonder is there something similar for MySQL? ...