mysql

In a database, would you use a date field or year and month fields if you only need year and month?

I am setting up a table where I need the year and month. In MySQL I believe I have 2 options: (1) 2 fields: 1 for year, 1 for month or (2) a date field (the day would always be 1). The 2 fields has the advantage of being some what faster (I think) because MySQL doesn't have to convert the value from a date to an integer although this is...

String formatting in MySQL

Is there a printf()-like formatting in MySQL? I couldn't find a straightforward way in the documentation. For example, how can I make something like: SELECT STRFORMATFUNCTIONIMLOOKINGFOR("%03d", 17) to get 017 ? ...

Special characters in PHP / MySQL

Hi, I have in the database words that include special character (in Spanish mostly, like tildes). In the database everything is saved and shown correctly with PHPmyAdmin, but when I get the data (using PHP) and display it in a browser, I get a weird character, like a "?" with a square... I need a general fix so I don't need to escape eac...

How to avoid double backslash in MySQL's SELECT INTO OUTFILE

In MySQL, the following: SELECT '\\' INTO OUTFILE 'c:/temp/backslash.dump'; writes two backslashes to the file, which makes sense. Trying to dump a single backslash, I changed SQL_MODE, like that: SET SESSION SQL_MODE='NO_BACKSLASH_ESCAPES'; SELECT '\' INTO OUTFILE 'c:/temp/backslash.dump'; but got two backslashes again. Is t...

Can you use auto-increment in MySql with out it being the primary Key

I am using GUIDs as my primary key for all my other tables, but I have a requirement that needs to have an incrementing number. I tried to create a field in the table with the auto increment but MySql complained that it needed to be the primary key. My application uses MySql 5, nhibernate as the ORM. Possible solutions I have though...

Elegant database design help... (MySQL/PHP)

Hi, I'm building a movies website... I need to display info about each movie, including genres, actors, and a lot of info (IMDB.com like)... I created a 'movies' table including an ID and some basic information. For the genres I created a 'genres' table including 2 columns: ID and genre. Then I use a 'genres2movies' table with two colum...

Innodb: does the space occupied by deleted rows get re-used?

I have read several times that after you delete a row in an InnoDB table in MySQL, its space is not reused, so if you make a lot of INSERTs into a table and then periodically DELETE some rows the table will use more and more space on disk, as if the rows were not deleted at all. Recently I've been told though that the space occupied by ...

How can I sum values in a MySQL query?

I am using MySQL. I have a P_id that should be able to find the max value. I want to show the sum of two formulas by following: Then Sum(max(pval)*S_val*Estimate) Sum(max(pval)*P_val*Analyze) Finally sum the Both above I used the following function but it gives me an error: > id Displa...

Implementation of Levenshtein distance for mysql/fuzzy search?

I would like to be able to search a table as follows for smith as get everything that it within 1 variance. Data: O'Brien Smithe Dolan Smuth Wong Smoth Gunther Smiht I have looked into using Levenshtein distance does anyone know how to implement this with it? ...

Does anybody know a command line tool for formatting MySQL queries?

I regularly have to debug large MySQL queries from a logfile and am looking for a tool to format and indent them - preferably windows command line, but will consider anything. ...

What is the best approach to list a user's recent activities in PHP/MySQL?

I want to list the recent activities of a user on my site without doing too many queries. I have a table where I list all the things the user did with the date. page_id - reference_id - reference_table - created_at - updated_at The reference_id is the ID I need to search for in the reference_table (example: comments). If I would do a ...

ODBC Connection to iSeries Giving Odd Number of Results

I'm using UnixODBC with PHP 5.2.4 on Ubuntu 8.04 LTS and trying to pull all the results from a table sitting on an iSeries and replicate them to a local MySQL table. Code-wise it is working with no errors but I'm ending up with more rows that what is contained on the iSeries. I should end up with 25,613 rows but PHP reports that 25,6...

Dynamic Titles

I have a site that has a main page and subpages for the articles comments. What I want it is for the titles of the pages of the comments to be the same as the article's page title. So I have this on the page file, the base for all the comments pages: class Page { private $title; private $scripts; private $css; functi...

How can I get both the count of a subset as well as the count of the total set in one query?

In the simple case, assume I have a table that looks like this: mysql> describe widget; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | name | varchar(255) | YES | | NULL | | | enabled | smal...

Select newest record matching session_id

I should have paid more attention in relational databases class, so I need some help. I have a table structured like so (there's more, but I'm only posting relevant info): +------------------+-------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------...

How do I specify unique constraint for multiple columns in MySQL?

I have a table: table votes ( id, user, email, address, primary key(id), ); Now I want to make the columns user, email, address unique (together). How do I do this in MySql? Thanks in advance. Of course the example is just... an example. So please don't worry about the semantics. ...

Why do I get the MySQL Error "Query was empty"?

$id = $_REQUEST['id']; $Section = $_REQUEST['section']; $Subject = $_REQUEST['subject']; $type = $_REQUEST['type']; $Start_date1 = isset($_REQUEST['startTxt'])?($_REQUEST['startTxt']):""; $Venue = isset($_REQUEST['venTxt'])?($_REQUEST['venTxt']):""; $Facilitator = isset($_REQUEST['faciTxt'])?($_REQUEST['faciTxt']):""; $Level = isset($_RE...

Select only unique rows in mysql

I want to select only rows that are unique according to the column userid from a mysql table. So if I have two rows with the same userid none of them gets selected but if its only one it gets selected. I use the following code: SELECT T.id,T.name,T.userid FROM tbltest T WHERE userid NOT IN (SELECT userid FROM tbltest WHERE tbltest.id<>...

How to Properly Convert or Query Date Range for Rails / MySQL DateTime Column

I have a rails application where I store created_at as datetime (standard). I am building a form for searching and I find I have to use find_by_sql to do some complex subqueries. The form has a date range (no time) to search on for items created_at field. The problem I find is that if I pass in just the date string for range to query......

How do you select rows from a mysql DB where a column begins with a number?

Beginning with a letter I know how to do: WHERE mov_title REGEXP CONCAT('^(the )?', '$letter') And this method will work if I substitute $letter with any number, so if its set to 1, it will find all records that begin with 1, but I need it to work for any number 0-9. How could I modify the query? ...