Retrieve A List Of Values Based On The String Length
Code:
$qry = "SELECT ID WHERE (SELECT CHAR_LENGHT('field_1') FROM tab = 8)";
View Complete Forum Thread with Replies
Related Forum Messages:
Retrieve List Of Users
I'd like to know a way to know which users had a birthday in last 7 days... Example, I've this: Name - Born John - 1963-12-24 Lisa - 1979-11-23 Peter - 1981-08-01 Then, script should return "Peter, 1981-08-01" The php script will be execd through cron once a week, and no matter which day it is executed, it must give the same result.
View Replies !
Save A List Of Unknown Length
I'm trying to create a db that I can store recipes in.I would like to have a table for the recipes and a seperate table for the ingredients,but each recipe will have an unknown number of ingredients.i don't really want to create max-ingredients +1 fields and I would prefer not to save a string of PKs that i have to parse appart.This has got to be a common task, how is it done?
View Replies !
N-length String Fields
I've managed to make a few member-driven websites with fleeting knowledge of the syntax. Now I'm looking forward, and how to do it better. Mostly, I want to know how to have a field that stores a string of n characters, normal C NULL terminated string. So far I've been doing VARCHAR(45), which to my understanding is 45 characters long. Does a field of type VARCHAR allow for what I want (up to a logical limit)?
View Replies !
Eliminating Values From A List / Finding Non-existing Values
I have a list of thousands of values, some of which exist in the database, and some of which do not. I'd rather not loop through the list, SELECTing each item to test its existence, and was wondering if there's a more elegant way to do this using an SQL statement. For example: Database: 1 3 5 6 List: 1 2 3 4 5 I'd like MySQL to return to me the following, a list of the non-extant items I passed: Result: 2 4
View Replies !
Retrieve Most Recent Records From Database Sorted By String Value
I'm having a problem with a simple select query. I've got users uploading data into a database. I want to do something that seems as if it should be really simple, but it isn't so far. Basically, I want to retrieve the latest 12 records from the table, sorted by the date-time field (uploaddate) then by a varchar field (name). Here's the SQL that I'm using: select * from tblcontent where filetypeid=1 and release='Y' order by uploaddate desc, name limit 12; And here's the table schema: Field,Type,Null,Key,Default,Extra ContentID,int(3),,PRI,NULL,auto_increment Name,varchar(50),,,, Filename,varchar(100),,,, Thumbnail,varchar(100),,,, FileTypeID,int(11),,,0, UploadDate,datetime,,,0000-00-00 00:00:00, Description,text,,,, ContributorID,int(10) unsigned,,,0, Release,enum('Y','N'),YES,,N, The problem I'm running into is that while I am retrieving the most recent 12 records, it's not sorting by the varchar field. Instead, the 'Name' field seems randomly sorted. If I change the sort order (name, uploaddate desc), I don't get the records that I want. This seems like it should be something very simple to do, but it's driving me nuts and I really have no idea how to resolve the problem. Code:
View Replies !
C API Question: Using Mysql_bind_result To Fetch String Data Of Variable Length
In the C API, is there any way to execute a query, store all the results, and somehow find the maximum realized width for each of the fields before fetching each row? Otherwise there is no way to efficiently retrieve string data. The best you can do is either allocate a static buffer of 64K (max size for a blob) or some smaller buffer that may not actually be able to contain all the data. In fact, all the mysql documentation examples, both on in the manual and in the test/client_test.c file that comes with the source distribution, seem to choose the latter approach, arbitrarily fixing the max size of their strings to, say, 255 bytes, binding using that as the buffer_length, and then fetch()ing. In my application I need to ensure that I get all the data, so this is unacceptable. I have tried using mysql_get_metadata() after mysql_stmt_store_result(), but even then the max_length value for the fields in question is always set to 0, not the maximum size of the field for the returned results. What I would love to do is somehow get the field width right before fetching each row so that I can allocate the right-sized buffer for it. Or, less ideally, tell mysql to allocate memory for me. Does anyone know of a way to do this? I am using MySQL version 4.1.1-alpha-standard on RedHat Linux 9, installed from the RPMs (server, client, devel) on the mysql.com website.
View Replies !
List Available Items Based On Prerequisite Table
I'm trying to develop such a scheme: TABLE Prerequisites PrerequsiteID (FK to Upgrades table; states a prerequisite for UpgradeID in the other column) UpgradeID (FK to Upgrades table as well) TABLE BuildingUpgrades BuildingID (FK to Buildings table) UpgradeID (FK to Upgrades table; states what Upgrade Building already has) So it's a Building -> Building_Has_Upgrades -> Upgrades -> Upgrades_Have_Prerequisites thing (Building has 1..n Upgrades, Upgrade has 1..n prerequisites). In order for Building B to have an Upgrade U, that Building B has to have all the Upgrades which are required to have Upgrade U. What I want to do is to list all buildings with all upgrades available for them. I've been trying for almost whole day to solve this issue with different inner, left and right joins, subqueries and what not but I can't really do it in a clean way. The only working thing is a query, which I am ashamed of because it seems like a real waste of CPU time to me: SELECT BUPG.BuildingID, PRE.UpgradeID, COUNT(PRE.PrerequisiteID) FROM BuildingUpgrades BUPG JOIN Prerequisites PRE ON BUPG.UpgradeID = PRE.PrerequisiteID GROUP BY BUPG.BuildingID, BUPG.UpgradeID HAVING COUNT(PRE.PrerequisiteID) = ( SELECT COUNT(PrerequisiteID) FROM Prerequisites WHERE UpgradeID = PRE.UpgradeID GROUP BY UpgradeID ) What it basically does is takes all the available Upgrades, counts prerequisites that have been met by the building and checks if it's equal to the number of all prerequisites for that Upgrade. And it works but I still think there is a much better way to do it.
View Replies !
Retrieve Lowest SUM Values
why this statement produces an SQL error around the MIN function? Code: SELECT hotels.id, sr.id, MIN(SELECT SUM(srr.price) FROM searchresultrooms srr WHERE srr.searchresult=sr.id)) AS theMin FROM hotels JOIN searchresults sr ON sr.hotel=hotels.id WHERE sr.search=169321 ORDER BY theMin.
View Replies !
Getting Records Based On Chars In A String
I have a table with 2 fields. Field 1 contains Names. Field 2 contains a single character A,B,C,D or E. I have a string "BCE" for which I want all the names that contain one of those characters (B,C, or E). For Example: Field1 ====== Jo Ron Mary Sue Mark Frank Field 2 ======= E D A C D B Desired Result after Query: =========================== Frank Sue Jo SELECT * from TestTable Where Field2 = SomeFunction("BCE") How can I do this using a SELECT statement?
View Replies !
Retrieve Values From Db And Assign Vars To Them
The following query is my umpteenth attempt. I am trying to bring four values out of my Db and assign a variable name to each. unless ($Customer_ID || $User_Name || $PassWord) {} else { $dbh->do("SELECT FROM $table (Customer_ID, User_Name, PassWord, Client_Full_Details) values ('$StoredCustomer_ID', '$StoredUserName', '$StoredPassWord', '$StoredClient_Full_Details')");
View Replies !
Deleting Entries Based On Date String
I'm trying to figure out how I can delete entries from a table based on a column that contains the date in string format i.e. 9/22/2008 month/day/year I want to delete all entries that are older then 60 days from today using the above column. I thought it might be something like this: delete from sequencer.parts where (SELECT DATE_SUB(curdate(), INTERVAL 60 DAY)>(SELECT STR_TO_DATE(packdate, '%m/%d/%Y') from sequencer.parts))
View Replies !
Query For Month Data Based On Occurance Against Master List
I have a query running nicely. Now I'm trying to expand it. It pulls the data from a table based on matching the id with another table and part of the grouping is by month and 2 other criteria. Now I'm trying to get the data pulled by month to pull only the info where the id matches the master file AND the FRANID's pulled are the same for each month. I'm trying to get a comparisson across times, but not every month has all FRANID's, depending on when they were entered and I ony want data that exists with matches over set months, starting for now with 12, perhaps also for 6 and 3 next time. I've simplified what I'm doing above, but it is detailed below.....
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 !
LIMIT Based On Unuiqe Values
i have a query where i search the table on the table using an alias (a in this case) and then find all rows for that user using the alias b, but now i need to limit the results to say 5 unique rows from table a but allowing any amount from table b, also table a may return multiple uids for one user Code: SELECT b.uid, b.field, b.value FROM user_s as a, user_s as b WHERE b.uid=a.uid <where clause for table a here> GROUP BY b.field, b.uid ORDER BY uid
View Replies !
Selecting Values Based On Column
I couldn'y find solution to this on forum so here it is : sample table : id | name | value 1 | x | y 1 | z | v 2 | x | y i would like to select id where x=y and z=v. so in this case result should be : 1 (2 is not in the result coz it doesnt match x=y). How to create such a query?
View Replies !
Selecting Values For This Record Based On Other Records
Is it possible in a select to reference the contents of a column in a different record to the one currently being assessed? Kind of like :- Select id,prev_id,title,flag FROM my_table WHERE flag != "S" AND flag(prev_id)="S"; What I'd like it to do is select records which have flag set "S", but the record referred to in prev_id do not have flag set "S". If it matter, I'm stuck on 4.0.
View Replies !
How To Insert New Records Based On Previous Record Values?
I have TABLE: year value It has for example, year, code, vlaue 2007, 33, 4883 2006, 33, 4883 2007, 32, 4883 2006, 32, 4883 2008, 31, 4883 2007, 31, 4883 2006, 31, 4883 I want to insert new records for for 2008 for all codes that do not already have a record for 2008 and increase the value for the 2008 record by 3%. My several attempts have led to this but which does not work. UPDATE TABLE a SET a.value = b.value * 1.05 WHERE b.year = 2007 AND code NOT IN (SELECT code FROM TABLE b WHERE year =2008 AND value !=0)
View Replies !
Get Min & Max Values Of A String
I have a field 'zip' in a table in my database that records values in the form on 'something/023/something', 'anything/346/somemore','kztt/1984/asdg' I want to get the min and max values of this field in particular the numbers in the string. Initially, I simply run a query to get the min and max value of this field which works until the numbers in the string went to 4-digits instead of the usual 3. Example query: select min(zip) as min, max(zip) as max from $dbtable Then I run an explode function to retrieve the numbers in the string. This query now return values like min=999, max=1000 instead, when the min number should be 023 and max is 1984
View Replies !
How To List Duplicate Values In A Column?
I have a large table which has duplicate values in one of the columns. I can easily see how many duplicates there are by using COUNT and DISTINCT, but I can't see what the duplicates actually are. I guess there's a simple way to list duplicates, but I can't get my head round it.
View Replies !
Extraction Of A Substring In A List With ; Separated Values.
In my database, a have a table with a field which you can put many values with a ; between each. The software is done like this. Exemple: Field: Version Data: 3.0.1;1;2;4;5.1.2 etc. I want to extract all entries in the database with version = 1. The problem is that the result give me entries with version 3.0.1 , entries with version 5.1.2 and entries with version 1. As you probably guess, I just want entries with version = 1. I tried with WHERE (version IN ('1;')) but it returns nothing except entries which version is only 1; I need something to find a substring = 1; with nothing in front of the 1 or substring = ;1;....... something like that... or better way.
View Replies !
Insert A String Of Values
as you will see iam new to php/mysql and desparately require some help. My problem is as follows - i need to enter a string of values from a list or selection of checkboxes into ONE field. Iam using dreamweaver and although it has the option for multiple entries it only inputs the first selected in a list. all i need it to do is be able to input for example beach facilities - toilets, slipway, lifeguard into one field from a list of facilities. i don't need to separate them at a later date as i will display them just as above. Your help will be greatly appreciated as it is a reg charity based website for marine wildlife that iam trying to construct.
View Replies !
Group Records? Only List Users With Only NULL Values
I have this result-table (running on 4.0.24), how do I collect all o_user_id which does not have NOT NULL value in reciept_id? (I do not want the 745-user in result, as it contains a value for reciept_id) +-----------+------------+-----------+-----------+-----------+---------+ | o_user_id | reciept_id | r_user_id | d_dist_id | r_dist_id | item_id | +-----------+------------+-----------+-----------+-----------+---------+ | 745 | 18 | 745 | 1 | 1 | 1 | | 745 | NULL | NULL | 2 | NULL | 1 | +-----------+------------+-----------+-----------+-----------+---------+
View Replies !
Select With Multiple Primary Keys Against List Of Values
I have created a method that automatically parses the metadata of a specified table and searches each field to effectively create a database search. The way it currently stands, it only supports tables with a single PK. Basically it returns a string that can be appended to the WHERE clause in a SELECT. i.e. "ID IN (1, 2, 3, 4)" Basically the PK field name and a list of matching PKs from the search. I realized though that this will break if I have a table with multiple primary keys. An elegant solution for this eludes me, and that is what I am asking for input on. Right now I can only think of returning something like : "(ID = 1 AND SSN = '123121234') OR (ID = 2 AND SSN = '432434321') OR ..." This obviously would become a monster if many results were returned. And if I did something like : "ID IN (1, 2) AND SSN IN ('123121234', '432434321')" This will either return false positives or nothing at all ... Is there a better way for me to create this conditional?
View Replies !
Comparing String Values In Two Different Columns
I am trying to do a string comparison between two columns located in two different tables. I tried the following SQL statement and it didn't work. This string comparison is for updating foreign keys in the referencing table. sn-table (referencing) e-table(referenced) update sn left join e using(id) set sn.Eid=e.id where sn.name like e.name; Although the command runs fine, it doe not produce the desired outcome! I've seen SQL statements where comparison with a literal string works: ... ... where sn.name like 'TX'; I want to compare variables not literals. I am trying to find a partial match between name on sn and name on e and then update FK in sn using corresponding id from table e.
View Replies !
Parent_id, Concatenation String Values
I'm using alot tables in this form: Code: tbl {id,parent_id,title} tbl INSERT (1,0,'Program Your Site') ,(2,1,'PHP') ,(5,2,'PHP Application Design') ,(3,1,'Databases') ,(6,3,'MySQL') ,(8,6,'MySQL 5') ,(9,6,'MySQL 6') ,(7,3,'Oracle') ,(4,1,'Ruby') simple right? easy implement retrieve function in PHP with recursion. the question is if it possible to implement some SQL query that for given ID will return full path to the ID? SQL Code: SELECT ... AS full_path FROM tbl WHERE id=9 The output: Code: Program Your Site :: Databases :: MySQL :: MySQL 6 isn't there some generic solution for this problem in SQL?
View Replies !
Selecting Multiple Columnn Values Into 1 Distinct/unique List
I have a table with several fields for email (primary email, alternative email, contact email) Some times, some of these fields will be blank and sometimes the same address will be enter for both primary and contact. I use this table and these fields to do a mailout but I don't want to mail people twice because there address occurs in more than 1 column. I need to end up with a list containing all email values from those 3 columns that is unique (no duplicates). I can do this by checking each one and putting it into a php array but thought there may be a nicer way to do it in the SQL?
View Replies !
Efficient String Compare To A Group Of Values
(Post edited to change "WHERE field = options" to "WHERE field IN options"). I have a website using PHP and MySQL 5.0; the database holds personal names, addresses and various information. Right now I'm building a query which checks whether a person lives in a certain group of UK counties. I've got arrays set up in PHP which hold the groups of counties, and I've dynamically created a "group" from each array. So, in PHP, $county_array = array('Kent','Surrey','London','Essex','...'); $county_group gets set to "('kent','surrey','london','essex',...)" Each group may hold perhaps 20 individual counties. Now, for my database query, I use: $query = "SELECT ... WHERE lower(county) IN $county_group"; Is the "WHERE field IN (a|b|c|d|e|...)" a slow comparison in MySQL? Is there a more efficient way to do it? Is this something that stored procedures can help me with?
View Replies !
Append String Of Column Values In SELECT Statement
I'm looking for syntax to append string of column values in SELECT statement. For example, following is a SQL statement to select the property_address1, property_address2 and property_address3. The values should be returned in accordance to their respective columns. SELECT property.property_address1, property.property_address2, property.property_address3 FROM orems_property property; is there any syntax that will enable us to append property_address1, property_address2 and property_address3 is a single column? I've no idea of MySQL, but Oracle syntax would look like the following: SELECT property.property_address1 || property.property_address2 || property.property_address3 FROM orems_property property;
View Replies !
Using A Field, Which Is A Comma Separated List, As The List In An IN Comparison Test.
The following query only reads the first character of a comma separated list stored in a field of the records being queried. The varchar field in table d named "display" holds 1,4 The query " select * from d where 4 IN (display) " tests false. If I change the field "display" to 4,1 Then the query tests true. It is either testing only the first character, or is stopping at the comma. I need this to test true if the value is anywhere in the list, what am I doing wrong here?
View Replies !
Full List Of MyTable21 And Some List Of MyTable22
data in myTable21 (id) country (1) Canada (2) France (3) Egypt (4) America data in myTable22 (id) city name (1) Montreol Jane (2) Paris Mary (3) Cairo Tom (4) New York Jane (4) Chicago Tom I have data in myTables like the above. The following code produces the following result. but I like to produce my target result. code1 select myTable21.id, name, country, city from myTable21 left join myTable22 on myTable21.id=myTable22.id where name='Tom' result1 (3) Egypt Cairo (4) America Chicago target result1 (1) Canada (2) France (3) Egypt Cairo (4) America Chicago code2 select myTable21.id, name, country, city from myTable21 left join myTable22 on myTable21.id=myTable22.id where name='Jane' result2 (1) Canada Montreol (4) America New York target result2 (1) Canada Montreol (2) France (3) Egypt (4) America Chicago code3 select myTable21.id, name, country, city from myTable21 left join myTable22 on myTable21.id=myTable22.id where name='Mary' result3 (2) France Paris target result2 (1) Canada (2) France Paris (3) Egypt (4) America How can I get the full list of myTable21 and selected values from myTable22?
View Replies !
Creating List Of People Not On List
I am using the following query to try to get a list of people not in a specific list. I can further restrict it by company name but I am getting duplicate entries if the person is listed on multiple lists. (It is further reduced by a clause to restrict by company, but that is not causing errors AFAIK) SELECT people.`First Name`, people.`Last Name`, people.PersonID, Company FROM people LEFT JOIN listtrack ON people.PersonID=listtrack.Person_ID WHERE (listtrack.List_ID<>4 Or listtrack.List_ID Is Null) If I remove the List_ID<>4 clause then I get people who are not on any list., but I want to be able to add one person to multiple lists, without running the risk of data duplication.
View Replies !
Using UPDATE To Insert A String Into String
Is there a way/function that will insert a string into another string using UPDATE? for example: I have a 'text (string) column' where I want to add '/' after every 4 characters. hex1hex2hex3 into hex1/hex2/hex3/ I was looking at the substring command, but it can only be used with SELECT?
View Replies !
Int Length?
So I'm working on a new table for a game database which keeps track of the player's inventory. As you can imagine there are a lot of items in the game, and thus a lot of primary keys are needed. Originally I had the primary key as an int(11). I thought increasing it to int(20) would give me more primary keys to work with, but I didn't see a different. I'm now using bigint, but I'm still stumped about the significance with a field's length. So... what's the deal?
View Replies !
How Do You Replace A String With Another String
How do I write a query in myPHP admin to search through a particular field in a particular table for a string and replace it with another string? The string is only a part of the value of that field. For example, how would I change "Hello there" to "Hi there"?
View Replies !
Max Key Length Error
I'm trying to set up a database table, and I keep getting an error saying: Max key length is 500 I have looked everywhere I can think of but I can't find an explanation for what is causing the problem so I can fix it,
View Replies !
Character Set & Length
The default encoding of MySQL is latin1. So, textual data should be interpreted as 8-bit character. Is that right? If the default character set to , say big5, in server startup, will MySQL interpretes all textual data as 16-bit character? e.g. in latin1 encoding the word "hello" is stored as : hello (5 characters + null terminator) how will it be interpreted if the default encoding is changed to big5? Will each 8-bit character be extended to 16-bit representation? Or will 2 8-bit characters combine to form 1 16-bit character?
View Replies !
COntrolling The Length
I'm currently trying to do a query on a mysql database which is going to be put into a drop down box on my website. However, some of the fields in question are very long (200+ characters) and this means that the drop boxes stretch right across the screen when displayed. I was wondering if there is anyway in mysql to truncate the results I get. I've looked on google and everything I find refers to the LIMIT keyword, which is not what I am looking for.
View Replies !
Average Length
Basiically I have a table called ‘Members’ with the following attributes, (the m is short for member), Members (mno , mname, date_joined, date_left, maddress, mtel) I need to produce a single SQL statement that will output the longest length of current membership, the shortest length of current membership and the average length of current membership.And the out put I get should be easily understood, not just a number if you what i mean.
View Replies !
|
TRACKER
