Author Archives: obergfellja

Apache2 website hosting for Linux

when hosting on a Linux variant, you may need a config file that will do proper redirect

create the a file, naming it a meaningful name ending in *.conf.
ie: if your domain is domain.com, create the name:  domain.com.conf
———————————–

<VirtualHost *:80>
ServerAdmin [Email Address here]
ServerName [subdomain].[domain].com
ServerAlias [subdomain].[domain].com
DocumentRoot /var/www/[codeDirectory]/
ErrorLog ${APACHE_LOG_DIR}/error.log
CustomLog ${APACHE_LOG_DIR}/access.log combined

<Directory /var/www/[codeDirectory]/>
Options Indexes FollowSymLinks
AllowOverride All
Require all granted
</Directory>
</VirtualHost>
———————————–
on the “server Name” line, if it is top level domain, exclude the “subdomain” section.  otherwise insert it in.

if you have any other domains you’d like to redirect here, server alias will be inserted on following line.  for instance:

you want people to go to www.domain.com, the “ServerName” will be www.domain.com, and alias will be domain.com  you can switch them if desired.

when the file is ready, upload to /etc/apache2/sites-available/ location and do the following

1) a2ensite domain.com.conf

2) systemctl restart apache2

changing the domain.com.conf to what you named your file and do a apache2 restart apache.

Linux Apache2 Redirect

if you ever have a problem with Site.com/page/ returning an error, look into your apache file

in /etc/apache2/apache2.conf

either download and edit or navigate there and load via your favorite tool (ie Nano, vi, etc)

Change “AllowOverride” from “None” to “All”.

<Directory />
Options FollowSymLinks
      AllowOverride All
Require all denied
</Directory>

<Directory /var/www/>
Options Indexes FollowSymLinks
      AllowOverride All
Require all granted
</Directory>

 

Reload Apache2 (run the following command in Linux)

systemctl restart apache2

Querying MySQL via PHP

TO Ensure we are able to dynamically call Selects inserts updates and Deletes from functions, see below on one method on how to do it via PHP to MySQL

<?php
//config.php
//echo "hit config 1 <br/>";

function OpenCon()
{
$isLocal = 0;

if($isLocal ==0)
{
$dbhost = "[IP]"; 
$dbuname = "[user name]"; 
$dbpass = "[password]"; 
$dbname = "[database]"; 
$port = "3306";
$charset = 'utf8mb4';
}else{
$dbhost = "localhost"; //need to change this to the eventual remote IP
$dbuname = "[user name]"; 
$dbpass = "[password]"; 
$dbname = "[database]"; 
$port = "3306";
$charset = 'utf8mb4';
}

$conn = mysqli_connect($dbhost, $dbuname, $dbpass,$dbname);
//echo "<br/>$dbhost, $dbuname, $dbpass,$dbname<br/>";
return $conn;

}

function CloseCon($conn)
{
//$conn -> close();
mysqli_close($conn);
}

function SelectCon($queryMe){
$conn = OpenCon();

if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

$result = mysqli_query($conn, $queryMe);// or trigger_error(mysqli_error()); 

return $result;
}

function InsertCon($insertMe){
$conn = OpenCon();
/*
echo "<br/>";
echo $insertMe;
echo "<br/>";
*/
if (!$conn) {
echo "<br/>connection failed<br/>";
die("Connection failed: " . mysqli_connect_error());
}elseif($conn){
$result = mysqli_query($conn, $insertMe);

if($result) {
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn);
CloseCon($conn);
return false;
}
}else{
echo "conn fail success not ran";
}
global $last_id;
$last_id = mysqli_insert_id($conn);
CloseCon($conn);

return true;
}

function UpdateCon($updateMe){
$conn = OpenCon();

if (!$conn) {
echo "<br/>connection failed<br/>";
die("Connection failed: " . mysqli_connect_error());
}elseif($conn){
$result = mysqli_query($conn, $updateMe);

if($result) {
echo "Records updated successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn);
CloseCon($conn);
return false;
}
}else{
echo "conn fail success not ran";
}

CloseCon($conn);

return true;
}

function DeleteCon($deleteMe){
$conn = OpenCon();

if (!$conn) {
echo "<br/>connection failed<br/>";
die("Connection failed: " . mysqli_connect_error());
}elseif($conn){
$result = mysqli_query($conn, $deleteMe);

if($result) {
echo "Records deleted successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn);
CloseCon($conn);
return false;
}
}else{
echo "conn fail success not ran";
}

CloseCon($conn);

return true;
}
?>

Batch restart MS SQL

if you need to reboot SQL server in Windows server(or local machine) and want to do this quickly on the server, login and create a batch file with the following parameters: (change mssqlserver for the server instance name you have)

 

net stop mssqlserver
net start mssqlserver
net start SQLSERVERAGENT
pause

save the file as “rebootSQLServer.bat”

End of Date Microsoft SQL Bug

if you have to enter the last moment in a day/month, note that 23:59:59.997 is the last moment within a day for Microsoft SQL.  The  23:59:59.998 and 23:59:59.999 milliseconds are typically dropped into the following date if we are doing a count(date).  a Known bug of Microsoft SQL, so if you encounter this, you do need to take these records into account.

So, the following causes some issues:

declare @startDate;
declare @endDate;

set @startDate = ‘2019-01-01 00:00:00.000″
set @endDate = ‘2019-12-31 23:59:59.999″

select count(date)
From transactions_table
where date between @startDate and @endDate

to correct this, make sure the @endDate = ‘2019-12-31 23:59:59.997’  (or whatever date value you choose but the time parameter ends with .997, otherwise it wraps into the following day.

Dynamic End of Month date parameter call

to dynamically set end of month date from a preset month.  When you do a stored procedure that pulls in a year as @year and month as @month, this following set code will set the last day of the month.

declare @dayMax as varchar(2);
set @dayMax = ’29’

if @month in (1,3,5,7,8,10,12)
set @dayMax = ’31’
else if @month in (4,6,9,11)
set @dayMax = ’30’
else if @month in (2)
if (@YEAR % 4 = 0 AND @YEAR % 100 <> 0) OR @YEAR % 400 = 0
set @dayMax = ’29’
else
set @dayMax = ’28’

 

Pulling Excel into SQL

Sometimes you may need to pull some excel files into a database, but unable to due to not having an “Import Excel” function in your sql tools, but still having the ability to run Insert and Update Functionalities on a command prompt or stripped down sql tool.  The following list is what you’ll have to do to obtain a list of inserts and/or updates.

1) Know the table columns in which are required.  In doing so, you can run the following queries.  Change “users” to whatever table you desire:
MySQL
———
DESCRIBE users;  //one way in MySQL
SHOW COLUMNS FROM users;  //Another Type in MySQL

Oracle
—————-
SELECT column_name
FROM user_tab_cols
WHERE table_name = ‘users’

2)  create an excel file with column names in Line 1, explicitly mimicking the columns that can’t be null.  You can exclude identity columns that auto fill, but any other columns that can’t be null need to be identified.  Nullable columns can be omitted, but if you’d like them to be identified, you can do so.  If an end user chooses to keep those columns blank, make sure that the cell(s) in question have at least one space for blank.

3) send off the excel file to end user or enter the data yourself.

4) when you retrieve the file back, make sure you massage single quote (apostrophes) .  You can do this by adding another apostrophe.  ie O’Riley will become O”Riley.  or if the business rules ask to remove them, than just replace the O’Riley to show ORiley.  Also search the excel files for any other delimiters that could murky up the code.

5)  Once you’ve sanitized your datasets, do go to VBA Macros and add the following code:

VBA Macro  (downloadable code here)
—————————-

6) save the vba macro and go back to the Excel file.  go over to one line over from the last column.  Lets say you have 5 columns to enter, Column A to E, You will put your cursor on line F2 and enter one of the following:

For Insert you will use the function (change “users for your table name):
=Insert2DB(a2:e2, $a$1:$e$1, “users”)

For Update, you will use the following function(change “users for your table name, assuming             column A and B are going to be the where clause):
=Update2DB(c2:e2, $c$1:$e$1, “users”, a2:b2, $a$1:$b$1)

Typically  for updates, you will bunch your “where clause columns” together to ensure it’s easier to grab them.  the above reads

the columns you will alter will come first in your function.  so, if you are inserting columns A-E, you will see that comes first, than the headers follow that (making the static headers columns with a $ function.  then set the table name.

insert into users(a1,b1,c1,d1,e1)
values(a2,b2,c2,d2,e2)

The update functionality has same base (Columns you are updating, their headers, the table name) but also adds 2 two parameters.  the last two sections are for the columns field values and headers for the where functionality. so it will turn into

Update users
set c1=c2,d1=d2,e1=e2
where a1=a2, b1=b2

Assuming the a1-e1 will be the static column names , and a2-e2 will be dynamically pulled in.

Once you complete this functionality as seen above, you will hit enter (on the column you are entering it), and you will see it will automatically turn it into an Insert or Update pending on the function you call.  When you see the query dynamically created, you can do a copy paste for all following rows with a drag/drop or copy paste in all columns you desire to copy into sql.  This can dynamically pull hundreds or thousands of records instantly and easily run them quickly with ease.