mysql

Filter results by number of rows in mysql

I have three tables: houses id | square_feet ------------------ 1 | 2000 2 | 1600 3 | 1000 energies id | house_id | kwh ------------------------- 6 | 1 | 10 7 | 2 | 100 8 | 3 | 200 cars id | energy_id | gallons -------------------------- 11 | 6 | 20 12 | 6 | 40 13 | ...

SubSonic Error with MySql CONVERT()

I has encountered conversion from integer to string with MySql+SubSonic3 (it generates wrong SQL query). After I found root of the problem in SubSonic sources and fixed it, everything works fine, but I'm not sure that it won't pull some other stuff. I believe in MySqlFormatter.cs the following code on line 309 sb.Append...

What is a practical way to sync data in a directory to a database?

My situation involves a directory containing MP3 files, and a database that should contain all the MP3 metadata (i.e. genres, artist names, album names, and track names) from the MP3 files in that directory. The database should always reflect what is in the directory, that is... the algorithm I'm searching for should never delete items f...

MySQL password function

Is it considered good or bad practice to use MySQL's password function to hash passwords used by an application? I can see pros and cons. I'm curious if there is a general consensus on whether it is good or bad. ...

Using char index to find numeric values

I have a column on a mysql table that stores mostly numeric values, but sometimes strings. It's defined as VARCHAR(20). There is an index on this column for the first four characters. ADD INDEX `refNumber` USING BTREE(`refNumber`(4)); Since the field is mostly numeric, it is useful for the user to be able to query for values that fall...

Setting up visual studio c++ 2008 with mysql c api

Hello, I'm wondering if anyone can help me on getting my visual studio c++ project setup correctly to work with MySql? I downloaded and installed MySql Server, and installed the developer content with the include files, but beyond that I'm a bit lost. I tried adding the 'include' directory in my MySql install path to my additional incl...

Mysql weird connection problem

Hi guys! I've a weird problem. I've mysql 5.1 installed on my ubuntu 9.04. I've used it a long time (say 3 month) and everything was going right. Until i faced this really weird problem. When i want to connect to a random database i get this message: ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect......

How to handle "order" of a list of items in DB (php)

I would like my client to be able to order items in the backend. I figured the easiest way to do it is to have the database table for an item have a field called "order" which gets a value. This is a value the client puts in. Then I can just run SQL queries ordering by the "order" field. Right now I have it very simple.. there is no ...

Caching large number of pages on php site

I'm involved in a project that will end up creating around 10 million new pages on an existing site. The site, and the new project, are built with CodeIgniter and connecting to MySQL. I've never dealt with a site of this size before, and I'm concerned about how we should handle caching. Has anyone dealt with caching on a PHP site of thi...

What is the best type to use for a column in MySQL containing the body of text output by a CMS?

I'm doing a quick and dirty CMS module for a site - just a jWYSIWYG editor. I've never really thought about the best type to use for the column that will contain the outputted XHTML from the editor. I have always used blob in the past, but am unsure why I picked that. Any recommendations? ...

Inserting a GROUP BY result into another table

Hello, I was migrating a field to a new table. The new table has three fields. What I tried was INSERT INTO foo VALUES ('', (SELECT bar FROM baz GROUP BY bar), ''); This resulted in an error due to multiple rows resulting from the select. What is the right way to do this? TIA jg ...

Weird utf8 conversion problem in php

So I'm working on a project that is taking data from a file, in the file some lines require utf8 symbols but are encoded oddly, they are \xC6 for example rather than being \Æ If I do as follows: $name = "\xC6ther"; $name = preg_replace('/x([a-fA-F0-9]{2})/', '&#$1;', $name); echo utf8_encode($name); It works fine. I get this: Æther ...

MySQL Field Concatenation

Given the following table structure: CREATE TABLE foo ( ID INT NOT NULL AUTO_INCREMENT, Category INT NOT NULL, Name VARCHAR(50) NOT NULL, PRIMARY KEY (ID)) Containing data: ID Category Name 1 1 Item 1-1 2 2 Item 2-1 3 1 Item 1-2 4 2 Item 2-2 How do I construct a query to return an every possi...

MYSQL Inner Join with 2 ON Clauses

I am trying to setup a simple database in which I have a user and store both their residential and postal address. I have 2 tables Users id (Primary Key) name (Varchar 255) residential_id (foreign key) postal_id (foreign key) Address id (primary key) type (enum of R and P) street (varchar 255) suburb (varchar 255) I am tring ...

ORDER BY addedOn DESC LIMIT in Mysql

I see ORDER BY addedOn DESC LIMIT in Mysql command in our apps i don't know Means of ORDER BY addedOn DESC LIMIT so what is ORDER BY addedOn DESC LIMIT ...

MySQL, Permission Issue

Hello, I have a MySQL database that has several databases used by more than 1 web app. I need to now create a new Database that can only be accessed via a new user account. I've been using Navicat v5.3.3 manage users to try to set this up but it doesn't seem to work and I don't know if that's bec of MySQL or Navicat. But given that I ...

Unknown column {0} in on clause

Hi, I'm doing some work on a MySQL database... and I'm completely stumped as to why I'm receiving the error 1054 - Unknown column 'Puzzles.PuzzleID' in 'on clause' Both the table and the column names exist and are correct... I mean, I just created it Navicat's visual designer... SELECT `PuzzleCategories`.`PuzzleCategory`, `Clients`....

how to design this mysql database

there are 2 million users each user has 4 tables . the data in 4 tables is not going to be appended and will remain fix. the structure of each users tables will be same. To store the data of these users in mysql i have to design a database. do i need to create 2 million databases each with 4 tables ? any help appreciated the 4 tab...

View All Table / Database Constraints in MySQL

How do you view all the constraints (Primary keys, Secondary keys, Assertions , Triggers, etc) in a MySQL Database / Table in the command line environment. ...

How to select date from datetime column?

I have a column, which is datetime type, the value is like this: 2009-10-20 10:00:00 I want to do a select, my query: SELECT * FROM data WHERE datetime = '2009-10-20' ORDER BY datetime DESC Should I do this: SELECT * FROM data WHERE datetime BETWEEN('2009-10-20 00:00:00' AND '2009-10-20 23:59:59' ORDER BY datetime DESC But i...