mysql

what is the best way to create this database

hello what is the best way to create a database for Questions and Answers Script the user can submit a question with a set of choices among which the right answer exists the database must save the question, all the choices and the right answer. the best way that comes to my mind is to make 2 tables: Questions Table: ID Question Rig...

mySQL query, for discrete results limited and ordered by a joined column

I have two tables User id name Result id user_id score created_at Every hour a cron job runs and puts data in the result table for each user - assigning them a score based on some external variable parameters. So the Result table contains many records for each user. I want to retreive a 'top ten' users at any given poin...

Alter MYSQL Table To Add Comments on Columns

I have been checking the MySql Documentation for Alter Table and it does not seem to include a way to Alter a MySQL Table for adding a comment for the columns. Anybody knows how can i do it? thanks // for table ALTER TABLE myTable COMMENT 'Hello World' // for columns // ??? ...

performing full text searches on huge amount of docs

Suppose you have huge amount of documents, a few millions, and more coming. Suppose you have to publish them on a website with hundreds of thousands of daily pageviews, and suppose you have to let the visitors perform searches on all the documents. What is the best way to store/retrieve the documents? And to perform searches on them? Cu...

Zend_Db order by field Value

I'm outputting the contents of a select menu from a model using this: $select = $this->select(); $select->order('name'); return $this->fetchAll($select); However, what i want to do is order by a specific value, and then by the name column. The SQL would look like this: SELECT * FROM `names` ORDER BY `name` = 'SomeValue' DESC,`name` ...

Why sql-script isn't executed?

CREATE TABLE PERMISSIONS( ID BIGINT NOT NULL PRIMARY KEY, NAME VARCHAR(255) NOT NULL, UNIQUE(ID) ) CREATE TABLE ROLES( ID BIGINT NOT NULL PRIMARY KEY, NAME VARCHAR(255) ) I want to run this in MySql. When I try to execute separately each create-query everything works fine but they don't work together. I thought that separ...

MySQL - How do I get SUM of a COUNT of a field?

I'm using the following statement and getting almost everything I need. In addition, I'd like to get the SUM of all of the row_total results. SELECT c.id AS campaign_id, c.amount AS campaign_amount, c.warning_trigger, COUNT(cc.id) as code_count, DATEDIFF(c.end_date, CURDATE()) as days_remain, SUM(c.amount) as row_total FROM campaign...

Why does MySQL Workbench produce incompatible syntax for simple table create on export?

Using MySQL Workbench 5.0.30 OSS, I try to produce a simple SQL script to create tables, yet what it produces doesn't seem to be the least bit compatible with MySQL Ver 14.14 Distrib 5.1.42, for Win32 (ia32). Update: Here is the SQL generated from MySQL Workbench: CREATE TABLE IF NOT EXISTS `mydb`.`Table1` ( `idClaimNum` NOT NULL ,...

partial results from a long-running SELECT query?

We are issuing some long running queries on a mysql database. (The context is offline data analysis, not an application.) How we will proceed in research terms depends on the results we obtain along the way. It would be useful for us to be able to view (partial) results as they are generated by a SELECT statement -- before the query comp...

Select, where id = multiple from other select

I'm tired and I can't wrap my head around this. I have two tables: table menu @id = id, @nr = id of parent |======|======| | id | nr | |======|======| | 10 | 9 | | 11 | 10 | | 12 | 10 | | 13 | 10 | |======|======| table content @id = id, @mf = menu - first in tree, @mi = menu item linked to content |======|======...

mysql query optimization

Hello, This is the table structure: for_id from_id for_folder from_folder deleted The number 2 represents my ID and this is the query: SELECT * FROM poruke_konverzacija WHERE ( ( for_id =2 AND for_folder = "inbox" ) OR ( from_id =2 AND from_folder = "inbox" ) ) AND deleted !=2 I have near 500k records on that table and when i run...

A Beginner Question on database design

Hi All,this is a follow-up question on my previous one.We junior year students are doing website development for the univeristy as volunteering work.We are using PHP+MySQL technique. Now I am mainly responsible for the database development using MySQL,but I am a MySQL designer.I am now asking for some hints on writing my first table,to g...

Reliable backups for huge mysql databases?

I have 200GB / 400Mrows mysql/innodb database - far beyond what's reasonable as I found out. One surprising problem is restoring backups. mysqldump generates huge sql files, and they take about a week to import back into a fresh database (attempts at making it faster like bigger/smaller transactions, turning off keys during import etc.,...

MySQL: Appending records: find then append or append only.

I'm writing a program, in C++, to access tables in MySQL via the MySql C++ Connector. I retrieve a record from the User (via GUI or Xml file). Here are my questions: Should I search the table first for the given record, then append if it doesn't exist, Or append the record, and let MySQL append the record if it is unique? Here is...

read-access to a MyISAM table during a long INSERT?

On mysql and using only myisam tables, I need to access the contents of a table during the course of a long-running INSERT. Is there a way to prevent the INSERT from locking the table in a way that keeps a concurrent SELECT from running? This is what I am driving at: to inspect how many records have been inserted up to now. Unfortuna...

Searching for base CMS

I'm searching for a PHP/MySQL based CMS with some basics like Register/Login. It shouldn't be that complex like TYPO3. Drupal and Joomla I don't want. ...

How do copy a modify field of a table to a new field in the same table?

I have a MySQL database table C with a field called Phone. I've already created a new field in the C table called PhoneFixed and I want to update that field with a modified version of the Phone field. The PhoneFixed field will use the following statement to get its value: Concat('(',Left(C.Phone,3),') ',Right(C.Phone,8)) As `PhoneFixed...

How can I write this MYSQL query without using UNION?

I'd like to do a single query on one table without using UNION Here are the two queries that I need to combine. SELECT field1, field2, field3 FROM table1 WHERE field4 != 'condition1' AND feild3 >= 'condition2' ORDER BY field3 ASC LIMIT 20; SELECT field1, field2, field3 FROM table1 WHERE field4 != 'condition1' AND feild3 < 'condition2' ...

Is using a database-level MD5 function a bigger security risk than an application level function?

I've got a chunk of code that validates a user's username and password, which goes something like this: $sql = "SELECT * FROM user WHERE username='{$_POST['username']}' AND password=MD5('{SALT}{$_POST['password']}')"; Is this any more/less secure than doing it like this? $sql = "SELECT * FROM user WHERE username='{...

MySQL group by url data rows that share root domain.

Currently have a database formatted as follows: id (unique id) url: http://domain.com/page.html Urls are both from the same domain and from different domains. This also needs to be accomplished in one query so that I can use things like limits when displaying the data to the end user. Data 1, http://domain.com/about.html 2, h...