asked on

PHP - modify class to use temporary MySQL table?

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

Pawan Kumar

Do you want to iterate through the temp table?

sabecs

ASKER

Yes that is correct, I want to use the temp table.

Pawan Kumar

Great ! So now tell me you have a temporary table, what do you want to do with it, exact requirement?

sabecs

ASKER

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?

Kim Walker

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?

sabecs

ASKER

I read somewhere that using 'p:localhost' will provide a persistent connection.
Can I just place this code inside the class somehow?

Pawan Kumar

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.

skullnobrains

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.

sabecs

ASKER

Thanks for your comments, very much appreciated.
A where clause sounds like a great idea, how would I code it?

ASKER CERTIFIED SOLUTION

skullnobrains

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sabecs

ASKER

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?

sabecs

ASKER

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

SOLUTION

skullnobrains

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sabecs

ASKER

Thanks for your help, very much appreciated.

skullnobrains

my pleasure. happy coding to you