RSS

Category Archives: DBMS

Need to generate n rows based on a value in a column t sql

By: gbn

I’ll assume

  1. MyRef etc is a column in TableA
  2. You have a numbers table

Something like:

SELECT * INTO #TableA
FROM
 (
    SELECT  1 AS ID, 3 AS QUANTITY, 'MyRef' AS refColumn
    UNION ALL
    SELECT 2, 2, 'AnotherRef'
) T


;WITH Nbrs ( Number ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + Number FROM Nbrs WHERE Number < 99
)
SELECT
   A.ID, A.refColumn + CAST(N.Number AS varchar(10))
FROM
   #TableA A
   JOIN
   Nbrs N ON N.Number <= A.QUANTITY

Copy from: http://stackoverflow.com/questions/6439716/need-to-generate-n-rows-based-on-a-value-in-a-column

 
Leave a comment

Posted by on July 15, 2016 in DBMS, SQL Server

 

MSDTC on server … is unavailable

By Sochinda,

Please go to Run: services.msc

and find service’s called Distributed Transaction Coordinator and start

11

 

 
Leave a comment

Posted by on November 11, 2015 in C#, LinQ, SQL Server

 

Installing MongoDB on Mac

By: STEPHEN KOCH

After we had a Processing workshop with Jer Thorpe he got to talking about Mongo, so we decided to have a gander at this “scalable, high-performance, open source, document-oriented database.” Read on for instructions on how to get it operational on your Mac OS X development machine, so you can start playing with Mongo using PHP.

Setting up MongoDB was easy enough on OS X (Snow Leopard), but getting it to play nice with PHP was a little odd. I think this is because I am using MAMP as my dev environment.

The first thing you will want to do before installing is to head on over to mongodb.org and do the 5 minute online shell tutorial. This will whet your appetite for Mongo and will quickly show you how easy it is to work with.

Install MongoDB

  • Download mongo: http://www.mongodb.org/downloads
  • Unzip into a location of choice. (Note: This is where you will run the daemon)
  • Open terminal and use the following command to make the directory where mongo will store data:
    $ mkdir -p /data/db
  • Start the mongodb server in a separate terminal window (if you get an error, you may need to ‘sudo’ this command):
    $ ./mongodb-xxxxxxx/bin/mongod
  • In a separate terminal window start the interactive shell:
    $ ./mongodb-xxxxxxx/bin/mongo
    > db.foo.save( { a : 1 } )
    > db.foo.find()

And with that you should have a running daemon and be able to use the shell just like you did in the online tutorial. If you receive an error about not being able ‘to create / open lock file’, you should run the command to make the db directory with ‘sudo’.

Getting PHP to behave

If you aren’t using MAMP you may be able to run the following command to install, and you should definitely give this a shot first. This worked successfully on my laptop, but I had issues on the machine running MAMP.

 

sudo pecl install mongo

These are the steps I took to get it working with MAMP:

  • Go to pecl.php.net to dowload the latest package (1.1.3)
  • Unzip and open README.md file. Instructions are simple:
  • Install package:
    $ phpize
    $ ./configure
    $ make
    $ sudo make install
  • This will create the ‘mongo.so’ file in the /modules dir. Copy this over to your MAMP extensions directory. You can find the location in your php.ini file. On my machine the path is ‘/Applications/MAMP/bin/php5.3/lib/php/extensions/no-debug-non-zts-20090626/.’ This will also copy the ‘mongo.so’ file to ‘/usr/lib/php/extensions/no-debug-non-zts-20090626/mongo.so.’
  • Add the following to your php.ini file found in your MAMP installation:
    extension=mongo.so
  • Start MAMP
  • Copy the example code found on php.net, place somewhere accesible from your browser and hit to test
  • You should see output in the browser as well as in the running server

Special Notes

If you want to run php from Terminal you will need to look in /etc for the php.ini file. If it doesn’t exist, you can open php.ini.default found there and create a new document. You will need to set the ‘extensions_dir’ variable to either as well as adding the ‘extension=mongo.db’ line to the file. You can issue the ‘php -i’ command from Terminal to see that you have the mongo extension properly loaded.

As I stated, I was able to run the pecl command from my laptop which basically issued the above commands and copied things to their proper locations except for the MAMP extensions dir. A shorter method to install so I recommend giving that a try first but don’t forget to copy the ‘mongo.so’ file over to the MAMP extensions directory.

Next steps and useful tips

  • Be sure to peruse the output when you issue the commands when installing the Mongo PHP package. It’s a lot of text, but if you are lost as to where things are, this output will tell you.
  • Experiment with PHP commands and create some test databases and collections.
  • For administering MongoDB I recommend RockMongo.
  • Try out some of the other drivers to play with Mongo.

Happy coding!

 

Copy from: http://www.bigspaceship.com/mongodb-on-mac/

 
Leave a comment

Posted by on September 3, 2015 in DBMS, MongoDB

 

Prevent Divide-By-Zero Errors in T-SQL

By Sochinda

NULLIF(expression, expression)

Ex: Column1 / Column2, so when Column2 equal 0 this equation will be error.

How to prvent divide by zero error, we have 2 steps as:

1. using NULLIF

NULLIF(Column2, 0) : it will return NULL when value equal 0

 

2. using ISNULL

ISNULL(NULLIF(Column2, 0), 1) it will return 1 when Column2 value is 0

 

So Column1 /  ISNULL(NULLIF(Column2, 0), 1), it will return Column1 Value when Column2 Value = 0

 
Leave a comment

Posted by on April 21, 2015 in SQL Server

 

Faceted navigation in PHP and MySQL

By: mightywebdeveloper.com

In this tutorial we are going to build a simple PHP/MySQL faceted navigation from scratch. In case you don’t know what faceted navigation is (or faceted search, layered navigation), it’s the nice filtering mechanism you see on most e-commerce sites in some form or another next to the product listing (see image).

Magento faceted navigation example

With each newly selected filter the resultset is narrowed down accordingly which allows user to quickly find the items (products, classifieds, articles, etc.) that meet their criteria. This search mechanism is most useful when a dataset needs to be filtered based onmultiple properties.

Unfortunately MySQL does not support faceted search out-of-the-box. There are dedicated external search applications such as SOLR or ElasticSearch which index the facets automatically for filtering. However not everyone has the knowledge or resources required to run such an application. Luckily we can achieve similar results while maintaining good performance using MySQL and PHP by setting up an index table and making some clever table joins.

The example data

You can download an SQL export of all the example data here.
Let me first introduce the dataset we’re going to work our magic on for this example:

table: products
+----+-----------------------+----------+-----------------+--------+
| id | name                  | type     | brand           | colour |
+----+-----------------------+----------+-----------------+--------+
|  1 | Black Jacket by Acme  | jacket   | Acme Apparel    | black  |
|  2 | Blue Jacket by Acme   | jacket   | Acme Apparel    | blue   |
|  3 | Blue Jacket by FF     | jacket   | Fictive Fashion | blue   |
|  4 | Orange Trousers by FF | trousers | Fictive Fashion | orange |
+----+-----------------------+----------+-----------------+--------+

The faceted navigation in this example without any of the filters selected will look something like this:

Type:
Jacket (3)
Trousers (1)
Brand:
Acme Apparel (2)
Fictive Fashion (2)
Colour:
Black (1)
Blue (2)
Orange (1)

The user can filter the product overview simply clicking on the filter links which makes it a form of navigation to the user (hence the term faceted navigation).

Note the item counts next to each facet value, these will be updated to reflect the current resultset whenever a filter is added or removed.

 

Faceted navigation index tables

To perform the faceted search in MySQL we need to create two extra tables. One table contains a list of the product facets and the other contains facts about the products.

table: product_facets
+----+--------+
| id | name   |
+----+--------+
|  1 | type   |
|  2 | brand  |
|  3 | colour |
+----+--------+

table: product_facts
+------------+----------+------------+-----------------+
| product_id | facet_id | facet_name | value           |
+------------+----------+------------+-----------------+
|          1 |        1 | type       | jacket          |
|          1 |        2 | brand      | Acme Apparel    |
|          1 |        3 | colour     | black           |
|          2 |        1 | type       | jacket          |
|          2 |        2 | brand      | Acme Apparel    |
|          2 |        3 | colour     | blue            |
|          3 |        1 | type       | jacket          |
|          3 |        2 | brand      | Fictive Fashion |
|          3 |        3 | colour     | blue            |
|          4 |        1 | type       | trousers        |
|          4 |        2 | brand      | Fictive Fashion |
|          4 |        3 | colour     | orange          |
+------------+----------+------------+-----------------+

We used the following PHP function to automatically populate the facts table with data. You’ll need to make sure the product_facts table gets updated for each CRUD operation on the original dataset.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
/**
 * Populates the product_facts table with data from the products table
 * @param object $mysql_connection the mysql connection object
 */
function populate_product_facts($mysql_connection) {
  //empty product_facts table
  mysqli_query($mysql_connection"TRUNCATE product_facts");  
  //fetch all product data
  $result = mysqli_query($mysql_connection"SELECT * FROM products");
  //loop through resultset rows
  while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    $facet_id = 1;
    //loop through table columns
    foreach ($row as $key => $value) {
      //create facts for all product fields except 'id' and 'name'
      if ($key != "id" && $key != "name") {
        $sql "INSERT INTO product_facts VALUES (" $row['id'] . ",$facet_id,'$key','$value');";
        mysqli_query($mysql_connection$sql);
        $facet_id++;
        echo "Added fact: (".$row['id'].", $facet_id, $key, $value) <br/>";
      }
    }
  }
}

Now that we have set up all the required tables we will look at how to query the tables in the next section.

Querying the data

Let’s say a user just opened the product browse page and hasn’t selected any filters yet. To retrieve the product data we can simply execute the query:

1
SELECT FROM products;

This will return a list of all products.

To construct the faceted navigation HTML we need to query the product_facts table.

1
2
3
SELECT pf.facet_name, pf.value, count(*) AS c
FROM product_facts pf
GROUP BY pf.facet_id, pf.value;

Which produces the following result:

+------------+-----------------+---+
| facet_name | value           | c |
+------------+-----------------+---+
| type       | jacket          | 3 |
| type       | trousers        | 1 |
| brand      | Acme Apparel    | 2 |
| brand      | Fictive Fashion | 2 |
| colour     | black           | 1 |
| colour     | blue            | 2 |
| colour     | orange          | 1 |
+------------+-----------------+---+

As you can see this contains all the data required to output the faceted navigation (including counts).

Now let’s say that a user selects the colour blue so that only blue products are shown. This changes the product query to:

1
2
SELECT FROM products
WHERE colour='blue';

Similarly the query to retrieve the facet data becomes:

1
2
3
4
5
SELECT pf.facet_name, pf.value, count(*) AS c
FROM product_facts pf
JOIN products p ON pf.product_id = p.id
WHERE p.colour='blue'
GROUP BY pf.facet_id, pf.value;

Which results in:

+------------+-----------------+---+
| facet_name | value           | c |
+------------+-----------------+---+
| type       | jacket          | 2 |
| brand      | Acme Apparel    | 1 |
| brand      | Fictive Fashion | 1 |
| colour     | blue            | 2 |
+------------+-----------------+---+

We can add an ORDER BY statement to sort the list of filters alphabetically:

1
2
3
4
5
SELECT pf.facet_name, pf.value, count(*) AS c
FROM product_facts pf
JOIN products p ON pf.product_id = p.id
GROUP BY pf.facet_id, pf.value
ORDER BY pf.facet_name, pf.value;

When the user selects multiple filters we simply add multiple conditions in the WHERE clause based on the selected filters.

In the next post I’ll explain how to create the interactive faceted navigation HTML interface based on the work we’ve done so far.

If you have any questions please leave a comment below!

Copy from: http://www.mightywebdeveloper.com/coding/faceted-navigation-in-php-mysql/

 
Leave a comment

Posted by on June 26, 2014 in MySQL Server, PHP

 

Install Apache + PHP + MySQL in Ubunut

We can Install Apache, PHP and MySQL in Ubuntu 3 types as bellow:

  1. When install Ubuntu Screen, please select ( [x] LAMP SERVER)
  2. Go to terminal :
    sudo apt-get install lamp-server^
  3. Install all application one by one as:

 

1 Preliminary Note

In this tutorial I use the hostname server1.example.com with the IP address 192.168.0.100. These settings might differ for you, so you have to replace them where appropriate.

I’m running all the steps in this tutorial with root privileges, so make sure you’re logged in as root:

sudo su

 

2 Installing MySQL 5

First we install MySQL 5 like this:

apt-get install mysql-server mysql-client

You will be asked to provide a password for the MySQL root user – this password is valid for the user root@localhost as well as root@server1.example.com, so we don’t have to specify a MySQL root password manually later on:

New password for the MySQL “root” user: <– yourrootsqlpassword
Repeat password for the MySQL “root” user: <– yourrootsqlpassword

 

3 Installing Apache2

Apache2 is available as an Ubuntu package, therefore we can install it like this:

apt-get install apache2

Now direct your browser to http://192.168.0.100, and you should see the Apache2 placeholder page (It works!):

Click to enlarge

 

Apache’s default document root is /var/www on Ubuntu, and the configuration file is /etc/apache2/apache2.conf. Additional configurations are stored in subdirectories of the /etc/apache2 directory such as /etc/apache2/mods-enabled (for Apache modules), /etc/apache2/sites-enabled (for virtual hosts), and/etc/apache2/conf.d.

 

4 Installing PHP5

We can install PHP5 and the Apache PHP5 module as follows:

apt-get install php5 libapache2-mod-php5

We must restart Apache afterwards:

/etc/init.d/apache2 restart

 

5 Testing PHP5 / Getting Details About Your PHP5 Installation

The document root of the default web site is /var/www. We will now create a small PHP file (info.php) in that directory and call it in a browser. The file will display lots of useful details about our PHP installation, such as the installed PHP version.

vi /var/www/info.php

<?php
phpinfo();
?>

Now we call that file in a browser (e.g. http://192.168.0.100/info.php):

Click to enlarge

 

As you see, PHP5 is working, and it’s working through the Apache 2.0 Handler, as shown in the Server API line. If you scroll further down, you will see all modules that are already enabled in PHP5. MySQL is not listed there which means we don’t have MySQL support in PHP5 yet.

 

6 Getting MySQL Support In PHP5

To get MySQL support in PHP, we can install the php5-mysql package. It’s a good idea to install some other PHP5 modules as well as you might need them for your applications. You can search for available PHP5 modules like this:

apt-cache search php5

Pick the ones you need and install them like this:

apt-get install php5-mysql php5-curl php5-gd php5-intl php-pear php5-imagick php5-imap php5-mcrypt php5-memcache php5-ming php5-ps php5-pspell php5-recode php5-snmp php5-sqlite php5-tidy php5-xmlrpc php5-xsl

Now restart Apache2:

/etc/init.d/apache2 restart

Xcache is a free and open PHP opcode cacher for caching and optimizing PHP intermediate code. It’s similar to other PHP opcode cachers, such as eAccelerator and APC. It is strongly recommended to have one of these installed to speed up your PHP page.

Xcache can be installed as follows:

apt-get install php5-xcache

Now restart Apache:

/etc/init.d/apache2 restart

Now reload http://192.168.0.100/info.php in your browser and scroll down to the modules section again. You should now find lots of new modules there, including the MySQL module:

Click to enlarge

 

 

7 phpMyAdmin

phpMyAdmin is a web interface through which you can manage your MySQL databases. It’s a good idea to install it:

apt-get install phpmyadmin

You will see the following questions:

Web server to reconfigure automatically: <– apache2
Configure database for phpmyadmin with dbconfig-common? <– No

Afterwards, you can access phpMyAdmin under http://192.168.0.100/phpmyadmin/:

Reference by:

http://www.howtoforge.com/installing-apache2-with-php5-and-mysql-support-on-ubuntu-13.04-lamp

http://www.liberiangeek.net/2013/05/want-lamp-linux-apache-mysql-in-ubuntu-13-04/

 
Leave a comment

Posted by on January 24, 2014 in Apache2, MySQL Server, PHP, Ubuntu

 

Optional Parameters in SQL Stored Procedures

CREATE PROCEDURE TestProc
(
@Param1 varchar(50) = NULL,
@Param2 varchar(50) = NULL,
@Param3 varchar(50) = NULL
)
AS
SELECT
*

FROM
TestTable

WHERE
((@Param1 is null) OR (col1 = @Param1)) AND 
((@Param2 is null) OR (col2 = @Param2)) AND 
((@Param3 is null) OR (col3 = @Param3))

 
Leave a comment

Posted by on April 26, 2013 in SQL Server