Solved

PHP - modify class to use temporary MySQL table?

Posted on 2016-10-08
15
60 Views
Last Modified: 2016-10-12
Hi,
I have a created a temporary `wp_ab_staff` table on another page to only include staff that service a particular Zip Code, but when function getCaSeSt executes it selects all staff.
I am a bit lost when it comes to Object-Oriented PHP, anyone know how I can modify getCaSeSt to use my temporary MySQL table?  

Thanks in advance.

  //database configuration
    $dbHost = 'p:localhost';
    $dbUsername = '$Username';
    $dbPassword = 'pass';
    $dbName = 'dbName';
    
    //connect with the database
    $db = new mysqli($dbHost,$dbUsername,$dbPassword,$dbName);
    
    //get search term
    $zip_code = $_GET['$zip_code'];
    
    //get matched data from skills table
    $query = $db->query("CREATE TEMPORARY TABLE IF NOT EXISTS wp_ab_staff AS(SELECT * FROM `wp_ab_staff` WHERE id IN (SELECT staff_id FROM wp_ab_staff_locations WHERE location_id = (SELECT id FROM `wp_ab_locations` WHERE `title` = '".$zip_code."')))");
	
	
    $query2 = $db->query("SELECT * FROM wp_ab_staff");

Open in new window


abstract class Config
{
    /**
     * Get categories, services and staff members for drop down selects
     * for the 1st step of booking wizard.
     *
     * @return array
     */


        public static function getCaSeSt()
    {
        $result = array(
            'categories' => array(),
            'services'   => array(),
            'staff'      => array(),
        );

        // This array is for collecting staff members for compound services.
        $staff_members = array();
        // Select all services (with categories and staff members)
        // which have at least one staff member assigned.
        $rows = Entities\Service::query( 's' )
            ->select( '`s`.`id`    AS `service_id`,
                IFNULL(`c`.`id`,0) AS `category_id`,
                IFNULL(`c`.`name`, \'' . Query::escape( __( 'Uncategorized', 'bookly' ) ) . '\') AS `category_name`,
                IFNULL(`s`.`title`, \'' . Query::escape( __( 'Untitled', 'bookly' ) ) . '\') AS `service_name`,
                `c`.`position`     AS `category_position`,
                `s`.`position`     AS `service_position`,
                `s`.`type`         AS `service_type`,
                `s`.`visibility`   AS `service_visibility`,
                `st`.`id`          AS `staff_id`,
                `st`.`position`    AS `staff_position`,
                `st`.`full_name`   AS `staff_name`,
                `ss`.`capacity`,
                `ss`.`price`' )
            ->innerJoin( 'StaffService', 'ss', 'ss.service_id = s.id' )
            ->leftJoin( 'Category', 'c', 'c.id = s.category_id' )
            ->leftJoin( 'Staff', 'st', 'st.id = ss.staff_id' )
            ->where( 's.type',  Entities\Service::TYPE_SIMPLE )
            ->whereNot( 'st.visibility', 'private' )
            ->fetchArray();

        foreach ( $rows as $row ) {
            $category_id = intval( $row['category_id'] );
            $service_id  = intval( $row['service_id'] );
            $staff_id    = intval( $row['staff_id'] );
            if ( ! isset( $result['services'][ $service_id ] ) ) {
                $result['services'][ $service_id ] = array(
                    'id'           => $service_id,
                    'name'         => Utils\Common::getTranslatedString( 'service_' . $service_id, $row['service_name'] ),
                    'category_id'  => $category_id,
                    'staff'        => array(),
                    'max_capacity' => $row['capacity'],
                    'position'     => $row['service_position'],
                    'visibility'   => $row['service_visibility'],
                );
            } elseif ( $result['services'][ $service_id ]['max_capacity'] < $row['capacity'] ) {
                // Detect the max capacity for each service
                // (it is the max capacity from all staff members who provides this service).
                $result['services'][ $service_id ]['max_capacity'] = $row['capacity'];
            }

            if ( ! isset( $result['staff'][ $staff_id ] ) ) {
                $result['staff'][ $staff_id ] = array(
                    'id'       => $staff_id,
                    'name'     => Utils\Common::getTranslatedString( 'staff_' . $staff_id, $row['staff_name'] ),
                    'services' => array(),
                    'position' => $row['staff_position'],
                );
            }

            if ( $row['category_id'] != '' && !isset( $result['categories'][ $category_id ] ) ) {
                $result['categories'][ $category_id ] = array(
                    'id'       => $category_id,
                    'name'     => Utils\Common::getTranslatedString( 'category_' . $category_id, $row['category_name'] ),
                    'services' => array(),
                    'position' => $row['category_position'],
                );
            }

            if ( ! isset ( $result['services'][ $service_id ]['staff'][ $staff_id ] ) ) {
                $staff_member = $result['staff'][ $staff_id ];
                unset ( $staff_member['services'] );
                $staff_members[ $service_id ][ $staff_id ] = $staff_member;
                if ( self::isPaymentDisabled() == false ) {
                    $staff_member['name'] .= ' (' . Utils\Common::formatPrice( $row['price'] ) . ')';
                }
                $result['services'][ $service_id ]['staff'][ $staff_id ] = $staff_member;
            }

            if ( ! isset ( $result['staff'][ $row['staff_id'] ]['services'][ $service_id ] ) ) {
                $service = $result['services'][ $service_id ];
                unset ( $service['staff'], $service['category_id'] );
                $service['max_capacity'] = $row['capacity'];
                $result['staff'][ $staff_id ]['services'][ $service_id ] = $service;
            }

            if ( ! isset ( $result['categories'][ $category_id ]['staff'][ $staff_id ] ) ) {
                $staff_member = $result['staff'][ $staff_id ];
                unset ( $staff_member['services'] );
                $result['categories'][ $category_id ]['staff'][ $staff_id ] = $staff_member;
            }

            if ( ! isset ( $result['categories'][ $category_id ]['services'][ $service_id ] ) ) {
                $service = $result['services'][ $service_id ];
                unset ( $service['staff'], $service['max_capacity'], $service['category_id'] );
                $result['categories'][ $category_id ]['services'][ $service_id ] = $service;
            }
        }

        $result = apply_filters( 'bookly_prepare_casest', $result, $staff_members );
        foreach ( $result['services'] as $service_id => $service ) {
            if ( $service['visibility'] == 'private' ) {
                unset( $result['services'][ $service_id ] );
            }
        }

        foreach ( array( 'categories', 'staff' ) as $key ) {
            foreach ( $result[ $key ] as $id => &$data ) {
                foreach ( $data['services'] as $service_id => $service ) {
                    if ( $service['visibility'] == 'private' ) {
                        unset( $data['services'][ $service_id ] );
                    }
                }
                if ( empty( $data['services'] ) ) {
                    unset( $result[ $key ][ $id ] );
                }
            }
        }

        return $result;
    }


    }

Open in new window

0
Comment
Question by:sabecs
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41835467
Do you want to iterate through the temp table?
0
 

Author Comment

by:sabecs
ID: 41835472
Yes that is correct, I want to use the temp table.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41835480
Great ! So now tell me you have a temporary table, what do you want to do with it, exact requirement?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:sabecs
ID: 41835494
Thanks for your feedback, I want the getCaSeSt to use my temp table but it's not?
Can getCaSeSt be altered to use staff from my temp table?
0
 
LVL 22

Expert Comment

by:Kim Walker
ID: 41835500
I have a created a temporary `wp_ab_staff` table on another page
Temporary tables persist as long as the database connection is open. But db connections are automatically closed when the page finishes loading. I'm not aware of a way to keep a connection open from one page to the next but I admit that there could be a way that I don't know of. How are you keeping the connection alive from one page to the next?
0
 

Author Comment

by:sabecs
ID: 41835511
I read somewhere that using 'p:localhost' will provide a persistent connection.
Can I just place this code inside the class somehow?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41835534
I think if you can place the logic you need in the DB only.

What is the requirement after creating the temp table? We can do that in mySQL only.
0
 
LVL 26

Expert Comment

by:skullnobrains
ID: 41836035
what about adding a where clause to the query around line 40 ?
if the lib is used elsewhere you can add a parameter to getCaSeSt()

if the tmp table is created in a different page, you cannot reliably expect it to be used by this one ( not without weird setups anyway ), and even if you do both on the same page, you'd need to use the same mysql connection and your class probably handles it's own connection to the db. simply put, you most likely need to find a different way.
0
 

Author Comment

by:sabecs
ID: 41836148
Thanks for your comments, very much appreciated.
A where clause sounds like a great idea, how would I code it?
0
 
LVL 26

Accepted Solution

by:
skullnobrains earned 500 total points
ID: 41836661
you don't provide the base classes or the db schema or your initial base attempt... why ?

mostly guessing, something like this should get you on the right track

$rows = Entities\Service::query( 's' )
            ->select( '`s`.`id`    AS `service_id`,
                IFNULL(`c`.`id`,0) AS `category_id`,
                IFNULL(`c`.`name`, \'' . Query::escape( __( 'Uncategorized', 'bookly' ) ) . '\') AS `category_name`,
                IFNULL(`s`.`title`, \'' . Query::escape( __( 'Untitled', 'bookly' ) ) . '\') AS `service_name`,
                `c`.`position`     AS `category_position`,
                `s`.`position`     AS `service_position`,
                `s`.`type`         AS `service_type`,
                `s`.`visibility`   AS `service_visibility`,
                `st`.`id`          AS `staff_id`,
                `st`.`position`    AS `staff_position`,
                `st`.`full_name`   AS `staff_name`,
                `ss`.`capacity`,
                `ss`.`price`' )
            ->innerJoin( 'StaffService', 'ss', 'ss.service_id = s.id' )
            ->innerJoin( 'wp_ab_staff_locations', 'sl', 'sl.staff_id = wp_ab_staff.id' ) # added
            ->innerJoin( 'wp_ab_locations', 'l', 'sl.location_id = l.id' ) # added
            ->leftJoin( 'Category', 'c', 'c.id = s.category_id' )
            ->leftJoin( 'Staff', 'st', 'st.id = ss.staff_id' )
            ->where( 's.type',  Entities\Service::TYPE_SIMPLE )
            ->where( 'l.title',  $zip_code ) # added
            ->whereNot( 'st.visibility', 'private' )
            ->fetchArray();

try and get the query to work with a hard coded zip code, and then make the zip code available in the class as you see fit. may be provided as an attribute of the object or as a parameter when calling the method
0
 

Author Comment

by:sabecs
ID: 41837620
Thanks skullnobrains for your help, very much appreciated.
I am a bit lost with OOP, where can I find the  initial base attempt and is the db schema the MySQL table structures?
0
 

Author Comment

by:sabecs
ID: 41837732
Thanks for your comments, it's nearly there.

I created a staff locations class as below

<?php // Andrew E added 11/10/2016
namespace Bookly\Lib\Entities;

use Bookly\Lib;

/**
 * Class StaffLocations
 * @package Bookly\Lib\Entities
 */
class StaffLocations extends Lib\Base\Entity
{
    protected static $table = 'ab_staff_locations';

    protected static $schema = array(
        'id'            => array( 'format' => '%d' ),
        'staff_id'      => array( 'format' => '%d', 'reference' => array( 'entity' => 'Staff' ) ),
        'location_id'   => array( 'format' => '%d', 'reference' => array( 'entity' => 'Location' ) ),
    );

    protected static $cache = array();

    /** @var Service */
    public $service = null;

}

Open in new window


and then added left join and where clause as per below, just need to work how to include variable $customer_location instead of hardcoded value of 759?


		$customer_location = $_SESSION['my_location_id'];
                //if customer location is blank use 3001 
		if ($customer_location = '') $customer_location = '3001';
		
		
        // Select all services (with categories and staff members)
        // which have at least one staff member assigned.
        $rows = Entities\Service::query( 's' )
            ->select( '`s`.`id`    AS `service_id`,
                IFNULL(`c`.`id`,0) AS `category_id`,
                IFNULL(`c`.`name`, \'' . Query::escape( __( 'Uncategorized', 'bookly' ) ) . '\') AS `category_name`,
                IFNULL(`s`.`title`, \'' . Query::escape( __( 'Untitled', 'bookly' ) ) . '\') AS `service_name`,
                `c`.`position`     AS `category_position`,
                `s`.`position`     AS `service_position`,
                `s`.`type`         AS `service_type`,
                `s`.`visibility`   AS `service_visibility`,
                `st`.`id`          AS `staff_id`,
                `st`.`position`    AS `staff_position`,
                `st`.`full_name`   AS `staff_name`,
                `ss`.`capacity`,
                `ss`.`price`' )
            ->innerJoin( 'StaffService', 'ss', 'ss.service_id = s.id' )
            ->leftJoin( 'Category', 'c', 'c.id = s.category_id' )
            ->leftJoin( 'Staff', 'st', 'st.id = ss.staff_id' )
	    ->leftJoin( 'StaffLocations', 'stl', 'stl.staff_id = st.id' ) #added 
	    ->where( 'stl.location_id',  '759' ) #added 
            ->where( 's.type',  Entities\Service::TYPE_SIMPLE )
            ->whereNot( 'st.visibility', 'private' )
            ->fetchArray();

Open in new window

0
 
LVL 26

Assisted Solution

by:skullnobrains
skullnobrains earned 500 total points
ID: 41837997
is the db schema the MySQL table structures

yes but if your current query works with the 759, don't bother, since you already worked through that part successfully

just need to work how to include variable $customer_location instead of hardcoded value of 759?

yes

 ->where( 'stl.location_id',  $customer_location ) #added

note : i'm usure you want to use session variables from within your class but i have little information about your code.
if not, store the location in the object before calling the method or pass it as a parameter

you may want to preserve backwards compatibility by using a like comparison and a default value of '%'
0
 

Author Comment

by:sabecs
ID: 41839178
Thanks for your help, very much appreciated.
0
 
LVL 26

Expert Comment

by:skullnobrains
ID: 41839925
my pleasure. happy coding to you
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question