Author: joe

SQL split entry by delimiter

November 28, 2023 /

Example of splitting a field using the colon as a delimiter.

SELECT ref,
SUBSTRING(ref, 1, CHARINDEX(‘:’, ref)-1) AS Book,
SUBSTRING(ref, CHARINDEX(‘:’, ref)+1, LEN(ref)) AS Chap
FROM _BibleTest;

Clean up WordPress post revisions

January 2, 2022 /

To delete the WordPress post revisions you only need one SQL statement. Execute this SQL query to remove all post revisions from your MySQL database:

DELETE a, b, c
FROM `wp_posts` a
LEFT JOIN `wp_term_relationships` b ON a.id = b.object_id
LEFT JOIN `wp_postmeta` c ON a.id = c.post_id
LEFT JOIN `wp_term_taxonomy` d
ON b.term_taxonomy_id = d.term_taxonomy_id
WHERE a.post_type = “revision”
AND d.taxonomy != “link_category”;
DELETE from `wp_posts` WHERE post_type=”revision”;

SQL get list of db tables and fields

December 10, 2021 /

This is how to get a list of all tables and fields in a database.

SELECT T.name AS Table_Name ,
C.name AS Column_Name ,
P.name AS Data_Type ,
P.max_length AS Size ,
CAST(P.precision AS VARCHAR) + ‘/’ + CAST(P.scale AS VARCHAR) AS Precision_Scale
FROM sys.objects AS T
JOIN sys.columns AS C ON T.object_id = C.object_id
JOIN sys.types AS P ON C.system_type_id = P.system_type_id
WHERE T.type_desc = ‘USER_TABLE’
and not P.name = ‘sysname’

Alternatively for a single table:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘expense2’

PHP: Check URL parameters

April 19, 2020 /

<?php
// ipaddress
$ipaddress = $_SERVER[‘REMOTE_ADDR’];
echo “ipaddress: “. $ipaddress. “<br/><br/>”;

// complete page url
$link = “http://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]”;
echo “link: “. $link. “<br/><br/>”;

// page url without parameters
$linkParts = explode(‘?’, $link);
$mypage = $linkParts[0];
echo “mypage: “. $mypage. “<br/><br/>”;

echo “=============================<br/><br/>”;

// parameter string from the url
$pageparms = $_SERVER[“QUERY_STRING”];
$pageparms = strtolower($pageparms);
echo “pageparms: “. $pageparms. “<br/><br/>”;

echo “=============================<br/><br/>”;

// number of parameters specified
$parmParts = explode(‘&’, $pageparms);
$numparts = count($parmParts);
echo “numparts: “. $numparts. “<br/><br/>”;

// 1st parameter if specified
if (0 < $numparts) {
echo “parm1: “. $parmParts[0]. “<br/><br/>”;
}

// 2nd parameter if specified
if (1 < $numparts) {
echo “parm2: “. $parmParts[1]. “<br/><br/>”;
}

// 3rd parameter if specified
if (2 < $numparts) {
echo “parm3: “. $parmParts[2]. “<br/><br/>”;
}

// 4th parameter if specified
if (3 < $numparts) {
echo “parm4: “. $parmParts[3]. “<br/><br/>”;
}

// 5th parameter if specified
if (4 < $numparts) {
echo “parm5: “. $parmParts[4]. “<br/><br/>”;
}

// 6th parameter if specified
if (5 < $numparts) {
echo “parm6: “. $parmParts[5]. “<br/><br/>”;
}

echo “=============================<br/><br/>”;

// see if cow was specified
if ($parmParts[2] == “cow”) {
echo “Found the cow <br/>”;
} else {
echo “Still looking for the cow <br>”;
}

?>