Return Info On Last Record With One Query
Lets call the table:
comics
Lets call the fields I am looking for:
ID
filename
I want to get the maximum ID from the table and the filename field corresponding to that maximum id.
I can do it easy in two queries:
select max(ID) as max_id from comics
then in php send
select filename from comics where ID=$id
(where $id is the number returned from the first query)
I would like to do ONE query like:
Code:
select max(ID) as max_id,
and filename
from comics
where ID=max_id
View Complete Forum Thread with Replies
Related Forum Messages:
MS Excel Doesn't Return All Info
I have a simple MYSQL database that I use an Excel front end as an input form / query tool. It all works fine except for a simple query. The syntax is: "Select manufacturerID, manufacturer, mfr_address as Address, mfr_phone from tblManufacturer" When I run this in MYSQL, either at command line or using the Query Browser tool, it returns all data. When I run the same SQL within MS Excel, only the manufacturerID and manufacturer fields are returned although all 4 field names are returned. I don't know how to explain this, the table set up is as follows: manufacturerID - Integer, autoinc manufacturer - character(250) mfr_address - character(20) mfr_phone - text
View Replies !
How Do I Return The First Record In DB?
Table name: Fabrics fabric_id fabric_name fabric_color I would like to retrieve only the first record in the database i.e. the record with the smallest fabric_id number. I thought I could do a SQL statement like the following, but it doesn't work. SELECT MIN(fabric_id) as id, fabric_name, fabric_color FROM fabrics
View Replies !
Return ID If Record Already Exist
I looked everywhere (Google, manual, etc.) but can't find an answer to this question. If I do a "INSERT IGNORE INTO" query into a UNIQUE field and the word I'm trying to insert already exists, can I get the ID of that existing record returned so I can use it in the rest of the query?
View Replies !
Return One Child Record
I have a table `entity`, which holds info about organisations (nurseries, botanical gardens etc). A child table `file` stores links to images for these organisations. A field `sort_order` lets me sort them in order of importance (1 is highest). If the field is null it is assigned 99, eg: if(sort_order is null,99,sort_order) as sort_order On a search results page, I would like to return only one image per organisation, but I'm struggling to create a query or view that can do this.
View Replies !
Insert Record And Return Id
lets say i have my db setup as such user id | name | email | default_pic picture id | user_id | name if each user can have multiple pictures with one being their default it would be helpful to insert their account and default picture into the database and update their pic. right now im doing something like this //insert user into db //insert picture into dabase //select most recent picture //update default_pic with most recent picture is there any way to do this //insert picture into db and return the id just created //insert user into database with pics id we just got running these 4 sql commands as shown above seems to be slowing my page down a great deal.
View Replies !
Return Most Recent Record For A Group Of IDs
How do I return only the 1 most recent record from one table given a list of customer IDs from another? We're unfortunately running just MySQL 4.0 Pseudo code: show a list of distinct customer IDs for those customers that have a currentbalance of > $0, who had an order marked canceled in the last 7 days. Given: orders - orders.customerID - orders.status - orders.updated (datetime) billing - billing.customerID - billing.currentbalance - billing.updated (datetime)
View Replies !
Memory Size Exhausted Error - Too Much Info Back From Query - What To Do?
Using PHP and MYSQL I changed one of my MySQL search queries to allow a situation where the user gets much more information returned then previously However, it has now given me the error message: Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 42 bytes) in /usr/share/pear/DB/mysql.php on line 290 OK I understand what this means (I assume) that the program is trying to allocate more memory to return my query result but it wont let it. However, I need this query to return this info, so what can I do?
View Replies !
Query Count Return Zero
I'm trying to query a database searching for everytime an event happened in the past 24 hours. I'm using a count(eventid) to add up each event during an hour time. However, if an event didn't happen say for 2AM then 2AM isn't shown in the return. How can I get 2AM to show as a row, but with a count of 0 (zero) ?? Here is my qeury... select count(eventid), DATE_FORMAT(CONCAT(eventdate,' ',eventtime),'%Y%m%d%H') as hour, time_format(eventtime, '%H:00'), eventdate from event where sensorid = '2' and eventdate between '2005-01-19' and '2005-01-20' and DATE_FORMAT(CONCAT(eventdate,' ',eventtime),'%Y%m%d%H%i%s') >= '20050119150018' group by hour order by hour desc;
View Replies !
Query To Return Two Count()s
I'm trying to figure out a better way of doing this: SELECT (SELECT COUNT(*) FROM GuestEvents WHERE EventID = 1 AND EventStatus = 'y' AND UserID = 'bob') AS Event1, (SELECT COUNT(*) FROM GuestEvents WHERE EventID = 2 AND EventStatus = 'y' AND UserID = 'bob') AS Event2 As you can see it's quite repetitive, I wondered if there isn't something like this which could optimise it a bit: SELECT COUNT(EventID=1) AS Event1, COUNT(EventID=2) AS Event2 FROM GuestEvents WHERE EventStatus = 'y' AND UserID = 'bob'
View Replies !
The Query Cache Does Not Return Stale
I am reading up on query cache feature and would like to have the following question clarified. From mysql 5.0 manual, section 5.14, it says "The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed." I would like to know what is the definition of "when tables are modified". does it mean? 1. new records inserted into table? 2. records gets updated? 3. table structure is modified by using alter table command?
View Replies !
Default MySQL Return Value For Query
I'm writing a multilingual web site where not all content will necessarily be multilingual. I have a languageid and want to retrieve the content for that language. However, if that content doesn't exist, I would want to retrieve content for the default language. My ideal query would be something like: SELECT content FROM page_content WHERE pageid=$pid AND languageid=$userlang IF NO RESULTS WHERE pageid=$pid AND languageid=$defaultlangid I imagine my made- up 'IF NO RESULTS' directive does not exist. Is there any way to get that kind of functionality from one query? Since I will be having queries all over the site to retrieve different kinds of localized things (image tags, html code, textual content), it would be awesome if I could figure out a way to consolidate. Maybe there is a way to sort results from this query: SELECT content FROM page_content where pageid=$pid AND (languageid=$userlang OR languageid=$defaultlang) ORDER BY .... ? I'm also not sure if the nested boolean will work.
View Replies !
Column Name Is SQL Keyword(Return) In SELECT Query
Is there a setting in MYSQL server that let it accept Select queries with Column names that also are keywords? "Return" is a keyword now in MySQL. Motivation: We had an old delphi application that uses a mysql database. Somehere in code it does: SELECT Return from CarUse; (Actually it does more but the above is the isolated problem.). Return is a Column name in the CarUse database. In our old setup with an old mysql database, the above code was accepted. I migrated the database to a new server with a new MySQL. The above code is no longer accepted.
View Replies !
Query To Return Totals Of 1-5 Votes, Even If That Number Is 0
query to return totals of the votes: SELECT count(good) as numgood, sum(distinct good) as scoregood, sum(good) as totgood from survey where jobclass = 'Salaried' group by good That returns this: numgood | scoregood | totgood 27 | 4 | 108 70 | 5 | 350 What I need are results that include no votes cast for the other values: numgood | scoregood | totgood 0 | 1 | 0 0 | 2 | 0 0 | 3 | 0 27 | 4 | 108 70 | 5 | 350
View Replies !
DateTime Query To Return Last Months Transactions
For the sake of example, let's use the following table. I want to return all txn_ids that occurred last month. I'd like the function to not care how many days there were last month, but still return the correct values. Table: transactions timestamp : DateTime txn_id : int I've done some reading, but everything I've found seems to use actual dates to do the subtraction. This requires that you know how many days are in that month. The other way requires that everything be referenced to the current date. SELECT * FROM txn_id WHERE timestamp BETWEEN '2005-01-01' AND '2005-12-31' SELECT * from txn_id where timestamp >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
View Replies !
Creating A Query That Will Only Return Records With Matching Counterparts
I'm using the table below as an example. I want to create a MySQL query that will return only records that have matching counter-parts where 'col1' = 'ABC'. Notice the 'ABC / GHI' record does not have a counter-matching 'GHI / ABC' record. This record should not be returned because there is no matching counter-part. With this table, the 'ABC / GHI' record should be the only one returned in the query. How can I create a query that will do this? id | col1 | col2 -------------------- 1 | ABC | DEF 2 | DEF | ABC 3 | ABC | GHI 4 | DEF | GHI 5 | GHI | DEF
View Replies !
Query- Get Every X Record?
My app loads my database every 30 seconds with weather data from several different weather stations (2880 records per station per day). I need to extract every 10th record for a station (288 records per day) to build a PHP array for a graph.
View Replies !
Query Works With One Record
This query works great if there is one matching record in the Albums table. When there's more than one matching record it returns nothing. Code: SELECT Artists.Artist, Albums.Album_Name FROM Artists, Albums WHERE SOUNDEX('colname') = SOUNDEX(Artists.Artist) AND Albums.Artist_ID=Artists.Id
View Replies !
Query Help: Get Oldest Record W/ Grouping?
I'm building a system that allows a maximum of two records per unit. When a new record is created for that unit, if there are already two records the oldest record is deleted. Thus, I'm trying to build a query that gets the oldest record for each unit, but also returns the number of records per unit. The trouble I'm having is in making sure the record I'm getting is the oldest one! I thought I could use the Order By clause to get the oldest record, but it doesn't work. In a database table where there are a total of four records: recordID unitID recordFile recordTime 10 1 someFile1.txt 1179778828 11 3 someFile5.txt 1179778828 12 3 someFile5.txt 1179778990 13 1 someFile5.txt 1179778956 The following query: SELECT unitID, COUNT(recordID) AS numRecords, recordID AS oldestID, recordFile, recordTime FROM records GROUP BY unitID ORDER BY recordTime DESC Returns the following result: unitID numRecords oldestID recordFile recordTime 3 2 12 someFile5.txt 1179778990 1 2 13 someFile5.txt 1179778956 These are the newest records, not the oldest. I'm not even sure that is a reliable case. I get the exact same result using the same query but with ASC instead of DESC!
View Replies !
Insert A Record Using MySQL Query Browser
I know I can probably type in the actual query, but I was wondering if there was a means of inserting a record without typing "insert into table_name (blah, blah2) values (blah, blah)". I'm looking for a method as easy as typing into an Excel spreadsheet or Access DB. (I know I'm gonna get bashed for mentioning Microsoft products.)
View Replies !
Query To Display A Record By Recent Time
Many users uploaded their files to my mysql table through php script, my table having the details of uploading time, file name, & uploader name. i need to find the recent uploaded file for all uploaders. i tried with this query, select file_name,uploded_by,MAX(date_time) from upload group by uploded_by; it is giving the recent time, but it is not giving the latest file, it is showing first uploaded file.
View Replies !
Lost Connection To MySQL Server During Query Socket Error On Read. WSAGetLastError Return 10054($2746)
We just migrated our MySQL database from a NetWare server running version 5.0.21 to a openSUSE box running version 5.0.45. Everything ran fine for a couple of days, then clients began losing their connections and creating new ones without the old threads being terminated. This behavior is the same for the Delphi client we use to access our database as well as the MySQL Administrator tool. I've tried adjusting connection_timeout, but overall the settings for this server are the same as the previous one that ran without issues for a year.
View Replies !
How To Write Query To Select The Max(version) For Each Unique File_name Record?
I am a MySQL newbie trying to write a query that selects file_name records possessing the highest numbered version for that unique file_name. I show sample data and two trial queries below. Logically I want to use max(version) as a constraint in a Where Clause. However, the max() function is not allowed directly in a where clause. I have contemplated a second table to track the max version for each file name. I would like to structure the data in an efficient manner for query performance when the data set grows to many thousands of unique file_name records with many hundreds of versions each........
View Replies !
Query To Retrieve 5 Records Immediately Before And After A Particular Record In Sorted Manner
Just consider a simple table with one integer column (however numbers are not inserted in sorted manner and some of them may be missing). Given a particular record, say 32, I would like to retrieve 5 records immediately before and after this record in ascending manner of sort. So, in this case I should get 25, 27, 28, 30, 31, 32, 33, 34, 37, 38, 39 (I did not have 26, 29 and 35 in the table) Why do I go about writing such query?
View Replies !
Return Only 3 Photos From 3 Specified Galleries In One Query (was "Is It Possible?")
Let me start from the beginning.... I have a table that lists photo details, with a foreign key to link them to a gallery. Lets say I have 20 photos altogether and 4 galleries, (for simplicity sake, 5 photos per gallery). My question is: Is it possible to query the table to get it to return only 3 photos from 3 specified galleries in one query? (So returning 9 photos altogether, 3 from each gallery) Rather than querying the table 3 times with a limit of 3 and merging the results.
View Replies !
Automatically Record Date Of Record Entry
I have my database table set up and I have an HTML form that is PHP driven that will add the information entered into the form into to my database table. I have a local buy-sell-trade Website. The way it has worked is that people fill out a form and the results emailed to me. I then take the information and enter it into a Web page. I only want the ads to be displayed for 30 days. I keep the ad for a total of 6 weeks (displays for 30 days and sits in limbo for 2 weeks afterwords) and if not renewed within that 2 week limbo period - I delete it. Entering all the ads and keeping up with the dates manually has become a burden. I've only recently began looking into databases. My hosting company provides me with phpmyadmin and mysql 5.0. I'm new to all of this but I have managed to set up a database table and a HTML form that is PHP driven that allows ads to be automatically added to the database table. There is a lot I need to do to make this ideal, but one step at a time. First, I need to know the date (March 02, 2006) the ad was created or added to the database table. I know that I need to add some piece of code to my php form to record this information, but what code and where do I put it? I know I will need to create an extra field in my database table to house the date - I can handle this. I've read the date and time information here http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html but there's a lot there and I don't know which is right for my needs. Plus, it doesn't tell me how or where to insert it into my php form (or does it?).
View Replies !
Want Only One Record Returned Per Post_id (was "Help With Query")
I can't figure out how to do this query. Help would be muchly appreciated! I want to get the DISTINCT(post.post_id) but not sure how to do this. Here is the query I have thus far but it's returning two records each with the same post_id but different cat_id's. SELECT post.post_id, auth_alias, pc.cat_id, post_heading, DATE_FORMAT(post_created, '%M %d, %Y'), post_body FROM posting post LEFT OUTER JOIN post_cats pc ON post.post_id = pc.post_id WHERE post_status = Ƈ' ORDER BY post.post_created DESC LIMIT 0,5
View Replies !
Getting Record Before Or After The Record That Meets The Criteria
In a query I want to get all the results and then order those results by last name, but then I want to filter those results down to only the record that comes before or after the record that has 'empno' = '1259'. I want to get all the results already ordered and then filter them down to one record either before or after (depending on what is needed) the record where 'empno' = '1259'.
View Replies !
How To Record The Current Record In A Table?
I'm looking at convert file formats to mysql. The original file format used an "ENTRY" conception to record the current record. But I cannot find a solution to emulate "ENTRY" conception in MySQL. After searching MySQL's tutorials, the AUTO-INCREMENT fields would only use "mysql_insert_id()" to obtain its value. But this value cannot identify the current record, Right? This value are only last record after inserting? Who can help me to find a better solution to emulate implementing what the "ENTRY" does. In other words, How to obtain the auto-increment value contains AUTO-INCREMENT columns in a record which generated by SELECT statement.
View Replies !
Getting Info
I'm going to select all articles from ~10 databases (on the same server) all with equal table structure. Lets call the db's for db1, db2 etc. And the table for 'article', the condition is that the column 'location' in article is equal to start(eq. WHERE location = 'start').
View Replies !
Db Info
I have just encountered the weirdest thing ever that I have no idea why it is happening. I had a site one 1 server switched it over without changing the db username and password and all the stuff worked on the new server even though the db info was wrong. Is there a way to fix this because I feel like thiscan be a major problem later on.
View Replies !
Looking For Info
I'm doing a University paper on MySQL and haven't been able to find any information on the minimum and recommended hardware requirements needed to run MySQL.
View Replies !
Getting Processlist Info
I have a need to get a list of currently connected users in a query for use in a view. I know the syntax for show processlist, but as of yet I cannot find a way of joining that information in a view. I also can't find any accessible table that lists the currently logged in users. The reason that show processlist isn't sufficient for my needs is that I need to display this information in a vb.net application, which as is currently designed uses one view. I would like to modify the view to include a boolean value based on whether or not they show up in the processlist. I don't care what process they are using, nor do I care about the state of the connection.
View Replies !
MySQL Info?
for a long time I have been using Postgresql as my application target database, and while I'm really pleased with it I miss many proffesional features availables in other databases (say Oracle or DB2), specially those points concerning High Availability, Clustering, monitoring, ... Now that CA has realeased Ingres under opensource I thought it was time to change to such database since Ingres has all the proffesional features one can spect and is a very alive project. I studied time ago to change to MaxDB/SAPDB when it was opensourced and asked in a lot of forums through the internet but what people told me (SAPDB administrators mainly) was discoraging and MaxDB looked to be "dead". My doubt comes now. What about MySQL? Last time I cheked it (ver 3.x) it was far away for suiting my needs (no ACID, no triggers, no clustering, ...) but I have read in the news many things are changing in the upcomming ver 5.0. How much true is that? Can I hope MySQL v5 to be able to compete with Ingres, Oracle or DB2 in features?. - Is it well documented ? (I was asthonised with Ingres that has been opensourced with more than 20 pdfs manuals covering every small detail about administration, development, monitoring, clustering, ... even with documents explaining the C-source and architecture internals). - Any good reasons you can imagine to choose MySQL instead of Ingres to update Postgresql?
View Replies !
Displaying Info
I'm working on my picture section, i was wondering if there is a way to order how things are layed out using mysql. I want something like this PIC 1 PIC 2 PIC 3 PIC 4 PIC 5 PIC 6 Ect ect. Is there a way to do this and what would it be called?
View Replies !
Sales Tax Info
where I can get sales tax info that I can integrate with a MySQL DB to calculate what tax to charge for each shipping location in my ecommerce app?
View Replies !
Info From Mysql
Are there any way to get info from mysql of the fulltext query passed to it?(ex num_optional_words). ex: for the query: 'car apple +store', one would have: num_optional_words = 2 num_compulsory_words = 1
View Replies !
Compatibity Info
Where can I find information on the compatibility of MySQL 5.x? More specifically, is it backward compatible with MySQL 4.1?
View Replies !
Info From 2 Tables
I'm struggling with mysql, I have 2 tables, ones called pmsys and the other is called user. I was reading about aliases so I thought I'd try it, but when I put in the following information it says the ON statement is wrong. This must have really gone over my head because I don't see what is wrong. how to get this right? SELECT t1.id, t1.to_id, t1.from_id, t1.time_sent, t1.subject, t1.message, t1.opened, t1.time_opened, t2.user FROM `pmsys` AS t1, user AS t2 LEFT JOIN user u ON t1.from_id = t1.id WHERE t1.to_id = '1' order by time_sent DESC; I need the info from the pmsys and the username of the person who sent the message by matching their id in the user table to the to_id in the pmsys table.
View Replies !
Info Into Table
I have made this site http://www.tinemuller.dk/folkebiblioteker/ and used mysql and have a lot of columns but what I need help to is "hjemmeside" which at the moment is empty for all the libraries (name). I have now found a excel-file where I have deleted all the columns that I shouldn't use and only left "name" and "hjemmeside" and name is identically with name in the database online. How do I get my database updated with the informations in "hjemmeside" from the excel-file and at the right place in the row of the right "name".
View Replies !
Can I Get Info About What Is In A Resultset?
I have a php class function that builds a query (on a single table) and invokes it to produce a resultset. Another function, would like to discover what is in that resultset so that it can display it nicely (ie appropriate name and widths for columns). I found the the SHOW command is able to give this sort of information for tables, but I could not find a way of getting the same infor for resultset's.
View Replies !
|
TRACKER
