how to fetch data from database for custom non-table view?

  • Resolved rlohmj

    (@rlohmj)


    3 years, 5 months ago

    Hi Peter,
    I would like to thank you for the awesome plugin!

    However my front-end interface is not in a table format and I require help fetching data from the database to display as described below:

    1) My current single highly-inefficient custom database table has columns of information containing:
    a) seller’s personal details,
    b) sellers’ store details,
    c) stores’ categories (in csv style string, eg:”entertainment,food-drink,fashion”),
    d) store’s products and details (1 data row per product, resulting in “repeated” data rows for same store),
    e) products’ categories (in csv style string).

    2) One of my front-end page displays product information in a “product catalog” form:
    a) I need to filter the data to display only products of a particular category,
    b) I need to sort the filtered data to display products by price in ascending order,
    c) I need to display only the product’s image, price and name in a 2-column border-less grid with hyperlink to individual product’s page. (The product’s image is currently stored as source URL string.)

    3) Another front-end page displays store information in 2 distinct columns.
    The left column displays the sellers’ store names as a clickable list.
    The right column displays the store’s “storefront” banner when its store name from the left column is clicked.
    a) I need to filter the data to display only stores of a particular category,
    b) I need to sort the data to display store names in ascending order,
    c) I need to display only the store names on the left column.
    d) I need to display only the store’s “storefront” banner on the right column only when its store name in the left column is clicked. (When another store name is clicked, the current “storefront” banner is replaced with the new banner. The banner’s image is currently stored as source URL string.)

    The shortcodes available is WP Data Access (free) to display data is in a table form which is not what I require.

    My current working pages use the Shortcoder plugin to dynamically add manually exported json data from the current database to each store/product category page using the page slug for javascript integration as it is still of small scale.
    When there are many store/product categories in the future, it will be a result in extreme page lag or page timeout.

    How do I fetch the data needed from the database for my display requirements described in points (2) and (3) above? Assume that I do not need unique store names for point (3) since I’ll need to cleanup the database and design it to be efficient using table “1:n” relationships.

Viewing 7 replies - 1 through 7 (of 7 total)
  • Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi @rlohmj,

    What kind of layout are you looking for? Is there a public URL I can visit to get an idea of the required outout?

    If you want to access your database directly from PHP, you can use WordPress class wpdb. Or you can use WP Data Access class WPDADB to connect to a remote database. Here is a minimal example showing how to use WPDADB:
    https://code-manager.com/code/?wpda_search_code_name=Execute%20query%20from%20PHP%20shortcode

    Does this help?

    Best regards,
    Peter

    Thread Starter rlohmj

    (@rlohmj)

    Hi @peterschulznl

    Sample ideal look from some other site:
    1) example home page has carousel ads banner/img at sidebar and body:
    home carousels
    – the images/banners has hyperlink to relevant pages/sites.
    2) example product category page has a header (red dots), 2 categories (black dots) and several products (green & orange dots)
    product list initial load
    – the images/banners has hyperlink to relevant product pages/sites.
    3) example store category page has a list on the left side and an “IMG” area on the right.
    store list initial load
    – “IMG” area has some placeholder text to guide people to click something from the list.
    1st click
    – IMG banner shown after clicking something on the list.
    2nd click
    – IMG banner is replaced with new IMG banner when clicking something else on the list.
    non banner type
    – Example of non-IMG banner showing some details of the store.

    I was allowed to see whats under the hood for those pages on that sample site and to modify them for my own use.
    They used a json data array to store the info on each relevant page and used javascript to “create” the innerHTML in a procedural manner such that the only difference between similar page types is the json data only.

    Example <script> used in sample (1) in its entirety, excluding the json data:

    
    /**
     *	Add/Change images to the list using the function "processRotatingData()".
     *
     *	Format of the function "processRotatingData()":
     *	processRotatingData([{
     *			"loc": "{sidebar / body}",
     *			"set": "{set number}",
     *			"desc": "{image short description / Company Name}",
     *			"source": "{image source}",
     *			"link": "{image hyperlink}"
     *		},
     *		{
     *			"loc": "{sidebar / body}",
     *			"set": "{set number}",
     *			"desc": "{image short description / Company Name}",
     *			"source": "{image source}",
     *			"link": "{image hyperlink}"
     *		}
     *	]);
     *
     *	For image sets that "rotate", use the same {set number}.
     *	"loc": "sidebar" starts with first image in the same set.
     *	"loc": "body" starts with a random image in the same set.
     */

    // Initiate JSON data processing
processRotatingData(some JSON data);

    // Process the JSON data
    function processRotatingData(rows) {
        // Iterate all rows and add slides
        for (var i = 0; i < rows.length; i++) {
            // Check last slide in set to start rotating
            var startRotate;
            if (i == (rows.length - 1)) {
                startRotate = true;
            } else {
                if (rows[i].set != rows[i + 1].set) {
                    startRotate = true;
                } else {
                    startRotate = false;
                }
            }
            slides_add(rows[i].loc, rows[i].set, rows[i].desc, rows[i].source, rows[i].link, i, startRotate);
        }
    }
    // append image to list and rotate when ready
    function slides_add(Loc, sI, Desc, Source, Link, thumbI, startRotate) {
        this.div = document.createElement("DIV");
        this.div.style.display = "none";
        this.div.className = "slideSet" + sI;
        if (Loc == "body" && document.getElementById("featuredBody") != null) {
            document.getElementById("featuredBody").appendChild(this.div);
            // thumb dots
            this.spanDots = document.createElement("SPAN");
            this.spanDots.className = "thumbDot";
            document.getElementById("featuredBodyMiniPics").appendChild(this.spanDots);
            this.spanDots.onclick = function () {
                currentSlide(thumbI + 1);
            };
            // thumbnails
            /*this.divMini = document.createElement("DIV");
            this.divMini.className = "miniPicContainer";
            document.getElementById("featuredBodyMiniPics").appendChild(this.divMini);
            this.imgMini = document.createElement("IMG");
            this.imgMini.className = "miniPic cursor";
            this.imgMini.style.width = "20%";
            this.imgMini.loading = "lazy";
            this.imgMini.style.height = "auto";
            this.imgMini.src = Source;
            this.imgMini.alt = Desc;
            this.imgMini.onclick = function() {currentSlide(thumbI+1);};
            this.divMini.appendChild(this.imgMini);*/
        }
        if (Loc == "sidebar" && document.getElementById("featuredSideBar") != null) {
            document.getElementById("featuredSideBar").appendChild(this.div);
        }
        this.a = document.createElement("A");
        this.a.rel = "noopener nofollow";
        this.a.style.textDecoration = "none";
        if ((Link.search("tel:") != 0) && (Link.search("mailto:") != 0)) {
            this.a.target = "_blank";
        }
        this.a.href = Link;
        this.div.appendChild(this.a);
        this.img = document.createElement("IMG");
        this.img.loading = "lazy";
        this.img.style.width = "100%";
        this.img.style.height = "auto";
        this.img.src = Source;
        this.img.alt = Desc;
        this.a.appendChild(this.img);
        // rotate if ready
        if (startRotate) {
            // only show selected image
            this.showSlides = function (s, n, t) {
                if (n > s.length) {
                    n = 1;
                }
                if (n < 1) {
                    n = s.length;
                }
                for (var i = 0; i < s.length; i++) {
                    s[i].style.display = "none";
                    if (t != null) {
                        t[i].classList.remove("activeThumbDot");
                    }
                }
                s[n - 1].style.display = "block";
                if (t != null) {
                    t[n - 1].classList.add("activeThumbDot");
                }
                return n;
            };
            if (Loc == "body" && document.getElementById("featuredBody") != null) {
                // prev & next buttons
                this.aPrev = document.createElement("A");
                this.aPrev.className = "prev";
                this.aPrev.innerHTML = "?";
                this.aPrev.onclick = function () {
                    plusSlides(-1);
                };
                document.getElementById("featuredBody").appendChild(this.aPrev);
                this.aNext = document.createElement("A");
                this.aNext.className = "next";
                this.aNext.innerHTML = "?";
                this.aNext.onclick = function () {
                    plusSlides(1);
                };
                document.getElementById("featuredBody").appendChild(this.aNext);
                // set thumbnail width
                /*var thumbs = document.getElementById("featuredBodyMiniPics").getElementsByClassName("miniPicContainer");
                for (var i = 0; i < thumbs.length; i++) {
                  thumbs[i].style.width = "calc(100% / " + thumbs.length + ")";
                }*/
                var thumbs = document.getElementById("featuredBodyMiniPics").getElementsByClassName("thumbDot");
                // get slides
                var slides = document.getElementById("featuredBody").getElementsByClassName("slideSet" + sI);
                var slideIndex = Math.floor(Math.random() * slides.length);
                // set slideshow functions
                var slideTimeout;
                this.slideActive = function () {
                    slideIndex = showSlides(slides, slideIndex += 1, thumbs);
                    slideTimeout = setTimeout(function () {
                        slideActive();
                    }, 2000);
                };
                this.plusSlides = function (n) {
                    clearTimeout(slideTimeout);
                    slideIndex = showSlides(slides, slideIndex += n, thumbs);
                    slideTimeout = setTimeout(function () {
                        slideActive();
                    }, 5000);
                };
                this.currentSlide = function (n) {
                    clearTimeout(slideTimeout);
                    slideIndex = showSlides(slides, n, thumbs);
                    slideTimeout = setTimeout(function () {
                        slideActive();
                    }, 5000);
                };
                // start rotation
                slideActive();
            }
            if (Loc == "sidebar" && document.getElementById("featuredSideBar") != null) {
                var slides = document.getElementById("featuredSideBar").getElementsByClassName("slideSet" + sI);
                var slideIndex = 0;
                this.interval = setInterval(function () {
                    slideIndex = showSlides(slides, slideIndex += 1);
                }, 2000);
                slideIndex = showSlides(slides, slideIndex += 1);
            }
        }
    }

    Example <script> used in sample (3):

    
// The JSON data (if "bimg" == "-", it is Text Listing.)
var jsonData = [
    {
        "cname": something,
        "caddress": something,
        "ctel": something,
        "cemail": something,
        "cweb": something,
        "bimg": something
    },
    {
        "cname": something,
        "caddress": something,
        "ctel": something,
        "cemail": something,
        "cweb": something,
        "bimg": "-"
    }
];

// Initiate JSON data processing
processData(jsonData);

// Process the JSON data
function processData(rows) {
  // Iterate all rows and add Listing entry
  for (var i = 0; i < rows.length; i++) {
    listings_add(rows[i].cname, rows[i].caddress, rows[i].ctel, rows[i].cemail, rows[i].cweb, rows[i].bimg);
  }
}
// Banner & Listing Script from here onwards. (Do not edit.)
// append entry to list
function listings_add(cName, cAddress, cTel, cEmail, cWebsite, imgSrc) {

MIX OF: 
- something = document.createElement("something");
- something.style.something OR something.attribute = something;
- document.getElementById("something").appendChild(something);
- conditions to show "IMG Banner" or just "Text Listing", and other conditions...

}

    You mentioned accessing the database directly using PHP and the WordPress class wpdb. I am trying to code it out right now.

    Rather that hard coding it to fit my needs exactly, I wanted to make the code flexible to allow different “views” per se. If I’m not wrong, you are working on something similar for your “reports” feature for this plugin.

    However, how do I include PHP codes into my website? I am not familiar with using PHP in WordPress. As mentioned before, I used Shortcoder to include my repetitive HTML/CSS/JS code snippets in specific pages, but the plugin does not allow the use of PHP.

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi @rlohmj,

    You can use the Code Manager to add PHP code to your website:
    https://www.ads-software.com/plugins/code-manager/

    The documentation to write your own PHP shortcode can be found here:
    https://code-manager.com/blog/docs/index/shortcodes/php-shortcodes/

    Here are a number of examples:
    https://code-manager.com/code/

    Here is an example for accessing the database using WP Data Access classes:
    https://code-manager.com/code/?wpda_search_column_code_name=Execute%20query%20from%20PHP%20shortcode

    The example works for local and remote databases.

    Does this work fopr you?
    Peter

    Thread Starter rlohmj

    (@rlohmj)

    Hi @peterschulznl ,

    Oh Wow!
    Thanks for telling me about your Code Manager plugin.

    I’ll try out my hardcoded PHP code first before working on the flexible one.
    The ability to add custom parameters should make it possible for the flexible version.

    I’ll give an update if the hardcoded one works once its complete.

    Thanks!

    Thread Starter rlohmj

    (@rlohmj)

    Hi @peterschulznl ,

    Once again I want to mention how awesome your plugins are!

    Here’s an update regarding my hardcoded version and a question (at the bottom).

    The full working code for “Home Carousels” Sample (rotloc type “sidebar” is prerequisite for using rotloc type “body”):

    <?php
global $wpda_shortcode_args;
if (isset($wpda_shortcode_args['country']) && isset($wpda_shortcode_args['rotloc'])) {
    global $wpdb;
    $sitecountry = $wpdb->esc_like($wpda_shortcode_args['country']);
    $rotloc = '%' . $wpdb->esc_like($wpda_shortcode_args['rotloc']) . '%';
    $approveads = 1;
    $result = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT * FROM rotatingads WHERE rotads_loc LIKE %s ORDER BY rotads_set ASC",
            $rotloc, // an unescaped string (function will do the sanitization for you)
        )
    );
    switch ($wpda_shortcode_args['rotloc']) {
        case 'sidebar':
            echo '<style>
            /* Style Sidebar */
            #featuredSideBar>div {
                box-sizing: border-box;
                width: 100%;
                margin-bottom: 5px;
            }
        
            #featuredSideBar>div:last-child {
                margin-bottom: 0px;
            }
            </style>';
            echo '<div id="featuredSideBar">';
            $slide = 0;
            echo '<div class="slideSet">';
            foreach ($result as $row) {
                if ($slide != $row->rotads_set && $slide != 0) {
                    $slide += 1;
                    echo '</div>';
                    echo '<div class="slideSet">';
                } elseif ($slide == 0) {
                    $slide += 1;
                }
                if (substr($row->rotads_link, 0, 4) == 'tel:' || substr($row->rotads_link, 0, 7) == 'mailto:') {
                    echo '<a rel="noopener nofollow" href="' . $row->rotads_link . '" style="text-decoration: none;">';
                } else {
                    echo '<a rel="noopener nofollow" target="_blank" href="' . $row->rotads_link . '" style="text-decoration: none;">';
                }
                echo '<img loading="lazy" src="' . $row->rotads_imgsrc . '" alt="' . $row->rotads_desc . '" style="width: 100%; height: auto;">';
                echo '</a>';
            }
            echo '</div>';
            echo '</div>';
            echo '<script>
            var SlideIndex = [];
            var SlideSet = [];
            for (var setnum = 0; setnum < jQuery("#featuredSideBar .slideSet").length; setnum++) {
                SlideIndex[setnum] = 0;
                SlideSet[setnum] = jQuery("#featuredSideBar .slideSet:eq(" + setnum + ") > a");
            }
            SlideMaker();
        
            function SlideMaker() {
                for (var setnum = 0; setnum < jQuery("#featuredSideBar .slideSet").length; setnum++) {
                    SlideIndex[setnum] = ShowSlides(SlideSet[setnum], SlideIndex[setnum] += 1);
                }
                slideTimeout = setTimeout(function() {
                    SlideMaker();
                }, 2000);
            };
        
            function ShowSlides(s, n, t) {
                if (n > s.length) {
                    n = 1;
                }
                if (n < 1) {
                    n = s.length;
                }
                for (var i = 0; i < s.length; i++) {
                    s[i].style.display = "none";
                    if (t != null) {
                        t[i].classList.remove("activeThumbDot");
                    }
                }
                s[n - 1].style.display = "block";
                if (t != null) {
                    t[n - 1].classList.add("activeThumbDot");
                }
                return n;
            };
        </script>';
            break;
        case 'body':
            echo '<style>
            /* Position the image container (needed to position the left and right arrows) */
            #featuredBody {
                position: relative;
            }
        
            /* Next & previous buttons */
            #featuredBody .prev,
            #featuredBody .next {
                cursor: pointer;
                position: absolute;
                top: 0;
                width: auto;
                padding: 5px 10px;
                margin-top: -27px;
                color: rgba(0, 0, 0, 0.5);
                background-color: rgba(255, 255, 255, 0.3);
                font-weight: bold;
                font-size: 10px;
                -webkit-border-radius: 3px;
                -moz-border-radius: 3px;
                border-radius: 3px;
                -webkit-user-select: none;
                -ms-user-select: none;
                -moz-user-select: none;
                -o-user-select: none;
                user-select: none;
            }
        
            /* Position the "next button" to the right */
            #featuredBody .next {
                right: 0;
            }
        
            /* On hover, add a black background color with a little bit see-through */
            #featuredBody .prev:hover,
            #featuredBody .next:hover {
                color: white;
                background-color: rgba(0, 0, 0, 0.8);
            }
        
            /* Add a pointer when hovering over the thumbnail images */
            .cursor {
                cursor: pointer;
            }
        
            /* The dots/bullets/indicators */
            .thumbDot {
                cursor: pointer;
                height: 11px;
                width: 11px;
                margin: 7px 2px;
                background-color: #bbb;
                border-radius: 50%;
                display: inline-block;
            }
        
            .activeThumbDot,
            .thumbDot:hover {
                background-color: #717171;
            }
            </style>';
            echo '<div id="featuredBodyMiniPics" style="text-align:center; margin: 0; padding: 0; line-height: 0.5;">';
            for ($i = 1; $i <= $wpdb->num_rows; $i++) {
                echo '<span class="thumbDot" onclick="CurrentSlide(' . $i . ')"></span>';
            }
            echo '</div>';
            echo '<div id="featuredBody" style="width: 100%; margin: 0 auto; padding: 0;">';
            echo '<div class="slideSet">';
            foreach ($result as $row) {
                echo '<a rel="noopener nofollow" target="_blank" href="' . $row->rotads_link . '" style="text-decoration: none;">';
                echo '<img loading="lazy" src="' . $row->rotads_imgsrc . '" alt="' . $row->rotads_desc . '" style="width: 100%; height: auto;">';
                echo '</a>';
            }
            echo '</div>';
            echo '<a class="prev" onclick="PlusSlides(-1)">?</a>';
            echo '<a class="next" onclick="PlusSlides(1)">?</a>';
            echo '</div>';
            echo '<script>
            var slideTimeout;
            var thumbs = document.getElementById("featuredBodyMiniPics").getElementsByClassName("thumbDot");
            var bodyslides = document.getElementById("featuredBody").getElementsByClassName("slideSet")[0].getElementsByTagName("A");
            var slideIndex = Math.floor(Math.random() * bodyslides.length);
        
            function SlideActive() {
                slideIndex = ShowSlides(bodyslides, slideIndex += 1, thumbs);
                slideTimeout = setTimeout(function() {
                    SlideActive();
                }, 2000);
            };
        
            function PlusSlides(n) {
                clearTimeout(slideTimeout);
                slideIndex = ShowSlides(bodyslides, slideIndex += n, thumbs);
                slideTimeout = setTimeout(function() {
                    SlideActive();
                }, 5000);
            };
        
            function CurrentSlide(n) {
                clearTimeout(slideTimeout);
                slideIndex = ShowSlides(bodyslides, n, thumbs);
                slideTimeout = setTimeout(function() {
                    SlideActive();
                }, 5000);
            };
            SlideActive();
        </script>';
            break;
        default:
            break;
    }
}
?>

    Going to work on the “Product List” and “Store List” now.

    By the way, I notice that “PHP Shortcode” type was the only type with custom parameters tutorial in the DOCS. Can custom parameters be used in the other shortcode types as well?

    Thread Starter rlohmj

    (@rlohmj)

    Hi @peterschulznl ,

    I’m liking your plugins more and more as I use them! ??

    I completed the hardcoded version for the sample pics and merged them into a single snippet.

    Some custom parameters are needed to work, namely “country”, “service”, “port”, “rotloc” (slideshow rotation location).
    I manage to separate the sevices/ports in the sample example by putting “0”s before and after each phrase in the database such that the custom parameters for “service”/”port” = “0?? ??-??0” work very well since numbers are not commonly used to label the service or port.

    Full PHP code to display the data as per the sample images here:

    <?php
global $wpda_shortcode_args;
if (isset($wpda_shortcode_args['country']) && isset($wpda_shortcode_args['rotloc'])) {
    global $wpdb;
    $wpdb->flush();
    unset($result);
    $sitecountry = $wpdb->esc_like($wpda_shortcode_args['country']);
    $rotloc = $wpdb->esc_like($wpda_shortcode_args['rotloc']);
    $result = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT * FROM rotatingads WHERE rotads_country LIKE %s AND rotads_loc LIKE %s ORDER BY rotads_set ASC",
            $sitecountry, // an unescaped string (function will do the sanitization for you)
            $rotloc, // an unescaped string (function will do the sanitization for you)
        )
    );
    switch ($wpda_shortcode_args['rotloc']) {
        case 'sidebar':
            echo '<style>
            /* Style Sidebar */
            #featuredSideBar>div {
                box-sizing: border-box;
                width: 100%;
                margin-bottom: 5px;
            }
        
            #featuredSideBar>div:last-child {
                margin-bottom: 0px;
            }
            </style>';
            echo '<div id="featuredSideBar">';
            $slide = 0;
            echo '<div class="slideSet">';
            foreach ($result as $row) {
                if ($slide != $row->rotads_set && $slide != 0) {
                    $slide += 1;
                    echo '</div>';
                    echo '<div class="slideSet">';
                } elseif ($slide == 0) {
                    $slide += 1;
                }
                if (substr($row->rotads_link, 0, 4) == 'tel:' || substr($row->rotads_link, 0, 7) == 'mailto:') {
                    echo '<a rel="noopener nofollow" href="' . $row->rotads_link . '" style="text-decoration: none;">';
                } else {
                    echo '<a rel="noopener nofollow" target="_blank" href="' . $row->rotads_link . '" style="text-decoration: none;">';
                }
                echo '<img loading="lazy" src="' . $row->rotads_imgsrc . '" alt="' . $row->rotads_desc . '" style="width: 100%; height: auto;">';
                echo '</a>';
            }
            echo '</div>';
            echo '</div>';
            echo '<script>
            var SlideIndex = [];
            var SlideSet = [];
            for (var setnum = 0; setnum < jQuery("#featuredSideBar .slideSet").length; setnum++) {
                SlideIndex[setnum] = 0;
                SlideSet[setnum] = jQuery("#featuredSideBar .slideSet:eq(" + setnum + ") > a");
            }
            SlideMaker();
        
            function SlideMaker() {
                for (var setnum = 0; setnum < jQuery("#featuredSideBar .slideSet").length; setnum++) {
                    SlideIndex[setnum] = ShowSlides(SlideSet[setnum], SlideIndex[setnum] += 1);
                }
                setTimeout(function() {
                    SlideMaker();
                }, 2000);
            };
        
            function ShowSlides(s, n, t) {
                if (n > s.length) {
                    n = 1;
                }
                if (n < 1) {
                    n = s.length;
                }
                for (var i = 0; i < s.length; i++) {
                    s[i].style.display = "none";
                    if (t != null) {
                        t[i].classList.remove("activeThumbDot");
                    }
                }
                s[n - 1].style.display = "block";
                if (t != null) {
                    t[n - 1].classList.add("activeThumbDot");
                }
                return n;
            };
        </script>';
            break;
        case 'body':
            echo '<style>
            /* Position the image container (needed to position the left and right arrows) */
            #featuredBody {
                position: relative;
            }
        
            /* Next & previous buttons */
            #featuredBody .prev,
            #featuredBody .next {
                cursor: pointer;
                position: absolute;
                top: 0;
                width: auto;
                padding: 5px 10px;
                margin-top: -27px;
                color: rgba(0, 0, 0, 0.5);
                background-color: rgba(255, 255, 255, 0.3);
                font-weight: bold;
                font-size: 10px;
                -webkit-border-radius: 3px;
                -moz-border-radius: 3px;
                border-radius: 3px;
                -webkit-user-select: none;
                -ms-user-select: none;
                -moz-user-select: none;
                -o-user-select: none;
                user-select: none;
            }
        
            /* Position the "next button" to the right */
            #featuredBody .next {
                right: 0;
            }
        
            /* On hover, add a black background color with a little bit see-through */
            #featuredBody .prev:hover,
            #featuredBody .next:hover {
                color: white;
                background-color: rgba(0, 0, 0, 0.8);
            }
        
            /* Add a pointer when hovering over the thumbnail images */
            .cursor {
                cursor: pointer;
            }
        
            /* The dots/bullets/indicators */
            .thumbDot {
                cursor: pointer;
                height: 11px;
                width: 11px;
                margin: 7px 2px;
                background-color: #bbb;
                border-radius: 50%;
                display: inline-block;
            }
        
            .activeThumbDot,
            .thumbDot:hover {
                background-color: #717171;
            }
            </style>';
            echo '<div id="featuredBodyMiniPics" style="text-align:center; margin: 0; padding: 0; line-height: 0.5;">';
            for ($i = 1; $i <= $wpdb->num_rows; $i++) {
                echo '<span class="thumbDot" onclick="CurrentSlide(' . $i . ')"></span>';
            }
            echo '</div>';
            echo '<div id="featuredBody" style="width: 100%; margin: 0 auto; padding: 0;">';
            echo '<div class="slideSet">';
            foreach ($result as $row) {
                echo '<a rel="noopener nofollow" target="_blank" href="' . $row->rotads_link . '" style="text-decoration: none;">';
                echo '<img loading="lazy" src="' . $row->rotads_imgsrc . '" alt="' . $row->rotads_desc . '" style="width: 100%; height: auto;">';
                echo '</a>';
            }
            echo '</div>';
            echo '<a class="prev" onclick="PlusSlides(-1)">?</a>';
            echo '<a class="next" onclick="PlusSlides(1)">?</a>';
            echo '</div>';
            echo '<script>
            var slideTimeout;
            var thumbs = document.getElementById("featuredBodyMiniPics").getElementsByClassName("thumbDot");
            var bodyslides = document.getElementById("featuredBody").getElementsByClassName("slideSet")[0].getElementsByTagName("A");
            var slideIndex = Math.floor(Math.random() * bodyslides.length);
        
            function SlideActive() {
                slideIndex = ShowSlides(bodyslides, slideIndex += 1, thumbs);
                slideTimeout = setTimeout(function() {
                    SlideActive();
                }, 2000);
            };
        
            function PlusSlides(n) {
                clearTimeout(slideTimeout);
                slideIndex = ShowSlides(bodyslides, slideIndex += n, thumbs);
                slideTimeout = setTimeout(function() {
                    SlideActive();
                }, 5000);
            };
        
            function CurrentSlide(n) {
                clearTimeout(slideTimeout);
                slideIndex = ShowSlides(bodyslides, n, thumbs);
                slideTimeout = setTimeout(function() {
                    SlideActive();
                }, 5000);
            };
            SlideActive();
        </script>';
            break;
        default:
            break;
    }
} elseif (isset($wpda_shortcode_args['country']) && isset($wpda_shortcode_args['service'])) {
    global $wpdb;
    $wpdb->flush();
    unset($result);
    $sitecountry = $wpdb->esc_like($wpda_shortcode_args['country']);
    $portservice = '%' . $wpdb->esc_like($wpda_shortcode_args['service']) . '%';
    $result = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT * FROM company WHERE company_services_ads_approval = %d AND company_country LIKE %s AND company_services_list LIKE %s ORDER BY company_services_ads_type ASC, company_name DESC",
            1, // an untrusted integer (function will do the sanitization for you)
            $sitecountry, // an unescaped string (function will do the sanitization for you)
            $portservice, // an unescaped string (function will do the sanitization for you)
        )
    );
    echo '<div id="listings" class="notranslate">';
    foreach ($result as $row) {
        echo '<p style="margin: 0px;">';
        if ($row->company_services_ads_type == 'banner') {
            echo '<a href="#Top" rel="noopener nofollow" style="font-size: 12px; line-height: 1;" onclick="showListing(this)" data-imgsrc="' . $row->company_services_ads_imgsrc . '" data-imglink="' . $row->company_services_ads_link . '">';
            echo '<strong style="font-size: 14px;">' . $row->company_name . '</strong>';
        } else {
            echo '<a href="#Top" rel="noopener nofollow" style="font-size: 12px; line-height: 1;" onclick="showListing(this)" data-list="<br>' . $row->company_address . '<br>' . $row->company_phone . '<br>' . $row->company_email . '<br>' . $row->company_website . '">';
            echo $row->company_name;
        }
        echo '</a>';
        echo '</p>';
    }
    echo '</div>';
    echo '<script>
    function showListing(elem) {
        if (elem.hasAttribute("data-list")) {
            document.getElementById("listingInfo").innerHTML = "";
            elem.infoDiv = document.createElement("DIV");
            elem.infoDiv.style.padding = "0.5em";
            elem.infoDiv.style.fontSize = "12px";
            elem.infoDiv.style.lineHeight = "1";
            elem.infoDiv.style.border = "solid 1px black";
            elem.infoDiv.innerHTML = elem.innerHTML + elem.getAttribute("data-list").replace(/(\<br\>-)/g, "");
            document.getElementById("listingInfo").appendChild(elem.infoDiv);
        } else {
            document.getElementById("listingInfo").innerHTML = "";
            // no link
            if (elem.getAttribute("data-imglink") == "-") {
                elem.infoImg = document.createElement("IMG");
                elem.infoImg.loading = "lazy";
                elem.infoImg.alt = elem.getElementsByTagName("STRONG")[0].innerHTML;
                elem.infoImg.src = elem.getAttribute("data-imgsrc");
                document.getElementById("listingInfo").appendChild(elem.infoImg);
                // with link  
            } else {
                elem.infoA = document.createElement("A");
                elem.infoA.href = elem.getAttribute("data-imglink");
                if ((elem.infoA.href.search("tel:") != 0) && (elem.infoA.href.search("mailto:") != 0)) {
                    elem.infoA.target = "_blank";
                }
                elem.infoA.rel = "noopener nofollow";
                document.getElementById("listingInfo").appendChild(elem.infoA);
                elem.infoImg = document.createElement("IMG");
                elem.infoImg.loading = "lazy";
                elem.infoImg.alt = elem.getElementsByTagName("STRONG")[0].innerHTML;
                elem.infoImg.src = elem.getAttribute("data-imgsrc");
                elem.infoA.appendChild(elem.infoImg);
            }
        }
    }
    </script>';
} elseif (isset($wpda_shortcode_args['country']) && isset($wpda_shortcode_args['port'])) {
    global $wpdb;
    $wpdb->flush();
    unset($result,$slide);
    $sitecountry = $wpdb->esc_like($wpda_shortcode_args['country']);
    $portservice = '%' . $wpdb->esc_like($wpda_shortcode_args['port']) . '%';
    $result = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT * FROM company WHERE company_ports_ads_approval = %d AND company_country LIKE %s AND company_ports_code_list LIKE %s ORDER BY company_ports_ads_type ASC, company_name DESC",
            1, // an untrusted integer (function will do the sanitization for you)
            $sitecountry, // an unescaped string (function will do the sanitization for you)
            $portservice, // an unescaped string (function will do the sanitization for you)
        )
    );
    echo '<div class="clearfix" style="float: left; width: 50%;">';
    echo '<h6 style="padding: 0; margin: 5px; margin-bottom: 0px; color: #000000;">MLO / Feeder / NVOCC</h6>';
    $slide = 0;
    foreach ($result as $row) {
        if ($row->company_ports_ads_type == 'right' && $slide == 0) {
            $slide += 1;
            echo '</div>';
            echo '<div class="clearfix" style="float: left; width: 50%;">';
            echo '<h6 style="padding: 0; margin: 5px; margin-bottom: 0px; color: #000000;">Consolidator / Forwarder</h6>';
        }
        echo '<div class="clearfix" style="float: left; width: 50%;">';
        echo '<a rel="noopener nofollow" target="_blank" href="' . $row->company_ports_ads_link . '" style="display: block; margin: 3px;">';
        echo '<img loading="lazy" src="' . $row->company_ports_ads_imgsrc . '" alt="' . $row->company_name . '" style="width: 100%; height: auto;">';
        echo '</a>';
        echo '</div>';
    }
    echo '</div>';
}
?>

    Now that I’ve written the full hardcode, I’m not sure if I can make the dynamic version. Its more complex than I expected.

    For those who want some sort of quick reference, the format is as such:

    <?php
// Enable the use of custom (defined) parameters.
//    This example defines custom parameters "arg1" & "arg2" only.
global $wpda_shortcode_args;
if (isset($wpda_shortcode_args['arg1']) && isset($wpda_shortcode_args['arg2']) /* && ... more custom parameters here*/) {
    // Enable / "initialise/clean" needed variables.
    global $wpdb;
    $wpdb->flush();
    unset($result);
    $arg1 = $wpdb->esc_like($wpda_shortcode_args['arg1']); // no wildcard.
    $arg2 = '%' . $wpdb->esc_like($wpda_shortcode_args['arg2']) . '%'; // wildcard before and after.
    // The query itself to pull data from database.
    $result = $wpdb->get_results(
        $wpdb->prepare(
            '', // SQL Query using parameters such as %s / %d / %f for security.
            // list parameter values as needed in the correct order. Only $arg1 & $arg2 listed in this example.
            $arg1, // an unescaped string (%s), integer (%d) or float (%f)... (function will do the sanitization for you)
            $arg2  // an unescaped string (%s), integer (%d) or float (%f)... (function will do the sanitization for you)
        )
    );
    // make variables for "counting" or echo anything needed before looping data rows here.
    foreach ($result as $row) { // loop over each data row.
        //custom code here to use the database table values.
        $col_1_value = $row->table_column1; // example to get data of first column as a variable.
    }
    // the rest of custom code not needing data from database table.
}
?>

    The comprehensive example with mix of HTML, JS, CSS, PHP by @peterschulznl is best seen here:
    https://code-manager.com/code/?wpda_search_column_code_name=code-manager-publication-layout-panel

    • This reply was modified 3 years, 4 months ago by rlohmj.
    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Thanks for sharing! ????

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘how to fetch data from database for custom non-table view?’ is closed to new replies.