Change Value In Field Based On Order
I have a field that is an INT and can contain a value of 0 or greater. When the value is 0 it creates a unique situation and users of the site will sometimes change this to 0 on purpose because this value can have your item show up in the search results on the first page. Why, if a person orders the information ASC, it will show up first.
I have to allow this field to be 0, long story, but I want to "punish" them if this is so. Based on the type of search (ASC or DESC) as the user can pick. I want those items to always show up last.
Currently I am passing a variable that gets set via a form w/ that information, but I don't know how to check in the query how the variable is set. I was thinking CASE but I thought that only looks at a field in the db, not some variable that is being passed.
Either way, I hope this is making sense. Here is the query (pretty simple).
Code: ......
View Complete Forum Thread with Replies
Related Forum Messages:
Select Record Based On 'change' Date
I currently have a database containing a table with the following fields: signid, sign_typ, changedate and time. The 'changedate' field is the date on which the star sign changes and becomes 'active' until the next 'changedate' in the table. I am trying to search this field by a date the user enters and display the 'sign_typ'.
View Replies !
Order Records Based On Two Columns
I'm working on PM feature for my site. So far I have this table: +-----------------+-----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-----------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | subject | char(100) | YES | | NULL | | | body | blob | YES | | NULL | | | r_id | int(11) | YES | | NULL | | | s_id | int(11) | YES | | NULL | | | date_sent | int(11) | YES | | NULL | | | date_read | int(11) | YES | | 0 | | | r_date_to_trash | int(11) | YES | | NULL | | | s_date_to_trash | int(11) | YES | | NULL | | | r_status | int(11) | YES | | NULL | | | s_status | int(11) | YES | | NULL | | +-----------------+-----------+------+-----+---------+----------------+ r_id - "Receiver's ID" s_id - "Sender's ID" r_date_to_trash - "Unix timestamp when receiver deletes the message" s_date_to_trash - "Unix timestamp when sender deletes the messages" r_status - "receivers msg status, if r_status == 1 then message is in the trash s_status - "senders msg status, if s_status == 1 then message is in the trash So this is the query I have right now: SELECT * FROM pm WHERE (r_id = 1 AND r_status = 1) OR (s_id = 1 AND s_status = 1) This is the result: +-----+------+------+-----------------+-----------------+----------+----------+ | ID | r_id | s_id | r_date_to_trash | s_date_to_trash | r_status | s_status | +-----+------+------+-----------------+-----------------+----------+----------+ | 104 | 1 | 2 | 1226972079 | NULL | 1 | 0 | | 110 | 1 | 2 | 1226971762 | NULL | 1 | 0 | | 111 | 1 | 2 | 1226971891 | NULL | 1 | 0 | | 112 | 1 | 2 | 1226972033 | NULL | 1 | 0 | | 113 | 1 | 2 | 1226972462 | NULL | 1 | 0 | | 120 | 2 | 1 | 0 | 1227103836 | 0 | 1 | | 122 | 2 | 1 | 0 | 1227103841 | 0 | 1 | | 127 | 2 | 1 | 1226972346 | 1226972090 | 1 | 1 | | 128 | 2 | 1 | 0 | 1226972095 | 0 | 1 | | 129 | 2 | 1 | 0 | 1226972176 | 0 | 1 | | 130 | 2 | 1 | 0 | 1226972396 | 0 | 1 | +-----+------+------+-----------------+-----------------+----------+----------+ This is the problem: I'm trying to list the messages that are in the trash in order by date they were deleted (r_date_to_trash, s_date_to_trash). I need to join these two fields somehow together and then I could just do ORDER BY whatever DESC. That way the messages in the trash will show up in the order they are deleted.
View Replies !
Order By Based On Results From Another Table
I have a query that , and i need to order the result based on wheter or not the ID of my main table appears on another table, somewhat like this: SELECT A.id AS Id, A.nome AS Nome FROM main_table A JOIN joined_table JT ON JT.id = A.id_cat ORDER BY ("first whoever have a row in" another_table AT) another_table stores the image paths related to main_table , i need to show all query results from main_table even if they don't have a image (row in another_table), but i need to show whoever have a image (a row in another_table) first... I've tryied to join with another_table and even to use sobqueryes in ORDER B... but so far i got no results. it's even worst because this query is a monter with multiple lines (the sample is simplyfied...)
View Replies !
Change Date Display Order?
I have a date tyoe set to a column. However, it is using the US style date like this: 2008-11-22 Is there a way I can change it to be a UK style like: 22-11-2008
View Replies !
How To Change The Physcial Order Of Rows ?
Instering into an empty table rows , stores them in order .let's say 9 3 6 7 so after a select the above order will be shown. How can I insert a new record so that it goes between 3 and 6 , and result of normal query ( select * from table ) to show them as: 9 3 555 6 7
View Replies !
Adding A Key Field Based On Other Columns
I have a MYSQL db based on an excel sheet There's a membership number field that's mostly empty, there's five different levels of membership from full to part time and so on, each represented by a 'Y' in the relevant field, also a date field with the date that they first joined What I want to do is allocate a unique membership number for each member, a six figure number starting at 100001 upwards for the full members starting with the oldest memberships, 200001 upwards for the part time members, and so on.
View Replies !
Selecting Fields Based On The Value Of Another Field
My friend asked me to help with this query but unfortunately I wasn't able to figure it our for him. Here's the info he presented to me: Table 1 - portrait field 1 - id field 2 - type (will be the id from a row in business or personal) Table 2 - business field 1 - id field 2 - bizname Table 3 - personal field 1 - id field 2 - firstname field 3 - lastname His PHP code will select a row from portrait where 'id' is a given number. Then based on the value of 'type' for that row he will either select the 'bizname' for that corresponding id or 'firstname' and 'lastname'. There is no way to know ahead of time whether the row is a business or personal account. If this can be done without a subquery, that is ideal. However, if a subquery is required, that's ok, too.
View Replies !
Combining Multiple Records Into One Based On A Key Field
I'm really scratching my head over this one. I'm working with CSV data exported from Excel, which explains why it's a mess to begin with. Within a table (or via any other means someone might be able to recommend) I need to combine multiple records which share two like fields. (If that's not clear, the real-world explanation is below.....
View Replies !
Selecting All Records Without Dups Based On One Field?
I have a table with 5 fields, of which several sometimes have duplicates. example; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 4 | 7 | 3 | 9 | 122.165.177.211 | | 5 | 11 | 3 | 9 | 177.15.99.111 | | 6 | 24 | 3 | 17 | 122.165.177.211 | As you can see the ip address may obviously have dupe entries (as do other fields but I don't care if they are dupes) and I want to query the table and get all the fields but only one where there is dupe ip_address 's. So that I would get these results; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 5 | 11 | 3 | 9 | 177.15.99.111 |
View Replies !
Detecting Duplicates Based On One Field In A Table?
I can't seem to find an answer anywhere for this as most people want to delete duplicate records. I have a table, lots of fields, one of which is "title". I want mysql to return all the rows where "title" is the same. For instance if "title" was "Lord of the Rings" on id = 1 and id = 2, then the query would return both of the records, but ignore the others. It doesn't matter what the other field values are. I have found some SELECT COUNT( * ) AS num_entries, title FROM title_table WHERE title >1 GROUP BY title But this doesn't seem to work, although looks like it's on the right sort of lines? I'm not a Mysql expert, so my experience is mostly limited to the usual select, insert, update and delete queries!
View Replies !
Change Of Field
I'm trying to change the field type from int to varchar and I get the following error message: Error SQL-query : ALTER TABLE `posters` CHANGE `poster_code` `poster_code` VARCHAR( 10 ) UNSIGNED DEFAULT '0' NOT NULL MySQL said: You have an error in your SQL syntax near 'UNSIGNED DEFAULT '0' NOT NULL' at line 1 Would anyone be able to suggest something to fix this up?
View Replies !
Updating A Database Field Based On A String Or Filename
I am using MySQL to store images that I serve up with Coppermine photo gallery. Each image is shown in low resolution format. I also store medium and high resolution versions of these files for download. There is a caption field where I put these links using BBcode. It looks something like this: Code:
View Replies !
Trigger That Fills In A Missing Foreign Key Based On Another Field
To provide an easy insert methods for our client, I'm trying to write a trigger that fills in a missing foreign key based on another field that is inserted. Basically, 'dr_market_id' is inserted and I'm trying to find 'market_id' by selecting the appropriate row in the markets table. area_codes_markets.market_id is foreign key to markets.id delimiter | CREATE TRIGGER area_codes_markets_ins BEFORE INSERT ON area_codes_markets FOR EACH ROW BEGIN DECLARE tmp_market_id INT UNSIGNED; DECLARE tmp_dr_market_id INT UNSIGNED; IF NEW.market_id is null THEN SELECT id INTO tmp_market_id FROM markets WHERE dr_market_id = NEW.dr_market_id ; SET NEW.market_id = tmp_market_id; END IF; END;| delimiter ; The trigger gives no warnings. However when I try to insert a row into area_codes_markets, I get a foreign key constraints error: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`area_codes_markets`, CONSTRAINT `area_codes_markets_ibfk_1` FOREIGN KEY (`market_id`) REFERENCES `markets` (`id`)) The table 'markets' is populated with the correct ids. Does anyone have an idea what I'm doing wrong? If not, is there a way to print out the values of tmp_market_id and NEW.market_id in the trigger that are echo-ed to the console?
View Replies !
How To Change Start Value For AUTO_INCREMENT Field?
there's probably a simple way to do this but ... basically, how does one change the start value (presumably the default is "0") of an AUTO_INCREMENT field in a table - as in, UID for membership in a Forum? for instance, if the "start value" was 1,000 then the first member will be recorded as "1,001" and so on.
View Replies !
Order By Field
I jsut want to order by a field, but not by the content of the field but just placing the fields that have values first.. (but not order by those values) for example, i want to order results of users by placing first the users with photos, but without ordering by the photo names .. first display users with photos and order by newest
View Replies !
Generating DAILY Min&Max Over A Period Of Time, Based On A "DateTime" Field
how to print the minimum and maximum of a specific field, between two datetimes (both having the time 02:00:00) - so I did this: WHERE ((tblvalues.dtLPDateTime)>="2006-01-01 02:00:00" And (tblvalues.dtLPDateTime)<"2006-01-03 02:00:00")) Now I want to print those min and max values but for each day in this cycle, i mean, for this example, there would be two records like: Day Min Max 2006-01-01 02:00:00 -> 2006-01-02 02:00:00 | 40 | 59 2006-01-02 02:00:00 -> 2006-01-03 02:00:00 | 49 | 68 where at this point i`m getting min=40 and max=68
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 !
Order By Field From Other Table
("SELECT * from `offers` WHERE `status` = 'On' ORDER BY `pay_value` ASC"); I need to pull the "pay_value" from 2 different tables and then order it by that only. The first table is called `offers` and the field I'm referencing is `pay_value` type decimal(10,2) and the second table is called `exceptions` with a `pay_value` type decimal(10,2). What I'm doing is showing a list of ads with the amount of money that each ad pays per lead/sale. Some of our clients have been given increased payouts. Well, when they go to sort all of the ads in the system via the 'Pay Value' I can't make it show the increased payouts from the `exceptions` table while at the same time, being in correct order with the other `pay_value`'s from the `offers` table. I've tried the following: PHP Code:.....
View Replies !
How To Order A Serialized Field?
A DB have table countaining a text field what store a serialized birthday date of the user. This serialized field has your formate variable, dependent of the language and/or format set by the user preferences. Example of 3 serialized date fields: a:3:{s:3:"day";s:2:"22";s:5:"month";s:2:"10";s:4:"year";s:4:"1952";} a:3:{s:5:"month";s:1:"9";s:3:"day";s:2:"21";s:4:"year";s:4:"1954";} a:3:{s:4:"year";s:4:"2008";s:5:"month";s:2:"10";s:3:"day";s:2:"23";} The first represents the date: 22/10/1952 The second represents the date: 9/21/1954 The third represents the date: 2008/10/23 I can't change the table struture or the form what the field is stored. And now I've the follow mission: Order the table by the birthday date!!!
View Replies !
Updating Table Based Upon Matching Field In Second Table
I have a database of books that was originally created as a flat file. Each record has a number of fields, including the authors name. I'm trying to convert the database to something a little more efficient. I've created a new table (called Authors) of unique authors names and assigned each one a unique ID. I've added a new field in the original table (called Books) for the author's ID. Now, I need to update the original table with the author ID from the Author's table. Something like this: UPDATE Books SET AuthorID = Authors.AuthorID WHERE AuthorName = Authors.AuthorName This obviously doesn't work. Any assistance on how to forumulate this query (or, if I'm headed down the wrong path, the correct way to do this operation) greatly appreciated.
View Replies !
DESC Tablename ORDER BY Field?
I searched the site as best I could, but didn't find anything relevant. know how to sort the output of the DESC command by the Field column? I tried the obvious DESC tablename ORDER BY Field but this is a syntax error, as later documentation research seems to indicate.
View Replies !
Get Data From Multiple Tables And Then Order By Field
$QueryHIST_QUOTES = " SELECT TIMEDIFF(TIMESTAMP, $starttime) AS diff, t.* FROM $currentTable t WHERE TIMESTAMP BETWEEN $starttime AND $endtime ORDER BY TIMESTAMP"; Originally I had the variable $currentTable = "PRICES_2008" but I now want to get rows from multiple tables, so I tried: $currentTable = "XAUUSDOZ_2003 XAUUSDOZ_2004 XAUUSDOZ_2005 XAUUSDOZ_2006 XAUUSDOZ_2007";
View Replies !
DateTime Field And Orderby - WRONG ORDER
I have a problem using ORDERBY on a datetime field, its returning results in the wrong order, for example its returning a current list : - 4 hours ago - 7 days ago - 4 hours ago - 4 hours ago - 1 week ago - 15 hours ago As you can see, that clearly wrong. I've googled and found nothing to relavent on this issue...I even tried ORDER BY UNIX_TIMESTAMP(`updated`) and no difference. My query, incase it matters MySQL SELECT DISTINCT(`thread_id`) as `id` FROM `Post` ORDER BY `updated` DESC LIMIT $from, $to
View Replies !
When Was "order By Field()" Introduced?
Simple question, not sure if it's the right forum -- which version of MySQL introduced the feature: order by field('foo', 1,3,5,7) that is, sort records in an order that the query specifies? I've written a perl script which relies on this feature and now I'm wondering which version of MySQL it requires to work. Also, is there some easy way of finding out this kind of thing? I can look at the documentation and go back version by version until it isn't there, but that seems like hard work!
View Replies !
Re-order The Natural Order Of All Of All The Rows Of A Table
Basically I want to re-order the rows of my entire database. It has many thousands of rows, and people are constantly running the same query against it. The problem is its quite slow (using a shared host). I know you can use an sql query to order by column, however I need to re-order twice and it really seems to be slow due to this. Since the query is always the same, if the rows were already ordered in the database , then the ordering query wouldn’t need to be done. Is there an easy way to do this, without deleting each and every row, then inserting them again? basically in the database I have: The natural order when you do a select * from this table (without using an order by query): ________________________ |cola |col.. |coln | |dataA |dataB |dataC | |dataX |dataY |dataZ | |datal |datam |datan | (obviously a lot bigger than this) and I want reorder the entire table , eg The natural order when you do a select * from this table: ________________________ |cola |col.. |coln | |dataA |dataB |dataC | |datal |datam |datan | |dataX |dataY |dataZ | So is there an easy way to do this, am I just being dumb? or do I need to delete the entire table and insert the rows one at a time in order?
View Replies !
Multiple ORDER & SUB ORDER On The Same MySQL Table
I am trying to do multiple ORDERS or SUB ORDERS on the same MySQL table, and I'm loosing my mind trying to fathom the logic and SQL statement to use, I'm no MySQL genius! more a newbie. *** SEE ATTACHED IMAGE PLEASE I have tried all sorts of SQL statements, e.g: SELECT * FROM categories GROUP BY parent ORDER BY order, parent DESC Nothing seems to work. I think my only solution is to do a bubble-sort after putting the whole table in a PHP ARRAY ? I'd also like the menu to act like the + pop-open sub-menu boxes on the Forum left column menu.
View Replies !
How To ORDER BY The Order Requested In The Query?
Here's my query: SELECT * FROM myTable WHERE id=14 OR id=3 OR id=8 Simple stuff, I know. The result of the query is three rows that are all sorted by their 'id' in ascending order. I don't want this. What I want returned are rows sorted by the order in which I requested them. I need the query to return row #14, #3 and then #8 in that order.
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 !
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 !
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 !
|
TRACKER
