Pull The Last 10 Records From A Transactions From A Table
I'm trying to pull the last 10 records from a transactions from a table using this query:
SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10
But I want to display the rows in ascending order by timestamp. I can't get the subquery below to work and not sure why:
SELECT *
FROM (SELECT *
FROM transactions
ORDER BY timestamp DESC
LIMIT 10)
ORDER BY timestamp ASC;
View Complete Forum Thread with Replies
Related Forum Messages:
Pull All Records
if it were possible to write a statement in SQL where one could select several fields from a table and then limit the records returned by only displaying those where, say, FieldA (a varchar) contains a given short string which could be only a part of the field's contents. As long as a record has this short string in Field A, it should be included in the resulting set of records.Something like this: SELECT FieldA, FieldB, FieldC FROM TABLE Table1 WHERE FieldA *contains string* Is there anything in SQL that could examine the contents of a field for a partial string, and then return the records that have that partial string in that field?
View Replies !
Pull Data From 2 Database Tables Into 1 Table On My Site.
I got 2 tables: a dev_tasks and a task_history table. The task table has to record information about the task I am working on AND the history table is for the task history logs. I am trying to display info from both tables into 1 table on my site. Most of the info will come from the dev_tasks table BUT i want to dedicate on column of the displayed info for the latest log made to the task_history table. The common column to both tables is the 'taskid' column. What would the query look like to pull the info from both tables, knowing that the taskid is common to both and that I want to display the last log per tasks from the task_history table?
View Replies !
Mixed Table Types In Transactions?
If I have a transaction that performs INSERTs or UPDATEs on a bunch of tables, some InnoDB and some MyISAM, and after performing a bunch of modifications on each I issue a ROLLBACK, does the fact that a MyISAM table is involved in the process cause any problems with the ROLLBACK at all?
View Replies !
Tracking MySQL Table Transactions
I am trying to understand how I could manage MySQL transactions. I have this database that contains a very important table. Its data changes a lot and contains student grades. I could have another table where I would keep track of the original table's transactions. I make a backup of this table on a weekly basis and I'm thinking that if I keep track of the sql queries I could recreate the data at any point. So, if a user tels me that he/she overwrote the data, I would restore the portion of the table from the weekly backup, and then go to the transaction table and issue the queries up the point where the data is valid. Is this a good way of restoring data? Is there a better way? Does MySQL have a buil-in system of restoing data?
View Replies !
All Records From Table A - All Records From Table B - Join Alike Records
I am by no means a SQl Jedi as will be apparent by my question, but I can usually figure out a select statement on my own. I have one today though that really has me stumped. I am working in MySQlL 5. In My first select statement I get all my records from Table B SELECT `table_A`.`ITEM`, `table_A`.`DECSCRIPTION`, `table_A`.`UM`, `table_A`.`PHASE`, `table_B`.`Qty`, `table_B`.`Calc` as calculated FROM `table_A` Inner Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID` In my second statement I get my records that match in this case phase 401 in Table B and all my Table A records for phase 401. SELECT `table_A`.`ITEM`, `table_A`.`DECSCRIPTION`, `table_A`.`UM`, `table_A`.`PHASE`, `table_B`.`Qty`, `table_B`.`Calc` as calculated FROM `table_A` Left Outer Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID` Where table_A.PHASE In ('401' ) Now I need to combine the Data of both recordsets. I need EVERYTHING in Table B, but I also need All Table A records that match the phase selection.... Can I write this one query or do I need to use a Temp table?
View Replies !
Selecting Records Belonging To Multiple Records Through Join Table
I have is 3 tables, 'categories', 'levels' and 'categories_levels', the 'categories_levels' table is a join table, with the `id`, `category_id` and `level_id` fields. Categories can belong to many different levels, so records in the join table might look like this (assuming that the category has an ID of 5) id, category_id, level_id 1, 5, 1 2, 5, 2 3, 5, 3 So you can see that category 5 belongs to 3 different levels (1, 2, and 3). On a web page, I've got a multiple select box, which allows a user to filter the categories depending on which levels they belong to. What I need a query to do is use that join table to find all categories which belong to the selected levels. Now for the tricky bit. Only the categories which belong to every single selected level should be displayed. So in the example above, if I select level 1, then category 5 will be displayed, but if I select levels 1 and 4, then category 5 should not be displayed, because although it does belong to level 1, it doesn't belong to level 4 as well.
View Replies !
Multiple DB Pull
Lets say there is a database of special users and a database of favorite movies SPECIAL USERS (username) bob jim FAVORITES (username - movie_title) bob - movie 1 kim - movie 3 jim - movie 1 larry - movie 2 jane - movie 2 How would I write a query that lists the most popular movies of special users without explicitly doing: SELECT * FROM FAVORITES WHERE username = 'bob' or username = 'jim' GROUP BY movie_title I want something like this: SELECT * FROM FAVORITES WHERE username = [all the usernames from the SPECIAL USERS DB] GROUP BY movie_title
View Replies !
Mysql Pull
I got this in my mysql databe table. a:1:{i:0;O:19:"test_Enclosure":4:{s:4:"link";s:36:"http://grabber/v/CAcpLiw4qt4.swf";s:4:"type";s:29:"application/x-shockwave-flash";s:6:"length";N;s:10:"javascript";s:2:"js";}} I want to pull out only the http://grabber/v/CAcpLiw4qt4.swf part?
View Replies !
Can Input Japanese, Can't Pull It Out
I figured out how to store Japanese characters by setting the collation of a particular field to utf8_bin. I can input Japanese characters, and I can see them just fine under PHPMyAdmin. The problem is that when I execute a query and print them to a web page, all I get is question marks instead of Japanese characters. This is while using the same browser that allows PHPmyAdmin to display the characters correctly. I added the following metadata to the <head> portion of the document but it didn't help: <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
View Replies !
Populate A Pull Down Menu From Db
how do I populate a pull down menu from a MySQL table? Using PHP/MySQL 4.0 Have table1 with names and ID and tableb with ID and statistical info.wnt to be able to scroll names and have users able to click on the name to follow a link to a page created using tableb data for each name listed. Table examples: tablea ID l Name --------------------- 1 Person, Joe 2 Smith, Joe tableb ID l units l margin -------------------------------- 1 5023 123 2 2536 72
View Replies !
Code To Pull Amount
I'm certain there is a function to do this but I forget what it is... Say I am pulling values from the database, but I only want to pull like the first 10 chars of the value.. how do I do that?
View Replies !
Pull Hosting Plans
I am currently creating a web hosting website for a client of mine, for the packages and the order process we are using a script called "Auto-Host (http://www.idevspot.com/AutoHost.php)", which stores all the hosting plans into a mysql database. This is a great script and it does exactly what we want it too, the only thing missing is it doesnt create a page where people can view the hosting plans and click a sign-up button. Can someone please give me guidance on how i can get a page with all the hosting plans on please?, alternatively i am willing to pay somebody for doing this on my behalf
View Replies !
Pull Related Info Using Subqueries
I'm creating a tool to browse a database of movie listings. The browser pulls up 25 results at a time, and you can page through them using 'Next' and 'Prev' tools. Pretty basic stuff. Here are my tables: movies directors comments movies_directors movies_comments etc... (primary key is movies.id) But here's where I'm stuck: for each film, I need to pull the movie info in 'movies', plus any related data from other tables, like this: The Lord of the Rings | Peter Jackson | 3 comments Episode II | George Lucas | 0 comments Indiana Jones | Steven Spielberg | 15 comments I seem to run into problems when I try to join info from all of these related tables. If there's a match, great. But, if not (like a movie with no comments), the movie is excluded from the result set. I've tried all sorts of SQL queries to make this work, but nothing seems to do the trick. SO, HERE'S MY QUESTION, IS THERE ANY WAY TO DO THIS QUERY WITHOUT SUBQUERIES/MYSQL 4.0? Or would the best approach be to use PHP to do all the subquery lookups?
View Replies !
Pull Similar Data From Two Tables
I need a query to search two seperate tables for similar data and return them in one result. I know you can do multiple select statements in one query but I dont know how to exactly organize them into one result. Does anyone know how to accomplish this.
View Replies !
Result Pull And Rating Sort
I have two databases. What I would like to happen is when I have a summary of products, I would like the query to pull the info from the product table and based on the id from that table cross reference with the reviews for that particular product and calculates all the reviews for that product into a single percentage. So when I access the assoc array from PHP I would like to see : Array(Array(['id'] => '2', ['prod_name'] => 'gummy bears', ['rating'] => '72'), Array(['id'] => '57', ['prod_name'] => 'socks', ['rating'] => '52')).
View Replies !
How To Pull Data From Multiple Tables
I have a db, with several tables in it, I have now reached to the point where I need to pull data from several tables. What is the best way to do this? Should I put a column in each table, and then insert a unique key in all of them at the time the record(row) is being created? If this is the way to do it, when I am putting the unique key in each table would I just do an insert in each of the tables?
View Replies !
Pull Data From Multiple Tables
Can you pull data from 2 tables in the same database onto the same page? What about more than 2 tables? Here is my dilema: I currently have a database with 1 table, and a ton of rows. The information is property information, and each property has multiple empty suites. As of now, I have columns for Building Name, Address, and then columns for Unit Type A (for the first unit), Rent A, Type B, Rent B, etc.... I was wondering if I would be able to put the listings we have into thier own property table, and then one bigger table of all the properties individual information. Keep in mind I need to call all tables together on my portfolio pages, so I would be connecting to quite a few tables at once. Or my other option I was thinking about was to just have 2 tables: 1 Buildings info, and 1 units info. Which of these would you do, or how would you organize this data.
View Replies !
Regular Expression :: Pull 3 Or 4 Digit Numbers
I would like to pull all the model number from my products table that are strictly only a 3 or 4 digit number ie: 034 or 3542 or 0243 or 934 etc Can someone advise me on how to construct a regular expression to give me the data I need?I have tried the following but it wasnt 100% accurate: Code: products_model REGEXP "[[:<:]][0-9][0-9][0-9][0-9][[:>:]]" or products_model REGEXP "[[:<:]][0-9][0-9][0-9][[:>:]]"
View Replies !
Inserting Data Into Existing Records Of A Table From Other Table
how to insert data from one table to an existing records of another table.... In specific how can i insert data into a table....in such a way that this data should not be inserted as a new record,rather it should be inserted into the existing records(these records at first contains only two fields of data..the remaining fields contain default values) my question is that how to insert the data into these remaining fields from another table.(more specifically what is the insert statement used).
View Replies !
Pull Info From One Database To Save In A Session To Push To A Different DB
I'm building a dynamic site that has 3 data bases. One DB contains all of my store info ID#, Name, Address, Phone. Another DB contains member info ID, Name, address, email,phone,etc... The 3rd DB Tracks when a user logs in and logs out at a store. This DB has the following columns Store ID, Member ID, log in time,log out time, time in store. The stores log in in the morning that starts a session-I need the session to pull the store's info ID#, Name,etc... so when a user logs in, in the store. The login page inserts the store ID, Store name, and user ID into the 3rd DB. There are a lot of stores how do I get the store variables to be inserted into the DB?
View Replies !
Trying To Pull Id, Count And Title But Lose Rows When I Add Extra Join
i'm trying to extract some information from my database, the query being PHP SELECT grps_c.catid, grps_c.title, COUNT(grps.groupid) AS COUNT FROM grps RIGHT JOIN grps_category grps_c ON (grps_c.catid = grps.catid) GROUP BY grps_c.catid ORDER BY grps_c.title which works fine. however some of the groups (grps.groupid) are hidden and i don't want to count them, so my thinking was add PHP LEFT JOIN grps_setting grps_s ON (grps_s.groupid = grps.groupid AND grps_s.hidden_group != Ƈ') however adding that removes the rows that have a 'count' or NULL or Zero.
View Replies !
Finding All Records In One Table That Don't Have A Corresponding Record In Another Table
I have photo information stored in a table called pictures. One column is keyword. Visitors to my site can search for photos by keyword. I record search terms in another table called searches. Each search term is recorded, meaning there are duplicate search rows. This allows me to see easily the popularity of a search term. Step 1. What query can I run to find all search terms that return no matching picture in the picture table. I tried the following, but no luck:Quote:select term from searches left join pictures on term like keyword where picture_id is NULL
View Replies !
Way To Use Transactions
I am a mssql programmer now using mysql for a project. Is there a way in mysql to use transactions. meaning i would begin transaction run a few sql statemenets end transaction with a way to rollback all the statements if any fail. can this be done in mysql?
View Replies !
Why My Transactions Over Lap?
I have 2 application is one windows application and a windows service which will update query on a remote server. My problem here is that I notice that the transaction of my windows service is running the query which are not even part of the program and which are part of my windows application. 081015 18:10:02 92 Quit 93 Connect root@localhost on mpcms5 93 Query SHOW VARIABLES 93 Query SHOW COLLATION 93 Query SET character_set_results=NULL 93 Init DB mpcms5 93 Query Select tblID.lastValue From tblID Where tblID.tableName='tblReceipt' 93 Quit .....
View Replies !
Transactions Tutorial
I'm looking for any online resources/tutorials and such about transactions. Preferable for MySQL, but others will do. Other than that, is there any good book about transactions. Again preferable for MySQL.
View Replies !
MySQL Transactions
I found a pretty good(and simplified) article on transactions for the new people to sql out there, however, you will have to have some prior knowledge of howto use commands, And why you would want to implement something of this nature. So, I guess, Intermediates in MySQL, go here.
View Replies !
Transactions VS Fulltext
I've got a bit of a problems. I'm using transactions to insert information into a couple of tables. Its an "all or nothing" procedure, and data integrety is important. However, I've come across a stumbling block. The information which is being inserted needs to be fully searchable with relevance. Which means fulltext indexing. Which I can't use on InnoDB tables.
View Replies !
Transactions & Commit
if you call a BEGIN and then you process some data, am i right in saying that you must call a COMMIT to actually have any effect on the database?
View Replies !
Transactions Supported
Looking at the docs, it says 3.23 supported transactions through using InnoDB tables. A person Im building a website for has already chosen a host that is using this version of MySQL. We may or may not need transactions, but Im just getting ready. Here is an article talking about their use with MySQL, but it states the example requires ver 4 http://www.devarticles.com/c/a/MySQ...QL-4.0-and-PHP/
View Replies !
Transactions Not Supported
Transactions not supported by database at /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBI.pm line 672. I was told that I need to recompile MySQL libmysqlclient as it doesn't look like it supports transactions. I tried to recompile with ./configure --without-server and replaced libmysqlclient.so.15.0.0 and libmysqlclient.a in the appropriate directories. I am still having the same problem.
View Replies !
Transactions & Locking
I am developing a multi-user database and I have a few questions about this. Will I need to use transactions or locking for single simple SQL statements? Or is MySQL smart enough to know not to let two people edit the same record at the same time?
View Replies !
Nested Transactions
If a transaction is started in a stored procedure 'A' that calls stored procedure 'B' which has its own start transaction... commit statements, can 'A' rollback and undo the changes made by 'B' if it decides not to commit any longer?
View Replies !
Auditing Transactions
With regard to auditing transactions : - Does MySQL have a table of users similar to Oracle's "ALL_USERS", allowing me to insert a user_id (rather than the full user_name string) into an audit column such as "last_updated_by" or "created_by" ? - Is there a function similar to Oracle's SYSDATE ?
View Replies !
InnoDB And Transactions
I have a problem with InnoDB and Transactions. I have a WebApplication running ColdFusion MX 6.1 and MySQL 4.0.17. I have many changes to database and all changes which belongs together a put in a transaction, because when errors occours, mysql rolls back the changes made. This works great. But now I have the following problem. When the user submits the form two times, both transaction are parallel. For example the first step is to select a number, and at the end of the transaction I insert a new line with "number+1". Normally it is not possible to get the same number in two lines. But when the user submits the form twice quickly and the first transaction is not ready yet, both transactions put a line with the same number. How can I make Mysql to wait with the second transaction on the same table until the first transaction is ready? I tried with "SELECT FOR UPDATE" and with the transaction-parameter "serializable", but both don`t work
View Replies !
Can I Do Transactions In MySQL?
I tried to change my sql queries for a program i originally wrote in SQL Server 2000 into MySQL, what irks me is that if i try to run two consecutive insert queries the first query is run but the second one gets ignored.WHY? The code reads thus: <?php $sql = "INSERT INTO smsusers (FNAME , LNAME , USERNAME , PASSWORD , TITLE , PHONENO) VALUES ('$thisFNAME' , '$thisLNAME' ,'$thisUSERMAIL' , password('$thisPASSWORD') , '$thisTITLE' , '$thisPHONENO')"; $result = MYSQL_QUERY($sql); ?> <?php $sql = "INSERT INTO profilecontents (CONTENTIDID , PROFILEIDNAME , CHECKER ) VALUES ('ACCTINFO' ,'$thisUSERMAIL')"; $result = MYSQL_QUERY($sql); ?> Perhaps there is sumthin' wrong with ma code????
View Replies !
Using Transactions With InnoDB
Right now I am experiencing with transactions in MySQL using the InnoDB engine. I found a really great article which includes all about transactions: http://dev.mysql.com/books/mysqlpres...rial/ch10.html However, I still got a question and I couldn't find the answer exactly in this article. A simple example: UPDATE users SET money = money - 1000 WHERE id = 1 ; UPDATE users SET money = money + 1000 WHERE id = 2 ; I just want both queries executed OR both not executed, so I should use a transaction. I thought the following would give me the right result, but it is not exactly what I want: START TRANSACTION ; UPDATE users SET money = money - 1000 WHERE id = 1 ; UPDATE users SET money = money + 1000 WHERE id = 2 ; COMMIT ; If user 1 doesn't have 1000 dollar anymore, both queries shouldn't be executed. The same for when the field name in the first query is wrong spelled or doesn't exist, both queries shouldn't be executed. I read something about ROLLBACK in the mysql article on the mysql.com website and I think I can solve my problem with this one, but I don't know exactly how to use this. The example in the article says: START TRANSACTION ; UPDATE users SET money = money - 1000 WHERE id = 1 ; UPDATE users SET money = money + 1000 WHERE id = 2 ; SELECT money FROM users WHERE id = 1 ; ROLLBACK ; But this one doesn't work for me. The ROLLBACK will always be executed, even when the user got enough money.
View Replies !
Transactions - Rollback
Is it necessary to call 'ROLLBACK' statement if one of your queries fails? Or is it sufficient just NOT to call 'COMMIT'. Seems to be working both ways for me.
View Replies !
Are Transactions Faster?
I know that database engines that support transactions tend to be slower than engines without support for transactions (e.g. innodb and myisam). But are transactions faster than separate queries? Example: I need to run 15 edit queries. Will it be faster if I just run them one by one or if I wrap them inside a transaction? My understanding of transactions is that the database gets queried only once with all queries inside the transaction and then it runs it one by one. So it should be faster or no?
View Replies !
MySQL And Transactions Entries
I need to insert a new entry in a table but one of its field is an order field so i use the MAX of current entries in the table. To lock tables to be sure that 2 persons can't have the same MAX at the same time, I use transactions. Nevertheless, How to be sure to get a WRITE lock on this table if my first statement is a select ? I'm doing this : BEGIN; SELECT MAX(ESSAILOCK_ORDER) AS MAX FROM ESSAILOCK; INSERT INTO ESSAILOCK (ESSAILOCK_TITRE;ESSAILOCK_ORDER) VALUES ('essai3',MAX); COMMIT; If the select is in first, am i just getting a reading lock, which could allow someone else to insert a new entry with higher order maybe.
View Replies !
Transactions With Java And MySQL
I'm trying to port the use of postgresql db to mysql. I'm using Java and having the following problem - I can't seem to write more than 1 query per execution. Easier to explain with an example that worked under postgresql and does not using mysql: aStatement.executeQuery("BEGIN; DELETE FROM table1; DELETE FROM table1; DELETE FROM table3; COMMIT;"); If I separate this into 5 different queries it works fine. Any ideas?
View Replies !
|
TRACKER
