mysql

MySQL ExecuteNonQuery hanging in mono

I am in the process of moving from Visual Studio to Mono. I have a query which dumps about 190000 rows of a select statement into a a text file which I process from c#. Normally the whole thing goes well and is done in less than 7 seconds. On mono however, it gets to the ExecuteNonQuery, and the OUTFILE is created successfully of exac...

What would the most efficient index type and table engine be for md5 lookups?

I have a table that contains a few columns and one of them is an md5 hash which is a unique key in the table. What would be the most efficient engine and index type (hash/b-tree) for the purposes of determining if a hash already exists in the table or not? I expect to have billions of rows across 200 partitions (mysql5.1) Right now I ...

Automatically generate SQL INSERT statements with data that respect database constraints

Here is a simple situation. (My apologies my SQL is quite rusty.) (I am using MySQL and Java but it should not matter too much.) Let's say I have two tables. Here is a simple SQL statement: A: insert into patient (patient_id) values (16) Now there is another table person, which has a person_id and that has to be constrained to ma...

MySQL - how many rows can I insert in one single INSERT statement?

depends on the number of values sets? depends on the number of bytes in the INSERT statement? ...

UNION syntax in Cakephp

Anyone knows a good way to make UNION query in CakePHP? I would like to avoid using $this->query();. With two tables t1, t2: SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id UNION SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id With three tables t1, t2, t3: SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id LEFT JOIN t3 ON t2.id = t3.id U...

MySQL Database - Related Results from same table / Many to Many database design problem

Hello, I am designing a relational database of products where there are products that are copies/bootlegs of each other, and I'd like to be able to show that through the system. So initially during my first draft, I had a field in products called " isacopyof " thinking to just list a comma delimited list of productIDs that are copies o...

How to join two tables mysql?

Hello friends, I have two tables: services id client service and clients id name email How to list table service and bring together the customer name that the customers table? field customer services in the table has the id of the customer at the customer table, I appreciate the help from you now ...

if no row return then it's false when using php with mysql

I noticed that a lot of tutorial instructions often have this code: $sql="SELECT * from table"; $num=mysql_num_rows(); if ($num > 0) { do something } Why do they have to use that condition "if ($num > 0)" when I write this code that I think is compact and readable: $sql="SELECT * from table"; $itExists=mysql_num_rows(); if ($itExis...

MySQL does not support recursive functions? why? since when?

I've written a stored FUNCTION that calls itself, recursively. However when I run it in a query I get this shameless error: Error: 1424 SQLSTATE: HY000 (ER_SP_NO_RECURSION) Message: Recursive stored functions and triggers are not allowed. "Not allowed"? Right. Why don't we just disable WHILE loops also, while we're at it? ...

Choosing tables for one-to-many relationships in MySQL

I was wondering if I allowed my users to pick which categories their post will be displayed in and they can pick multiple categories from one to many should my MySQL tables look something like the tables below? If not what should I add or delete? Here is my MySQL tables. CREATE TABLE users_categories ( id INT UNSIGNED NOT NULL AUTO_...

MySQL: selecting rows where a column is null

I'm having a problem where when I try to select the rows that have a NULL for a certain column, it returns an empty set. However, when I look at the table in phpMyAdmin, it says null for most of the rows. My query looks something like this: SELECT pid FROM planets WHERE userid = NULL Empty set every time. A lot of places said to mak...

Use select in PHP, MySQL when I know exactly which column i need

Hello, now i use this code: $welcome_text = mysql_query("SELECT * FROM `text` WHERE `name` = 'welcome'"); while ($row = mysql_fetch_array($welcome_text, MYSQL_ASSOC)) { echo $row['content']; } Is it possible to use it without WHILE if i know exactly which one column i need? Something like this: $welcome_text = mysql_query("SELECT '...

Mysql pick/join multiple rows in one row

SELECT `a`.`department`, `a`.`category`, `a`.id, (SELECT group_concat(t_0) FROM images WHERE ad_id=a.id) t_0, (SELECT group_concat(t_1) FROM images WHERE ad_id=a.id) t_1, (SELECT group_concat(t_1) FROM images WHERE ad_id=a.id) t_8 FROM `ads` AS `a` WHERE (a.department = 1) AND (a.category = 12) AND (a.charged=1) ORDER BY `a`.`id...

how to define integer array as a field when creating new table in mySQL

how to define integer array as a field when creating new table in mySQL ...

I want to give JDBC connection to mysql. I dont wish have the data values inside my class people().

import java.util.*; import org.directwebremoting.util.Logger; public class People { public People() { people = new HashSet(); random = new Random(); log.debug("Generating a new set of random people"); for(int i = 0; i < 5; i++) people.add(getRandomPerson()); } public Set getAllPeople() { return people; } ...

Database issue, how to store changing data structure

Hi there, I'm building some application, the involves training programs. my issue is like this, A workout could be simple as this: 3 sets of 45 push ups. so I would just create 2 fields, sets / count BUT a workout could be also: 45 minutes run, 3 sets of 45 pushups, 2 minutes of rope jumping, 150 meter swimming. so i need to bu...

help regarding commenting system in PHP

Hi there, I am building a blog like application where an author creates a post and the users without registering comment on the post the comment will be displayed if and only approved by the author, the help i want is regarding the database table, i have created a table which will hold the records such as name, email, phone, location et...

MySql server startup error 'Manager of pid-file quit without updating file.'

I read in some forums that doing a giving a /bin/sh before the /etc/init.d/mysql start would solve the problem and it did. but we don't want to start it every time like that...what is the solution? I have installed the following packages from http://www.mysql.com/downloads/mysql/#downloads, I am using CentOS 5.: MySQL-community-debuginf...

SQL Comment table insert statement

Hi there, I want to develop a system where a user should be able to post the comments on the author published news. I am very much confused about the Insert Statement that i should be using to store the user commenting system, i have two mysql table one is news and another is comments below is the screenshot of two tables. news co...

Mysql access main table inside right joined subquery

SELECT id,region FROM ads RIGHT JOIN (SELECT * FROM ads a2 WHERE a2.region=ads.region LIMIT 4) AS regions ON regions.id=ads.id Says "unknown column ads.region". How to access main table column region? I need to fetch 4 rows for each region ...