Solved

PHP - modify class to use temporary MySQL table?

Posted on 2016-10-08
15
77 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 27

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 27

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 27

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 27

Expert Comment

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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article discusses how to create an extensible mechanism for linked drop downs.
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

685 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