mysql-query

sorting month-year wise

i have some entries which are having data month-wise like May-2009 April-2009 March-2009 - - - - - - January-2000 Now i want this data to be sorted monthly via mysql query. $q=mysql_query(SELECT id,action, L1_data, L2_data, L3_data, L11_data from table where action='feild name' ORDER BY DATE_FORMAT(L11_d...

How do i make custom query with wordpress?

Hi, i need to make a query in a wordpress plugin but i couldn't find a wordpress function and i'm not sure is right to use mysql_query the functions i've found enable ordering and grouping but i need to use also join and in (list) is there a way? thank you ...

Django - Perform Raw SQL Update/Delete and return row count

Going off the documentation here: http://docs.djangoproject.com/en/dev/topics/db/sql/ >>>cursor = connection.cursor() >>>cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz]) >>>print cursor.fetchone() None Does anyone know how to return the modified row count? (NOTE: I've played around with the placement/order of trans...

Mysql not using my indexes

I am using 'explain' to view why my query is hitting every single row in my database, and I do not understand why it is doing so. Could someone take a look and give me a hint what I am missing? My database is MyISAM, and I am using Mysql 5.1, PHP5 Here is my table: -- -- Table structure for table `users` -- CREATE TABLE IF NOT EXIST...

General SQL model optimization question (MySql)

Hi everyone! I need to know if there's a best way to optimize this kind of DB model : Here's my tables : [category] idCategory name [postCategory] (a post can be in more than 1 category) idCategory idPost [post] idPost post [comment] idComment idPost inputDate comment I'm going to have to display all the posts, from a specific ca...

Distinct Help

Alright so I have a table, in this table are two columns with ID's. I want to make one of the columns distinct, and once it is distinct to select all of those from the second column of a certain ID. Originally I tried: select distinct inp_kll_id from kb3_inv_plt where inp_plt_id = 581; However this does the where clause first, and th...

How to get mysql random integer range?

I am trying to generate a random integer for each row I select between 1 and 60 as timer. SELECT downloads.date, products.*, (FLOOR(1 + RAND() * 60)) AS timer I have searched and keep coming up to this FLOOR function as how to select a random integer in a range. This is giving me a 1 for every row. What am I missing? I am on mysql ...

MySQL query: Get all album covers

I need some help formulating this query. I have two (relevant) tables, which I will dump here for reference: CREATE TABLE IF NOT EXISTS `albums` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT 'owns all photos in album', `parent_id` int(11) DEFAULT NULL, `left_val` int(11) NOT NULL, `right_val` int(1...

confused by insert on duplicate key update

I'm trying to implement a 3 table item tagging system Table: Item Columns: ItemID (primary, auto_increment), Title, Content Table: Tag Columns: TagID (primary, auto_increment), Title, TagCount Table: ItemTag Columns: ItemID, TagID Each Title of a tag is unique. I would like to implement an efficient way of inserting to the Tag ta...

MySQL: Noob join question

MySQL noob with a table-joining question: I have two tables, "splits" and "dividends" that describe events in the stock market. The splits table has "day", "ratio", and "ticker". The dividends table has "day", "amount", and "ticker". I would like to get a resulting joined table that has both tables' information, AND sorted by date and...

Why does MySQL Rand() hate me?

Here is a simplified query of something I am trying to do on a larger join query. It is still breaking on this small scale. I am trying to generate a random number for each row pulled back in the range of 1-60. I then want to order the returned rows by this random number. SELECT downloads . * , (FLOOR( 1 + ( RAND( ) *60 ) )) AS random...

What is the best practice to count equal values in a database?

I have a fairly large mysql database and I would like to count all the equal values, to explain myself better: I have a country field which has several countries values (USA, UK, MEX, etc..) and I want to get the number of countries without duplicate (if I have 100 USA and 2 UK and 1000 MEX the result would be 3). The only way I could ...

Slightly complex MySQL query

Hi, employees e_id first last 1 John Smith 2 Bob Smith 3 Alex Smith 4 John Doe 5 Ron Doe clockpunch e_id time for adjustment 1 0650 in early 3 0710 in late 4 0725 in early 1 1100 lunch --- 2 1150 in late 2 1400 lunch --- 4 1320 out --- I need a SINGLE query that will list all employee names, along with a...

Select from a table where associated table fieldname = 'x' in Cake PHP

In CakePHP, I have two tables, Countries & Networks. They have a HABTM relationship and are joined by countries_networks. I'm trying to get all countries from the countries table where the 'name' field in Networks = 'o2' I've realised I can't do this using a basic find(), so I've been experimenting with the containable behaviour. I hav...

Django ORM Creates Phantom Alias in SQL Join

Hello, I am executing the following code (names changed to protect the innocent, so the model structure might seem weird): memberships = models.Membership.objects.filter( degree__gt=0.0, group=request.user.get_profile().group ) exclude_count = memberships.filter( member__officerships__profile=req...

MySQL - Update view column every time the row is SELECTed

Hi.. I have a table that has a "view_count" column. I would like to increment said column every time I select the row (even with a generic SELECT, such as SELECT * FROM table WHERE 1.. This would increment by 1 the view_count of all rows). Is there a way to do it automatically "server-side" (where mysql is the server and my application...

MySQL: carriage-return in query

I have a query that exports data from two columns of each row into a file. In the file data from each column should be separated by carriage-return, something like this: row1column1 row1column2 row2column1 row2column2 row3column1 row3column2 I tried using char(13): SELECT CONCAT(column1, char(13), column2) FROM my_table INTO outfile '...

mysql search prepending "the" or "and/&" ambiguity

I'm trying to do a title search in mysql across two different databases to match up data from seperate sources. In both db1 or db2, the titles will sometimes start with "The first title" in one db, and just "first title" in the other db, or "far and away" vs "far & away". Mysql fulltext search doesn't seem very effective at figuring th...

Control not entering into PHP Class File : ERROR

consider the following file (db-com.php): If you wish You may skip to the question below it directly while reading it in parallel. echo "entered db-com.php"; class DBCom { /** * @var string Holds the query string. * If the blank constructor is used, make sure to use the parametrized functions. */ var $queryStrin...

Mysql Join with limit?

I have a table with category, product and count. All integers. I'm looking for the most efficient query that will give me the top 10 products (highest count) for each category. I've tried several subselects and joins but couldn't figure out how to do it in a single query. Thanks for your help. ...