MySQL « Richard Kotze – Web Technology, Design and Development

Posts Tagged ‘MySQL’

Use MySql with c-sharp .Net

Posted on Tuesday, July 5th, 2011 in Web Development

I’m going to show how to connect, read and update a MySQL database using C-sharp .Net. You can use this for your web applications or other types of C# apps you create.

You can check out how to get your connection string from the app.config for console applications. This also applies for web applications to access the web.config. This will help you better understand the config file and how to access it. But I have made the first method in the code below get the database connection if you already aware of the web.config.

Firstly to access a MySql database you will need to download the MySQL connector dll from MySQL which you reference in your project. Read more

Comments Off

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();

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

Console C# getting database connection string from app.config file

Posted on Monday, November 1st, 2010 in Top Tips

When writing a console application using C# / .NET and you want to connect to a database, the connection string is typically stored in the app.config file. Below is an example of a web.config which is the same an app.config file used in C#.Net. It is essentially an XML file that stores required data to make your application work. It keeps these bits of data in one place, making it easy to manage when things change. There will be no need to hunt down connection strings or other configuration data with in your application code.

To extend further on this top tip I have written a tutorial on how to use CSharp with MySQL.

 <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
       <ConnectionString>
         <add key="MySQL.DB" value="server=localhost;database=mysqldbname;user=username;password=12345678;" />
       </ConnectionString>

       <system.web>
        <compilation defaultLanguage="c#" debug="true" />
      </system.web>

    </configuration>

To access the connection string the first step would be to make sure that a reference is added to System.Configuration. Then make sure you include it in the class using System.Configuration and the code below is used to access the connection string.To access the connection string the first step would be to make sure that a reference is added to System.Configuration. Then make sure you include it in the class using System.Configuration and the code below is used to access the connection string.

using System.Configuration;

string MysqlConnectionString = ConfigurationManager.ConnectionStrings["dbconnectionstring"].ConnectionString;

This method can also be used to access the app.config file for any web applications.

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:

Firstly getting data that is equal to and greater than the date-time in the text file.
Then assign each row with a row number using MySQL variables @rownum:=@rownum+1 this is placed inside a sub query
Then after the first sub query create a where statement where rownum is greater than the next sub query
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

Tag cloud

Posts Tagged ‘MySQL’