mysql

How much difference does BLOB or TEXT make in comparison with VARCHAR()?

If I don't know the length of a text entry (e.g. a blog post, description or other long text), what's the best way to store it in MYSQL? ...

Selecting unique rows in a set of two possibilities

The problem itself is simple, but I can't figure out a solution that does it in one query, and here's my "abstraction" of the problem to allow for a simpler explanation: I will let my original explenation stand, but here's a set of sample data and the result i expect: Ok, so here's some sample data, i separated pairs by a blank line -...

How to select from Varchar where where `Value` is not part of a group

I'm trying to do this SELECT `Name`,`Value` FROM `Constants` WHERE `Name` NOT IN ('Do not get this one'|'or this one'); But it doesn't seem to work. How do I get all the values, except for a select few, without doing this: SELECT `Name`,`Value` FROM `Constants` WHERE `Name` != 'Do not get this one' AND `Name` != 'or this one' T...

Possible to use SQL to sort by date but put null dates at the back of the results set?

I have a bunch of tasks in a MySQL database, and one of the fields is "deadline date". Not every task has to have to a deadline date. I'd like to use SQL to sort the tasks by deadline date, but put the ones without a deadline date in the back of the result set. As it is now, the null dates show up first, then the rest are sorted by dead...

What's the best way to search a MySQL database with PHP?

Say if I had a table of books in a MySQL database and I wanted to search the 'title' field for keywords (input by the user in a search field); what's the best way of doing this in PHP? Is the MySQL LIKE command the most efficient way to search? ...

Loading UTF-8 encoded dump into MySQL

Hi, I've been pulling my hear out over this problem for a few hours yesterday: I've a database on MySQL 4.1.22 server with encoding set to "UTF-8 Unicode (utf8)" (as reported by phpMyAdmin). Tables in this database have default charset set to latin2. But, the web application (CMS Made Simple written in PHP) using it displays pages in u...

Unknown Column In Where Clause

I have a simple query: SELECT u_name AS user_name FROM users WHERE user_name = "john"; I get "Unknown Column 'user_name' in where clause". Can I not refer to 'user_name' in other parts of the statement even after select 'u_name as user_name'? ...

Natural Sort in MySQL

Is there an elegant way to have performant, natural sorting in a MySQL database? For example if I have this data set: Final Fantasy Final Fantasy 4 Final Fantasy 10 Final Fantasy 12 Final Fantasy 12: Chains of Promathia Final Fantasy Adventure Final Fantasy Origins Final Fantasy Tactics Any other elegant solution than to split up th...

change default collation in phpmyadmin

It seems to me that phpMyAdmin imports tables by default with collation latin1_swedish_ci, how i change this? ...

How can I apply mathematical function to MySQL query?

I've got the following query to determine how many votes a story has received: SELECT s_id, s_title, s_time, (s_time-now()) AS s_timediff, ( (SELECT COUNT(*) FROM s_ups WHERE stories.q_id=s_ups.s_id) - (SELECT COUNT(*) FROM s_downs WHERE stories.s_id=s_downs.s_id) ) AS votes FROM stories I'd like to apply the following mathematic...

How can I use mysqldump to replicate views between accounts?

I'm using mysqldump to replicate a database between accounts on a particular machine. Everything works just great, except when we get to our defined views. Because the dump includes a line like the following ... /*!50013 DEFINER=`user_a`@`localhost` SQL SECURITY DEFINER */ ... when loading the dump into mysql on user_b we receive an...

What is the best way to store media files on a database?

I want to store a large number of sound files in a database, but I don't know if it is a good practice. I would like to know the pros and cons of doing it in this way. I also thought on the possibility to have "links" to those files, but maybe this will carry more problems than solutions. Any experience in this direction will be welcome...

mysql timestamp column

Hi, Is it possible to define a timestamp column in a MySQL table that will automatically be updated every time a field in the same row is modified? Ideally this column should initially be set to the time a row was inserted. Cheers, Don ...

Nested dropdown

Hi all, I'm building a form with php/mysql. I've got a table with a list of locations and sublocations. Each sublocation has a parent location. A column "parentid" references another locationid in the same table. I now want to load these values into a dropdown in the following manner: --Location 1 ----Sublocation 1 ----Sublocation 2 --...

How do I get PHP to work with ADOdb and MySQL?

I'm trying to get a PHP site working in IIS on Windows Server with MySQL. I'm getting this error Fatal error: Call to undefined function mysql_connect() in C:\inetpub...\adodb\drivers\adodb-mysql.inc.php on line 363 Update This link outlines the steps I followed to install PHP on my server: How do I get PHP and MySQL working on IIS...

Displaying whitespace in HTML when pulling from MySQL TEXT column

I have saved input from a textarea element to a TEXT column in MySQL. I'm using PHP to pull that data out of the database and want to display it in a p element while still showing the whitespace that the user entered (e.g. multiple spaces and newlines). I've tried a pre tag but it doesn't obey the width set in the containing div elemen...

MySQL commercial license costs

We are building a commercial product that is using MySQL as the back-end. The product will not be open source (at least not initially). We have had a very hard time getting any costing information from MySQL, they seem to want a percentage of the list price of the product, but no clarity is given on what that percentage is. Has anyone ...

How to import a SQL Server .bak file into MySQL?

The title is self explanatory. Is there a way of directly doing such kind of importing? ...

How do I import a whitespace-delimited text file into MySQL?

I need to import largish (24MB) text files into a MySQL table. Each line looks like this: 1 1 0.008 0 0 0 0 0 There are one or more spaces after each field, and the last field is tailed by about 36 spaces before the newline. How do I import such a file into MySQL? From the documentation it seems that LOAD DATA expects all fields...

MySQL query to append character to each entry

I have a table of users which has a username column consisting of a six digit number e.g 675381, I need to append a zero to each of these usernames e.g. 0675381 would be the final output of the previous example, is there a query that could handle this? ...