mysql

How do you limit compound records in MYSQL?

I've been working with MYSQL for a few years now, and I often find myself wondering about this problem, but have never gotten around to researching it until now. So here goes. Let's say I have a table of authors, and a table of books. Each book has a field to link it to an author. Now let's say I want to get an author and his books, all...

MySQL : Order by 2 fields

Suppose I have a list of products, and each has a price. I wish to show the most expensive product, and if 2 products or more tie, then I wish to order by their name. Doing this doesn't seem to produce the results I want: ORDER BY cost DESC, product_name What is the syntax which I am looking for? ...

SQL how to map a row to column?

Database structure with two 1-n connections. User table ========== user_id Attribute table =============== attribute_id user_id attribute_name Attribute_Value table ===================== attribute_value_id attribute_id attribute_value Is there a way that I can receive the data in the following row style: user_id | firstname | lastn...

CakePHP and HABTM Model Limit Error

I've got a series of Post models that hasAndBelongsToMany Media models. In certain function calls inside of the Post model, I don't need to retrieve the entire list of Media models. However, when I use the following code: $this->unbindModel( array('hasAndBelongsToMany' => array('Media')) ); // Rebind to get only the fields we need: $t...

MYSQL: returning zero when no value is present, categorized monthly

I have the following two tables that record expenditure and provide expenditure category information: Table transactions: +-------+--------+--------+ | month | cat_id | amount | +-------+--------+--------+ | 1 | 2 | 3 | | 1 | 2 | 8 | | 2 | 1 | 7 | | 2 | 1 | 5 | +-------+--------+-...

Help searching MYSQL database with a JOIN - possible self JOIN ?

In a View i have a table of users combining data from multiple database tables. The important tables are Users and Invitations. Im performing a join LEFT JOIN invitations ON (users.id = invitations.invitee_id) which gives me access to users.invitee_id, but i also have dynamic search parameters in my query and i cant search for an invi...

Mysql, reshape data from long / tall to wide

I have data in a mysql table in long / tall format (described below) and want to convert it to wide format. Can I do this using just sql? Easiest to explain with an example. Suppose you have information on (country, key, value) for M countries, N keys (e.g. keys can be income, political leader, area, continent, etc.) Long format has 3 ...

What mysql query would let me get aggregated stats, by month from this table?

I have a points system setup on my site, where every single point accumulated is logged in the points table. The structure is simple, p_userid, p_points (how many points accumulated during this action), and p_timestamp. I wanna display top 3 point accumulating users, for each month. So essentially, it should sum the p_points table for...

Find duplicate Records in MySQL using LIKE

I would like to find all duplicate records by name in a customer table using MySQL including those that do not match exactly. I know I can use the query SELECT id, name FROM customer GROUP BY name HAVING count(*) > 1; to find all rows that match exactly, but I want to find all duplicate rows matching with a LIKE clause. For instance...

MySql ALTER TABLE on Production Databases - Any Issues?

I have about 100 databases (all the same structure, just on different servers) with approx a dozen tables each. Most tables are small (lets say 100MB or less). There are occasional edge-cases where a table may be large (lets say 4GB+). I need to run a series of ALTER TABLE commands on just about every table in each database. Mainly addi...

Best way to implement Client <-> Server <-> Database architecture in an Android application?

I am making an Android application. Since it is so simple, I first thought I could simply eliminate the need for Java application on the server which acts as a middleware. I tried directly connecting to the database using the JDBC driver for MySQL but my program is crashing so I'm not sure if Android "supports" the JDBC driver for MySQL....

Mysql how to do this?

I need to select records from table,but one field(id2 as example) replace with value from other table with id==id2 from first table ...

Directly accessing server database via Ajax (without PHP or some other intermediate)

With powerful frameworks like jQuery, it seems to be possible to build an entire app logic on the client side. It's very much analogous to building a client app as a native program. Now suppose this client app needs to access a remote database. The usual solution seems to involve the layers Ajax/PHP/MySQL. It seems to me that the PHP l...

What is a SPATIAL INDEX and when should I use it?

Like most of the average PHP web developers I use MySql as a RDBMS. MySql (as other RDBMS also) offers SPATIAL INDEZ features, but I'm don't get it very well. I goole for it but I don't found clear real world examples to clearify my bad knowledge about it. Could someone explain me a little bit what is a SPATIAL INDEX and when should I u...

problem saving image urls to databse table

I am trying to save image urls to a MySQL database table The column field is long enough. The table and database are using UTF-8 CI-general collation (IIRC) The urls look something like this: http://example.com/media/images/47142000/jpg/_47142379_005857853-1.jpg but they get saved like this: http://example.com/media/images/47142000/...

Get mysql result and count specific field

I have database result like this ID | NAME | TYPE -------------------- 1 | baseball | 1 2 | kickball | 1 3 | football | 1 4 | soccer | 2 How do I do a select * so get all results but also get a total count of type = 2 in the results? Any help appreciated. ...

The use of .log.txt file in mysql

I am using mysql server 5.0 I see there is a file called as log.txt under the path "C:\Program Files\MySQL\MySQL Server 5.0\data.log.txt". What is the use of this logfile? ...

Display same-day events together (PHP/MYSQL)

$sidebarEvent = '<h4 class="scheduler_sidebar_link"><a href="{*URL*}#date={*DATE_SQL*},mode=month" target="_blank">{*DATE*}</a></h4> <div class="scheduler_sidebar_text">{*TEXT*}</div>'; This is a snippet from a plugin that I'm using. It prints event date followed by description. How do I modify it so that all same-day ...

how to get mysql database connection in android emulator

How to get the MySQL database connection in android emulator? I try to connect using java.sql.* package like this I get the exception: java.sql.SQLException:no suitable drive How can I use sql packages in android mobile? ...

How can I re-use WHERE clause logic with DBI?

Hi all: Disclaimer: first time I've used DBI. I have a MySQL table with a lot of indexed fields (f1, f2, f3, etc) that are used to generate WHERE clauses by long-running processes that iterate over chunks of the database performing various cleaning and testing operations. The current version of this code works something like this: s...