Advertise here


Tag cloud


 

You are currently browsing the archives for the MySQL category.

Archive for the ‘MySQL’ Category

Concatenate rows into one field

Posted on Wednesday, April 13th, 2011 in MySQL

This article explains how to concatenate rows using SQL into one field.

This example is based on a website that uses tags to relate articles and is using a MySQL database. An article will have multiple tags and each tag is in a row in the tags table. By grouping an article by the news_id field this will return more that one row if the article has many tags. The issue now is you only want to return a single row as the article with the tags nicely grouped together in one field. This can be achieved by using Group_Concat() method which requires the field name to be concatenated. By default each row is separated by a comma but this can be changed by entering a SEPARATOR and then the character straight afterwards.

SELECT story.*, GROUP_CONCAT(tags.tag) as tagGroup FROM story
LEFT JOIN tags ON tags.news_id = story.news_id
WHERE story.news_id = 13
GROUP BY story.news_id

The example above shows one way to use Group_Concat()

GROUP_CONCAT(tbl_list SEPARATOR '-')

This shows how to change the default separator.

This method can be used in other ways to concatenate items, to read further see Group_Concat() dev.mysql.com.


Comments Off

Count rows while using limit

Posted on Sunday, February 27th, 2011 in MySQL

A very useful feature that MySQL provides is the ability to calculate the number of rows returned when using limit.

By using the SELECT parameter SQL_CALC_FOUND_ROWS in your first query and then in the next query use SELECT FOUND_ROWS(); function which will return the number of rows.

SELECT SQL_CALC_FOUND_ROWS col_a, col_b
FROM tbl_name
WHERE col_c = 8 LIMIT 10;
SELECT FOUND_ROWS();

Read more


Comments Off

Insert else update

Posted on Sunday, February 20th, 2011 in MySQL

Here is something that I recently found very useful. If you have a table in your MySQL database which you want to insert a record if it does not exist but update the row if it does. MySQL 5.x has a very neat piece of script to handle this with out any conditional statements.
Read more


Comments Off

CSharp make MySQL variables work

Posted on Monday, August 30th, 2010 in MySQL

To get MySQL variables in c# to work you must put this text at the end of your connection string “Allow User Variables=True“. The reason this is turned off in the first place is because when c# parameters are used, they are identified by an ‘@‘ sign which conflicts with MySQL variables. But now you can use the ‘?‘ question mark to add parameters to the query string using the method AddWithValue(“?param”, paramVar);.

mysqlCommand.Parameters.AddWithValue("@lastestUrl", lastModified);

Comments Off

Assign row numbers and get rest of data according to row value

Posted on Saturday, August 28th, 2010 in MySQL

I recently found myself in a situation where I had to get data from MySQL database which had identical date-times but had text data in another column to uniquely identify a record. So I could use the combination to identify a unique record the problem was when appending a file with new records which had half of the records with the exact same date-times.

I constructed a query with some help to get the rest of the records from the last entry in the file. This is done by:

  1. Firstly getting data that is equal to and greater than the date-time in the text file.
  2. Then assign each row with a row number using MySQL variables @rownum:=@rownum+1 this is placed inside a sub query
  3. Then after the first sub query create a where statement where rownum is greater than the next sub query
  4. In the next sub query it will return the rownum the query should start from by running the same query as the first one but with a where clause identifying the the unique text.

You can use the query below for any instance where one column has exactly the same numbers which you need to order by with another uniquely identifiable column, so you know where you should start pulling from.

A clever use of SQL variables and sub queries.

Select x.column c
     FROM (select @rownum:=@rownum+1 as rownum, a.column
             FROM table a, (SELECT @rownum:=0) r
             WHERE a.datetime >= '2010-08-02 12:53:12' ORDER BY a.date ASC) x
where x.rownum > (Select x.rownum FROM
             (select @rownum:=@rownum+1 as rownum, a.text
             FROM table a, (SELECT @rownum:=0) r
             WHERE a.datetime >= '2010-08-02 12:53:12' ORDER BY a.date ASC) x
where x.text = 'unique-descriptive-text');

Comments Off

Insert Ignore and Replace

Posted on Monday, July 26th, 2010 in MySQL

How the INSERT IGNORE works in MySQL: If a record exists in the table it will be ignored else a new row will be inserted. It works in the same way as INSERT IF NOT EXISTS. If the record has a slight difference then a new record will be added creating a duplicate record. Also if your table has a unique key of some kind an error will occur and the insert will be aborted.

To avoid duplications a better method is REPLACE INTO which replaces any data in any field that is not up to date. This prevents any duplicate records from appearing in the database. This overwrites the current records but if you don’t want this you are better off implementing some sort of version control. For instance when an article is edited, the updated version is inserted as a new record. Then you just use a date time stamp to identify the latest version. This does mean when there are a lot of edits the database will increase in size very quickly but its really easy to write a function that deletes old records.

Both INSERT IGNORE and REPLACE use the insert … values or insert … set syntax.


Comments Off


Web Design Essex | Richard Kotze – Web Technology, Design and Development powered by WordPress | Entries (RSS)