MySQL UPDATE a column value with add or subtract

If you have a numerical value in a MySQL database table, that you want to UPDATE by adding a value to it, or subtracting a value from it. This can be done with a single MySQL query.

So for an example, I have a database with a product which has a quantity of 100 in stock, somebody has just bought 10 so I need to subtract that value from the database quantity.

1. UPDATE products_table SET quantity = quantity - 10 WHERE product_id = 1001 

Later in the shop admin you receive new stock and so need to UPDATE the product quantity by adding 10 new items to its current quantity of 5.

2. UPDATE products_table SET quantity = quantity + 10 WHERE product_id = 1001

Read More

How to change the document root in XAMPP on windows ?

I have installed XAMPP in C drive on my windows. Today I thought I will move my installation to another directory.

After searching the web for couple of minutes, I got a solution that worked perfectly. Here are the steps you have to follow.

1. Open the file httpd.conf from the location C:/xampp/apache/conf
2. Search for DocumentRoot and you will get something like this
   

   DocumentRoot “C:/xampp/htdocs”

3. Update the DocumentRoot to new location. For example, if you want to update the DocumentRoot to D:/www , then you will have the following line after updating the location
   

   DocumentRoot  “D:/www”

4. Then find the Directory and you will fine something similar to
   

   <Directory “C:/xampp/htdocs”>

5. Update it to new location like this
   

   <Directory “D:/www”>

6. Then finally restart the Apache server using XAMPP control panel or manually.

That’s it, you are done. The DocumentRoot has been moved to new place successfully.

Read More

How to Solve XAMPP Problem Port 80, 443 in use by Skype ?

I’m trying to start XAMPP Control Panel, but I got these error messages:

Port 80 in use by "c:\program files (x86)\skype\phone\skype.exe"! 

Port 443 in use by "c:\program files (x86)\skype\phone\skype.exe"!

The easiest way to solve this XAMPP problem is shutdown your Skype, and restart your XAMPP Control Panel. But that is not a smart solution.

So how to solve this port in use issue? In Skype Options: [Connection] , there is a check-box labelled Use port 80 and 443 as alternatives for incoming connections. This is the root problem, so simply clear / unchecked it and save. You may need to restart Skype.

 Restart your XAMPP Control Panel, and the error message is now gone! Try to start Apache, and succeeded.

What if the port is not used by Skype, but by other applications such as TeamViewer? If so, then XAMPP have to use another port.

How to change Apache default port in XAMPP?

From XAMPP Control Panel, under Apache, click the Config button, and select the Apache (httpd.conf).

Inside the httpd.conf file, find line that says

Listen 80

And change the 80 into any number / port you want. In this example I’m using port 8080.

Listen 8080

Still from the httpd.conf file, find another line that says

ServerName localhost:80

And change 80 to 8080.

ServerName localhost:8080

Next step, still from XAMPP Control Panel, under Apache, click the Config button again, but this time select the Apache (httpd-ssl.conf). Inside the httpd-ssl.conf file, find line that says

Listen 443

And change the 443 into any number / port you want. I’ll using 4433 as the new port number.

Listen 4433

Still from the httpd-ssl.conf file, find another line that says

<VirtualHost _default_:443>

ServerName localhost:443

And change 443 to 4433.

<VirtualHost _default_:4433>

ServerName localhost:4433

Remember to save httpd.conf file and httpd-ssl.conf after you make some changes.

Now it’s time to fire-up our Apache in XAMPP Control Panel. If your doing good on editing those files, you should see that Apache is running and the port number has changed to 8080 and 4433.

Read More

SQL ROUND() Function

The ROUND() Function

The ROUND() function is used to round a numeric field to the number of decimals specified.

SQL ROUND() Syntax

SELECT ROUND(column_name,decimals) FROM table_name

Parameter	Description
column_name	Required. The field to round.
decimals	Required. Specifies the number of decimals to be returned.

SQL ROUND() Example

We have the following "Products" table:

Prod_Id	ProductName	Unit	UnitPrice
1	Jarlsberg	1000 g	10.45
2	Mascarpone	1000 g	32.56
3	Gorgonzola	1000 g	15.67

Now we want to display the product name and the price rounded to the nearest integer.
We use the following SELECT statement:

SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products

The result-set will look like this:

ProductName	UnitPrice
Jarlsberg	10
Mascarpone	33
Gorgonzola	16

Read More

SQL LCASE() Function

The LCASE() Function

The LCASE() function converts the value of a field to lowercase.

SQL LCASE() Syntax

SELECT LCASE(column_name) FROM table_name

Syntax for SQL Server

SELECT LOWER(column_name) FROM table_name

SQL LCASE() Example

We have the following "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Now we want to select the content of the "LastName" and "FirstName" columns above, and convert the "LastName" column to lowercase.
We use the following SELECT statement:

SELECT LCASE(LastName) as LastName,FirstName FROM Persons

The result-set will look like this:

LastName	FirstName
hansen	Ola
svendson	Tove
pettersen	Kari

Read More

SQL UCASE() Function

The UCASE() Function

The UCASE() function converts the value of a field to uppercase.

SQL UCASE() Syntax

SELECT UCASE(column_name) FROM table_name

Syntax for SQL Server

SELECT UPPER(column_name) FROM table_name

SQL UCASE() Example

We have the following "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Now we want to select the content of the "LastName" and "FirstName" columns above, and convert the "LastName" column to uppercase.
We use the following SELECT statement:

SELECT UCASE(LastName) as LastName,FirstName FROM Persons

The result-set will look like this:

LastName	FirstName
HANSEN	Ola
SVENDSON	Tove
PETTERSEN	Kari

Read More