I have one table with USERS and another table with RSS FEED URLS. Both are InnoDB type. Each user may be subscribed to any number of the feeds. I'm trying to figure out the best way to keep record of which feeds users are subscribed to. One way is to have a feed_ids varchar column in users table and separate each. Is there a better wa...
What is the best database schema for polls? Is one-to-many relationship good for this? I'm thinking about having two tables: poll_questions int id varchar body datetime created_at datetime updated_at poll_answers int id varchar body int votes default 0 int question_id (foreign key to poll_questions.id) ...
I'm trying to optimize this slow query (>2s) SELECT COUNT(*) FROM crmentity c, mdcalls_trans_activity_update mtu, mdcalls_trans mt WHERE (mtu.dept = 'GUN' OR mtu.dept = 'gun') AND mtu.trans_code = mt.trans_code AND mt.activityid = c.crmid AND MONTH(mtu.ts) = 2 AND YEAR(mtu.ts) = YEAR(NOW()) AND c.deleted =...
I am currently trying to solve this problem in MySQL. I have MySQLdb installed. What is the best way to write a list in Python, e.g. [1, 2, -1, -2] to a column of a MySQL database table? I would like each consecutive entry of the list to be put into consecutive rows of a MySQL column. I can obviously run individual queries in a loop, ...
Hello! I have a field in my database and i want to store the information encoded. So i would like use the AES_ENCRYPT(), AES_DECRYPT() functions. I have a little problem about the right usage... So this is my code what i need to modify, but i try it many ways and always get SQL syntax error. $query="update users set test='".$_POST...
Hi guys, how can I count the number of connection and queries by user per day? I need a little list of users, previously registered in my MySQL Server, with the total of the queries and connections. For instance: users | Queries user01 | 120000 user02 | 340000 user03 | 1540000 user04 | 1244000 thanks ...
Hello there I have a database that has 2 tables in it, one is the categoryTable and the is the userMenuTable, the categoryTable currently has two columns in it, categoryId and categoryTitle it currently holds 2 rows of data, the categoryId's = 1 and 2 and the categoryTitles = News and Blog, in the the userMenuTable I keep a record of wh...
Hi, We have a system in which we must use pessimistic locking in one entity. We are using hibernate, so we use LockMode.UPGRADE. However, it does not lock. The tables are InnoDB We have checked that locking works correctly in the database (5.0.32), so this bug http://bugs.mysql.com/bug.php?id=18184 seems to be no problem. We have chec...
Hey, Sorry for not formatting my code. the toolbar was gone... I want to insert some data into a mysql db. I've wrote a function in php: function add_ID($ID, $token) { $add = "INSERT INTO ids (ID, token) VALUES ('$ID', '$token')"; mysql_query($add); echo 'added successfully'; } if(isset($_GET['addDeviceID'])) { add_ID($_GET['...
Hi, somehow wild characters has cropped up in my mysql table while storing UNC path in my table. and all the \r onces are showing up a square in the fields. Can anyone tell me how to fix these please? Thanks. ...
Suppose I have a people and their GPS coordinates: User1, 52.99, -41.0 User2, 91.44, -21.4 User3, 5.12, 24.5 ... My objective is: Given a set of coordinates, Out of all those users, find the ones within 20 meters. (how to do a SELECT statement like this?) For each of those users, get the distance. As you probably guessed, these co...
I have over 200 SQL files which contain misc data that need to be executed into my Mysql Database, I'm wondering if anyone knows a good way to accomplish this task other than executing each file one by one manually? ...
How can I optimize this mysql query: SELECT * FROM messages WHERE toid='".$mid."' AND fromid='".$fid."' OR (toid='".$fid."' AND fromid='".$mid."') AND subject != 'something' order by id ASC LIMIT 0,5 I need to get messages between users in one page. This query id taking to much time and server resources. Can it be done in...
Hi, I have tried creating my mysql tables with both UTF-8 and Latin1 Char sets. When I use Latin1 my indexes are used, when I use UTF-8 indexes are not used when selecting/limiting records. Is there something I am missing with the Char sets that causes this to happen? Cheers Ke ...
Hello! I am inserting a record and i want to use the id of the last record inserted. This is what i have tried: $sql = 'INSERT INTO customer (first_name, last_name, email, password, date_created, dob, gender, customer_type) VALUES(:first_name, :last_name, :email, :password, :date_created, :dob, :gender, :customer_type)' . ...
I need some help solving a problem with mySQL, is it possible to pass an array to a function and then run a match agains the array values? I have this query function getMenu($cookieId) { $this->db->select('*'); $this->db->from('categoryTable'); $this->db->join('userMenuTable', 'categoryTable.categoryId = userMenuTable.categ...
I am saving a raw email in a TEXT column in MySQL with Ruby on Rails. It keeps getting truncated to 65535 characters, does anyone know what causes this? MySQL is running with max_allowed_packet=64M ...
Hi all, I am trying to make a cocoa application which will have form like fields and will be used to store and retrieve values from table in MySql database at some local linux server. I want to know that is there any connectivity tool in cocoa which I can use or I have to design API for it?? Thanks, Miraaj ...
+---------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | user_id | int(11) | NO | UNI | NULL | | | utm | point | NO | ...
Hi, I maintain a custom built CMS-like application. Whenever a document is submitted, several tasks are performed that can be roughly grouped into the following categories: MySQL queries. HTML content parsing. Search index updating. Category 1 includes updates to various MySQL tables relating to a document's content. Category 2 inc...