Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 132
  • Last Modified:

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

0
sabecs
Asked:
sabecs
  • 7
  • 4
  • 3
  • +1
2 Solutions
 
Pawan KumarDatabase ExpertCommented:
Do you want to iterate through the temp table?
0
 
sabecsAuthor Commented:
Yes that is correct, I want to use the temp table.
0
 
Pawan KumarDatabase ExpertCommented:
Great ! So now tell me you have a temporary table, what do you want to do with it, exact requirement?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sabecsAuthor Commented:
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
 
Kim WalkerWeb Programmer/TechnicianCommented:
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
 
sabecsAuthor Commented:
I read somewhere that using 'p:localhost' will provide a persistent connection.
Can I just place this code inside the class somehow?
0
 
Pawan KumarDatabase ExpertCommented:
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
 
skullnobrainsCommented:
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
 
sabecsAuthor Commented:
Thanks for your comments, very much appreciated.
A where clause sounds like a great idea, how would I code it?
0
 
skullnobrainsCommented:
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
 
sabecsAuthor Commented:
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
 
sabecsAuthor Commented:
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
 
skullnobrainsCommented:
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
 
sabecsAuthor Commented:
Thanks for your help, very much appreciated.
0
 
skullnobrainsCommented:
my pleasure. happy coding to you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now