mysql

First-time database design: am I overengineering?

Background I'm a first year CS student and I work part time for my dad's small business. I don't have any experience in real world application development. I have written scripts in Python, some coursework in C, but nothing like this. My dad has a small training business and currently all classes are scheduled, recorded and followed up...

Linked Server with MS SQL crashing / stopping after 5 sec

Hey, I have been having a problem with my linked servers. I am using Microsoft Server 2003 and Microsoft SQL Server Management Studio 2005. I am linking to a MYSQL database through Microsoft studio since all of our other databases are controlled through MSMS. The problem is that for any query that last for more than 5 sec I get this ...

MySQL Fulltext Stopwords Rationale

I am currently trying to develop a basic fulltext search for my website, and I noticed that certain words like "regarding" are listed as stopwords for MySQL fulltext searches. This doesn't bother me too much right now since people searching for a given news item wouldn't necessarily search using the word "regarding" (but I certainly can...

Why is it still possible to insert a foreign key that doesn't exist?

mysql> create table products(id integer unsigned auto_increment primary key); Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE orders ( -> id integer PRIMARY KEY auto_increment, -> product_id integer REFERENCES products (id), -> quantity integer, -> INDEX product_id_idx (product_id) -> ); Que...

mixing basic DataSource with connection pooling DataSource: when to call close()?

I'm in the process of adding connection pooling to our java app. The app can work with different rdbmses, and both as a desktop app and as a headless webservice. The basic implementation works fine in desktop mode (rdbms = derby). When running as a webservice (rdbms = mysql), we see that connection pooling is needed to get good concurr...

Concurrent access problem in mysql database

Hi i'm coding a python script that will create a number child processes that fetch and execute tasks from the database. The tasks are inserted on the database by a php website running on the same machine. What's the good (need this to be fast) way to select and update those tasks as "in progress" to avoid to be selected by multiple tim...

How to override NULL value from aggregate query using MySQLdb module in python?

I am selecting the maximum date value from a MySQL database table in python using the MySQLdb module. If the result comes back as null, I want to override it with a default value. I could do this in the MySQL using a sub-query, but would prefer to do it in python. Any recommendations on a simple way to do this? I figure this is an easy ...

Several similar DDL with different result in foreign key

This is a further discussion on this question: http://stackoverflow.com/questions/2321270/why-is-it-still-possible-to-insert-a-foreign-key-that-doesnt-exist This works: CREATE TABLE products ( id integer unsigned auto_increment primary key ) ENGINE=INNODB; CREATE TABLE orders ( id integer PRIMARY KEY auto_increment, pr...

How do I convert this INNER JOIN query from SQL Server to MySQL?

I'm currently migrating a customers application from ColdFusion on Windows with SQL Server to ColdFusion on Linux with MySQL and I'm running into some issues recreating their views with regards to joins. Can anyone help me work out how the following should be converted. SELECT <columns> FROM assetType INNER JOIN assets INNER JOIN ...

Most useful SQL meta-queries

I have a passion for meta-queries, by which I mean queries that answer questions about data rather than answering with data. Before I get a lot of justified criticism, I do realize that the approach of meta-queries is not ideal, as eloquently described here for example. Nevertheless, I believe they do have their place. (So much so that ...

Is this a good case for decomposing a table and having a 1-1 table relationship?

I am storing information about websites in a table. One set of information is the whois data about a websites domain name. This set of data contains about 40 fields and each record relates to a single website. I have no requirement to track updates. I could put all the whois data in the websites table, but it seems 'cleaner' and more int...

Email isn't inserted into mysql database from form.

I have a newsletter for one of my sites and I can't the email posted to the mysql database. Here is the html form code: subscribe <h2>newsletter</h2> <br /><input type="text" name="email" value="" id="email" /> <input type="button" name="submit" onclick="submit_it(...

C# MySqlParameter problem

(int) faultsGroup is 0 or 1 but i always get this error: Column 'FaultGroup' cannot be null Does anyone tell me why? Syntax looks ok. MySqlCommand cmdAdd = new MySqlCommand("INSERT INTO Faults (" + " FaultGroup, Text, Date, IP" + ") VALUES (" + " @FaultGroup, @Text, @Date, @IP" + ")", conn); MySqlPar...

SQL: Having a table twice in the FROM clause

I am using MySQL. Here is my schema: Suppliers(sid: integer, sname: string, address string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) (primary keys are bolded) I am trying to write a query that selects pairs of sids that supply the same part: -- Find pairs of SIDs that both su...

Delete confirmation in php

I'm trying to delete a record in mysql using php. What do I have to add in my code so that there will be a delete confirmation first. Because in my current code, it automatically deletes the record corresponding to the pnum inputted. <html> <style> input { font-size: 16px;} </style> <?php include('header.php'); ?> <div id="main_content...

how can I use curly braces in a mysql query?

I tried to do this: INSERT INTO test123 VALUES(Some text, 'More Text (and in braces.)'); but it doesn't work. The braces in the string 'More Text (and in braces.)' cause the syntax error. How can I make it work - I need mysql to accept the braces. ...

filter mySQL database with multiple words in any order in Concatenated field

I have Concatenated in mySQL to produce a field that I can search with. This contains animal as well as owner names and addresses for a client database. I need to be able to search by animal name, owner names and postcode in any order. Thus if if my name is john smith and i own a dog called Fido and live in postcode AB1 2CD, I want the s...

how can i share data between PHP pages?

I have a PHP page and I want to share some data between pages like UserID, password. I'm learning about sessions and I'm not sure if Im using it correctly. <?php require_once('database.inc'); $kUserID = $_POST['kUserID']; $kPassword = $_POST['kPassword']; if (!isset($kUserID) || !isset($kPassword)) { header( "Location: http://dom...

Why is MySQL it outputting data from, effectively, two different queries?

Gday All, I am trying to get the details of the first ever transaction for a given customer within a period of time. Consider the following: SELECT MIN(t.transaction_date_start), t.* FROM transactions t WHERE t.customer_id IN (1,2,3) AND t.transaction_date_finished >= '2010-02-01 00:00:00' AND t.transaction_date_finished <= '2010-02-...

creating a dynamic php insert into mysql function?

hi all, i have a post with a LOT of variables, and I was wondering if there was some way of inserting the information into mysql dynamically, rather than typing it all out manually, as the post variables change depending on what the user selects. ...