Turning A Column Into A Row
Is there a was to take tableA with this data:
Letter ID
A 1
B 2
C 3
And insert it into TableB like this:
ABC
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Turning Mysql Off On Mac?
My friend is running OSX and has mySQL running, she doesn't know why its running or how it got there, how do you turn it off? What are the steps to go through?
Turning On Logging
I have a database that has various information submitted to it from the internet. I am having problems with people hacking/submitting info that shouldn't be submitted. I don't know how they are doing it but I though that if I turned on the Logging for that particular DB maybe it would give me some clues i.e. IP addresses, ISP, date/time info, SQL Statement executed etc.What form of logging would I need to turn on?
Turning Off Foreign Key Checks
Is it possible to temporarily turn off foreign key checks in a db? I know how to do it from the mysql prompt but I have a number of scripts using Java, etc which seem to be breaking because of the table ordering with respect to foreign keys does not seem to be honored (tables with FKs are declared before the tables the keys refer to). If there was some way to turn off checking for a db, run all the creation and import scripts I have, then turn the checking back on it would save a ton of effort.
Apostrophes Turning Into Question Marks?
This may be easily resolved but I haven't ever encountered it before. I am entering text into my mysql database that contains apostrophes. This field is generally around 500-800 characters. When I add the information, there are apostrophes but when I go back to use the information, all of the apostrophes have been changed into question marks. Any ideas how to keep the apostrophes? or why they are being changed? Also, if I go through and change all the question marks back to apostrophes, they will stay apostrophes!
Turning On Slow Query Logging?
Background: I paid a young admin set me up on a database server. He installed the basic I needed for the server...at my request...No Cpanel...mysql and apache and some tight security w/o even a domain name to SSH into. Unfortunately, he's a busy kid, and teens sometimes don't realize that people depend on them...and well, I can't really seem to get him to do much so I gave up and figured it's a good way to force me to learn all this myself... Well anyway, now I want to turn on Slow Query logging. But before I do that, I need to know how MySQL is running. Is SQLogging turned on already? Where is it logging to? So first thing I want to look up is, when the server is rebooted, what's the command to restart mysql? No clue. How do I change the setting? And of course, the server is production, so when I make the change, it needs to be quick, it needs to be smooth, and I need to be able to roll back to the previous config if necessary. I'm running Redhat Enterprise.
Turning Localhost To A Network Server
I have a small home network with two computers. On my computer I am running MySQL 4.1.21-community as localhost. I have only be using this for development work. My husband wants to learn SQL so I would like to be able to have Mysql Query Browser installed on his machine, with him accessing all the databases I have set up on my machine. How do I go about giving him access to my databases? My machine runs Windows XP and his runs Windows 2000.
Turning Off Allowing Multiple Non-unique Rows
I feel lazy and I am busy so i don't want to fix my perl code to only alllow insertion of unique rows. I know mysql is set to allow multiple rows of the same thing but I would like to turn that feature off. Is that possible?
Selecting Records, Then Summing Parts Of A Column, Then Sorting By That Column...
I’m keeping track of baseball stats, and each row represents one line of stats (from a box score) for one player of a single game. Because of this, a single player may have multiple rows in the table. I want to cumulate each player’s stats (so they’ll be one row per player) and display as output, which isn’t a problem. Then I want sort by a certain stat, but by now I’ve already looped through the table, so I can’t sort using a mySQL query at this point. I tried first putting values into an array in a previous project, but that became extremely complicated. What’s the best way to approach this?
Update Part Of Column Into Another Column
I'm looking for a way to update a SQL column with a portion of info from another column in the same table. example of a sql command -------------------------- UPDATE table1 SET table1.columnname1 = table1.columnname2 FROM table WHERE blah blah blah Here's the thing... I only need a portion of the data found in the source column. I'm not sure how I would do this then. for example, the database has countries and states combined into one column like this 'US-DC', 'US-CA', US-FL', etc. I want to separate these into two columns, a country column and a state column.... and I dont want to go though all the results and do this line by line. How would I write the SQL command so that it puts just the country in the country column, and puts just the state in the state column, and it omits the dash all together. any ideas?
Differentiate Between Column Alias And Other Column With Same Name
Sample SQL: SELECT a, (SELECT a + 3) AS b, (SELECT y FROM t2 WHERE b = b) FROM t1 1. SELECT a + 3 will calculate a column with alias b 2. t2 has a column named b Question: How can I differentiate between column alias b and t2.b. MySql treats alias b as t2.b in statement (SELECT y FROM t2 WHERE b = b)
Column = Column + 0
Is column = column + 0 allowed as part of a MySQL query? I am experiencing problems using it, but I can't be sure if it is that or something else in the code..
Add A Column
is there a way to add a column to an existing table?
Add New Column
I Want To Add New Colume In Exitsing Table Which Have 3000 Records New Columns Not Contain Null Values How To Add Then Not Null Column In Exitsing Table
My Own Column
This is my first time using MySQL and I need to know how I can add in my own boolean column to the select statement so that I can bind a gridview checkbox to it.
Max Of A Column Where ...
How can I select , for a given name, that record with the highest id for something like: id INTEGER(11) PRIMARY KEY NOT NULL AUTO_INCREMENT name VARCHAR(40) I want to select the highest id where name='something'. I've tried along the lines of: select MAX(id) as id where name='something' but with no success. I know this is oversimplified -Ive stripped it down here intentionally to try to determine how you select for a given row where a particular integer value is the max value.
Column Name
is there any way to select a column name rather than the values stored in it?
Row Into Column
i have this structure of tables: tehnic_detalis(id_detail, detail_name) products(id_product, id_detail, detail_value) I wish is to transpose row from tehnic_detalis into column for products in something like this short_description(id_product, detail_name1, detail_name2...)
Can You Add A Column?
Is it possible in a SQL query to have it select all entries for one coloumn and add them together? instead of looping?
Column Name
I created a database, then when I tried to create a table, one of my column names was supposed to be UNION (as in labor union). I kept getting a sytax error until I removed it. Then it worked fine. Anyone know why?
Column Name Containing . (dot)
I have a query that contains some column names with a dot (.) as part of the column name. How can I select these columns in another query? Example: The first column is 'Name', the second 'Name.Old' Now: SELECT * FROM (qryName) works fine. SELECT Name FROM (qryName) works fine. SELECT Name, Name.Old FROM (qryName) does NOT work.
Add A Column
I need to know how to add a column of numbers and get a total using my sql command. SELECT v.vid, title, purchdate, sum(cost) AS Total FROM Video v, Vidcopy vc WHERE vid.v = vid.vc I need to know how to add the cost column and get a total.
Add Column Name
I have been export data from MySQl by using the code below select date, time into outfile 'c:/testexport.txt'from test1 The text file will have the data but has no names of the column. Is there a way that I can add a column name of the data in the exported text file here?
Set Column Value
i have 2 colmuns that i want to use the difference between them as the value of another column. all columns are in the same table. the type of the 2 column is "Date". so what i want is to get the difference between the 2 dates and assign it to the 3rd column.
As Column
I have a query that selects... YEAR(date) as theyear But I get an error every time I try to use 'theyear' in my WHERE clause... It just says 'Unkown column "theyear"'
SUM(column)
I trying to add a column where the field is equal to 0' I try something like this but does not work SUM(IF(pitcherstats.win = 0)) AS loose,
Add A Column
i have two tables. i want to modify one table in such a way that i want to add a column in one table whose default value is obtained from the other table. how can i do that ??? example ------- alter table table2 add column column3 int default "????"; wheres as i want "????" to be obtained from other table
Re-name A Column
I am using Query Browser version 1.1.2, MySQL Administrator 1.0.14, MySQL Version 4.1.7-nt on Windows XP Service Pack 2 ALTER TABLE `purpleflavours`.`purchaseorderitem` CHANGE COLUMN `PurchaseCost` `PurchaseOrderItemCost` DOUBLE UNSIGNED ZEROFILL CHARACTER SET NULL COLLATE NULL NOT NULL DEFAULT 0000000000000000000000 Generates when I click the apply changes button in the MySQL Table editor window after changing the column name. This generates an error telling me I have an error in my SQL Syntax Can anyone tell me what is wrong? By the way I can use the table editor to change VarChar column names with no problem but I always get a syntax error if the column type is not a VarChar, i.e. Double, DateTime, MediumBlob, Integer etc.
Associated Column
Consider the following dataset: ID / Category 1 / 1 2 / 1 2 / 2 3 / 2 If I SELECT where Category = 1 or Category = 2, I will get two results. What would be the correct way to accomplish obtaining only the IDs which contain BOTH Category 1 and 2 (In this example, ID 2)?? -- Basically a boolean 'AND' search of some sort... saying 'SELECT * .. WHERE Category = 1 AND Category = 2' obviously doesn't work.
Column Value
I made this query which has this sum fuction which calculates the value of the contents of the individual rows. e.g SELECT (SUM(case when R1='yes' then 1 else 0 end) + SUM(case when R2='yes' then 1 else 0 end) + SUM(case when R3='yes' then 1 else 0 end) ) AS yes, (SUM(case when R1='no' then 1 else 0 end) + SUM(case when R2='no' then 1 else 0 end) + SUM(case when R3='no' then 1 else 0 end) ) AS no FROM tblname1 where faculty_name = 'abc' AND student_name = 'efg; i want to display the values of the faculty_name and the student _name and one other column (comments) . but it shows error. i tried doing union but its not working either
Where Column
I initially thought when doing a query with a where clause that has WHERE something='value' that it would look for exact value. but when I ran a query like this: "SELECT * FROM table where id='65'or id='64' it brings back the row where id=65 (id is column type: id INT(5) NOT NULL auto_increment) (I was testing some queries to verify user input and its results in php)
Column Specified As Not Null
I have a column in mysql that is specified as "Not Null" I have noticed that my form fields that map to these columns are able to populate data into the table regardless of the not null, like if I have an email field and I leave that blank on the form then submit well all other data of my form fields populate and I guess I am expecting the behaviour of the not null on the email column to deny data entry for that entire form submission thus record not entered.
Ordering A NOW() Column?
I have built a web application that allows users to input data into the database, and each entry is marked with the date and time in its seperate DB column using the NOW() mysql function inside the INSERT query. But now that it has passed new years... I've noticed that all new entires in 2007 are being placed at the bottom of the sort order when SELECT'ing all records from the DB and using ORDER BY now_col (which is the NOW() column). Why is this happening? Is NOW() not a good function to use to record the date and time of a DB record insert? What can I do to fix this or is there another solution? Add-In: The column type is DATETIME
Question About Column
I am going to import about 200 generated codes into a MySQL table... Just had a queation, the codes will look like this as an e.g. 7dYhsj8. So would I just make then 'Text' as the data type and Not Null?
Un-UNIQUE'ing A Column
How do I do this? I accidentally set a column as UNIQUE and I don't need it set that way. How can I set it back to not unique?
Column VARCHAR > 255 ???
I'm building a custom CMS and I need to know what field type I can use to get > 255 characters as char & varchar have a 255 limit. The column will hold news item details... Lastly how do I ensure line breaks remain ? htmlentities and html_entity_decode?
Insert Column BETWEEN
Is it possible to (like phpmyadmin) insert new columns between existing columns using a php script? I have quite a few databases that I need to update with new columns in some of the tables and don't what to have to do it manually for every db. Writing a script would be much easier (and faster?).
What Column Type Should This Be?
im using phpmyadmin and i want a column to be default as 1 and only able to be either 1,2,3,or 4 so which type should i put it as under column? i was thinking int but that allows any number so should it be enum or set?
Another Column Or Another Table?
I have a table which will typically have approximately 3000 rows. When displaying the data on a webpage I want to display some of the rows in a different format based upon the data. For example, I may have 3000 items and I might want to change the display format for only 12 of those items. My options are to have an additional column in the main table which will just be a tinyint(1), so basically a boolean to say that item needs treating differently. Or I can have another table to store the items id and so flagging it for different display. I also need to do the same for highlighting certain items so again I'd either need a another column with a boolean or a third table to store the item id. Its going to be far easier from a coding and management point of view to have 2 extra columns with a boolean value. But is it better to have 2 extra tables?
Connecting To Value Instead Of Column
myTable1 n country_id city 1 3 Tokyo 2 1 New York 4 1 Chicago 5 2 Peking 6 3 Nagoya 7 4 Paris myTable2 id continent_id country 1 1 America 2 2 China 3 2 Japan 4 4 France myTable3 c continent 1 America 2 Asia 4 Europe myTable4 time param 10 : 10 c=4 10 : 12 c=2 10 : 30 n=4 10 : 35 c=1 10 : 37 n=5 10 : 50 c=2 10 : 54 c=2 10 : 55 n=1 10 : 58 c=2 11 : 15 n=7 11 : 20 c=1 I have data in my tables like the above. And I have a numeric variable which is connected to c in myTable3. Let's suppose the numeric variable is "1." The following code produces the following result. code select time from myTable4 where substring(param,3)=1 result 10 : 35 10 : 55 11 : 20 But I don't like to produce 10:55, because param value of 10:55 is n=1 instead of c=1. The following would-be code doesn't work correctly, but it will show what I want. code select time from myTable4 where <IF left(param,1) is c> substring(param,3)=1 <ELSEIF left(param,1) is n> select time from myTable1 inner join myTable2 on myTable1.country_id=myTable2.id inner join myTable3 on myTable2.continent_id=myTable3.c where myTable3.c=1 </IF> order by time target result 10 : 30 10 : 35 11 : 20 target result when the numeric variables=2 10 : 12 10 : 37 10 : 50 10 : 54 10 : 55 10 : 58 target result when the numeric variables=4 10 : 10 11 : 15 I hope you give me a precise solution. Should I use UNION? (I am using mySQL 4.0)
DROP COLUMN X.y;
I tried using the above command to get rid of a column (y) (from table (x) because the column name was not unique). This didn't work so i just dropped the table and created another. If my table had been bigger this would have been a b***er. I looked in the MySQL manual but DROP only seems to work on db's or tables, or does it?
Summing A Column?
PHP session_start();ob_start();$item = $_GET['item'];$user = $_POST['username'];$pass = $_POST['password'];mysql_connect('localhost', 'mysqluser', 'mysqlpass');mysql_select_db("mysqldb");$result = mysql_query("SELECT * FROM users WHERE user = $user AND password = $pass");$row = mysql_fetch_row($result);if ($row <= 0) { header("Location:item.php?item=failed");} else { $_SESSION['login'] = 1; $_SESSION['user'] = $user; header("Location:item.php?item=$item");}ob_flush(); I think it should work, but it's repeatedly telling me Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /my/directory/html/item.php on line 10. I can't figure out for the life of me why it won't work. It connects properly, I've been doing SQL queries all over the site. The variables POST correctly, and the SQL variables are all correct as well.
Default Value For A Column, When Value Is Not Specified
We have migrated our database from DB2 to MYSQL. In DB2, there is an option like 'GENERATED BY DEFAULT AS IDENTITY' which will cause DB2 to generate unique values for the identity column during insert operations if no value is specified for the identity column. However, if a value is specified for the identity column, DB2 will use it in the insert operation. In MYSQL, is there an option like this?
|
TRACKER
