mysql

How can I do this in SQL in a Single Statement?

Hi, I have the following MySQL table: +---------+------------+------+--------+------+---------+------------+-------+---------+----------+------------+------------+ | Version | Yr_Varient | FY | Period | CoA | Company | Item | Mvt | Ptnr_Co | Investee | GC | LC | +---------+------------+------+--------+------...

elapsed time finding

Hi,everyone while uploading the file i ll be getting upload time something and given the time duration 24hrs within that user need to download the file and edit.how i can find how much time still left to complete the task.please anyone help out..how i can give the time duration like 24hr using php/mysql ...

On getting any db error lock wait timeout, com.mysql.jdbc.exceptions.jdbc4.CommunicationsException, the data disappears from the db

i am implementing jpa using toplink, and whenever any sort of error comes in the log file, all db entries after the error DISAPPEAR from the db, all errors are caught in a try, catch statement and sent to an error page, still the data continues to disappear, i call commit() after every transaction, i have not called rollback in the code,...

Mysql field sizes on meta data

I want to create a table that will contain dynamic data, it can be in a form of a date, boolean or a text article for example: meta_key = "isActive" meta_valu = "1" or meta_key = "theDate" meta_value = "Sat Jul 23 02:16:57 2005" or meta_key = "description" meta_value = "this is a description and this text can go on and on so i nee...

datetime difference for this syntax(d/m/Y H:m:s)

[Duplicate of DateTime difference from two tables] Hi, I need to find the date time difference from 2 tables. My date time type is Varchar and the format is(d/m/Y H:m:s). How to find the datetime difference from 2 tables. ...

Database table with 3.5 million entries - how can we improve performance?

We have a MySQL table with about 3.5 million IP entries. The structure: CREATE TABLE IF NOT EXISTS `geoip_blocks` ( `uid` int(11) NOT NULL auto_increment, `pid` int(11) NOT NULL, `startipnum` int(12) unsigned NOT NULL, `endipnum` int(12) unsigned NOT NULL, `locid` int(11) NOT NULL, PRIMARY KEY (`uid`), KEY `startipnum`...

Mysql: latin1-> utf8. Convert characters to their multibyte equivalents

There was a table in latin1 and site in cp1252 I want to have table in utf8 and site in utf-8 I've done: 1) on web page: Content-Type: text/html;charset=utf-8 2) Mysql: ALTER TABLE XXX CONVERT TO CHARACTER SET utf8 _ This SQL doesn't work as I want - it doesn't convert ä & ü characters in database to their multibyte equivalents Pl...

How do I simply retrieve the last most recent publish post in wordpress with mysql?

hi there, I'm looking to retrieve the most recently published post in a wordpress system. I have some basic mysql understanding and I can find where the revisions are stored in the wp_posts table but what I can't seem to find is how to retrieve the permalink for the most recent post. any ideas on how to retrieve the most recent post w...

PHP variables in mysql queries

Hi. Im using mysql with PHP, I was just wondering if this query, $query = "UPDATE tblName SET field='$fieldValue' WHERE field2='$fieldValue2'" would cause an Out of Memory Error in mysql. Will this query, $query = "UPDATE tblName SET field='".$fieldValue."' WHERE field2='".$fieldValue2."'" consume less memory than the previous one? Im ...

Need help with normalizing my database

I am woundering whether someone has time to normalize my database? I dont know if it needs normalization, but I want to do everything perfectly, and I dont know if it is already good or not. I have used phpMyAdmin to create my database... Background: Im making a classifieds website. One category of ads is 'cars'. So far, I have create...

While select in mysql stored procuder

i will try a select into my Stored Procedure like a while e.g. while ( select * from t where t.c = 1 into test_t ) { var_t_e = test_t.c; } i hope i can be helped here. :) ...

If column is default, calculate value in mySQL

I have the following table: id | kwh | cost --------------------- 1 | 10 | 8.95 2 | 20 | 0 3 | 100 | 16.54 I need to get the average cost. If the cost is still the default (0), the cost value is calculated with the following formula: (7.87 + (kwh * 8.19)/100) * 1.03 So id 2's cost will be estimated at 9.79...

Unable to connect to a local MYSQL server on wireles LAN.

Ok, Here is the technical description. My laptop's config: Ip Adress:192.168.2.5 Mysqlserver 5.0 on port : 3306 Operating system: Ubuntu jaunty (9.04) 3306 is open for both incoming and outgoing. My friend's laptop config: Ip Adress:192.168.2.4 Mysqlserver 5.0 on port : 3306 Operating system: Windows XP pro 3306 is open for both incomi...

How to pass "--external-locking" for mysqld in Ubuntu

Hi, I would like to start my mysql server with the --external-locking option. As mysqld is run by the /etc/init.d/mysql script ubuntu (karmic), I guess that's where I should set this "--external-locking" thing, just can't figure out where to put it(am far from expertise in shell scripting:). Could anyone please help me with that? Sett...

How to get the auto increment id for two fields in a table

Hi i want to know that how the auto increment id can be get from the mysql db for two fields in a table ...

mysql - query three tables

I have a relational database with three tables. The first containts id's that relate to the second. The second contains id's that relate to the third. The third contains the results I am after. Is it possible with a single query to query an id in the first table which gives all results from the third table that relate to it? Sorry I am...

How to make session to allow different project?

I am planning to make a project management with PHP/MySQL/Codeigniter. It will have 10 - 20 users and 20 - 30 projects going on at the same time. Let's say John is a member of project A, B and C. Cindy is in A, D, F G etc. I want to make it only members of project can access the project. Now I am not sure how to approach this. Wha...

MySql unique id for several records

I use one table withe some casual columns such as id, name, email, etc...also I'm inserting a variable numbers of records in each transaction, to be much efficient I need to have one unique id lets call it transaction id, that would be the same for each group of data which are inserted in one transaction, should be increment. What is the...

Sorting SQL by first two characters of fields

I'm trying to sort some data by sales person initials, and the sales rep field is 3 chars long, and is Firstname, Lastname and Account type. So, Bob Smith would be BS* and I just need to sort by the first two characters. How can I pull all data for a certain rep, where the first two characters of the field equals BS? ...

MySql autoincrement column increases by 10 problem

I am a user of a some host company which serves my MySql database. Due to their replication problem, the autoincrement values increses by 10, which seems to be a common problem. My question is how can I simulate (safely) autoincrement feature so that the column have an consecutive ID? My idea was to implement some sequence mechanism ...