mysql

What's the best free MySQL backup solution on a windows server?

I was wondering what is the best free MySQL backup solution on a windows server? I am interested in backing up the data only. Constraints free solution backup should be done to somewhere outside the server windows only mysql only if you provide a suggestion with batch files & co. please add source code. thx. ...

join query and what do i do w/ it to display data correctly?

i have a table that stores all the volunteers, and each volunteer will be assigned to an appropriate venue to work the event. there is a table that stores all the venues. it stores the volunteer's appropriate venue assignment into the column 'venue_id' table: venues columns: id, venue_name table: volunteers_2009 columns: id, lname, fn...

Populate select drop down from a database table

I have a table ("venues") that stores all the possible venues a volunteer can work, each volunteer is assigned to work one venue each. I want to create a select drop down from the venues table. Right now I can display the venue each volunteer is assigned, but I want it to display the drop down box, with the venue already selected in th...

MySQL and comments

Is it possible to add comments somehow, somewhere? I don't pretend to be any sort of expert when using MySQL and certainly don't spend all day in it. More often than I would like I forget how I intend to use a column (usally the bit ones) and would be very excited if I could add a comment to remind me if 1 is good or bad, for example...

How do you join on the same table, twice, in mysql?

I have 2 tables. One (domains) has domain ids, and domain names (dom_id, dom_url). the other contains actual data, 2 of which columns require a TO and FROM domain names. So I have 2 columns rev_dom_from and rev_dom_for, both of which store the domain name id, from the domains table. Simple. Now I need to actually display both domain n...

Can you use an alias in the WHERE clause in mysql?

I need to use an alias in the WHERE clause, but It keeps telling me that its an unknown column. Is there any way to get around this issue? I need to select records that have a rating higher than x. Rating is calculated as the following alias: sum(reviews.rev_rating)/count(reviews.rev_id) as avg_rating ...

how do you know how to design a mysql database when creating an advanced php application?

i've never created a shopping cart, or forum in php. aside from viewing and analyzing another persons project or viewing tutorials that display how to make such a project or how to being such a project. how would a person know how to design the database structure to create such a thing? im guessing its probbably through trial and error.....

Update column from another table - mySQL 3.5.2

Hi, I've tried a couple of approaches to update a column in a mySQL database table from another table but am not having any luck. I read somewhere that version 3.5.2 does not support multi-table updates and I need a code-based solution - is that correct? If not can anybody point me in the right direction using sql? UPDATE products ...

SQL(?): Counting the time between two datetime values

Hi, What is the best way to count the time between two datetime values fetched from MySQL when I need to count only the time between hours 08:00:00-16:00:00. For example if I have values 2008-10-13 18:00:00 and 2008-10-14 10:00:00 the time difference should be 02:00:00. Can I do it with SQL or what is the best way to do it? I'm buildi...

How do I see all foreign keys to a table or column?

In MySQL, how do I get a list of all foreign key constraints pointing to a particular table? a particular column? This is the same thing as this Oracle question, but for MySQL. ...

How to make MySQL handle UTF-8 properly

One of the responses to a question I asked yesterday suggested that I should make sure my database can handle UTF-8 characters correctly. Anyone know how I can do this with MySQL? Thanks! Ben ...

Pure-SQL Technique for Auto-Numbering Rows in Result Set

I'm looking for a way to sequentially number rows in a result set (not a table). In essence, I'm starting with a query like the following: SELECT id, name FROM people WHERE name = 'Spiewak' The ids are obviously not a true sequence (e.g. 1, 2, 3, 4). What I need is another column in the result set which contains these auto-numbering...

How do you write a case insensitive query for both mySQL and postgres?

I'm running a mySQL database locally for development, but deploying to Heroku which uses postgres. Heroku handles almost everything, except that my case insensitive Like statements become case sensitive. I know I could use iLike statements, but my local mySQL database can't handle that. What is the best way to write a case insensitive q...

How would I write this 3 level deep mysql query?

This might be a little hard to explain, but I will try. I want to display a list of categories (stored in 1 table), and number of domains associated with each category (stored in another table). The monkey wrench in this case is that each domain has a set of records associated with it (which are stored in a 3rd table). I only want to ...

Strange MySQL syntax error

I get this error:- You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' at line 1 whenever I tried something like this:- mysql> source /home/user1/sql/ddl.sql mysql> source /home/user1/sql/insert.sql mysql> source /home/user1/sql/cleanup.sql The in...

Does field size affect query time?

My question is in regards to MySQL, but I also wonder how this affects other databases. I have several fields that are varchar(255) but my coworker insists if they were varchar(30) -- or any smaller size -- then queries would run faster. I'm not so sure, but if it's so I'll admit to it. :) Thanks. ...

Select from same table as an Insert or Update

Clearly the following is incorrect. INSERT INTO `aTable` (`A`,`B`) VALUES((SELECT MAX(`A`) FROM `aTable`)*2),'name'); I get the value: SQL query: INSERT INTO `aTable` (`A`, `B` ) VALUES ( ( SELECT MAX(`A`) FROM `aTable` ) *2 , 'name' ) MySQL said: 1093 - You can't specify target table 'aTable' for update in FROM clau...

MS SQL - MySQL Migration in a legacy webapp

I wish to migrate the database of a legacy web app from SQL Server to MySQL. What are the limitations of MySQL that I must look out for ? And what all items would be part of a comprehensive checklist before jumping into actually modifying the code ? ...

MySQL LEFT JOIN SELECT not selecting all the left side records?

I'm getting odd results from a MySQL SELECT query involving a LEFT JOIN, and I can't understand whether my understanding of LEFT JOIN is wrong or whether I'm seeing a genuinely odd behaviour. I have a two tables with a many-to-one relationship: For every record in table 1 there are 0 or more records in table 2. I want to select all the ...

How to change MySQL root password to default?

My initial installation for the MySQL had no password for root. I assigned a password for root and everything worked fine. Due to some reason (don't ask why) I had to revert back to the original settings where root didn't have any password. I changed the root password to '' (empty string). The problem now is that MySQL doesn't run with ...