mysql

MySQL: CONCAT_WS function is running on local but not on server.

Some days ago I asked a question about my problem and I was advised to use CONCAT_WS function. I am using CONCAT_WS on my local mysql database and it is working perfectly. But it is not working on server(application hosted) and generate the following error. FUNCTION test.CONCAT_WS does not exist Here test in error string is my data...

MySQL export query as insert statements

Hi, I have two mysql tables which both have a typeID in common. I am wanting to select everything from these two tables with the same typeID, query below: SELECT ta.requiredTypeID, ta.typeID, ta.quantity FROM `invtypes` as t, `typeactivitymaterials` as ta WHERE volume > 0 AND t.typeID = ta.typeID; This gives me the correct res...

Track weekly changes a.k.a trends (db design)

I have a site where people can add their favorite TV shows. I would like to have some trends statistics. Example: (1 unchanged) The Big Bang Theory (3rd last week) How I Met Your Mother (2nd last week) House (30th last week, up 400%) Nikita I'm not sure how to design the database for this, but here is my idea: Once a week, I run ...

MySql post archives. What's the best way to handle massive archived posts that need to be kept?

If my wordpress site generates thousands(perhaps millions) of posts a day, what is the best way to keep the site from taking a performance hit with posts that only need to be seen if someone searches old posts or for legal purposes? My first thought was to run a cron job during a lull and move the out-of-date posts to an archive databas...

php/mysql app security issues?

Possible Duplicate: Where can I find a web-project security checklist? i was just wondering when creating an php/mysql app, whats the main security issues a developer is got to consider, i know this could be a major broad topic, but i just wanted an overview of where to look, and while im wrting and devloping the app, what i n...

MySQL order by COUNT DISTINCT

Hello I have a sql table: 2.8.7 2.8.3 2.8.2 2.8.7 2.8.5 2.8.7 2.8.7 2.8.5 2.6.0 2.8.3 2.6.4 2.6.3 2.8.4 2.8.0 2.6.3 2.8.5 2.8.5 2.8.5 2.6.0 2.8.2 How do I bring a unique value version sorted by the number of these versions? At the exit I want to get the following: 2.8.5 5 2.8.7 4 2.6.0 2 2.6.3 2 2.8.2 2 2.8....

How to change time part of a datetime value with doctrine?

I need to change the time part of a datetime in a database. What I got is in the database: '2010-01-01 01:00:00' I need to update this to '2010-01-01 03:00:00' Only thing I have is '03:00:00' As I'm using doctrine I could iterate through all objects but this would decrease the perfomance. So what I tried was: $q = Doctrine_Query::crea...

How can I stop a running MySQL query?

I connect to mysql from my Linux shell. Every now and then I run a SELECT query that is too big. It prints and prints and I already know this is not what I meant. I would like to stop the query. Hitting Ctrl+C (a couple of times) kills mysql completely and takes me back to shell, so I have to reconnect... Is it possible to stop a query ...

How can I add headers and format MySQL query output files?

I connect to mysql from my Linux shell and use something like this: SELECT * FROM students INTO OUTFILE '/tmp/students'. Why do I see \N at line endings? I want each record in a row, but why do I see the \N explicitly printed? How can I print all column headers in the first row? ...

How to convert an array into MySQL Table?

What I want to do is: $array_data = array( "a" => array(1, 2, 3), "b" => array( 1, 2, 3 ) ); $table_converted = CONVERT_TO_MYSQL_TABLE( $array_data ); while ($row = mysql_fetch_assoc( $table_converted )) { echo $row['a'] . " union " . $row['b']; } Thanks! ...

How can I compare a variable to a MySQL full-text index without a gigantic loop?

Let's say I've got two tables: NOTES has two columns: ID and BODY. BODY is a FULLTEXT index. KEYWORDS has two columns: ID and KEYWORD. I want to search every BODY for every KEYWORD and get one row for each match, the NOTES.ID and the KEYWORD.ID. So if there were 15 notes and each one matched 3 keywords, I would get 45 rows. But as ...

My SQL query within a query

I have 2 tables that I am trying to combine in a specific way Table 1: ‘part_defs’ Table 2 Items_part_values in value_defs: ID | Name ------------- 1 | color 2 | size 3 | weight in Items_part_values ItemID | valueID | Value ------------------------- 10 | 1 | red 11 | 1 | blue What I need is a query where...

Is the specification of ssl-cipher enough to secure a MySQL client connection to a SSL Enabled MySQL Server

I have successfully setup my MySQL server to support SSL connections I'm just wondering if I need to copy client certs to each other server I want to connect to it OR if I can get away with just specifying: [client] ssl-cipher = DHE-RSA-AES256-SHA:AES128-SHA All I need to do is encrypt the traffic between servers (s...

Implementing not automatic badges with PHP and MYSQL

I have users' table users, where I store information like post_count and so on. I want to have ~50 badges and it is going to be even more than that in future. So, I want to have a page where member of website could go and take the badge, not automatically give him it like in SO. And after he clicks a button called smth like "Take 'Made...

MySQL Select by random.

I have a list of items for businesses in a database: listings `id` `location` `item` `status` So in the database I might have something like: 19 Loc A Green Beans for $12 active 20 Loc B Two Gatoraids for $3 deactive 21 Loc A Ham for $2 per lb active 22 Loc A Pepsi 2 for $2 active 23 Loc C House pla...

Need to optimize a nested select statement

I've got the following SQL: SELECT customfieldvalue.ISSUE FROM customfieldvalue WHERE customfieldvalue.STRINGVALUE IN (SELECT customfieldvalue.STRINGVALUE FROM customfieldvalue WHERE customfieldvalue.CUSTOMFIELD = "10670" GROUP BY customfieldvalue.STRINGVALUE HAVING COUNT(*) > 1); The inner nested select return...

How to create temp table from tags?

I have a product_table with a product_id among several other fields. I am creating a tag_table with 2 fields, tag_id and tag_name. The tags could be anything like "silver" or "penn state" etc. I am also creating a product_tag_map table with product_id and tag_id in order to map a product to any number of tags. If I wanted to create a...

Not recognizing Date Type in Java MySQL DB Call

I have the following calls in one of my classes @Override public Integer save(NewsItem item){ ConnectionPool pool = new ConnectionPool(); Connection connection = pool.getConnection(); PreparedStatement ps = null; try{ String query = "INSERT INTO newsItem (type,title,content,link,layout,imageUri,timestamp)" + ...

mysql count selected field

I have a query that selects the nth results (in this case the 10th) I just want to use the reply_chunk_id of that nth result and count others like it to see how many there are in total with the same id. Does anyone know how to do this in one query? SELECT reply_chunk_id,message FROM ( SELECT * FROM messages ORDER BY timest...

MySQL JDBC driver & FLOAT(N, M) column type

Hi to all. I've recently got a bug that's somewhat weird for me. Let's consider that we have a table with a column of FLOAT(8, 3). Let's also consider we have a validated table editor accessible via web browser. The table editor supports the client-side validation for every column, and does not allow to put floats that are not in the ra...