Filtering The Rows Based On Its Timestamp
I'm trying to write a backup script that takes the last modified rows of a table in x days. I can do it if I have a timestamp column for every table in the database, but my aim is to write a script for general use as anyone can use it with his/her database without the need to add that column.
This will be possible if there a property gives the last modified timestamp of a row.
does anybody has any way to reach that target?
View Complete Forum Thread with Replies
Related Forum Messages:
Timestamp Based Queries
I would just like some links or advice to information on how to do a query based on a timestamp. Ie, I would like to find all the records in a movie database where was made after the year 2002.
View Replies !
Rows Based On First Letter
I'm trying to figure out the fastest/best way to return rows based on the first letter of the title column in my table. For example, I want to get all the article titles that start with the letter "a". Is there any difference between the two select statements? Any reason to use one over the other? Memory? Speed? SELECT title FROM table WHERE LEFT(title, 1) ='a'; or SELECT title FROM table WHERE title like 'a%';
View Replies !
Selecting Timestamp Rows
What is the best way to select date ranges with a time stamp? This is the query I'm using: "SELECT keywords, DATE_FORMAT(time_stamp,'%m %d, %y') as 'date' from keywords WHERE time_stamp >= '".$start_date."' and time_stamp <= '".$end_date."'"; Its returning dates a little bit outside of the range. Its off by a month a so.
View Replies !
Determine The Size Of A Set Of Rows Based On A Where Clause
I have tried looking for a solution to this problem but no luck. The thing is, in my system, I have a set of users who have a 'quota' limiting their usage. They can create n number of tables and put m number of rows in it, but at the end, their 'usage' can not exceed, lets say, x MB. My problem is, how to I evaluate the space being used, given the fact that I can pull all data for a user by including a user ID in the where clause. In other words, I need to find size of the result set containing all user's rows. I am using mysql jdbc driver and not sure if there is any API that deals with the physical size of the resultset. resultset.getFetchSize() talks in terms of rows v/s the actual space they take.
View Replies !
Query Which Gets Rows Based On A Radius, Lon And Lat Doesn't Work?
I have found this query which gets rows based on a radius. I need this for zip codes based on lon and lat's. $sql2 = "SELECT * FROM table as z WHERE (SQRT( (69.1 * (".$userLat." - z.lat)) * (69.1 * (".$userLat." - z.lat)) + (53.0 *(".$userLong." - z.lon)) * (53.0 *(".$userLong." - z.lon))) <= ".$userRadius." )"; return $sql2; $res = mysql_query($sql2, $connDB) or die(mysql_error()); $row = mysql_fetch_assoc($res); based on a test zip code gives a result like SELECT * FROM table as z WHERE (SQRT( (69.1 * (52.399834 - z.lat)) * (69.1 * (52.399834 - z.lat)) + (53.0 *(4.840762 - z.lon)) * (53.0 *(4.840762 - z.lon))) <= 100 ) the lat and lon of the test zip code are right. As you can see z.lat and z.lon don't get any value. And these would be every lat and lon in table. In my db table lon and lat are decimal(10,6) type with a default value of 0.000000
View Replies !
Select Based On Two Rows In Mapping Table
I have a many to many relationship between a resource and tag: resource - 1:M - tagged_resource - M:1 - tag So a resource can have many tags and a tag can belong to many resources. The data would look like this: RESOURCE TABLE id: 1 name: resource1 TAG TABLE id: 1 tag: new id: 2 tag: tested TAGGED_RESOURCE resourceID: 1 tagID: 1 resourceID: 1 tagID: 2 How can I select the single row from the RESOURCE TABLE which has the tags 'new' and 'tested' in the TAGGED_RESOURCE TABLE? I have tried using 'IN' but it acts like an OR operator rather than AND.
View Replies !
Selecting Rows From Timestamp Field
I've got a database with al lot of rows. Each row has a timestamp field, wich indicated the time that that the row was added. The timestamp field is generated by time(). Now, my question, how can I get all the rows, for specific day? So I would like all the rows from yesterday, or 12 days back, or 3534 days back.
View Replies !
Returning Rows In Left Table Based On Mutliple Criteria In Right
This is starting to get to me. I'm sure there's a simple way of handling what i'm trying to do, but someone might be able to help out quicker than spending another hour searching and testing for this. Here's the problem: Simplified tables: ARCADES ======= ID, name GAMES ====== ID, name ARCADES_GAMES ============= ID, arcade_iD Games_ID Straightforward enough so far right? I'm trying to get all arcades that have ALL games in a passed in set of game_id's. So for instance I might want all arcades that have the games 11 and 14, but it must have both those. I can do soemthing like... SELECT a.name FROM arcades a WHERE EXISTS(SELECT 1 FROM arcades_games WHERE arcade_id=a.id AND game_id IN (11,14)) But that'll return all rows that match ANY of (11,14) rather than ALL of 11,14. It all comes down to the simple thing of getting rows in a table where all criteria from a list is met, but any advice on how i would do something like this?
View Replies !
Now() Filtering
I need to filter in my database like this => Select * from customers where enddate > now() but i want to filter on now() + 15 days. Is this possible?
View Replies !
Filtering 3 Tables
A client sent me a database with three tables: e.g.: MEMBERS DENTISTS FISHERMEN MEMBERS was the original table and DENTISTS and FISHERMEN were created from said table. Every table has a MemID which corresponds to each FirstName and LastName. What I would like to do is strip the MEMBERS list of members that exist within DENTISTS and FISHERMEN. i.e.: RESULT from MEMBERS == (MEMBERS - DENTISTS) + (MEMBERS - FISHERMEN) or something along those lines... Should this be a join query or some sort of other query
View Replies !
Filtering Special Characters
I want to fetch the page via php from a mysql-db, which is called by an emulated filename (mod_rewrite, but that might be unimportant). So I have a file, which is called by guestbook.html - so I have to find the entry in my db, where the page title is guestbook. But there isn't a only column for this virtual filenames, but only entries like "Guestbook", "This & much more..." and so on. Becaus I'm not able to use "This & much more..." or thinks like this as filenames, I have first have to bring the letters to lower case and then to filter all the special chars. but how can i do this? Code:
View Replies !
Filtering The Birthdays By Month
I'm working on a procedure about the birthdays of the employees, but I'm having a hard time making it appear in the browser.. I'm not sure about the right query in filtering the b-days by month.
View Replies !
Filtering Odd Ascii Characters From MYSQL Input
My users are sometimes pasting "funny" quotation marks into their FCKEditor, also some kind of elongated hypen. How can I filter these out before writing the user input to mySQL? Obviously I could filer each one out, but I rather like something more generic.
View Replies !
How To Delete From 1 Table Some Rows Based On Match Results In 2nd Table?
How to delete from TABLE-1 all rows with indexes "i" that match to index j=2 from TABLE-2? TABLE 1: +---+------+ | i | name | +---+------+ | 1 | item1 | | 1 | item2 | | 7 | item3 | <-- delete all rows with i=5,6,7 | 6 | item4 | <-- delete | 5 | item5 | <-- delete | 5 | item6 | <-- delete | 7 | item7 | <-- delete +---+------+ TABLE 2: +---+---+------+ | j | i | name | +---+---+------+ | 1 | 1 | item1 | | 1 | 3 | item2 | | 1 | 2 | item3 | | 2 | 5 | item4 | <---- j=2 => i=5 | 2 | 6 | item5 | <---- j=2 => i=6 | 2 | 7 | item6 | <---- j=2 => i=7 | 3 | 8 | item7 | +---+---+------+
View Replies !
Updating Rows In Table B Based On Related Field In Table A
Ver 4.1.8-standard for apple-darwin7.6.0 on powerpc (Official MySQL-standard binary) I am trying to do some data migration based. I have several tables that contain our legacy pkey field and I want to update the tables with new ID's. I need to do this several times and have tried it several ways to no avail. Table A --------- companyID int(10) pKey legacyID int(10) old legacy pkey Table B --------- bAID int(10) pkey companyID int(10) legacyID int(10) Table A has values for both companyID (unique key) and legacyID. Table B has values for bAID (unique key) and legacyID but companyID is empty. I need to update tableB.companyID with tableA.companyID based on tableb.cSerialID to tablea.cSerialID relationship. I need a query that will update ALL rows.
View Replies !
Nested Sets And Filtering Conversion To Nested Arrays, How?
I am currently working on a project where we want to reuse a tree structure stored in the database. The tree are basically groups/categories of all the content available within the project. Now we want to reuse this data in forum and only show the featured items/nodes. An idea of the tree structure is: + root + Music (f) + Genre - Electronic (f) - Classical (f) Now the problem is that we don't wan to show the whole tree structure within this new forum. But only the ones for example, which are flagged to be featured. For this, we have a field in the groups table. Only I happen to have some problems with reusing this tree hierarchy: The main problem I have is that when I get the whole tree structure using the query SELECT * groups ORDER BY left_id then I have the whole tree. Only when I add "WHERE featured=1" then the left and right node values are becoming invalid because I filtered out possible child or parent nodes. I am curious if anyone could help me how to get the structure in such way I can easily reuse it. I would love to have the featured nodes in a nested array. Something like: root children: music children: electronic, classical etc.
View Replies !
Timestamp And Current_date/timestamp
Im stuck with a sql query. Basically I have a db that stored a timestamp off everyone who has a successful login. I want to be able to count all the people who have logged into today? How do I do that? I thought the following would work but it didn't: SELECT count(date) FROM nn_users_logs WHERE date = CURREN_TIMESTAMP
View Replies !
Next And Previous Buttons MySQL Rows Displaying 3 Rows Per Page
I have got a table "members" and the DB name is "jutland". I have got columns - id, name, surname & email. I want to display 100 records with 5 rows per page using PHP going back and forth with previous(previous 5 rows) and next (next 5 rows) links. I have the knowledge of the LIMIT x, y condition but still stuck on passing required parameters.
View Replies !
Any Way To Optimize Join To Find Rows Without Conditional Foreign Rows?
I've got a table of keywords that I regularly refresh against a remote search API, and I have another table that gets a row each each time I refresh one of the keywords. I use this table to block multiple processes from stepping on each other and refreshing the same keyword, as well as stat collection. So when I spin up my program, it queries for all the keywords that don't have a request currently in process, and don't have a successful one within the last 15 mins, or whatever the interval is. All was working fine for awhile, but now the keywords_requests table has almost 2 million rows in it and things are bogging down badly. I've got indexes on almost every column in the keywords_requests table, but to no avail. I'm logging slow queries and this one is taking forever, as you can see. What can I do? # Query_time: 20 Lock_time: 0 Rows_sent: 568 Rows_examined: 1826718 .....
View Replies !
Select Based On FK
I have two questions regarding the MySQL v4.1.16 scenario below: 1. What should the FK on update and on delete parameters be (i.e., default, cascade, restrict, no action, etc.)? 2. How can I insert the correct id’s as implied below? I have tried just about everything (including “Insert …select”, “LAST_UPDATE_ID();”, etc.) and whenever I think I am starting to get close I either get more errors or ‘0 records added’ with no error message; I would greatlyappreciate someone who could instruct me on how to accomplish this task.
View Replies !
Using A Web Based Database
My web site host allows me to create MySQL databases on its database server. I've created a database as a trial. Now I would want users of my website to supply data to the database and for me to be able to extract it for analysis.
View Replies !
Web-based Chat
I'm debating between 2 different table setups for a web-based chat system (gchat clone). The first is just one table labeled 'chat' which has the columns 'user_1' 'user_2' 'time' 'message'. The second would be dynamically creating/deleting tables with the user names as table names like 'user_1/user_2'. I don't know a whole lot about DB table organization but I was thinking the actual 'select' statement would be faster with seperate tables. Any ideas?
View Replies !
UPDATE Based On Value In Another Table
How do I update a table to remove orphaned references to a second table? I've deleted rows in the second table, which has a unique auto_increment key. The first table now has references to keys that no longer exist. I need to update the first table, setting the value to NULL where the referenced key no longer exists. Something like: UPDATE table1 SET table2ID = NULL WHERE table1.table2ID NOT FOUND IN table2.ID; The NOT FOUND IN isn't SQL, of course, but I'm not sure what should go there.
View Replies !
Union Based Upon First Query
I have a query that I would like to use a union statement in to grab the number of replies to a specific thread. The initial topic thread is in a different table, which I am grabbing in the initial query... I would prefer to do this in the single query, however I supposed I could do a separate loops and grab the number of replies with a totally distinct query ....
View Replies !
Syntax Based On MS Access SQL
Our shop is using an old version of MySQL (3.23).In testing our database concept we put together a test database in Access and the sql statement in MS Access looks like SELECT DISTINCT FROM table1.col1, table2,col1 FROM table1, table2 WHERE (((table1,col1) like "*" & table2!col2 & "*")); this gives us output from both tables(tables1 and tables2) while looping over the values of table2.col2.How does MySQL do this type of function?
View Replies !
SELECT Based On 2 Tables
I'm writing some custom stuff to pull from a WordPress install I have one one of my servers. For one function I want to SELECT all the rows from one table based on criteria from that table and another table (they are related by the row ID of the first table). Here is the SQL I have now.... sql Code: Original - sql Code SELECT DISTINCT t2.* FROM wp_term_relationships AS t1, wp_posts AS t2 WHERE t1.term_taxonomy_id = '4' AND t2.post_status = 'publish' AND t2.post_type = 'post' ORDER BY t2.post_date DESC SELECT DISTINCT t2.* FROM wp_term_relationships AS t1, wp_posts AS t2 WHERE t1.term_taxonomy_id = '4' AND t2.post_status = 'publish' AND t2.post_type = 'post' ORDER BY t2.post_date DESC It seems to be returning too many results. It's just returning all the rows from wp_posts that match the post_type and post_status criteria and ignoring the term_taxonomy_id (from wp_term_relationships) requirement.
View Replies !
Items Based On The First Letter
we have products database with CD's, and would like to create an alphabetical list of artists, then display the CD's that the artist has underneath them. The database has the products in them, with the artists name being called 'prod_artist_name'. the query i tried, though I knew it wouldn't work is: SELECT * FROM products WHERE prod_artist_name LIKE '%n%' Now, this displays all the artists with the letter 'n' in their name, obviously. just wondering if there was a way to display the artists with 'n' as the first letter of their name.
View Replies !
Update One Col From Another Col Based On Primary Key
I have a table which stores referral URLs in column 'ref' and primary key of the table is 'clid' I have added another column which will store the name of the search engine referral by the name 'SearchEngine'. Now what I want to do is to avoid PHP processing and write a mysql query as follow, Code: UPDATE clicks SET SearchEngine=( SELECT ( CASE WHEN (LOCATE('.google.',ref)!=0) THEN 'Google' WHEN (LOCATE('.yahoo.',ref)!=0) THEN 'Yahoo' WHEN (LOCATE('.msn.',ref)!=0) THEN 'Microsoft' WHEN (LOCATE('.microsoft.',ref)!=0) THEN 'Microsoft') WHEN (LOCATE('.live.',ref)!=0) THEN 'Microsoft') ELSE 'Others' END ), @tmpo:=clid FROM clicks WHERE ref!='' ) WHERE (clid=@tmpo); but it gives me following error, Code: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') WHEN (LOCATE('.live.',ref)!=0) THEN 'Microsoft') ELSE 'Others' END),clid:=@tm' at line 1 Basically i want to parse referral url, decide the search engine name and then store its name in the new column based on primary key so that correct values are stored in correct record.
View Replies !
Date Based Calculations
I'm looking for a way to create a query that generates a record for every day of the last year and using that date information for each day generates a unique database query (sub query?)for each day of the year. I could probably do this in php but I'd like to do it for performance reasons from mysql (or at least most of it). Does any one know of any good online tutorial/s that may help me build this sort of project? Or have some basic scripts that may be a good starting point?
View Replies !
Update Based On Another Table
Is it possible to do an update in MYSQL based on another table? I have version 3.23 and when I try to run this statement: UPDATE ApplicationTbl INNER JOIN AcademicTbl ON ApplicationTbl.CampusID = AcademicTbl.CampusID AND ApplicationTbl.Application_Period = AcademicTbl.Application_Period SET ApplicationTbl.App_Status = 'Qualified' WHERE AcademicTbl.Sem_OnCampus >= '1' AND AcademicTbl.GPA >= '2.4' AND AcademicTbl.Judicial_Sanction IS NULL It keeps saying its wrong. Even though I know its not
View Replies !
Update One Column Based On Another
I have two tables. One set up like this: id item_id group_id userid outcome settled The second is userid yes no I would like to look at the first table and find all rows that have a specific group_id, then update the userid yes/no based on the outcome on that row. Outcome Y then add 1 to the yes column of that userid, outcome N then add 1 to the no column of the userid. Is this possible in one SQL statement?
View Replies !
Sql Based Contact Form
how would I create a contact form that would store the results in a sql databse, be able to see the results in a text file, and also would have a link to delete that result from the database in the results text file?
View Replies !
Increment Based On Another Column
I'm having a little trouble working out the best way of doing something. I'm storing a key split across two fields, the first field is 3 letters, the second is three numbers: CREATE TABLE table1 ( id_a CHAR(3) NOT NULL, id_b SMALLINT(3) NOT NULL, ... ); What I'd like to do is have it so that when a new row is entered 'id_b' is calculated as MAX(id_b) + 1 WHERE id_a = new_id_a. Is it safe to do this as some sort of nested query or is there a better way?
View Replies !
User Based PHP W/MySql
If I were to take a few different (gpl released) php programs that use MySql databases for the user data, could I integrate all the users into one database? For instance, a user signs up in one of my forums, and their info is stored in a database. Then if they go to my auction script using the same database, they don't have to sign up again. My guess is that yes it would work, but I'd have to go through each php file and edit where it called for the table data in each database to point to the same places.
View Replies !
Web Based MySQL Editor
I am looking for a Web Based MySQL Editor to manage, database, users, etc and if you can, tell me why you like it, good points, bad points, etc. I use PHPMyAdmin right now, so I am looking for something possibly better.
View Replies !
Retreaving Data Based
I have tables called room_status and room_info room_status= room_id : date : status room_info= room_id : type : rate What I want to do is only make an entry in room_status when a room is booked, as it is obviously available otherwise(duh) but I don't know how to format a SELECT statement to bring back rooms available on a specific date by only returning results from the room_information table which do not have an entry in the room_status table for a specific date, which is the case if I don't formally record that a room is available.
View Replies !
Selecting Another Column Based On Max()
I've got a funky query I'm trying to set up. I've got this poll answers database, and each row has a qid that matches to a question. So I can group answers by the question they correspond to. Now I'm trying to select the total votes and the answer with the most votes for each question. The total votes part is working fine, but I'm having trouble with the winning answer part. I've got a MAX(votes) that works well, and finds the answer with the most votes for every question, and returns its votes. What I want to do is select the `answer` column (the one that houses the actual text answer) of the answer with the greatest votes (the winner). Code:
View Replies !
SQL Selection Based On Date
I'm using this function / string to pull data based on yesterday: $tomorrow = date('Y-m-d',strtotime('tomorrow')); How can I pull data similarily based on "last month"?
View Replies !
Deleting Based On Subquery
i need to DELETE the results of this query : SELECT bizName, COUNT( bizName ) AS name_count FROM `datanewest` GROUP BY bizName HAVING name_count >1 I use this to remove double entries. But when i run something lie this DELETE FROM `datanewest` WHERE bizName IN (SELECT bizName, COUNT( bizName ) AS name_count FROM `datanewest` GROUP BY bizName HAVING name_count >1) i get this error : #1241 - Operand should contain 1 column(s)
View Replies !
Ordering Based On Occurence?
I have this part of my query SELECT DISTINCT country FROM traffic_outgoing and I want to limit it to the top 10 countries that have the highest number of occurences. How would I do that?
View Replies !
Selecting From One Table Based On Another?
I have a table for products and another table for the type of product it is like this: I need this is be in a seperate table because one product can be several different types like ring,band,wedding,three-stone,etc. but how can I then select a product based on criteria from the types table say I wanted to select all products that are type = "Band" and type != "Fancy" ?
View Replies !
|
TRACKER
