mysql

MySQL JOIN the most recent row only?

I have a table customer that stores a customer_id, email and reference. There is an additional table customer_data that stores a historical record of the changes made to the customer, i.e. when there's a change made a new row is inserted. In order to display the customer information in a table, the two tables need to be joined, however ...

Python & MySQL: Matching element from list of with a record from database.

Hi there- I have a list of objects which I built with a class, and one of the properties of this class is the variable "tag". (below called tagList) I am trying to match this variable from a record that is bought in using MySQLdb. (below called record) I can output both to the screen, and see them identically by eye, although cannot g...

Faster MySQL Join for DISTINCT records

After reviewing the related questions, none of them seem to address mine, so here goes: I have two tables containing Course information. One table [course] holds, currently, nearly 25k records with fields such as CourseName, CourseCode, Term, Instructor, etc. The second table [courseCatalog] holds merely the CourseCode and CourseName....

MySQL problem involving crazy multiple self-joins

As part of the process of replacing some old code that used an incredibly slow nested select, I've ended up with a query that looks like this: SELECT r3.r_id AS r3_id, r2.r_id AS r2_id, r1.r_id AS r1_id FROM table_r r3 LEFT JOIN ( table_r r2 INNER JOIN ( table_r r1 INNER JOIN table_d d ON r1.r_id = d.r_id ) ON r2.r_id = r1...

mysql create a trigger in a database listening to table changes in a different database

something like: CREATE TRIGGER schema1.triggername AFTER INSERT ON schema2.table FOR EACH ROW BEGIN ; END; ERROR 1435 (HY000): Trigger in wrong schema ...

Cycling through enums in MySQL

Hey guys, I have a table in my database with an enum column with 3 values: ('Offline', 'Online', 'Expired') I want to be able to do an update that cycles the value of the this column to the next value in the list... and wrap back to the start. i.e. 'Offline' will change to 'Online' 'Online' will change to 'Expired' 'Expired' w...

Strange MySQL results

Hello. I was wondering, why does the query select * from (select * from users join users a) b cause the Duplicate column name error? While the inner query returns a result set with duplicate columns, postfixed with _1, the outer one reveals a column from the table. ...

is php sort better than mysql "order by" ?

Hi. I was wondering if, in terms of performance and considering a mysql select on a table with very very very many (> 1.000.000) records, is better sorting results with sql "order by" or sorting results after the query with classical programming sort algorithm ... someone has any suggestion? Tanks ...

How to deal with large data sets for analytics, and varying numbers of columns'?

I'm building an analytics system for a mobile application and have had some difficulty deciding how to store and process large amounts of data. Each row will represent a 'view' (like a web page) and store some fixed attributes, like user agent and date. Additionally, each view may have a varying number of extra attributes, which relate...

Encrypting or Hiding Strings sent in the url

Hello I am trying to send a Topic name in the URL like, <a href="hello?TopicN=blahblahblha"> and then output the topic name as the Forum topic title. But the problem is the user can just change the name if they want which doesnt do any harm since I dont really do anything with teh name but I was wondering is there a way to encrypt or...

How to make static nested SELECT faster

Hey I have a query similar to this FROM products AS p, .. LEFT JOIN ( SELECT .. ) AS popularity on popularity.products_id = p.products_id LEFT JOIN ( SELECT .. ) AS reviews on reviews.products_id = p.products_id WHERE.. AND.. .. The nested SELECTs from the LEFT JOIN are static, I mean the returned result is not influenced by external...

How do you use DROP TABLE IF EXISTS in a mySQL Stored Procedure?

I'm writing a rather long mySQL Stored Procedure that will do a bunch of work and then load up a temp table with the results. I've seen a few ways to do the temp table thing. Basically, you either create the temp table, work on it, and then drop it at the end ... or you drop it if it exists, create it, and then do your work on it. I ...

efficient way to store sitewide user activity

Hi, I tried searching through on stackoverflow as well as googling around a lot, but am not able to find answers to my problem (I guess I'm searching for the wrong keywords / terms). We are in the process of building a recommendation engine, and while we are initially logging all user activity in custom logs (we use ruby / rails), we ne...

Is there a better way to group query results with a loop in PHP?

Hello, that´s my first question in stackoverflow. I have two MYSQL tables: categories and products. I manage the query results with a while loop in PHP to group every category with its products. It´s the first time I do something so, and I think I made it very "crafty/hardcoded" (Sorry for my English). I think that should be a better wa...

Deleting a row based on the max value.

How can I structure a mySQL query to delete a row based on the max value. I tried WHERE jobPositonId = max(jobPostionId) but got an error? ...

mysql query not giving correct results

I have a table called records which has several columns, one of which is fromphone which represents a phone number. I can run this query to see the different phone numbers: SELECT DISTINCT fromphone FROM records and it shows the different phone numbers. However, if I run this query: SELECT * FROM records WHERE fromphone = '123-456-...

How can I get a custom CallableStatement object out of a prepareCall method.

I want to create a subclass that extends the CallableStatement object. I want to do this so I can override the execute and executeQuery methods to track some metrics on each SP call. Currently I have code that looks like this: Connection db = poolingDataSource.getConnection(); CallableStatement cstmt = db.prepareCall("{call pSampleSto...

PHP SQL SELECT where like search item with multiple words

Hi, I have a select where like query for a seach form which is as follows: <?php $bucketsearch = sanitizeone($_POST["bucketsearch"], "plain"); $bucketsearch = strip_word_html($bucketsearch); ?> if(isset($_POST['search'])){ $result=MYSQL_QUERY( "SELECT * FROM buckets where bucketname like '%$bucketsearch%' order by ...

mysql and 30 days

i'm working on a site that handles free subscriptions and i will like to now how to cancel their subscriptions after 30 days inactivity, i know that this has to be done with cron-jobs but i have no idea on how to count 30 after the last time the user logged in? ...

MySQL - How to get EXACT difference of hours between two dates

I'll illustrate what I would like to get in the following example: '2010-09-01 03:00:00' - '2010-09-01 00:10:00' Using TIMEDIFF(), we get 2 as a result. This means, it's not considering the 50 minutes left. In this case, what I'd like to get is: 50 (minutes) / 60 = 0.83 period. Therefore, the result should be 2.83 and not 2. ...