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?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Allowing Multiple Hosts...
I am creating a vb.net app that conncects to mysql with the corelabs provider. This app will be deployed on our intranet. My question is this: Do I have to create different user for each host? For example, my ip range is 192.168.1.*. Can I create one user with this host "Range" and allow them to connect. If so, how do I accomplish this.
Best Way To Do This... Unique Key But 3 Rows Need It?
I have a weekly schedule table, each week should have a unique number but each week requires 3 different records (one for each game). What would be the best to create a unique identifier that is the same for the 3 records each week?
5 Unique Rows
My SQL so far is: PHP Code: $sql = "SELECT Manufacturer.ManID, Manufacturer.ManName, Manufacturer.Active AS manactive, Manufacturer.ManText, Models.ModelID, Models.ModelName, Models.ModelText, Models.Active AS modelactive FROM Manufacturer INNER JOIN Models ON (Manufacturer.ManID = Models.ManID) WHERE Models.ModelName UNIQUE AND Manufacturer.ManID = " .$manid. " AND Models.ModelID = " .$modelid. " AND (Models.Active = 1 AND Manufacturer.Active = 1) LIMIT 5 ";
Impose A Unique Key With 11K+ Rows
I have a logging database that is doing some work for me, but I've found a surplus of data that I want to try and get a handle of. There are two fields that tend to be duplicated across a couple rows and it would help clean up the data if I could make those two fields unique from here on. Is it possible to create a query that will delete all of the "duplicate" rows (leaving 1 row but removing 4 or 5 "duplicates")? CREATE: MySQL CREATE TABLE `SearchLog` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `IP` VARCHAR(25) DEFAULT NULL, `query` VARCHAR(50) NOT NULL DEFAULT '', `product_count` INT(5) UNSIGNED NOT NULL DEFAULT Ɔ', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=11662 DEFAULT CHARSET=latin1 AUTO_INCREMENT=11662 ;
Selecting Unique Rows
I have a query that checks a list of users against a list of items and returns for example the following. SELECT users.username, ratings.recording_uid FROM users INNER JOIN ratings ON (users.uid = ratings.user_uid) WHERE ((users.username = 'a') OR (users.username = 'b')) which returns... username recording_uid a 1 b 1 a 2 b 2 b 3 a 4 b 4 b 5 How can I add to that query so that I know what user a has that user b doesn't and vice versa? eg. so that it returns username recording_uid a 3 b 5
Select / Insert Multiple Rows As A Single Row Of Multiple Columns
I have a nice database set up that contains information about orders and the items on those orders. If an order has 10 items on it, I can select the item data which returns 10 rows of data (let's say 5 colums each). Beautiful! Now I find myself needing to satisfy a program that requires all of the data on a single row. I can do this in a higher level language, but if I could accomplish it all in mysql it would be better. I don't need to sum or do any calculations. I just want to select those 5 columns of data about those 10 rows worth of items as a single row with 50 columns. For example, I'd want this: 1-1,1-2,1-3,1-4,1-5 2-1,2-2,2-3,2-4,2-5 To become: 1-1,1-2,1-3,1-4,1-5,2-1,2-2,2-3,2-4,2-5 The first complication is that the number of items on an order is variable, but is always at least 1 and can not exceed 20. The closest I've been able to get is to do something like: SELECT GROUP_CONCAT(item_number,",",qty,","",description,"",",price,",",location_number SEPARATOR ",") FROM items WHERE order_number=12345 This will give me a single text string containing the value content of the INSERT query (which will need to be manipuated outside of the SQL query to pad it with NULL values for the unused items' columns etc).
Multiple Unique Indices
Consider this table: ID | Number | Name 1 | 10 | X 2 | 11 | Y 3 | 12 | ABC 4 | 13 | X ID is the primary key, and there is a unique index on Number. My program loads the table for editing (ID is read-only, Number and Name are modifiable), then checks through each line if any was updated by the user and writes the record back to the database using UPDATE statements (the ID must stay the same). Consider that the numbers of Y and ABC are exchanged, such that: ID | Number | Name 1 | 10 | X 2 | 12 | Y 3 | 11 | ABC 4 | 13 | X When my program executes the query "UPDATE Table SET Number = 12 WHERE ID = 2", the unique index throws an error since ABC (ID=3) still has Number=12. How could I avoid that and update both rows safely (without first deleting all records)?
Multiple Non-Unique Columns
I'm trying to store the call detail reports from a phone system to a database. I've successfully been able to read the CSV files it gives me into an InnoDB table using PHP. I just realized that the system does not give a single unique key in the raw data, but that two columns together will identify a unique call entry. The columns if interest are the timestamp (two people could conceivably make a call at the same time) and a call identifier (which seems to be unique over a month or so, then the system starts recycling the call identifier). With these two columns you can identify a unique call over ANY given time. I'm not sure how to use that fact to assure I'm not inserting the same data if I were to read the same CSV file twice.
UNIQUE As Multiple Columns
its possible to define multiple columns as being UNIQUE - not the value of either column, but the two together. For example, you're storing people's names and addresses in a table. Two or more people could have the same lastname, and more than one person will surely have the same firstname. However, no two people can share the same lastname AND firstname. Is there a way to set this constraint?
Innodb Multiple Tablespace & Unique Name
I'm using Mysql Max-4.1.4-gamma for Linux. I have a question related to a problem with my database server. If on my db server I have 2 different database, say DB1 & DB2, I create two INNODB tables with the same name (foo), one for each database, how they are stored in the innodb data dictionary ? DB1.foo & DB2.foo or simply foo ?
Joins With Multiple Tables And Multiple Rows
I'm making a good ol' forum, and i have three tables, users, threads and posts. when i query my threads table with a join, i need to access the users table twice to get the username of the first poster and last poster. But how? I can only figure out how to get one or the other. Is my design bad? eg SELECT TopicID, FirstPostID, LastPostID, Replies, Views, Topic, username FROM DiscussionThreads, users WHERE DiscussionThreads.FirstPostID=users.ID ORDER BY FirstPostDT DESC LIMIT 10 .
Enforcing Unique Field Values Accross Multiple Tables
I have 2 tables (t1,t2) with unique keys defined in each. In addition to enforcing unique key values in each table, I would also like to enforce unique key values accross both tables (ex. If key value 'XXX' appears in t1 I don't want to allow entry of key value 'XXX' in t2 and vice versa).
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?
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.
Only Allowing One Entry
I am trying to create a simple guetbook. I have it working, but the thing is, it only lets me enter one entry and can't work out why. This is the insert code i have: PHP Code: $insert="Insert into visitordata (name,email, location) values('$name','$email, '$location')"; mysql_query($insert) or die("Could not insert your entry");
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.
Not Allowing To Read Files
I have program with data accessed by MySQL. In my assume, I copy program and its data files to my friends, and they run program with pleased faces. And now, the first friend wants to delete all program related data files and copy program related data files from second friend(or he can also get any program formatted data files) and save them into data folder. My problem: the first friend can run program with only my data files; he can get any data files from anywhere and save them into data folder, but program won't be allowed to read these data. Does everyone have some good ideads?
Allowing Access Over The Internet
I am writing a java program that connects to a database. This is fine when I connect from my local machine but I can't seem to get it done when connecting over the internet. How do I allow a user to access my database over the internet.
Allowing Remote Login
I have been using MySQL for awhile, and for the first time someone from outside our portable /16 needs access to some tables. We have given them the server IP address, the dB name, the port 3306, the root username, and the password, but they still can't get access. Of course, no firewalls or other things in the way. Is it true that MySQL defaults to local access only, and to enable remote access you must do something unusual with grants? If so, how would I do this? (warning, we use the win version, but this question is not a windows-only thing so it is topical for this list:-)
Only Allowing Root Access
I want to be able to only allow root access while I dump the databases, thus locking out all non-root users ( even remote logins) then allow all normal users back in after the dump has been done. How would I go about this.
MySQL Dump Is Not Allowing Application To Run
I am creating a backup once per day automatically. The current size of the file is around 4.1 Gig uncompressed. The command that I use is as follows: mysqldump --opt -q -R -u username-p'passwd' dbname | gzip > /filepath/$(date +%F).sql.gz This takes around 25 - 30 minutes to fully backup the database. The problem that I have seen is that while this backup is occurring I cannot access my JBoss application. Oddly enough, I tried to access the database directly and everything is working fine, but any other remote user cannot access the database. I want to know a couple of things: 1) Is my backup command optimized or is there something else I can add to make it faster. Now I do need to backup the data, table structure, stored procedures, etc. 2) How can I conduct a backup that is more efficient where it does not interfere with the remote users?
Special Characters Allowing Insert
I'm trying to learn SQL and my first attempt is to take all of my flat text files and add them to the database using perl. I wrote a script that will open every file and insert it's contents to the database. So each line is a seperate prepare() execute() call. I get through 4 records then it stops and my error log says 'error around 's'' or something. I look at the s it is talking about and it is the first instance of an apostrophe so I figure that is probably what is causing it. I tried $text =~ s/(['|"])/($1)/g; but that didn't work out well
Multiple Rows
I've got a whole bunch of rows to create, each with a unique key. Each row gets the same value ("New") set in the "Age" column. I've seen the INSERT INTO table (rows,) VALUES (val for row1), (val for row2), etc.But what if each row gets the same exact value?
Multiple Rows
I was wondering if it is possible to get all of the inserted id's of an auto increment column when doing multiple inserts at 1 time. For example: INSERT INTO people (fname, lname) VALUES ('john', 'smith'), ('eric', 'robinson'), ('mark', 'appley'); is it possible to retrieve all of the insert ids of those inserts instead of having to loop through each individual insert and retrieve each individual row id?
Multiple Rows
I create a table with 7 columns id Mdate col1 col2 col3 col4 col5 as above shown id is small int, mdate is date and col1 to 5 are tinyint and i want to insert the values . id is auto increment and i want mdate a day for one row. and other column set to zero what i want is to enter in single query.
Get Multiple Rows Using Subquery?
I have a query similar to the following, however i'd like to get another row from the subquerys - currency. SELECT *, (SELECT xml_result_value FROM lodging_links_allocation INNER JOIN xml_results ON lod_link_alloc_link_id = xml_link_id AND xml_result_value != 'X' AND xml_nights = 1 AND xml_source_id = 19 WHERE lod_link_alloc_lod_id = lod_id ORDER BY CAST(xml_result_value AS UNSIGNED) LIMIT 0, 1) as price_from_1, (SELECT xml_result_value FROM lodging_links_allocation INNER JOIN xml_results ON lod_link_alloc_link_id = xml_link_id AND xml_result_value != 'X' AND xml_nights = 1 AND xml_source_id = 13 WHERE lod_link_alloc_lod_id = lod_id ORDER BY CAST(xml_result_value AS UNSIGNED) LIMIT 0, 1) as price_from_2, (SELECT xml_result_value FROM lodging_links_allocation INNER JOIN xml_results ON lod_link_alloc_link_id = xml_link_id AND xml_result_value != 'X' AND xml_nights = 1 AND xml_source_id = 12 WHERE lod_link_alloc_lod_id = lod_id ORDER BY CAST(xml_result_value AS UNSIGNED) LIMIT 0, 1) as price_from_3 FROM ( SELECT * FROM [..snip..] GROUP BY lodging_master.lod_id ORDER BY RAND(��-12-17') ) as foo HAVING (price_from_1 > 0) && (price_from_2 > 0) && (price_from_3 > 0) LIMIT 0 , 30
Multiple Update Of 21 Rows
I have a database table which holds price ranges for various classes of hire car and various hire periods. There are 7 different hire classes and 3 different hire periods. 21 records in total. Heres an example of what the table contains: ID......HIRE PERIOD.....HIRE GROUP....PRICE 1..........14.....................a...............25.00 2..........28.....................c...............15.00 The rows are shown in an editable form, which is basically a grid of text fields. I need to figure out the most efficient way of updating all the records when the grid is edited. Do I need to perform 21 individual updates? as I'm worried that this will be too cumbersome when there are multiple users. I thought of one way which involves having a hidden field for each row, called 1,2,3 etc, each with a value of eg. 14,a,25.00 as a comma seperated list. Then I split the post values into arrays and perform various updates. Seems a bit clunky though,
Count Multiple Rows
I have a table that tracks dealer transactions. The fields are Date, Dealership, Amount, A, D, W, F. A=Approved D=Denied, W=Withdrawn and F=Funded. I have made it where if a loan has been approved then A would =1 and D W F would be null. Same goes if the record was withdrawn W would =1 and the other would be null. So here is my problem: I want to group by dealership and then count how many were approved, denied, withdrawn, and funded. i am running version 3.23.58. After doing much research I either need to do unions or subqueries. However, both are not availble until 4.x. Is it possible to do what I am looking to do without upgrading?
Update Multiple Rows
I have about 20 rows that need to updated together and would rather not have 20 seperate update lines but am not sure what else to do. At the moment it looks something like this: UPDATE movies SET mon='10pm', tues='10pm', wed='11pm' WHERE movie_id='19'... UPDATE movies SET mon='9pm', tues='11pm', wed='4pm' WHERE movie_id='37'... UPDATE movies SET mon='8am', tues='1pm', wed='5pm' WHERE movie_id='19'... There is no logical order to the movie_id's they are selected from the movies table using specific criteria.
Collapsing Multiple Rows Into One.
I'm setting up a database of links, where each one can be in multiple categories. I could make each category id a column in the links table, but I'd rather store it in a separate table so I don't have a bunch of NULLs cluttering up the main table and also to allow unlimited categories. That table has two columns, linkid and categoryid. The problem is that I'd like to retrieve it as one row somehow, something like this: "linkid category1 category2 etc..."
Updating Multiple Rows
The following UPDATE query works fine if run directly into phpMyAdmin (I take the $sql output of the script with the data in it and paste it into phpMyAdmin). But it doesn't update my records if run from the PHP script. I can't seem to figure out where the bug is: Code:
Selecting From Multiple Rows??
i have made a voting script... every time someone votes it goes into a table called 'voting'... since people can vote on multiple things in the site there are multiple instances of each user in the 'voting' database... if each entry has a 'userID' a 'ratingGiven' and a 'objectBeingVotedOn' field how would i go about grabbing say the "ratingGiven" field from all of one particular user's entries .
Updating Multiple Rows
I need to update multipule rows a once, using PHP. Here's what I mean. I have a mysql query out put the data as a form like this: Item 1 <input type="hidden" name="id" value="1"> <input type="text" name="order" value=""> Item 2 <input type="hidden" name="id" value="3"> <input type="text" name="order" value=""> Item 3 <input type="hidden" name="id" value="4"> <input type="text" name="order" value=""> <input type="submit"> Order need to be updated. I could setup to do a loop with multiple queries.
Update Multiple Rows
I have an array that I need to put into an existing db column using a perl foreach loop. I can do this using an INSERT statement but the script will be run every day and the source array will be changing as it is based on directory contents so I need to UPDATE vs. INSERT With the INSERT statment the script places each array element on a new row of the db as it should but when I try to use an UPDATE statement, I get a single array element written over and over on each row of the db. Code:
Insert Multiple Rows
I want to insert multiples rows and use sintax "INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);". But, I get the following error: "Column count doesn't match value count at row 1". The number of fields is the same, only that insert multiple rows.
How To Evaluate Multiple Rows As One Using IF( )
Each record for a PhysicalSource has a Status number. I need to do a SELECT statement to find out if there is a Status = 2 in any of the records. If Status = 2 then I need to return all rows for this PhysicalSource else if Status != 2 then I need to exit the query (don’t return anything). The statement is as follows: SELECT SourceID, OriginalTime, State FROM sources WHERE SourceID = ? AND PhysicalSource = ? AND IF(Status = 2, 0, 1) Order by SourceID ASC As I found out the “IF(Status = 2, 0, 1)� evaluates every row and returns all others with Status !=2. The options are limited, I inherited a GUI (can’t modify it) that must be use and only accepts one query at a time using MySQL version 4.1.11. Could someone help with a way to have this evaluate multiple rows, as one, and if in any of them Status = 2 then exit the query?
Matching Multiple Rows
I've got the following tables with the following columns: property: id, address, number_of_bedrooms attribute: id, title (e.g. washing machine, central heating, furnished) property_attribute: property, attribute (composite primary key on both id columns) what i'm trying to do is select property ids that have all the attributes i'm looking for, for example, all those properties that have a washing machine and are furnished. I've been trying things like the following which aren't working, probably because the attribute column won't be equal to two different values at once. SELECT pa.property FROM property_attribute AS pa INNER JOIN attribute AS a ON pa.attribute=a.id WHERE a.title='washing machine' AND a.title='furnished' this is returning an empty set because, i think, a.title can't have two values at once. what i want to say in pseudo-sql is: SELECT pa.property FROM property_attribute AS pa INNER JOIN attribute AS a ON pa.attribute=a.id WHERE (pa.property=X AND a.title='washing machine') AND (pa.property=X AND a.title='furnished') so the only results that will be returned are where a property has both attributes. i can't think of how to do this since there will be varying numbers of attributes i need to search on. perhaps i need to use a variable for pa.property - i tried that, setting it to SELECT DISTINCT property FROM property_attribute, but mysql didn't like it because this query returned multiple rows. i may need some kind of set variable or an array.
Select Multiple Rows With The Same Id
So I have this table: table_name id_one ---- id_two 1 ---------- 1 1 ---------- 2 1 ---------- 3 2 ---------- 1 2 ---------- 3 I want to select all of the id_one rows where the id_two is '1' and '2'. The desired output is: id_one 1 1 Later I might use SELECT DISTINCT to produce the following: id_one 1 So far I have: SELECT id_one FROM table_name WHERE id_two IN ('1','2') But this selects all the id_one rows where id_two is '1' OR '2'. I want the rows where it is '1' AND '2'.
Delete Multiple Rows At Once
My question is, what code would i use to delete multiple rows of data at the same time. if i had: username password userid john fubar 13402 mike yippy 13679 and i wanted to erase both users in one query, what would it be?
Subtracting From Multiple Rows.
I'm wondering if it's possible to subtract a single value from multiple rows. For example, say I have two rows containing pieces of an account balance, which add up to a total balance. We'll say the two rows hold balances of $10 and $20. Now I want to subtract $25 from them (meaning subtract $20 from one and $5 from the other). Is there a way to write this in one query? If I use the SUM() command can I subtract from the total instead of each unique row?
|
TRACKER
