mysql

Pass index to temporary table from regular table?

I am creating a temp table with a query like this: CREATE TEMPORARY TABLE temp_table SELECT * FROM regular_table WHERE 1 But regular_table has FULLTEXT index on some of the fields. I try to do a FULLTEXT search on the new temporary table and I get an error telling me "Can't find FULLTEXT index matching the column list". So obviusly th...

Future proofing FULLTEXT search..

I have a FULLTEXT index called cart_product_fti on a table Whenever I use it, I have to list all the fields that are in the index exactly: MATCH (foo,bar,etc) My thoughts are, if later I decide to add/remove a field to the index, all the scripts are going to have to be updated or they will be broken. So I am wondering if there is a...

disable foreign key check deleting InnoDB tables Perl Script

Am fairly new to using MySQL and a total novice at Perl but am trying to hack someone elses script to help me out. I got the script from here. It looks great so far but it fails as the tables have some foreign key check going on. I could go through phpmyadmin and try and delete them all one by one but this takes for ever and is my thi...

Mysql many to many query

Having a mental block with going around this query. I have the following tables: review_list: has most of the data, but in this case the only important thing is review_id, the id of the record that I am currently interested in (int) variant_list: model (varchar), enabled (bool) variant_review: model (varchar), id (int) variant_rev...

Problem using Perl to connect to MySQL database on remote server

I have a Perl script that gets data from a MySQL database on one server (let's call it server1), does stuff with it and writes it out to another database on another server (server2). Both servers are remote to the server that runs the Perl script. I can connect to the DB on server1 OK, but when I try to connect to the DB on server2, usi...

selecting records in mysql db from 1, 7, and 30 days ago with datetime and php

Im using the following query to target results that are exactly X days older than current time. SELECT *, DATE_FORMAT(datetime, '%m/%d/%Y') FROM table WHERE datetime BETWEEN SYSDATE() - INTERVAL 30 DAY AND SYSDATE() ORDER BY ID DESC Problem is its returning data from current day if a record from ...

go up through hirachical data for specific row in the same table (row = location in opengeodb)

Hi! I need to query the opengeodb to figure out the 'Bundesland' to a given location. You can find information to the DB schema at DB Schema. Sorry there is only German documentation text, but the graphical schema on this page is in english. To make it possible that the opengeodb is extended later the data is stored in a special way, w...

how to disable mysql's match query's 50% exclusion

So apparently if a Mysql table's fulltext index contains a keyword that appears in 50% of the data rows, that keyword will be ignored by the match query So if I have a table with the fulltext index 'content' which contains 50 entries and 27 of the entries contains the word 'computer' in the content field, and I run the query: SELECT *...

Using MySql with Entity Framework 4 and the Code-First Development CTP

I thought I'd experiment a bit with Scott Guthrie's latest post on code-first dev with Entity Framework 4. Instead of using Sql Server, I'm trying to use MySql. Here are the relevant parts of my web.config (this is an Asp.Net MVC 2 app): <connectionStrings> <add name="NerdDinners" connectionString="Server=localhost; Databas...

Deleting a row from a MySQL database

Hello, I have a MySQL table called "privatemessage" with a primary key field called "messageid." I would like to have a PHP query that deletes any row from "privatemessage" where "messageid = $messageid." What sort of query does this? Thanks in advance, John ...

concat tables with different content

Hi, I am currently building a small web application for publishing stories using PHP and MySQL. Each story may either be a article (with lots of fields and data), just a hyperlink/url, just an image, or a downloadable file with certain attributes (once again with lots of fields). My problem is related to finding the optimal database la...

Salting in PHP and MySQL

I have been developing a login library for a website using CodeIgniter. The authentication code is as follows: function signin($username, $password) { $CI =& get_instance(); $query_auth=$this->db->query('SELECT user_id, banned FROM user WHERE username=? AND password=SHA1(CONCAT(?,salt)) LIMIT 1', array($username, $password)); ...

Update mySQL table based on row=column name

A little background so you understand what I'm trying to do. I am making a page that takes two user input products and checks to see if they are compatible with one another. This is an n+1*n mySQL table because there are n products. The table looks like name a b c d e a b c d e I already know that every product is comp...

use mysql SUM() in a WHERE clause

suppose I have this table id | cash 1 200 2 301 3 101 4 700 and I want to return the first row in which the sum of all the previous cash is greater than a certain value: So for instance, if I want to return the first row in which the sum of all the previous cash is greater than 500, is should return to row 3 How do I d...

PHP beginner: How to "Order by" that query?

Hello, I have a rather (I think) easy problem to solve but being a PHP newbie, I can't seem to get the answer... The query below, print a cloud of tags on my page. I'd like to add "ORDER BY TagID ASC" to this query so that the tags appeared according to their ID #, but I have no idea, where to put it (I didn't write that query, obvious...

Refactor Foreign Keys with Update after Primary Key change

We ran into a problem with out primary key. It was set to a meaningful value for ease of data entry since all data was originally added directly. However now the meaningful value is not always present in all entries. So now we are moving to an auto-generated, non-meaningful key. But I have to update the database to reflect this. So ...

How to restore data from MYSQL dump without any duplicate key errors?

Everytime I try restoring the dump, I get : ERROR 1062 (23000) at line 10297: Duplicate entry 'spaß' for key 'PRIMARY' I am trying to restore it using: mysql -u root -ppassword database < 0719.sql ...

Basic optimisation with an index for mysql

Hi, i have a question about basic mysql database optimisation. I have 3 tables, Articles, Tags and Taggings (which is a join table). Articles Taggings Tags id id id name article_id name tag_id I am retrieving the articles that exactly match the ...

Doctrine relation lost on record update

I'm using Symfony and Doctrine, and have several many-to-many relations that work fine. But there is a table called "document" that holds documents that can relate to several kind of contents, and has an admin section of it's own. Whenever I update a Document, every relation it had to the rest of the tables is lost. I Googled it for a...

Combine two columns together from separate tables

Let's say for instance: I have two tables: old_data and new_data. Both old_data and new_data have one column called this_is_col. Both old_data and new_data have various (hundreds) of rows of dates (2010-02-06, 2010-01-09, 2007-06-02, etc.). Both tables don't necessarily have the same dates, but they both have the same format. The fiel...