mysql

Mysql Join and Order

Hello, I have 3 tables that have same columns,but different data ( deposits,withdrawals,transfers ) CREATE TABLE IF NOT EXISTS withdrawals ( id int(11) NOT NULL auto_increment, user varchar(12) default NULL, amount double(12,2) default NULL, date timestamp NULL default CURRENT_TIMESTAMP, time varchar(50) defau...

SQL: Get a random entry iff condition is false.

Using Firebird: I want to select a random entry in the table if the first SQL query returns 0 rows. Is there anyway to combine these two queries? SELECT * FROM table WHERE cond=1; SELECT FIRST 1 * FROM table ORDER BY rand(); Im using ExecuteNativeQuery on the java-side which takes basic SQL statements. Sadly, If-Else statements don'...

pcntl_fork and the MySQL connection is gone

I have a foreach loop that forks within it. After the process forks, it accesses the database. I get an error: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away The thing is, I'm connecting to the database after I've forked. My question: Why would this be happening? If this happens, am I actually accessing the database...

Is there a performance difference in MySQL between IN and several OR clauses?

Is there any performance difference between the two following queries: SELECT foo,bar FROM table WHERE id IN (1,2,3); and SELECT foo,bar FROM table WHERE id = 1 OR id = 2 OR id = 3; I'm sure the difference in negligible in a small example like this, but what about when the number of valid IDs is much larger? ...

How to create an array from this result set (nested categories stored in databased with traversal model)?

Based on this question: http://stackoverflow.com/questions/1310649/getting-a-modified-preorder-tree-traversal-model-nested-set-into-a-ul The logic bellow is used to build an ordened list, but how to do the same with an array? I want to build a nested array. // bootstrap loop $result = ''; $currDepth = -1; // -1 to get the outer <ul...

Incrementing a mysql count field

I have a count field in a table that I need to increment and this is what I have. $click_tracker_row = mysql_fetch_array($result); $current_count = $click_tracker_row['count']; $new_count = $current_count + 1; $query_wiki ="UPDATE click_tracker SET count = '{$new_count}' WHERE click_tracker_id = '{$click_tracker_row['click_tracker_id']...

ActiveRecord to use mysql named pipes on windows

Hi Is it possible to connect from ruby/ActiveRecord to a mysql database over named pipes. OS is Windows. Thanks. ...

Client want a field like PREFIX20100001

Client wants a field in the mysql DB to be composed of a prefix, the year, and a counter that resets each year. PREFIX 2010 0001 ... PREFIX 2010 0734, then PREFIX 2011 0001. Are there any mysql tricks to make that happen or do I keep track of the largest number used for each year. Any thoughts appreciated. ...

Trailing whitespace in varchar needs to be considered in comparison

I am using a table with a varchar column. I did not realize that trailing whitespace is not considered in comparisons (and that, apparently, two values that differ only in amount of trailing whitespace will violate the uniqueness property, if specified). I need to fix this in the table, preferably in place. Is there a recommended path...

php & mysql sum/total top ten problem.

I am trying to return the top ten entities that are associated with articles after doing a search on the articles. Lets say I have the following set up: CREATE TABLE IF NOT EXISTS `articles` ( `id` char(36) NOT NULL, `html_content` text NOT NULL, `featured` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), FULLTEXT KEY `ht...

Optimal database structure design for one to many

I am building an inventory tracking system for internal use at my company. I am working on the database structure and want to get some feedback on which design is better*. I need a recursive(i might be using this term wrong...) system where a part could be made up of zero or more parts. I though of two ways to do this but am not sure wh...

how do i select entity from a different table?

Hi, i have a commenting system where i am storing the News ID in the comments table to refer and to fetch the value from the newstable, my two table is like this. New Table, CREATE TABLE `news` ( `id` int(20) NOT NULL auto_increment, `timestamp` int(20) NOT NULL, `title` varchar(255) NOT NULL, `content` text NULL, `pic_title` varcha...

mysql "datetime NOT NULL DEFAULT '1970-01-01' " gets turned to 0000-00-00 00:00:00

I have a column created as `date_start` datetime NOT NULL DEFAULT '1970-01-01' However when I upload data from a CSV file with the LOAD DATA command with a blank entry for date_start the value saved is 0000-00-00 00:00:00 ? ...

What collation should I use to store foriegn characters in a table?

I'm using google translate with my website to translate short, frequently used phrases. Instead of asking google for a translation every time, I thought of caching the translations in a MySQL table. Anyway, it works fine for latin characters, but fails for others like asian. What collation/charset would be the best to use? Also - I've ...

Recommended people (like social network)

Hi, i'm writing a kind of 'dating service' website (just for fun & practice). But right now i'm stuck. I use PHP and MySQL. Every new member must complete 'What do you like (to do)?' question separated by commas: Like this: computing, dancing, reading, TV, movies and so on. My question is 'how can i show recommended people to s...

left join with empty results when no match is found

Say I have these three tables: Table: Baskets id | name 1 Sale 2 Premium 3 Standard 4 Expired Table: Fruit id | name | basketid 1 Apples 1 2 Oranges 2 3 Grapes 3 4 Apples 2 5 Apples 4 Table: Veggies id | name | basketid 1 Carrots 1 2 Peas 2 3 ...

How do you serialize a struct and store it in a MySQL database?

I am using C++ for an online game server project and I need to store some structs as binary blobs in a MySQL database. I am using RakNet for networking and have tried using its BitStream class to serialize the data but I don't know how to do it right. My question is, how do you turn a structure into a stream of bytes that you can pass in...

MySQL STR_TO_DATE gives invalid results?

I'm using MySQL 5.1.49 on Win64. We're seeing the following behaviour on Solaris machines as well. Here's my test table: CREATE TABLE `date_test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `date1` datetime NOT NULL, PRIMARY KEY (`id`), KEY `Index_2` (`date1`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; mysql>...

mysql query optimization

SELECT b.categoryid, SUM(viewcount) AS cnt, categoryname FROM bookvisit AS bv INNER JOIN book AS b ON b.isbn = bv.isbn LEFT JOIN category AS c ON b.categoryid = c.categoryid WHERE b.categoryid IS NOT NULL AND b.categoryid <> 0 GROUP BY b.categoryid ORDER BY cnt DESC, bv.isbn LIMIT 0, 4 I ha...

How do i improve this code to make my code less trip to database?

Hi, I have written a code where i am frequently tripping to database which is kinda awkward, i am using the code to count the number of approves, pending, and spam in comments table. here is my code. $query_approved = "SELECT COUNT(*) as approved FROM comments WHERE approve = '1'"; $result_approved = mysql_query($query_approved); $...