Replace Some Username Values With The Users ID In A Different Table
I really can't figure this out. I want to replace some username values with the users ID in a different table.
The tables are
pictures
user (Example FRED)
picture (Example pic.gif)
users
id (Example 5233)
user (Example FRED)
What I want to do is replace the user column in pictures with the correct id from the users table
So in the pictures table, it would no longer be FRED it would be 5233
Is there a query I can run to do this?
I'd really appreciate some feedback
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Replace Values In SELECT
I'm having a simple query: SELECT user, user_group FROM table I automatically want to replace the user_group, which is a non saying string with the name of the user group when viewing the table. There's no other table listing the user group name and id, I could use a join query for, thats why I somehow have to user a IF user_group = xxx -> user_group = yyy Is there any way to do this with a subquery or anything?
View Replies !
View Related
Replace Values In All Tables...
I have a database that contains lots of tables, that control sales orders, purchase orders etc etc. I want to replace all instances of "pro 10" with "pro-10"! So what I'm after is a query with the effect of: UPDATE AllTables SET * = "pro-10" WHERE * = "pro 10"; ie, this should affect every column in every table? Does something like this exist?
View Replies !
View Related
Replace Values By Select-Statement
i've on Table with people and one boolean for sex. I try to create one Querry, which replace or creates a new column, where instead of sex=true/false the string male/female can be read. I tried with SELECT DISTINCT name, REPLACE(sex, false, 'male') AS sex FROM ... But the rest of the world is represented by 0 instead of 'female' and i can't access this new column.
View Replies !
View Related
Replace Multiple Values In Select Statement
I'm trying to replace a value of a field within a select statement. However, I see that the replace statement only allows one value to be checked and replaced. How can I replace a field for two values? Is it possible? Example: Table contains field response_flag with potential values of "A","R" or "T". When I select the data, I want to change values "A" and "R" to "C". I do not want to change the values in the table. Here's how it can be done in my current environment: select case response_type = "A" then "C" case response_type = "R" then "C" else response_type from.....
View Replies !
View Related
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 !
View Related
Inserting Into A Table Static Values And Values From An Existing Table
I am designing a content management system for my work-term and I basically need some help in data insertion. What I have is two tables, each with different field (Column) lengths. I am taking data I store in what is a mysqllate table ( basically mysqllate/default information), and appending it to another table which will be an actual production table that will be used in the website. This table still has 3 more fields so basically this is how it goes: mysqllate table: DOCID int(11) No DTID int(11) No ID int(11) No requirements text latin1_swedish_ci No reqd varchar(100) latin1_swedish_ci Yes NULL date varchar(100) latin1_swedish_ci Yes NULL Production Table: RID int(11) No DID int(11) No DTID int(11) No ID int(11) No req mediumtext latin1_swedish_ci Yes NULL reqd varchar(45) latin1_swedish_ci Yes NULL date varchar(45) latin1_swedish_ci Yes NULL comply varchar(45) latin1_swedish_ci Yes NULL notes mediumtext latin1_swedish_ci Yes NULL Therefore as you can see RID, comply and notes, are to be static values determined before the insertion of the data. I just do not want to have to query 900 rows of mysqllate info through PHP append the static values and then re-insert there has to be an easier way, perhaps through a view..
View Replies !
View Related
Regularly Replace Table With Csv File
I am working on a project where a .csv file will be uploaded nightly to a server. A perl script will parse the csv file, and upload the field contents to a mysql database table. Easy so far. There are about 12,000 rows. When done, the mysql table should mirror the csv file- any changes made to the csv file prior to upload each night- adds, changes, and deletions, should be reflected on the server after the csv file is processed and loaded into the table. I guess, for all intents and purposes, I am replacing the entire table. What would be the most efficient way to do this? Should I essentially nuke the table, and upload my csv sourced data into a new table each night? Or, should I use mysql logic to see if any of my csv rows have in fact changed when compared to the mysql rows, and only update the existing mysql table rows if that is the case? If I go this route, I guess I would need to build in some mechanism to determine if there are rows that were deleted entirely in the csv, and should be deleted in the rows on the mysql table. With all the processing involved with comparing the data of the csv file to the table, I'm wondering if all the processing would be inefficient, and maybe I should replace the table entirely with data from the csv. To make matters even more fun, the user would like the table locked during this procedure. I'm not sure the processing times of these two scenarios, and how much "down time" might be expected when processing at table with 12,000 rows, and about 10 fields.
View Replies !
View Related
Replace In One Table With Words From Another Table,
How is possible to replace in INSERT_NOW - CURRENT with words from STORAGE - OK_WORDS when BAD_WORDS is like NOT_OK_WORDS only from mysql console? Short if i have storage of words named xxx and users insert in the insert_now words like sex, and this is in bad_words ; sex ; What replace an update should run? Here is data: CREATE TABLE `bad_words` ( `id` int(11) NOT NULL auto_increment, `not_ok_words` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; -- -- Dumping data for table `bad_words` -- INSERT INTO `bad_words` VALUES (1, 'sex'); -- -------------------------------------------------------- -- -- Table structure for table `insert_now` -- CREATE TABLE `insert_now` ( `id` int(11) NOT NULL auto_increment, `current` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; -- -- Dumping data for table `insert_now` -- INSERT INTO `insert_now` VALUES (1, 'sex'); -- -------------------------------------------------------- -- -- Table structure for table `storage` -- CREATE TABLE `storage` ( `id` int(11) NOT NULL auto_increment, `ok_words` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; -- -- Dumping data for table `storage` -- INSERT INTO `storage` VALUES (1, 'xxx');
View Replies !
View Related
Logon FTP Users In A Table
i have proftpd on my server and the user is in the database koll and the table ftpusers. how can i get out information abut when the custummer have loget on the last time and also how can i se witch custummer thats have not been loged on the last 30 days.
View Replies !
View Related
Join Two Users Table
I have a wordpress blog(and it's user database is integrated with bbPress, so I know this is possible) and need to synchronize the user database with some tournament management code, which has it's own user tables. Could I change the "users" table in the APL tournament management code to wp_users as it appears in the Wordpress database structure? Wordpress integrates with bbPress somehow by taking bbPress's table prefix, _bb. I'd be most grateful for any direction and input in my road block which I've stopped at. Thank you for your time and consideration.
View Replies !
View Related
Grab All Users Not In Second Table.
I have two tables. I want to grab all the users from the users table who are not found in the second table. It's just seems to be one of those days where I can't perform the simple tasks, I think I need to go back to bed. [table] users [field] userid [table] user_details [field] userid Select userid from users where userid not in user details
View Replies !
View Related
Best Practices For A 'Users' Table
I am wondering what would be the best way to design the table(s) for users in a database, or at least get some opionions of how people would go about it. Say I have the normal stuff about a user such as name, email, phone etc, a good number of user attributes. Then I also have profile kind of attributes such as photo, more personal details, various preferences etc, again pleanty of attibutes. Then I also have a bunch of access or priviledges attributes such as status and whether they can access/modify certain modules. I could get all these in a single table since a user is going to potentially have all of these attributes once (though a lot could initially be null) but this is going to be one huge (wide) table maybe 30 columns or more. Would it make sense to split it into 3 tables like 'users', 'profiles', 'priviledges'? If so, should all these be created at the same time when a user signs up or as needed when a user decides to add a profile or get assigned special proviledges. If I create then on demand I will probably be saving a lot of unnecessary rows but I will have to be dealing with outer joins and defaults most of the time, ie more complex code. On the other hand if I depend on all three existing (besides the waste issue) I might get the wrong results if, for any reason, a row for each user fails to get created in all tables (or is accidentally removed).
View Replies !
View Related
How To Replace Table Column From External File
I have a table called "shipmentdetails" with a column called "shipmentdates". Assuming that I messed up all the shipment dates, and I now hold a shipmentdate.txt file that is updated, how can I replace the column "shipmentdates" in table "shipmentdetails" by loading the text file "shipmentdate.txt" ?
View Replies !
View Related
Can't Add New Users & Passwords To Grant Table
I am running MySQL 4.1.7 on my Windows XP system. I can create databases, ect. on the command line, but when I try to run a PHP script I receive the 'Access denied' error message. I have tried using the Grant command to add a new user but nothing comes up when I show SHOW GRANT except the root user. How do I add the new users for running the PHP script?
View Replies !
View Related
Fields In Users Db Table For Authentication
which fields do you place in your db for user authenication? for example..: CREATE TABLE `users` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `Username` VARCHAR(40) NOT NULL default '', `Password` VARCHAR(40) NOT NULL default '', `Lastlogin` datetime default NULL, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`) ) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin; i wonder for some more useful fields for software logging/management people sometimes (or seldom :-)) use like: lastip createdon lastlogin active ('yes', 'no', 'banned') -
View Replies !
View Related
Self Join (?) To Get Data For Multiple Users From One Table
I am trying to generate a report containing ebay feedback scores for multiple users. Here is the database structure for the table that contains the scores: TABLE: feedback identities_id int(10) <- contains the user id which is stored in another table feedback int(10) <- contains the feedback score for a specific date and time date_recorded datetime <- date the feedback was captured I need to get the feedback scores for let's say two users (I will want up to five but I figure the query will be almost the same) over a thirty day period. Here is the query I have created thus far (which does not return the correct results): mysql SELECT user1.feedback AS user1feedback , user2.feedback AS user2feedback , UNIX_TIMESTAMP(user1.date_recorded) AS date_recorded FROM feedback as user1 LEFT JOIN feedback as user2 ON user1.date_recorded = user2.date_recorded WHERE user1.identities_id = 1 OR user2.identities_id = 2 AND user1.date_recorded > NOW() - INTERVAL 30 DAY ORDER BY date_recorded ASC This returns virtually the same data for both users which means I am not distinguishing the two properly.
View Replies !
View Related
How Do I Replace Data In An Existing Table - Based On A Column With An Identifier
Each record in the table I want to update has a unique identifier: products_model. For a given model number, I want to replace its image, which is located in a field called products_image. The file with the new data is a .txt file, a sample of which looks like this: v_products_modelv_products_imageEOREOR 5361453614.jpgEOREOR 5361553615.jpgEOREOR 5372453724.jpgEOREOR The beginning part of the table looks like this: +----------------------------------+---------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------------------+---------------+------+-----+---------------------+----------------+ | products_id | int(11) | NO | PRI | NULL auto_increment | | products_type | int(11) | NO | | 1 | | | products_quantity | float | NO | | 0 | | | products_model | varchar(32) | YES | MUL | NULL | | | products_image | varchar(64) | YES | | NULL | | | products_price | decimal(15,4) | NO | | 0.0000 | | | products_virtual | tinyint(1) | NO | | 0 | | From what I've read over the past few days...I think I need to: (a) delete the EOREOR column from the text file; (b) use the LOAD DATA INFILE command somehow, telling it to ignore the first line of column headers in the text file.
View Replies !
View Related
Making Site Login Add Users To Phpbb Database Table
I have a login script for my site that I got off the net and was planning to use, but, I also wanted to make it so that when a user signed up thru the registration form it would also add them to the phpbb database's table. It wasn't discussed heavily from where i got the script but from what was mentioned the encryptions differ which results in a rejected login. Code:
View Replies !
View Related
Finding Column Values Present In One Table, But Not In Another Table
have two tables, 'widgets' and 'widget_cats'. The primary key for widgets is 'widget.widget_id' and the key for widget_cats is a two-column key, 'widget_cats.widget_id,widget_cats.cat_id'. A particular widget_id can have more than one category, and so be present multiple times in the widget_cats table. I'm looking to find the widget_id's of ids that are present in the widget_cats table, but ont in the widgets table. For instance say the pair '515,A98' is in the widget_cats table, but '515' is not present in the widgets table. I'd like '515' to be among the values returned.
View Replies !
View Related
Inserting Values From 1 Table Into Another Table
I have 2 tables: A and B Table A contains fields u, x and y Table B contains fields u and z Table A has all possible variations of u. Table B has some of the varaiations of u in it. My problem is that I'm trying to insert the z-values from Table B into Table A's y field and link them up based on the u-values. I'm running into problems like truncating at row 0. I've tried the following queries: update TableA set y =(select TableB.z from TableB where TableA.u=TableB.u) update TableA,TableB set TableA.y =(select TableB.z from TableB where TableA.u=TableB.u)
View Replies !
View Related
INSERTing Into A Table, With Values From Another Table
I'm programming a shopping cart application, where the products are categorized into different lines. I have a table that defines the different product lines, and now want to populate the product table. However, the text file with the products in it, which I will use to populate the products table, uses the line NAME for each product, when I wish to use the line ID instead. To get the line ID, I would need to get the value from the line table. My question is: is it possible to populate the products table with data from the products text file, as well as a column from the lines table? That is, the INSERT (or LOAD DATA) statement will insert column values from the text file, but the line id from the lines table instead of the line name found in the file (WHERE line name = line name in the lines table). I'm still not sure whether to use an INSERT, and write PHP code to go through the text file, or to use LOAD DATA for this task, because I don't know which statement would support what I'm looking for.
View Replies !
View Related
Add Values To Table
I have a table tblMailingList. It has 3 columns; Name, EmailAdd, AutoID. I am using mysql> INSERT INTO pet -> VALUES ('name1','u@me.com'); but I am unsure as to what to do for the AutoID field. If I use the example given, the error message says that the number of fields is different. How do I get this info into my table?
View Replies !
View Related
Update A Table With Values In Another
I have the following table tableA column_a column_x column_y column_z tableB column_x column_y column_z How do I update the rows of tableA that match rows of tableB (all rows of tableB are unique) What I want to acheive is a check against tableA.column_x with tableB.column_x, if they match, then update the row in tableA.column_y with the value of tableB.column_y and tableA.column_z with tableB.column_z
View Replies !
View Related
Select Values From The 2 Different Table
Sample tables (company_id RK companies.id): Companies id company 1 … 2 … 3 … Locations id company_id location revision 1 1 … 1 2 2 … 1 3 1 … 2 4 1 … 3 5 2 … 2 6 1 … 4 How to write an SQL query to select values from the following columns (companies.company, locations.location) where locations.revision value will be the highest for each company I try Select distinct companies.company, locations.location from Companies, Locations where companies.id=Locations.company_id order by locations.revision
View Replies !
View Related
Duplicate Values For Another Value In The Same Table
I have a permission table that gives a userID permission to a module and the any actions within that module. What I want to do is duplicate his permissions to another user. Permission table has: id (PK) userID (FK) module action When I do an insert and do a select of the values of the first user, it says It cannot use the target table in the FROM clause.
View Replies !
View Related
DISTINCT Table Values
I use DISTINCT to select distinct values of one column... but i can not use distinct selecting more values of that row. select DISTINCT(field1) from table works select DISTINCT(field1), field2, field3 from table does not work
View Replies !
View Related
Editing Values In A Table
I just moved from SQL Server to MySQL and in the Enterprise Manager in SQL Server I could query the entire table and then modify the values with the table without writing an UPDATE statement. I cannot do that in one of my tables because the EDIT at the bottom is greyed out. There are other tables where I can make a modification to any value I want within the table.
View Replies !
View Related
Update Table Values
I have a csv.txt file with contacts and their information in it. Then I also have a table in mysql with the same contacts and their various information. I need to merge these to so that if a field value in the database is NULL the value that is in the csv.txt file is inserted into the database field. I have been looking at the mySQL site on how to do this but have not been able to produce a working solution. Below is a more detailed example of what I am looking for. Code:
View Replies !
View Related
INSERT Values From One Table To Another
In table A i have a.id and name and in table B, i have b.id, a.id(FK) and owns. Table A -------------- a.id | name | -------------- 12 | James | 13 | Paul | 14 | Tom | 15 | Andy | Table B -------------- b.id | a.id | owns | -------------------- 1 | 12 | Car | 2 | 12 | Bike | 3 | 13 | PC | Now what i want is to add two new names to table A which will be Tom and Andy and have the same data in table B for Tom and Andy with just different b.id (PK). How can i insert this new data in table B using insert?
View Replies !
View Related
Update Values In Another Table
Table one has columns thread_forum_id and thread_thread (and a bunch of other stuff) Table two has columns topic_id and post I would like to populate thread_thread with the data from post however the columns thread_forum_id and topic_id need to correspond to each other when i do this. for instance if topic_id = 1 and post = A and another row is 2 and B then i want the resulting table one to have 1 and A 2 and B but not 1 and B or the other way around.
View Replies !
View Related
Returning Values That Are In One Table, But Not In Another
I'm looking for a query to return values which occur in one table, but not in the other. Say we have two tables (table_1, table_2) each with a column titled "appointments". I want a list of results for appointments which are in table_1, but not in table_2 I have tried the following, but all I get is a huge list of duplicate appointments which occur in both tables: SELECT table_1.appointments FROM table_1, table_2 WHERE table_1.appointments<>table_2.appointments; I've also tried 'NOT IN' for this but the result was just the same.
View Replies !
View Related
Most Common Values In Table
I've been looking around the MySQL and PHP documentation to no avail, Basically, say we have a table of names: id, name 1, Danny 2, Sam 3, Kerry 4, Danny 5, Paul 6, Sam 7, Kerry 8, Danny 9, Jack 10, Hudson ...and we want to determine the most popular, second most popular, etc. In this example, the most popular name is Danny (3 rows), the second most is tied between Sam and Kerry (2 rows each), then Jack, Hudson and Paul only have 1 row each. In the case of a tie, I would simply sort the two rows alphabetically. The resulting popularity ranking would be: 1) Danny (3 results) 2) Kerry (2 results) 3) Sam (2 results) 4) Hudson (1 result) 5) Jack (1 result) 6) Paul (1 result) Is there any way I can do this with MySQL?
View Replies !
View Related
Replace Information In One Table With Information From Another Table
I've got two tables in one DB they have various columns; what I need to know is how I can take the information from a column in one table and put it in to a column in another table. table1 - characters table2 - characters_jackedup I want to take the information from column maxMp in table1 and put the information in table2 under maxMp the column exists in both; but the columns are in a different order.
View Replies !
View Related
Generating A Table With Missing Values In SQL
I have the following table: +------+------+------+-----------+ |ID |GROUP |CODE |ANSWER | +------+------+------+-----------+ |1 |1 |001 |01-01-2003 | |2 |1 |002 |Yes | |3 |1 |003 |NA | |4 |1 |004 |297797 | |5 |1 |005 |0 | |6 |2 |002 |Yes | |7 |2 |004 |297852 | |8 |2 |005 |1 | +------+------+------+-----------+ This means that code 001 and 003 of group 2 are missing. Result of the query should be: +-------+-----------+-----------+-----------+-----------+--------+ |GROUP |ANSWER1 |ANSWER2 |ANSWER3 |ANSWER4 |ANSWER5 | +-------+-----------+-----------+-----------+-----------+--------+ |1 |01-01-2003 |Yes |NA |297797 |0 | |2 |NULL |Yes |NULL |297852 |1 | +-------+-----------+-----------+-----------+-----------+--------+ The missing values should be replaced by a NULL value in my resultset. Is this possible? I don't have any clue how my query should look like, can anyone help me and show me a solution?
View Replies !
View Related
Renaming Values In Table With ENUM
It seems that this table was created with some ENUM data types so I'm trying to figure out how this works. I can post the script that created this table but basically I'm trying to rename a value in this column document_type ENUM('FAQ', 'Sheet', 'Marketing Resource', 'Teacher Guide', 'Reference / User Guide') NOT NULL, I'm not sure how this array is stored and what would happen if I tried to rename the value ....
View Replies !
View Related
Renumber Mysql Table Values
There are 100000 rows in a table, I need to renumber them from 1,3,6,8,11,14,21,55,888,1000,9999...99999 to 1,2,3,4,5,6,7,8,9,10,11,12,13,14... etc I also need to update an associated table, so its going to need to lock the tables, find the lowest available index in table1, then update table1 index, and then update table2 to use the same index as well, then unlock tables What would be the procedure to do that?
View Replies !
View Related
Update Query Where Values Will Come From Other Table
I'm creating an update query which the value will come from another table. I have here my current query which unfortunately makes the system hangs. Probably because of the query itself is not properly coded. update boxes b inner join messages m on b.ctnnumber = m.ctnno set b.consigneerecv = m.CName, b.consigneerecvdate = m.DateRcv, b.phrecventered = "Y", b.PhilStatus = "delivered", b.prevreleasestatus = b.releasestatus, b.releasestatus = "delivered", b.PhilStatusDate = m.smsrecvdate, b.phdelprice = "0.00", b.phdelamt = "0.00", b.recvrelation = m.Relation, b.APRecventered = m.smsRecvDate where b.consigneerecv = '' or b.consigneerecv = 'NA' or b.consigneerecv is null; I'm thinking revising it so that it will not cause the system to hang but I don't know how. Guys please help me with this one. I also have this another idea which probably will not work. My idea was something like this: Update table1 set table1.column1 = (select table2.column1 where table2.column1 = table1.column1), table1.column2 = (select table2.column2 where table2.column1 = table1.column1), .....
View Replies !
View Related
An Enum Field Using Values From Another Table
The first table, called genus, contains several fields, the important one in genus is name, and there are others about the individual plant types. The second table 'plants' needs one field for the genus name, so that I can link tables on this. If there a way to make a foreign key from the genus table into an enum field on the second? My idea is that then, with phpmyadmin or similar I can just select the genus name from a list when entering new plant data.
View Replies !
View Related
|
TRACKER
