mysql

MySQL Datetime <-> Java Date

Hello! I use Hibernate and I have a datetime in my MySQL database. Now I want to store the date/time which is a java.util.Date into the database. So I can write: SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); testObject.setCreationDate(df.parse("2010-06-06 13:20:15")); But how can I set the current date/time? Bec...

Writing to mysql databse PHP. How to update value in field??

I have a field in my mysql table that I want to update. Example: In my database the field amount has a value of 4 and I want to add 6 to the value. How would I add 6 to the existing value??? This is what I have so far but this just deletes the existing value and adds a new one: mysql_send("UPDATE peopletable SET amount='$amount' WHER...

MySQL- complex data query in a single statement

Hi, Consider the following structure : Ignore the table user_token. Now, imagine that you need to get all the roles related to an user, wich may be through it's related groups or directly related to him. In case the same role appears related to a group and the user directly, the role related to the user will prevail over the role gi...

how can i make sure only a single record is inserted when multiple apache threads are trying to access the database?

I have a web service (xmlrpc service to be exact) that handles among other things writing data into the database. Here's the scenario: I often receive requests to either update or insert a record. What I would do is this: If the record already exists, append to the record, If not, create a new record The issue is that there are cert...

cakephp database not found

I have project developed using cakephp which is getting data from different DBs, but if one of theses database some pages not open and give me the following error : Database table tablenae for model moedlname was not found. ..and I have in this page other data displayed from the other database which work probably. how i can deter...

MySQL query against pseudo-key-value pair data in WordPress custom query

I'm writing a custom WordPress query to use some of the data which the Woothemes Diarise theme creates. Diarise is an event planner theme with calendar blah, blah... and uses custom fields to store the event start and end dates in WP custom fields in the *wp_postmeta* table, which implements a key-value store. So for each post in the "ev...

Database design grouping contacts by lists and companies

Hi, I'm wondering what would be the best way to group contacts by their company. Right now a user can group their contacts by custom created lists but I'd like to be able to group contacts by their company as well as store the contact's position (i.e. Project Manager of XYZ company). Database wise this is what I have for grouping cont...

Select Union Query problem

I have 2 tables Table A id name ------------ 1 Scott 2 Dan 3 Sam Table B id name ------------ 1 Dan 2 Andi 3 Jess Result needs to be Id Name Found 1 Scott A 2 Dan C i.e. found in both 3 Sam A 2 Andi B 3 Jess B I am able to do UNION to fetch the result but how i generate column Founds. Any idea ...

Rolling back call to stored procedure in MySql

I have a scenario where I need to insert, call and drop a stored procedure in several databases. This is done in a powershell script. I want to be able to roll back the changes made by the stored procedure if some situations occur further down the in the script. The problem is that I'm not able to roll back the changes made by the stor...

PHP / MySQL Query Builder UI with jQuery?

So, I need to construct a front-facing query builder for a database, but I'm having trouble finding existing code for what I can't imagine is a rare requirement. Basically, I have non-SQL fluent people needing to build queries on the fly and view the results. I found this: http://plugins.jquery.com/project/SQL_QUERY_BUILDER (Demo: htt...

How would I go about writing a conditional statement to check if visitor is coming from a particular site?

Hello guys, What I have in mind is this... We are going to have people come from a particular site during a acquisition campaign and was wondering how I could conditionalize a certain section of my site to display a thank you message instead of the sign up form as they would have had the opportunity to fill this out before coming to my ...

SQL where clause to work with Group by clause after performing a count()

Tried my usual references at w3schools and google. No luck I'm trying to produce the following results. QTY is a derived column | Position | QTY -------------------- 1 Clerk 2 2 Mgr 2 Here's what I'm not having luck with: SELECT Position, Count(position) AS 'QTY' FROM tblemployee Where ('QTY' != 1) ...

Rows dropping when I try to join data from two tables

I have a fairly simple query I'm try to write. If I run the following query: SELECT parts.id, parts.type_id FROM parts WHERE parts.type_id=1 OR parts.type_id=2 OR parts.type_id=4 ORDER BY parts.type_id; I get all the rows I expect to be returned. Now when I try to grab the parent_unit from another table with the following qu...

How do I drop 'NOT NULL' from a column in MySQL?

A show create table command shows the following: 'columnA' varchar(6) NOT NULL DEFAULT ''; How do I modify that column so that the not null is removed? I need it to be: 'columnA' varchar(6) DEFAULT NULL; I thought the following would work, but it has no effect: ALTER TABLE tbl_name MODIFY columnA varchar(6) DEFAULT NULL; ...

delete all from table

what's faster? DELETE * FROM table_name; or DELETE * FROM table_name where 1=1; why? does truncate table work in access? ...

Mysql SQL join question

I am trying to find all deals information along with how many comments they have received. My query select deals.*, count(comments.comments_id) as counts from deals left join comments on comments.deal_id=deals.deal_id where cancelled='N' But now it only shows the deals that have at least one comment. What is the problem? ...

Ruby, Rails: mysql2 gem, does somebody use this gem? Is it stable?

I found mysql2 gem. It works fine with ruby 1.9 and utf8 encoding (as opposed to mysql gem). But I have doubts. Does somebody use this gem in production? What about the performance test? Is it faster than mysql gem? Is it stable? ...

Getting id of row just inserted into MySQL database

I have my table columns set like this: likes(id, like_message, timestamp) id is the primary key that is auto incrementing. This is the SQL that I use to add a row: $sql = "INSERT INTO `likes` (like_message, timestamp) VALUES ('$likeMsg', $timeStamp)"; Everything works, but now I need to throw back the id attribute of the ne...

Creating a foreign key in MySQL produces error:

I'm trying to create a foreign key on a table in MySQL and I'm getting a strange error that there seems to be little info about in any of my searches. I'm creating the key with this (emitted from mysql workbench 5.2): ALTER TABLE `db`.`appointment` ADD CONSTRAINT `FK_appointment_CancellationID` FOREIGN KEY (`CancellationID` ) REFE...

Recipe for creating a corrupt mysql table

We had a process that crashed while trying to manipulate an expected mysql record set, running the offending query from the mysql cli showed the following. mysql> SELECT ...; ERROR 1030: Got error 127 from table handler Is there a way to easily recreate this condition so we can validate our fix ? (production DB was already repaired). ...