mysql

Column locking in innodb?

I know this sounds weird, but apparently one of my columns is locked. select * from table where type_id = 1 and updated_at < '2010-03-14' limit 1; select * from table where type_id = 3 and updated_at < '2010-03-14' limit 10; the first one would not finish running even in a few hours, while the second one completes smoothly. the only ...

What is wrong with my SQL syntax here?

I'm trying to create a IT asset database with a web front end. I've gathered some data from forms using POST as well as one variable that had already written to a cookie. This is the first time I have tried to enter the data into the database. Here is the code: <?php //get data $id = $_POST['id']; $company = $_POST['company']; $loca...

How do I write this MySQL query?

I am working on an Asset DB using a lamp stack. In this example consider the following 5 tables: asset, server, laptop, desktop, software All tables have a primary key of id, which is a unique asset id. Every object has all asset attributes and then depending on type of asset has additional attributes in the corresponding table. If ...

How to write a stored procedure using phpmyadmin and how to use it through php?

I want to create stored procedures through phpmyadmin and later on use it on php. But I dont know how to?. From what I know, I found out that, we cannot manage stored procedures through phpmyadmin. What other tool can manage stored procedure? And I am not sure if it is better option to use stored procedure instead? ...

manage.py runserver not working

I am new to django and python in general, so pardon me for any simple mistakes I may be doing. I am trying to setup my first django project on my local windows vista machine. I have created the project successfully with no problems. The issue I am coming across is when my settings.py has values for my database keys, the manage.py runs...

select for update problem in jdbc

I'm having a problem with select for update in jdbc. The table i'm trying to update is the smalldb table, i'm having problems-- i'm using select for update which does a lock on the selected row the update statement is -- String updateQ = "UPDATE libra.smalldb SET hIx = ? WHERE name = ?"; the select statement is -- rs = stmt1.execut...

rails large amount of data in single insert activerecord gave out

So I have I think around 36,000 just to be safe, a number I wouldn't think was too large for a modern sql database like mysql. Each record has just two attributes. So I do: so I collected them into one single insert statement sql = "INSERT INTO tasks (attrib_a, attrib_b) VALUES (c1,d1),(c2,d2),(c3,d3)...(c36000,d36000);" ActiveRecord:...

Event feed implementation - will it scale?

Situation: I am currently designing a feed system for a social website whereby each user has a feed of their friends' activities. I have two possible methods how to generate the feeds and I would like to ask which is best in terms of ability to scale. Events from all users are collected in one central database table, event_log. Use...

How to store data in mysql, to get the fastest performance?

Hey, I'm thinking about it, which of the following two query types would give me the fastest performance for a user messaging module inside my site: The first one i thought about is a multi table setup, which has a connection table, and a main table. The connection table holds the connection between accounts, and the messaging table. I...

Mysql return value as 0 in the fetch result.

I have this two tables, -- -- Table structure for table `t1` -- CREATE TABLE `t1` ( `pid` varchar(20) collate latin1_general_ci NOT NULL, `pname` varchar(20) collate latin1_general_ci NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; -- -- Dumping data for table `t1` -- INSERT INTO `t1` VALUES ('p1', ...

Worpress WorkFlow Modfications

Hi All WordPress Lovers, I would like to ask a help about Zensor which is a plugin that you publish a post then a moderator approves the post to be published on the wordpress blog site. When a post is awating for approval, each awaiting post is appearing "waiting moderation". But, I dont want any link appears before moderator approval....

XML output from MySQL

Hi, is there any chance of getting the output from a MySQL query directly to XML? Im referring to something like MSSQL has with SQL-XML plugin, for example: SELECT * FROM table WHERE 1 FOR XML AUTO returns text (or xml data type in MSSQL to be precise) which contains an XML markup structure generated according to the columns in the ...

How to kill mysql process through C#.

I am getting "too many connections" problem in an Asp .Net Mvc application which get fix when i manually kill process through Mysql v6.56 IDE, But on remote hosting computer where i can't kill process each time how can i fix this error. I have tried making a connection to information_schema DB's PROCESSLIST table but when connection is ...

SQL most popular

I have a mysql table with items in relation to their order. CREATE DATABASE IF NOT EXISTS `sqltest`; USE `sqltest`; DROP TABLE IF EXISTS `testdata`; CREATE TABLE `testdata` ( `orderID` varchar(10) DEFAULT NULL, `itemID` varchar(10) DEFAULT NULL, `qtyOrdered` int(10) DEFAULT NULL, `sellingPrice` decimal(10,2) DEFAULT NULL ) I...

SQL Query over three different tables

i got three tables CATS id name ------------------------------ 1 category1 2 category2 3 category3 4 category4 PRODUCT id name ------------------------------ 1 product1 2 product2 ZW-CAT-PRODUCT id_cats id_product ---------------------------...

How does Wordpress link posts to categories in its database?

At present I am displaying a list of the last 5 posts in a site's blog in its footer using this mysql query: SELECT post_title, guid, post_date FROM wp_posts WHERE post_type = 'post' AND post_status = 'Publish' ORDER BY post_date DESC LIMIT 5 How can I edit this query to restrict the search to a particular category id? I thought it wo...

How to identify composite primary key in any Mysql Database table?

How to identify composite primary key in any Mysql Database table? or EDIT 2 what sql query should be used to display the indees of any table who contains the composite primary keys? I have many tables in mysql database which are having composite keys of 2 or 3 primary keys, I am using phpmyadmin, and I have to code a php scr...

Sorting mysql array value after string chars swapped from fetched DB data.

I want to sort one column fetched from mysql DB, and stored in an array. After fetching I am doing below steps. DB Fetching fields array in row format. ->Field1, Field2, Field3, Field4, Field5 From that fields array One columns data [Field3], swapping string keywords. eg. AB013, DB131, RS001 should become: 013AB, 131DB, 001RS N...

Help with MySQL query

I have a table that contains the next columns: ip(varchar 255), index(bigint 20), time(timestamp) each time something is inserted there, the time column gets current timestamp. I want to run a query that returns all the rows that have been added in the last 24 hours. This is what I try to execute: SELECT ip, index FROM users WHERE ip...

output txt file with selectbox options in php

I am trying to display two selectboxes in my php page ,where the user can select options from first selectbox to add it to second box and then click a button to generate a textfile with all the options in the second selectbox. Can anyone please help? (IE 7 only please) Here is the code that I tried till now. <html> <head> <script lang...