Choosing Name Of Table From A Previous Query
Got this working at last ...
View Complete Forum Thread with Replies
Related Forum Messages:
Using IF() For Choosing The Query Fails
The following code is for the Archive. (Where users can store special messages) I want to receive one row for each message that has been sent OR that has been received. With the code beneath I get this error: "#1241 - Operand should contain 1 column(s)". How can I fix this? INFO AND CODE ......
View Replies !
Previous, Current, And Next In One Query?
This situation has come up a couple times and I've wanted to know if there's a better way to do it. Given something unique (an ID number, a specific date/time, etc) I want to find the thing that comes before it and the one after it when sorted by something else. Oh, and the "something unique" isn't sequential - random intervals between each. Code: ....
View Replies !
Update Query Fails In Previous MySQL Version
The following query runs fine on my development server (v4.0.17): "update user inner join events on user.username = events.username set user.LastMessageDate = '$current_time' where events.text_sent=0 and events.to_mobile >0 and events.GMT_event_send_time<='$send_time'" but fails on my production server running v3.23.56. Anyone any ideas why it doesn't run and what I need to do to fix it?
View Replies !
Unique ID Field, Getting Next And Previous Existing ID From Table
This has been haunting me. In a query I select an ID (the ID field in the table is set to unique) The variable to check against ID is created by user in a form. To prevent unneccesary queries, I'd also like to know what the *next* and the *previous* ID will be. This is the first query $results = mysql_query("SELECT * FROM $mytable WHERE ID='$id'"); And now I want to find out what the next ID and previous ID will be from that table. The user can delete records from table, so I can't assume that if user wants a result where ID=8, that records with ID=7 and ID=9 exist. I hope I explained myself clearly :)
View Replies !
How To Use Previous Select Query Results In New Select Query?
I have tested everything and it works OK in its current form. However, what I need help with is the part of the query highlighted in red. As it currently stands, the PHP while statement loops through the rows and echos out the html. The issue I have is that it echos out the same speaker name and subject type for each iteration. Now I know this is happening because I have set subject.event_id = 1, so what I need is to use the event_id of the current iteration as the clause and that is what I need help with....
View Replies !
Version Choosing
Could you give me some advice? 1. Should I choose mysql 5.0 or 5.1 with some msyql-cluster? 2. Which mysql-cluster is more stable, mysql-cluster 6.2 or 6.3 ? 3. Or am I too early to think about mysql-cluster?
View Replies !
Choosing A Version
would just like to know which version of mysql to install. Apparently I need the i686 version, but I can only find 64bit options. I have a 32bit PC. >uname -a Linux ubuntu 2.6.27-9-generic #1 SMP Thu Nov 20 21:57:00 UTC 2008 i686 GNU/Linux
View Replies !
On Choosing The Right Type For A Column
The online MySql manual gives advice on column types for the most efficient use of storage, but I can't find any advice on column types for the most efficient use of computer cycles. I know on some machines it may be faster always to use (say) fixed length strings and unsigned integers, even if these types take up more storage than is strictly needed. Does any reader of this ng have an informed opinion on this matter?
View Replies !
Choosing A Particular Row From Grouped Rows
I have a table which contains two strings and a date. page_views ----------------- sessionId page timestamp This is added to each time someone views a page on our site. I want to select the last page each user looked at, so I need to group on the sessionId whilst getting it to select the one with the highest date timestamp.
View Replies !
Choosing From Multiple Tables
I have a table of users where some users have a "commander" who will benefit from that users stats. This is updated daily where a users benefits are calculated and updated via a cronjob. I would like to update users, and if they have commanders, update them as well in the same MySQL statement. So the only way I can do it no is like this: Expand|Select|Wrap|Line Numbers
View Replies !
Choosing Index Type
I use mysql 5.0.18. I need some advice in what type of index to prefer when using MEMORY tables - BTREE or HASH? I want to put in memory several tables with number of rows from 100 throuh 20000 in each. Not a lot I guess. My memory tables contain a kind of vocabulary with common table structure like create table: ( id int, name CHAR(200), primary key (id), {key | unique key} `name` (name) ) There can be duplicates in some columns but I feel it would not be many of them. Is it possible to gain meaningful time profit from choosing one index type and not the other when selecting from such tables? (selects may contain many joins with these tables)
View Replies !
Choosing The Right Local Server
i seem to have a problem with connecting to the database on everything i try! At the moment I am trying to connect moodle to my MySQL database. I want the MySQL host to be pcol-webtest - as this is the server host - not locahost at all. How do I change the host, do I have to set the database data directory onto that host? Through MySQL Administrator its telling me the following: Connected to MySQL Server Instance Username: mooldeadmin (set this in command line) Hostname: localhost Port: 3306 Server Info MySQL version: MySQL 4.1.10-nt via TCP/IP Network Name: pcol-webtest IP: 212.219.110.235 OS: Windows 2k
View Replies !
Comparing Dates And Choosing
if( date_add(cur_date(), interval 30 day)>='$thisDate','$thisDate',date_add(cur_date(),interval 30 day) So: if the current date plus 30 days is larger then thisDate, use thisDate else use the current date plus 30 days.
View Replies !
Choosing A Linux Distribution
I'm building up a mysql server to run our lab's internal data base, and was wondering how to go about choosing a linux distribution. Anyone have any advice as what to take into consideration, or have any specific advice? Also, is there any big disadvantage to just running the server on an XP box? The server itself will run on an AMD 64 processor.
View Replies !
Choosing Storage Engine
I am setting up a database and I was wondering what storage engine to use, MyIsam or InnoDB. First of all I am looking for the most trustworthy solution. I have been searching the forum, but I could not find a definitive answer. I did find that MyIsam is faster than InnoDB, but InnoDB provides better data integrity and would even be more robust. I do not need full-text searching. I foresee to access the database using PHP/Apache. InnoDB would be the best way to go then, right? Second question, if it was not for full-text searching, why would anyone choose for MyIsam, is it really that faster?
View Replies !
Choosing A Version Of MySQL For Local Web Server
In the process of setting up a localhost web server to test things before I put them on my shared host server. Mostly I'm just dabbling - not making a living at it. My server is Apache 2.2.3 with PHP 5.2.0 on WinXP-Pro. What version of MySQL is most compatible with these versions of server software? Can someone point me to an installation how-to doc? Would also appreciate pointers to pages I should read to familiarize myself with MySQL. I've dabbled with dbs's as standalone apps but never used one on a server before.
View Replies !
Subtract Previous Value.
I'm trying to get one column of numbers to subtract from another column and to keep a running total. Column A is a dollar amount from an invoice. Column B starts out with an opening amount ($10,000) and I want to have Column A to subtract from that. Code: COLUMN A--------COLUMN B $0.00 $10,000 $1000 $9,000 $500 $8,500 $2000 $6,500
View Replies !
ID Of Previous Insert
Im thinking i need a transaction here. I want to insert a record into a table, which has an autoincrement field as the primary id. Then use this id to insert into another table. In the time between the first insert and then getting the id, couldnt there possibly be another user that causes an insert and hence the id the first user gets back isnt correct ?transactions to the rescue ?
View Replies !
Previous Record
I need to get a previous record from database - i need to retrieve records which differs from the previous one about 5s example: select eventDate,previousEventDate,eventDate-previousEventDate as delta from events having delta<5 order by eventDate; eventDate is normal column from table previousEventDate is an "alias" for the previous date ... how can I write it in SQL? Please help. I can use SELECT as subquery, but it would be probably really slow, the table will have 100000s maybe milions of records soon.
View Replies !
Previous Row Comparison
I have a table in this format: Column A | Column B 1 | 2 1 | 3 1 | 4 2 | 5 ..... ..... What I want to do is something like this: If in Column A there is a change in the number i.e. from the above table there were many 1s which transitioned into a 2. I want to record this change. I want to extract these two rows into another table. How can I make this possible?
View Replies !
Previous To Last With A Certain WHERE Condition
How do i fetch the previous row record with the same WHERE when the last record has only a lately date. for example field CConsorcio CRubro ImporteA FechaA 1 105 1000 2006-05-10 2 110 30 2006-05-12 5 105 300 2006-05-13 4 104 600 2006-05-14 2 103 800 2006-05-15 the WHERE CConsorcio=2 the last record: 2,103,800,2006-05-15 and i want to select the row: 2,110,30,2006-05-12
View Replies !
Get Next And Previous Records
Our website has a portfolio database which uses a column entitled 'list_order' to set the order of how we want the projects to display in a list. Each project has a link to click into it to see the full details of the project. Inside this details page we would like to add a View Previous Project and View Next Project link which would allow the user to navigate through our projects. The problem is that we are using an order set by 'list_order' and not an auto incremented column. How would I setup the Query to grab the previous and next rows no matter which project the user clicked on? For example, our database has these records: id - list_order 102 - 100 89 - 200 7 - 250 30 - 300 97 - 400 20 - 500 62 - 600 88 - 650 103 - 700 85 - 800 112 - 900 95 - 1000 So, if someone clicked into id 97, we'd need a query that would grab id numbers 30 and 20 for the previous and next id to create the links.
View Replies !
Select Previous And Next Id After Id=# ?
For a gallery I'm trying to query for a picture by its id and also get some images before and after that id (according to current sort) are there references like DOM previousNode or stuff like that to use in the WHERE statement? Or how could I do this? Currently I have: ($picId is a php variable) '(SELECT id, picUri, picUriSmall FROM pictures WHERE id < '.$picId.' ORDER BY id DESC LIMIT 2) UNION (SELECT id, picUri, picUriSmall FROM pictures WHERE id = '.$picId.') UNION (SELECT id, picUri, picUriSmall FROM pictures WHERE id > '.$picId.' LIMIT 2)' This works but doesn't seem to be the best way. I want to be able to have it working for other sorts than by id. And the two first images gotten by this query will be in reversed order (can be fixed in php). Is there a way to ask for my picIDs position in a query and then use another query to make the LIMIT 10,14 if my image was in position 12?
View Replies !
Paging With Only Next/previous
I'm running PHP + MySQL (innodb) and in some pages, the paging is slowing down the system a lot. Now I've seen on some sites that have paging implemented in an interesting way. They only let you go forwards and backwards one page at a time, and some don't even tell you how many results there are. For instance if you look at google search results, they only says "about XXX results". Plus they never actually let you jump to the last page. I'm thinking of implementing my system this way in certain pages as well, to cut down on the load times. However, I'm exactly sure how that cuts down the load. Can someone shed some light on this?
View Replies !
Determine Previous Month
basically I need to display a list of "unpaid" statements for the previous Month, no matter what day the search/request is made on. I know MySQL has the DATE_SUB function where I can subtract 1 Month from the current date, but will that subtract the date exactly 1 month to the day ? Or will it simply give me the Month as I need ? What happens if the search takes place on the 31st of a month (for example: July), yet the previous month only has 30 days (June). Will it roll back to the previous month correctly or think that the date is 31st June or try to roll back further until it finds another month which has 31 days ?
View Replies !
Getting Next, Previous Record After INSERT
When I insert a record in a given index order I want to be able to traverse to the previous or next record of that index order, before or after the one I inserted. So for example if I insert a record with an address of "20 Heather Court" using an address index order, I want to be able to push a Next button (on a Web HTML form) to go to the record corresponding to "21 Heather Court" or, if I press the Previous button I I want to be able to go the record corresponding to "19 Heather Court". Assuming of course that both the Next and Previous records exist before I insert "20 Heather Court". If I change the index order to customer first name and insert a new record for a customer named "Harry" I then want to be able to push the Next button and see the record for a customer named "Jan" or if I push the Previous button I likewise want to pull up the record for a customer named "Garry". Assuming that the records for Jan and Garry already existed before I inserted the one for Harry and that they became the Next and Previous records with respect to Harry's after the insertion. Using an auto increment field does no good because every time I change the index order any kind of sequential relationship between the values in the auto increment field gets totally messed up. I have also tried statements like " SELECT * FROM Customers WHERE Customer_ID <349 ORDER BY `Customer_ID` ASC LIMIT 0 , 1" without success if there are 10 records before the one with a Customer_ID of 349. A LIMIT of 0,1 will start at record 0 for only 1 record. I want to pull up the record immediately preceding the one with a Customer ID of 349. In short is there some way to tell which record is Next or Previous to one that I insert under a given index order?
View Replies !
Get Previous And Next ID Of Current Record
I want do create a "Previous" and a "Next" link, all I have is the ID of the current record. Is there a good way to get the previous and next id of a record with a single SQL Query? example: ID's: 4,10,7,9,13 I open my page and show all details from the record with the ID 7, now I want to create the links and I need the previous and the next ID, 10 and 9. I could make a query that returns only the ID's and loop through the recordset till i have my ID's but that's such an ugly solution, not to mention the performance issues.
View Replies !
Phase Previous And Next Record
I want do create a "Previous" and a "Next" link, all I have is the ID of the current record. Is there a good way to get the previous and next id of a record with a single SQL Query? example: ID's: 4,10,7,9,13 I open my page and show all details from the record with the ID 7, now I want to create the links and I need the previous and the next ID, 10 and 9. I could make a query that returns only the ID's and loop through the recordset till i have my ID's but that's such an ugly solution, not to mention the performance issues.
View Replies !
To Find The Previous Record
I am preparing a ledger using PHP and mysql....I just want to bring the previous closing stock value as the opening stock value of the current transaction.For that i need to find the closing stock value of previous transaction....Is it possible to write a query to find the previous record?
View Replies !
Show Empty Field On Same Previous Value
I have this query now: SELECT table_2.id, table_1.naam, table_2.bedrag FROM table_2 LEFT JOIN table_1 ON table_2.table_1_id = table_1.id this is the result: id naam bedrag 1 pat 200 2 carl 100 3 carl 300 4 bob 500 5 bob 750 6 bob 800 what I want is get the double names replaced by a empty or space: 1 pat 200 2 carl 100 3 300 4 bob 500 5 750 6 800
View Replies !
Compare Previous String In Resultset With Next
The code below does not work. while(resultSet.next()) { if (!resultSet.getString.next().equals(resultSet.getString.previous()) { out.print("..."); } } Is it possible to compare previous string in a resultSet with the next string? If so, could you please show me an example.
View Replies !
Previous Versions Of MySQL With XAMMP
I've downloaded XAMPP for Linux 1.5.1 for use on Fedora Core 4. I notice it only has the latest version of MySQL on it. I'm working on two web sites (for different clients) and they are hosted on different shared Linux hosting servers (with PHP), each with different versions of MySQL available, i.e: MySQL 4.0.25-standard MySQL 3.23.52 Is it possible for me to use older versions of MySQL with XAMPP? Could I just turn off MySQL in XAMMP and just run and older version of MySQL independently of XAMMP? If I can then how would I install multiple versions of MySQL in Fedora Core 4?
View Replies !
Getting Data Entered In The Previous Week
When a product is added to the database it has three seperate fields storing the date added in(01 - 31 format) month (January-December) and year( xxxx). Is there anyway that I can run a mysql/php query that will just retrieve those products entered within the last xx weeks?
View Replies !
Best Way To Select Next And Previous Items With Order By
to select the previous and next items. I am using this: DO @a:="title_of_current_item"; SELECT * from tabled where itemtitle < @a order by itemtitle desc limit 0,5; --previous items SELECT * from tabled where itemtitle > @a order by itemtitle asc limit 0,5; --next items is there better (one query) and/or faster way?
View Replies !
Two Table Query: Grab Rows From One Table Even If No Related Row In Other Table
PHP $gettray = mysql_query("SELECT trailers.title, trailers.link, trailers.movie, movie.title AS mtitle FROM trailers,movie WHERE trailers.movie=movie.word ORDER BY trailerid DESC LIMIT 6",$connm); It works great, but there is one problem. It will not grab any rows from the 'trailers' table if a corresponding movie row does not exist in the 'movies' table. I want it to pull ALL rows from the 'trailers' table, even if the corresponding row in the 'movies' table does not exist yet. If the row does not exist in 'movies', the program than uses the entire trailer title like so PHP if($ttray['mtitle']) { $newttitle = explode("-",$ttray['title']); $newttitle = array_reverse($newttitle); $ttitle = $newttitle[0]; $ttitle = $ttray['mtitle'] ."- ". $ttitle; } else { $ttitle = $ttray['title']; } Thanks Ryan
View Replies !
Get Previous And Next Records Relative To An Index Record
Suppose I have a table "product" with fields id,name,price. I want to get a resultset of previous and current and next record when the resultset is sorted by id and id=10. The table data is shown in the following: id Name Price 1 : : 5 : :<--------------previous record 10 : :<--------------id=10 12 : :<--------------next record 14 : : How do I write the sql statement if I do not know 5 and 12 are the previous and next id respectively?
View Replies !
|
TRACKER
