mysql

Correct way to set up MySQL tables

I can't work out what I should be doing here... I have a database with around 20,000 records. Each of these records has about 20 columns to it. I want to add around 20 or so additional columns to this database which would be on the lines of a load of different URLs for each record. Mostly, these will be blank. What's the "right" way o...

mySQL: Deleting with JOIN ?

Hi everybody, This problem is giving me a real headache. I have two tables in my database that solves sorting of categories with the help of a design called "Closure table". The first table is called categories, and the other one is named categoriesPaths. Categories is simply a language-based table: id | name ---------------- 0 | C...

SQL select all items of an owner from an item-to-owner table

I have a table bike_to_owner. I would like to select current items owned by a specific user. Table structure is CREATE TABLE IF NOT EXISTS `bike_to_owner` ( `bike_id` int(10) unsigned NOT NULL, `user_id` int(10) unsigned NOT NULL, `last_change_date` date NOT NULL, PRIMARY KEY (`bike_id`,`user_id`,`last_change_date`) ) ENGINE=...

Running rake migrate gives Mysql error schema_migrations.frm

I am running 5.1 on Ubuntu 10.04, and the remote server is running 5.0.51, is this a problem? Mysql::Error: Incorrect information in file: './user/schema_migrations.frm': SELECT version FROM schema_migrations I should say that trying to install it gives the error of the package (mysql-server-5.0 referenced but not available). I believe...

What is the most efficient way to keep track of all user traffic inside a database

Currently I am using mysql to log all traffic from all users coming into a website that I manage. The database has grown to almost 11m rows in a month, and queries are getting quite slow. Is there a more efficient way to log user information? All we are storing is their request, useragent, and their ip, and associating it with a certain ...

Is there a way to use mysql stored proc variable as a php variable variables?

Here is my procedure... DELIMITER $$ CREATE PROCEDURE syncSites(IN m_id INT, IN p_aux VARCHAR(2), IN c_aux VARCHAR(2), IN price_override VARCHAR(8)) BEGIN INSERT INTO product SELECT m_id, product_id, product_name, IF(price_override IS NULL OR price_override = '', price, price_override)...

Check if email address already exists if so show message if not then add to table

Hey guys, I was hoping to get a little insight on this. What I have is a form collecting firstname, lastname, city, state, and email address. This form is using jquery validation plugin and the form plugin. I would like to check if email already exists... if so then spit out a message that tells them they already exist. This is what ...

Output MySQL query results in Django shell

I have the following Django Model which retrieves 3 records from a database. The class below represents a Model within a Django application I'm building. I realize that the parameters taken in by the create_hotspots function are not being used. I just simplified what the code looked like previously for the purposes of explaining my probl...

55 gig - comma delimited file - reading into mysql DB

any ideas on how to open this file? i have no choice whatsoever on the size of this file. its regular text data the reason i cannot break up the file is because it is stored remotely and the ONLY thing i can do is run sql statements on it please help! ...

mysql custom sorting first alpha then numeric using case when

How can you sort a query using ORDER BY CASE WHEN REGEXP? or other alternatives? I don't want to use UNION. Thank you mysql> SELECT `floor_id`, `floor_number` FROM `floors`; +----------+--------------+ | floor_id | floor_number | +----------+--------------+ | 1 | 4 | | 2 | 7 | | 3 | G ...

How to make <option selected="selected"> set by MySql and PHP?

How to make <option selected="selected"> set by MySql and PHP? My code: echo '<select>'; $tempholder = array(); $rs = mysql_query("SELECT * FROM id ORDER BY year"); $nr = mysql_num_rows($rs); for ($i=0; $i<$nr; $i++){ $r = mysql_fetch_array($rs); //if($year==$r["year"]){ $selected=' selected="selected"'; }//doesn't work so ...

Not able to create a datasource registration with WAMP MysQL 5.0 in DBArtisan

I try in a test version of DBArtisan to set up a datasource and register both servere as localhost, database to use, my username and password, but, when testing the connection, I get message that connection failed. Hooking up from eg. Delphi and other utilities run nicely. But not DBArtisan, Is MySQL 5.0 not supported yet ?? Or is ...

Alphanumeric Order By in Mysql

Hi, How can i make only numeric order by when the column containing alphanumeric characters in mysql ? column (name) is unique field. my table contains the records, id name 1 ab001 2 ab010 3 aa002 4 ac004 5 ba015 6 ba006 7 aa005 8 ac003 The results must be like this, id name 1 ab001 3 aa002 8 ac003 4 ac0...

Hard mySQL query problem

Hey all, I am up against a big problem with trying to figure out how to go about this query I am needing. I need to only display the companies that the user has in their account and also only display the company if they are on the calendar for that day. My userinfo database is this (only showing needed table info): ID | UserID | i...

Structure of a music database - multiple or single table?

Hi, I'm creating a music player, where the user can search for artists, albums, or songs. I have created a script that reads all the tags from the mp3s in the music library, and updates a database of songs, in a single table, containing artist names, albums, track titles, etc. Currently, this works well, because it can scan for any ch...

Why this doesn't work: [MySQL][ODBC 5.1 Driver]Access denied for user

So I know that there is a very similar question to this all over the web as well as stack overflow: http://stackoverflow.com/questions/2539961/error-access-denied-for-user-mysql-server but my question I think is different. So I have set up a linked server and it works...I have the correct permissions set up for my IP's that I am using....

Weird problems with mysql outfile under FreeBSD

See my answer below. Leaving this up in case it helps someone else. Hi guys, What follows is a series of attempts to dump a query to an outfile on a new FreeBSD box that my site has moved to. The results are the same if I log in as me or if I log in as root. I hope the style isn't too annoying. I have my comments commented out arou...

How to display comments and their replies using PHP & MySQL.

I was wondering how can I display comments and their replies using PHP & MySQL. An example would help me out very much as I don't know how to do this. In particular, a visual example would be great and I can take it from there. Thanks! Here is my MySQL table. CREATE TABLE comments ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, parent_comme...

Symfony 1.4 setter type double precision

Hi volks, I want to save a number with 14 decimal places, but symfony only saves 6. How can I control this: $loc->setSinRadLon(0.73946213661883); In the schema the column looks like: sin_rad_lon: { type: double(), scale: 14, notnull: true } The DB stetting is: sin_rad_lon double(18,14) In the DB the column value is: 0.73946...

mysql_field_type differentiate between varchar and text

I need to get the datatype of a column, but both varchar and text datatypes both return the value 'string' under the mysql_field_type function in php. Is there a better, more specific method of checking a columns datatype? ...