I've written a query returning rows associating Customers and Salespeoeple. Note that the query joins several database tables. And note that not all customers have a salesperson. c_id c_name s_id s_name 24 microsoft 1 mike 27 sun 1 mike 42 apple 2 bill 44 oracle 1 mi...
I have the same situation as this other question, but I don't want to select the rows, I want to update these rows. I used the solution Scott Saunders made: select * from table where email in ( select email from table group by email having count(*) > 1 ) That worked, but I wanted to change/update a row-value in these entries, so ...
I want to destroy all objects that have parent_type == 'Profile' or child_type == 'Profile', like this: Relationship.destroy_all(:parent_type => "Profile") Relationship.destroy_all(:child_type => "Profile") How do I combine that into one method and one sql call? Went with something like this: class Group < ActiveRecord::Base has_m...
Hi, how would I disable the mysql benchmark function, thus not being subject to blind sql injection attacks such as "select if( user() like 'root@%', benchmark(100000,sha1('test')), 'false' );" select * from func does not show up a function benchmark. Kind Regards Charles ...
I have come across various situations, where I want to store some formatted data in a way such that it can be easily queried. For example $ cat so.txt "question_id": 58640, "tags": ["polls", "fun", "quotes"], "title": "Great programming quotes" "question_id": 184618, "tags": ["polls", "fun", "comment"], "title": "What is the best comme...
What's the best way to make 1 SQL call that checks if the join model already exists before we try to create another with this setup: class Parent < ActiveRecord::Base has_many :relationships, :as => :parent has_many :children, :through => :relationships, :class_name => "Child" end class Child < ActiveRecord::Base has_many :relati...
I have a table with a whole bunch of FKs. Table Vehicles ----------- ColorID -> Color.ID MakeID -> Make.ID ModelID -> Model.ID etc... My issue is that I forgot a few columns and I need to add them. I can add them through right clicking on the table and choosing 'Design', but not if I want to make them NOT NULL, or delete a column. I...
Please help me to generate the following query. Say I have customer table and order table. Customer Table CustID CustName 1 AA 2 BB 3 CC 4 DD Order Table OrderID OrderDate CustID 100 01-JAN-2000 1 101 05-FEB-2000 1 102 10-MAR-2000 1 103 01-NOV-20...
Basic question about a good way of organizing data in a database. Let's say I have a search site for multiple stores. Each store has a number of products. What is the best way of organizing price and inventory data? I see two ways of doing this: a table for every store , with the first column or two identifying a product, a column for ...
I am using the following model with Django: class Hit(Model): image = ForeignKey(Image) user = ForeignKey(User) timestamp = DateTimeField(auto_now_add = True) What I need is basically a list that contains the count of "first hits" (i.e. hits with no earlier timestamp for the same image) for every user to create sort of a r...
I am creating a login form. I am learning how to use SHA-1 to encrypt passwords. I used SHA-1 to encrypt the password that the user created during registration. In the database I inputted pretend username and password data, to have something to work with. I'm having problems getting my login form to work. // Database Connection $con = ...
Hi, A wrong percentage has been applied to a field (TotalPercentageAmount) and I need to correct it. Given 2 fields Amount and TotalPercentageAmount how can I calculate what percentage was applied? I need to work out percentage applied to TotalPercentageAmount and UPDATE the column with correct percentage. Little script I have created ...
I have a SUPER slow query, which I posted here: http://pastebin.com/E5sdRi7e. When I did an EXPLAIN, I got the following: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL...
I have tables item and store (it's a store management system). item table has a column called store_id and another column called status. item.status can be 'sold' or 'unsold'. I need help writing a query which will do these things: select all items of all stores if a store has just one item and that item is 'sold', remove that item fr...
Hi there. I need help with building SQL query. I have 4 tables: Sellers, Goods, Projects and Sales. Sellers table has following structure: SellerID (int) PK SellerName (nvarchar) SellerStatus (int) SellerCity (nvarchar) Goods: GoodsID (int) PK GoodsTitle (nvarchar) GoodsColor (nvarchar) GoodsSize (int) GoodsCity (nvarchar) Proje...
I am using a subquery to return a count as an integer value to my main query. This query is used to rebind an ASP.NET DataGrid and I have only two characters width available for this column. I want to restrict the width to two characters. So, I want to set a value of 99 when the count exceeds 99. I can't figure a way to do this? I c...
Hi, I'm trying to join tables to get a count on a field but am having trouble figuring out how to do it. here are my tables: tags id INT tag VARCHAR project_tags id INT project_id INT tag_id INT projects id INT ... I want show in my view something like this: [tags.tag] x 23 [tags.tag] x 12 ... Now I'm no S...
I'm using prepared statements and MySQLi with my queries to protect against injection attacks. Would prepared statements remove the need for mysql_real_escape_string entirely? Is there anything else I should consider when securing my site? ...
I have a question about MySql. I have a table with 7.479.194 records. Some records are duplicated. I would like to do this: insert into new_table select * from old_table group by old_table.a, old_table.b so I would take out the duplicated entries...but problem is that this is a large amount of data. The table is MyIsam. Thi...
Hello. I have a activities page and a statusmessages page for each user. In activities it contains what the users have done, such as being friends with someone, commented on pictures and so. users_activities id | uID | msg | date In users_statusmessages, I got the statusmessages, the user creates. users_statuses id | uID | messag...