Virtual Fields
Is there a way to do a mysql select with - for lack of a better word - a 'virtual field'. In other words, I'd like to select on a field that is based on the contents of other fields. Like:
If field A is greater than field B, select field A, otherwise select field B
Specifically, I'm working with date fields. I have a table of events where there is a start date and an end date. Most events are one day only, so they have only the start date. I would like to select and sort on the latest date field - either the end date or, if there isn't one, the start date.
View Complete Forum Thread with Replies
Related Forum Messages:
Virtual Domain
I have a dedicated server with 5 virtual domains under iis. i just installed mysql. how do i create a separate instance of mysql for each of my virtual domain so that one domain affects only tables created by that domain and not step on tables of other domain? let's say that each virtual domain is a separate web site with different fqdn
View Replies !
Db On A Virtual Web Server
i am running my web server with dsvr.co.uk. a uk based web server hosting company. I am trying to use a piece of software called smoothflash at the moment to show pics. dsvr have informed me that I have mysql on my server but i cannot for the life of me work out how to connect to a db, een to create a new db. www.smoothflash.com has the install manual on it and it all seems to straight forward. I know I have enabled mysql on my server, this has been varified by dsvr. but then I have no idea how I am meant to create a db, connect to it, i have mysql admin for windows but without knowing how to create a db, get a user name and password for it i have no idea how to get any further, if any of you clever people out there could help me in this matter i'd forever be in your debt.
View Replies !
Virtual Column
I want to get users informations from multiple tables and each user has many dependants (in tbl_dependants) and the dependants table has user_id foriegn key. Is it possible to SELECT user informations and beside the user results I create a virtual column contains dependants names for each user?¿?
View Replies !
Virtual Folder
in my database i have a table which stores a directory structure folderstructure table id, foldername, parentid parentid points to its parents "id" (:p ) i need to retrieve this info so that its sorted by foldername and its children are listed below also sorted by foldername; there can be a infinite amount of nested folders.... im not really sure how to go about this; i was hoping that maybe theres some magical sql statement that can do this for me i sort of dont want to put this info in a array then sort it with recursion (which i tried and failed), nor do i want loads of sql queries
View Replies !
Mysql To Virtual Domain
I have a dedicated server with 5 virtual domains under iis. i just installed mysql. how do i create a separate instance of mysql for each of my virtual domain so that one domain affects only tables created by that domain and not step on tables of other domain? let's say that each virtual domain is a separate web site with different fqdn
View Replies !
Using Virtual Column In WHERE Clause
SELECT IF( some_id > 3, 'foo', 'bar' ) AS 'virtualcolumn' FROM table WHERE virtualcolumn = 'foo' MySQL will complain that 'virtualcolumn' is unknow in WHERE clause. However, if I have another query like SELECT IF( some_id > 3, 'foo', 'bar' ) AS 'virtualcolumn' FROM table ORDER BY virtualcolumn ASC .
View Replies !
Not Working With Virtual Host
I set up a virtual host with Apache and my previously-working MySQL configuration now will not work! I did this a long time ago and can't remember how to fix the problem...Do i need to change some configuration so that MySQL runs on the virtual host and not localhost?
View Replies !
MySQL Virtual Hosting
When you create a new user, let's call him "userJohn", is it possible to set a different data foler for that user? I was thinking it would be great if you could keep user databases seperate from each other, kind of like every user has thier own folder on a virtual database folder, and access can be restricted for that user to the databases in their folder. So if user1 creates a database named "Database1", and later user2 creates a database with the same exact name, it will not give an error, and when user1 alters his "Database1," it will not effect any other users "Database1". Similair to webhosting, where every user has their own folder they keep thier data in.
View Replies !
Chew So Much Virtual Memory On Windows?
Have a fairly busy MySQL server using MyISAM and InnoDB tables on Win 2003 with plenty of available RAM (total 4GB, with about 1.5GB unused) which is using a massive amount of virtual memory despite having tweaked MySQL to use as much memory as it needs. RAM usage never gets much above 400MB, however Windows is reporting that MySQL is using a massive 1.5GB of virtual memory. Most other processes use a similar amount of RAM and virtual memory, so why would MySQL be using 4~5 times more virtual memory than physical memory? Especially when there is so much free physical memory?
View Replies !
Fields In One Table Overwrite Fields In Another Via JOIN
I'm writing a simple web game which uses mysql to store data. The prototype works ok so far, but now I'm refactoring it to support multiple players. This means coming up with a way to store the player's current world state. I'm thinking of doing this by having a table that defines the initial world state, and then a table containing the 'state' of anything different. Example: The 'objects' table looks like this: object_id, name, room 1, Hammer, 1 2, Mirror, 1 3, Spade, 2 So, at the start, room 1 contains a hammer and a mirror, and room 2 contains a spade. Now, if player 12 picks up the hammer then drops it in room 2, I store this override in the 'state' table player_id, object_id, room 12, 1, 2 So. I'm trying to see if there's any kind of join syntax that would return all the objects in a room for a given player's 'session'. Obviously it could be done with temporary tables, or just comparison outside of SQL, but I keep thinking this might be possible *somehow*
View Replies !
One Table With Many Fields Or Many Tables With Few Fields?
I need to build a database, but I'm torn between these 2 choices: Is it better to have one table which has many fields or many tables which each has few fields? Is it true that the latter is worse because it will require many join operations? What is the limitation of the first option (one table with many fields)?
View Replies !
Key Fields?
I have a few MySQL tables that do not have key fields associated with them. I am linking to this MySQL database from Microsoft Access 2000 using the DoCmd.TransferDatabase acLink method. When the link is created, I get prompted with a "Select Unique Record Identifier". Here are my questions: 1. Is there a way to make this message not appear (or programatically repsond to it) other than by adding a key field to the table? 2. If I select no Unique Record Identifier, the table becomes Read Only. Is there a way to make this table link updatable without assigning a key field to it?
View Replies !
100 Fields
I want to store many attributes in a db. Like this: User_1 field_1, field_2, field_3, etc, etc, etc------------>field_100 User_2 User_3 User_4 User_5 etc etc Is this possible? Is this bad db design?
View Replies !
Cap Fields
Is it possible to run a query and CAP all fields in a column. Can mysql do this kind of stuff?
View Replies !
2 Fields
I have a search procedure for a db of users: the form has one input box that lets them enter first name, last name, both, etc. I split the keywords into an array (searchterm1, searchterm2, etc) (using php) I'm wonderig if I can search on SearchName (rather than LName, FName, etc) in the following query: $query="SELECT CONCAT(LName,', ',FName,' ',MName) as SearchName WHERE $and I want to concat fields FIRST so that I can use a loop to query if SearchName is like searchterm1 AND searchterm2, rather than if LName is like searchterm1 or searchterm2 OR FName is like searchterm1 or searchterm2, which gets me too many results. I don't want to use full-text search, because these are simply names, and therefore does not seem appropriate for full-text. I am using MySQL 3.23.58
View Replies !
Sum Of 6 Fields?
I need to build a table of 5 countries each countries have 6 users and each user has 6 grades and an extra field to have the sum of the 6 grades, I use PHP to display my results but when I come to display the result of each user I simply sum all the variables and then I display my total field, my question is there a way to solve this without adding my variables? A way that can be done though my tables? I am asking this bocz at some part later I will have to display the top 10 students and I will the total of every user, one option I thought of is after adding the 6 variables is to insert into total the total grade, but I want to know if there are any better ideas, thanks in advance, this is part of my code which I use to fetch my data and display them: while($row = mysql_fetch_array($query_list)) { $user_name = $row['user_name']; $country_name = $row['country_name']; $score_1 = $row['score_1']; $score_2 = $row['score_2']; $score_3 = $row['score_3']; $score_4 = $row['score_4']; $score_5 = $row['score_5']; $score_6 = $row['score_6']; $total = $score_1 + $score_2 + $score_3 + $score_4 + $score_5 + $score_6; }
View Replies !
Selecting Fields
Suppose I have two tables A and B Table A has fields A1, A2, A3, ... A9 Table B has fields B1, B2, B3, ... B9 I want to select all fields in A and only B3 in table B. I dont want to list individual fields for A because they are too many I simply want to use a *. How do I do this? I tried this way but they are not working Select * from A1, B3 from B Select *, B3 from A, B
View Replies !
Foreign Fields
I have created a database with about 17 tables. I have been creating foreign keys some of which have worked but when creating others I get the message below ************************* 1005 (ER_CANT_CREATE_TABLE) Cannot create table. If the error message refers to errno 150, table creation failed because a foreign key constraint was not correctly formed. *************************** I have checked to see If there are any obvious differences between the tables that allowed the foreign keys to create and those that wouldnt. But I could not find any differences
View Replies !
INDEX'd Fields
I have a database, about 12 fields per record and roughly 100,000 records per month. I would do the following query: select count(*), type from TABLENAME where field0='23-23-23' group by type; the query would run an average of about 2secs. Not bad until I am running the same query about 1400 times all with slightly different field0 values. I added an index for field0...with: alter table TABLENAME add index (field0); Now those same queries run about an average of 1/10th of a sec!! My question is, is there a negative effect by indexing this field? Obviously you woudn't index all fields...but could you?
View Replies !
Combine 2 Fields
I've got the firstname and lastname in my member-table and I want to compare the whole name with the entered search string, like this: select id from members where (fname + " " + sname) = 'Matt Dillon' How do I do that? When I try the above I got the whole member list in result.
View Replies !
Combine Fields
i need to actually move one field from one table to another.... not combine it later on in php or anything like that, but actually move the field over... the database is called dg_reviews, and the structure is.. games > system > reviewer games2 > year > genre i want to put the tables from games into games 2
View Replies !
Comment Fields
If I have a comments field on a web form how would I structure the database field table?? I've been using a VARCHAR. Should I use a BLOB option. What's the difference. Where can I go to read up on these options better.
View Replies !
Concatinating Fields
i have a table with 2 fields as follows c1 c2 -- ---- 10 2,3 13 Null 14 5 16 6,7,8 I want to concatinate c1 & c2 is c2 is not null c1 is integer and c2 is string.. The result should be 10,2,3 13 14,5 16,6,7,8
View Replies !
Counting Fields
I need to retrieve the number of occurences of each distinct field in a specific column. This is what I am doing right now: Code: SELECT DISTINCT referrer FROM counter and for each record that that query returns: Code: SELECT COUNT(*) AS count FROM counter WHERE referrer = '".mysql_real_escape_string( $row[ 'referrer' ] )."' (using PHP) is there a way to make a single SQL query that will count the occurences of each distinct field of a specified column?
View Replies !
FIELDS TERMINATED BY
I am running MySQL 4.1.13 and have the following SQL query in a file: SELECT `title`, `url`, `description` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY ' ' FROM `nuke_links_links`; When I run it using "mysql < script.sql", it works fine on my machine. When I move it to my hoster who has MySQL 4.0.25, I get: ERROR 1064 (00000) at line 1: 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 'FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' I can also run in on my local machine and reading off the remote database and I get the same error.
View Replies !
Filling In Fields
I have a MySQL5 database that has 35,000 names in it. I just added a column that is a yes/no field. I have added 'yes' to about 300 rows. Is there an easy way to add 'no' to all the rest?
View Replies !
Fields Updated
Is there a way to extract what fields were actually updated in an update query? For example, let's say I have a table with a firstname, a lastname, and a company field, with values of "John," "Smith," and "IBM," respectively. I run this query: Update table set firstname = "John", lastname = "Smith", company = "Open Source Development" where ID = 1 Is there a mySQL function/query that will tell me that the only field actually updated in this query was the "Company" field, and the other two remained unchanged?
View Replies !
How To Call All Fields?
domains: id, domain_name bids: id, domain_id, buyer_id, seller_id, bid_status users: id, name So basically in table bids domain_id is equal domain_id in domains and buyer_id = userid in users and seller_id = userid in users So now, how do I do my sql statement do display domain_name, name and name? This is what I already ahve done: Code: SELECT domains.*,bids.* FROM domains LEFT JOIN bids ON bids.domain_id = domains.id WHERE bids.bid_status !='incomplete' GROUP BY bids.id ORDER BY domains.domain_name So this actually work okay for table bids and domains, but I want to display name from users table too.
View Replies !
Copy First Two Fields
i have a table with 200 rows called solutions the first two fields are ID and user i want to create a new table with the all the information i have on this two fields and their rows.how do i do that if im using phpmyadmin?
View Replies !
Insert 4 Fields
i have two tables: solutions and passwds table passwds right now only has two fields: username and id how can i insert the last 4 fields from table solutions right after the id field, on table passwds?
View Replies !
Integer Fields
I have a column, smallint(4) UNSIGNED ZEROFILL, and one of my values look like this: 0100 It shows 0100 in phpmyadmin, but when I select it in my app, it shows 100 instead of 0100. How do I retrieve the entire value without getting the 0's truncated infront of the numeric string?
View Replies !
Separating Fields
I can't work out how to use load data infile so that the fields are split up and put into separate fields. I use a comma to delimit.
View Replies !
Max 3 Fields In A Table?
I am new to using mysql and php and am running into an issue which absolutely baffles me. I am trying to create a table in a database and simply print the values back. Everything works fine if I do it as below: ....
View Replies !
Merge Fields
I have 2 fields in a table that I want to merge together to form one field and make this a primary key. I did a priliminary search and found nothing relevant n this topic.
View Replies !
Order By 2 Fields
has anyone has examples or ways that able to use mysql statements to order by two fields say example i've people table date_entry and ranking, i would like to order by ranking and date_entry? can i?
View Replies !
Ordering Certain Fields Last
I have a search query: select * from mgi WHERE activate='1' && mgi_name Like '%".$search."%' ORDER BY mgi_name ASC This list all the business names for a search. I have another column in the table called "placement" placement has either a Yes or No value. How can I change this query so that all the rows with placement "No" is listed last
View Replies !
Sum Two Fields Separately
I want to design a database that includes 2 fields that collect a dollar amount. Is it possible to have these two fields total themselves? I don't want both fields to total together, I want field 1 to total and field 2 to total seperate of each other.
View Replies !
Primary Fields
Is it possible to have two fields be primary keys that work in an AND type fashion? For instance: Field #1| Field #2 DataA | DataB (Allowed) DataC | DataD (Allowed) DataA | DataC (Allowed because Field #2 does not contain the same Field #2 information as row #1) DataA | DataB (NOT allowed because Field #1 & Field #2 are indentical for both row #1 and row #3.) - Rows 1 & 2 are the first two rows of unique data. - Row #3 has "DataA" in Field #1 (same as row #1), but since Field #2 contains something different than "DataB", the entry is allowed. - Row #4 has the same data in both Fields #1 & #2 as row #1, therefore the entry is NOT allowed. Is it possible to make a MySQL table behave in this fashion?
View Replies !
Remove '' From Fields
I imported data from a CSV file and all the fields end with a symbol thats equivalent to ' ' so how do i remove it from my fields.Because of these i cant even use queries select * from country where Name = 'Belgium' doesnt work but when i do this select * from country where Name = 'Belgium ' works fine.
View Replies !
Removing Fields
Is there anyway to delete all the rows that have a certain field with less than say 5 characters from a table? I have a table with entries that are like "efg" or something that don't make sense. I need to get rid of all of them.
View Replies !
Searching Several Fields
I'm after a solution to a problem which I can solve by doing lots of queries, but was wondering if there was a more elegant mySQL way of doing it.I'm writing a calendar app which will display dates of various courses. Each course has several dates associated with it stored in several fields of a database.I want to be able to search all the date fields (stored in datetime format) and return rows which contain dates within a certain range (i.e. for a specific date).However, what I want to be able to do upon getting the row result (or several rows for several matches) is for mySQL to be able to tell me which field the match came from.
View Replies !
|
TRACKER
