#StackBounty: #custom-post-types #database #query #search #terms Custom Search | Wrong output & question

Bounty: 50

Currently I am working on a search function for a custom post type. The custom post type “posts” are transformed from an XML to the database every X hours. Would use rest but yeah … work with what you have.

Anyway, I have added a function to use the search to search directly for the ZIP code or Location in the main search input. But for some reason, even if I search for a zip, which is pretty clear and shouldn’t show wrong results, it shows wrong results.

function abc18_search_where( $where ) {
    global $pagenow, $wpdb;

    if ( is_search() ) {
        $where = preg_replace(
            "/(s*".$wpdb->posts.".post_titles+LIKEs*('[^']+')s*)/",
                        "((abc18meta.meta_key = 'plz') AND (abc18meta.meta_value = $1))
                        OR ((abc18meta.meta_key = 'ort') AND (abc18meta.meta_value = $1))
                        OR ((abc18meta.meta_key = 'bundesland') AND (abc18meta.meta_value = $1))
                        OR ((abc18meta.meta_key = 'regionaler_zusatz') AND (abc18meta.meta_value = $1))
                        AND (".$wpdb->posts.".post_type != 'post')", $where );
    }

    return $where;
}
add_filter( 'posts_where', 'abc18_search_where' );

The whole code for all that’s related to the search is:

/* Immo Search */
// Join left
function abc18_search_join( $join ) {
    global $wpdb;

    if ( is_search() ) {
        $join .=' LEFT JOIN '.$wpdb->postmeta. ' abc18meta ON '. $wpdb->posts . '.ID = abc18meta.post_id ';
    }

    return $join;
}
add_filter('posts_join', 'abc18_search_join' );
// include in search query
function abc18_search_where( $where ) {
    global $pagenow, $wpdb;

    if ( is_search() ) {
        $where = preg_replace(
            "/(s*".$wpdb->posts.".post_titles+LIKEs*('[^']+')s*)/",
                        "((abc18meta.meta_key = 'plz') AND (abc18meta.meta_value = $1))
                        OR ((abc18meta.meta_key = 'ort') AND (abc18meta.meta_value = $1))
                        OR ((abc18meta.meta_key = 'bundesland') AND (abc18meta.meta_value = $1))
                        OR ((abc18meta.meta_key = 'regionaler_zusatz') AND (abc18meta.meta_value = $1))
                        AND (".$wpdb->posts.".post_type != 'post')", $where );
    }

    return $where;
}
add_filter( 'posts_where', 'abc18_search_where' );
//prevent dubs
function abc18_search_distinct( $where ) {
    global $wpdb;

    if ( is_search() ) {
        return "DISTINCT";
    }

    return $where;
}
add_filter( 'posts_distinct', 'abc18_search_distinct' );

Next problem is, I am trying to let people filter whatever kind of object they need and if they search for something to buy or to rent, so I’ve made a custom search form and a result page, but the problem is, that the XML is based on a german standard called OpenImmo and (why the hell ever) exports the distribution like “buy=1/0” and/or “rent 1/0” instead of making it “distribution=1/2/3/4” I still have to check if it is EITHER miete (rent) OR kauf (buy) by hand. Right now it is not really working fine at all. So if anyone could help me, that would be super awesome.

Search form:

Result:

<?php
$args2 = array(
    'posts_per_page' => 10,
    'post_type' => 'immomakler_object',
    'meta_query' => array(
    'relation' => 'AND',
    array(
        'key' => 'objektart',
        'value' => $_GET['objektart'],
        'compare' => '='
    ),
    array(
        'relation' => 'OR',
        array(
            'key' => 'kauf',
            'value' => $_GET['kauf'],
            'compare' => 'EXISTS'
        ),
        array(
            'key' => 'miete',
            'value' => $_GET['miete'],
            'compare' => 'EXISTS'
        )
    )
)
);
$loop2 = new WP_Query( $args2 );
while ( $loop2->have_posts() ) : $loop2->the_post();
echo '<h3><a href="';
the_permalink();
echo '">';
the_title();
echo '</a></h3>';
endwhile;
?>


Get this bounty!!!

#StackBounty: #php #database #laravel #database-design The database is correctly structured to handled the scenario when "all_part…

Bounty: 50

I have a context where I have a form for a user do a registration in a congress. And there are two different scenarios, if “all_participant” is “1” in the congress table is necessary to collect the name and surname about each participant that the user is registering, if “all_participant” is “0” is only necessary to use the info (name, surname, auth id) of the auth user (the user that is doing the registration) for the registration.

When “all_participant” is “1” it works like:
For example if the user “John” selected 2 tickets, one ticket of the ticket type “tt1” and other of the ticket type “tt2” and “all_participants” is 1 which means that he needs to enter the name and surname of each participant to associate each participant to a ticket type. So it will appear two name and surname fields in the registration form. And the John enter in one name field his name and in one surname field his surname and in the other name field the name of his collueague Jake and in the other surname field the surname of his collueague Jake.

Then, each ticket type can have 1 or more custom question(s) associated, for example, the ticket type “tt1″ can have the custom question Whats your phone?” associated and the ticket type “ticket type 2” dont have any custom question associated. So it will appear only one field “Whats your phone?” in the registration form, because only the ticket type “tt1” has 1 custom question associated.

When the user click “Go to step 2” in the registration form, is inserted an entry in the Registrations table, an entry for each participant in Participants table and entries in the answers table relative to the answers to the custom questions. So the database stays like below when the user click in “Go to step 2” in the registration form:

Registrations table: 
id       status        congress_id        main_participant_id
7          C              1                         1   
Participants table:
id   registration_id      ticket_type_id        name        surname
12        7                     1                  John         W
13        7                     2                   Jake        Y
Answers table:
id    participant_id     question_id      answer
2           12              1               0002
3            13             1               0003

When “all_participant” is “0” it works like: (DOUBT)

My doubt is about how to store the information when “all_participants” is “0”. So the user John that is doing the registration selected 2 tickets, one ticket of the ticket type “tt1” and other of the ticket type “tt2”, and the ticket type “tt1” has 1 custom question associated and now “all_participants” is “0”, which means that is not necessary to collect info about each participant, is only necessary to use the info of the auth user to do the registration.

But if there are custom questions is necessary that the auth user (the user that is doing the registration) answer these custom questions, but if “all_participant” is “0” only the user that is doing the registration needs to answer these questions, so for example if the user selected two tickets and 1 or more have some custom questions associated, in the registration form, besides the user selected 2 tickets in the registration form it will only appear once the custom question(s) and not twice because is only for the user that is doing the registration to answer. So in this scenario when user clicks in the “Go to step 2” in the registration form the database stays like:

Registrations table:
id       status        congress_id        main_participant_id
10         C                1                   1   

Participants table: (name and surname and blank because when "all_participant" is "0" is not necessary to collect name and surname of each participant)
id   registration_id      ticket_type_id        name        surname
18        10                     1                          
19        10                     2                   
Answers table:
id    participant_id     question_id      answer
4           18              1               0002

Doubt:

My doubt is if you know if this is structured correctly because as it is it seems that is not possible to know to which user the answers belong
when “all_participant” is “0” and there are custom questions in 1 or more ticket types selected by the user. Because the ansers table only have the participant_id which in this case is “18” but the user that did the registration is the user in the users table with id “1”.

The main_participant_id in the registrations table is the id of the user in the users table that did the registration which allow to know which user did the registration.

Relationships relevant for the question:

1 to many between Congresses and Registrations
1 to many between Congresses and TicketTypes
1 to many between Registrations and Participants
1 to many between TicketTypes and Participants
1 to many between Participants and Answers
1 to many between Questions and Answers
Many to Many between TicketTypes and Questions
1 to many between Congresses and Questions


Get this bounty!!!

#StackBounty: #database #uicomponent #plugin #magento2.2.3 Magento 2 : Notice Error – Array to String conversion

Bounty: 50

I’ve been trying to save Multi Select field values to DB.

I’ve added field to sales rule form using ui_component.

<fieldset name="exclude_brands">
    <argument name="data" xsi:type="array">
        <item name="config" xsi:type="array">
            <item name="label" xsi:type="string" translate="true">Exclude Brands</item>
            <item name="collapsible" xsi:type="boolean">true</item>
            <item name="sortOrder" xsi:type="number">15</item>
        </item>
    </argument>
    <field name="start_date" formElement="date">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="source" xsi:type="string">sales_rule</item>
            </item>
        </argument>
        <settings>
            <validation>
                <rule name="validate-date" xsi:type="boolean">true</rule>
            </validation>
            <dataType>text</dataType>
            <label translate="true">Start Date</label>
            <visible>true</visible>
            <dataScope>start_date</dataScope>
        </settings>
    </field>
    <field name="end_date" formElement="date">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="source" xsi:type="string">sales_rule</item>

            </item>
        </argument>
        <settings>
            <validation>
                <rule name="validate-date" xsi:type="boolean">true</rule>
            </validation>
            <dataType>text</dataType>
            <label translate="true">End Date</label>
            <visible>true</visible>
            <dataScope>end_date</dataScope>
        </settings>
    </field>
    <field name="brand_ids" formElement="select">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="source" xsi:type="string">sales_rule</item>
            </item>
        </argument>
        <settings>
            <validation>
                <rule name="validate-brands" xsi:type="boolean">true</rule>
            </validation>
            <dataType>varchar</dataType>
            <label translate="true">Brand List</label>
            <dataScope>brand_ids</dataScope>
        </settings>
        <formElements>
            <select>
                <settings>
                    <options class="VendorModuleBlockSalesRuleBrandList"/>

                </settings>
            </select>
        </formElements>
    </field>
</fieldset>

To save this field i’ve created plugin with afterexecute method.

<type name="MagentoSalesRuleControllerAdminhtmlPromoQuoteSave">
    <plugin name="save_brands" type="VendorModuleModelPluginbrands" sortOrder="1"/>
</type>

Vendor/Module/Model/Plugin/Brands.php

public function afterexecute(MagentoSalesRuleControllerAdminhtmlPromoQuoteSave $save,$result)
{

    $ruleId =  $save->getRequest()->getParam('rule_id'); 

    $brandIds = implode(',',$save->getRequest()->getParam('brand_ids'));

    try{
        $salesRuleData =  $this->salesRule->load($ruleId);
        $salesRuleData->setStartDate($save->getRequest()->getParam('start_date'));
        $salesRuleData->setEndDate($save->getRequest()->getParam('end_date'));
        $salesRuleData->setBrandIds($brandIds);
        $salesRuleData->save();         
    }
    catch(Exception $e)
    {
        echo $e->getMessage();  
    }

}

Now, issue is field value is saving to DB but, Getting exception like below.

Something went wrong while saving the rule data. Please review the error log.

Check Below Error Log.

(Exception(code: 0): Notice: Array to string conversion in C:\xampp\htdocs\mage2\vendor\magento\zendframework1\library\Zend\Db\Statement\Pdo.php on line 228 at C:\xampp\htdocs\mage2\vendor\magento\framework\App\ErrorHandler.php:61)"} []

Anyone facing same issues before. Really need help with this.

Thanks in Advance.


Get this bounty!!!

#StackBounty: #magento-1.9 #database #mssql Add connection to MS SQL database

Bounty: 50

I want to add a connection to our ERP database that uses MS-Server, but i can’t figure out what I’m doing wrong.

I know how to connect to another MySQL DB, but for MSSQL?

config.xml

<resources>
    <erp_db>
        <connection>
            <host><![CDATA[1.2.3.4somename]]></host>
            <username><![CDATA[user]></username>
            <password><![CDATA[password]]></password>
            <dbname><![CDATA[db_name]]></dbname>
            <!-- ??? -->
            <model>mssql</model>
            <type>pdo_mssql</type>
            <!-- ??? -->
            <active>1</active>
        </connection>
    </erp_db>
    <erp_write>
        <connection>
            <use>erp_db</use>
        </connection>
    </erp_write>
    <erp_read>
        <connection>
            <use>erp_db</use>
        </connection>
    </erp_read>
    <erp_setup>
        <connection>
            <use>core_setup</use>
        </connection>
    </erp_setup>
</resources>

If I try this to list all tables …

$resource   = Mage::getSingleton('core/resource');
$connection = $resource->getConnection('erp_db');
$results    = $connection->fetchAll('SELECT * FROM sysobjects WHERE xtype="U"');
var_dump($results);

I get …

Fatal error: Class name must be a valid object or a string in /app/code/core/Mage/Core/Model/Resource.php on line 225

I also read Using MS SQL with Magento 1.9, some say it’s not supported, but from comments it seems to be possible …

The PDO adapter exists in Zend_Db_Adapter_Pdo_Mssql, but how to correctly use it?


Get this bounty!!!

#StackBounty: #magento2 #magento-2.1 #database #sales-order Random integrity constraint errors saving orders in Magento 2.1 after payment

Bounty: 50

I have a system that generates quotes, then submits the quotes, which processes a payment. The system is pretty standard: nothing really complicated is going on. The quotes have a single product in them.

After the payment is processed, Magento saves the Order and the payment. However, sometimes we get an exception:

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (sales_order_address, CONSTRAINT SALES_ORDER_ADDRESS_PARENT_ID_SALES_ORDER_ENTITY_ID FOREIGN KEY (parent_id) REFERENCES sales_order (entity_id) ON DELETE CASCADE) in /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:228

This error suggests to me that it is trying to save the sales_order_address for a non-existent sales_order record, but why would that happen?

The payment was processed successfully on the backend but there is nothing recorded in the database to indicate that. Retrying the same quote again with the same input data usually results in success – the error appears to be transient.

How can I figure out what is causing the sales_order record to be deleted? How is this even possible? I tried searching the Magento code for a transaction rollback that swallows an exception, but didn’t find any. We are using a third-party Stripe payment module but the order-saving part happens after the payment was successfully placed. From what I can see whatever error is happening here is entirely in the core code. The error is happening on the latest (2.1.11) version.


Get this bounty!!!

#StackBounty: #database #wordpress #workflow #multisite WordPress multisite and single database for all developers

Bounty: 100

I’m in the process of planning a development team workflow for a WordPress multisite setup. We’ve chosen multisite because we want to limit the update/upgrade process and because the vast majority of our clients have the same website structure/requirements as far as the back-end goes. For the front-end, we’re using a ‘vanilla’ theme that contains our css/js frameworks and the main settings, post types, options etc and we will be building all our websites based on our vanilla theme and extending it for each client via child themes.
I’ve decided to keep everything under version control (Git), even WordPress core files.

As far as our current development environment: instead of a LAMP stack or a VVV/Docker setup, we’ve been using a centralized web server and each developer accesses his projects via a url that maps to his local repo by using a separate vhost per project (site). So, when working on projectA, John’s url is http://john.projectA.dev, whereas Jack’s url is http://jack.projectA.dev.

Using the same DB for all developers initially seemed like the ‘logical’ way to go in order to ensure data consistency, common settings etc but I’ve not been able to tie the ends yet. Due to the way WordPress stores the siteurl and homeurl URLs in the wp_options table, I’ve failed to find a way to map John to the site he’ll be working on via URL, or to allow all developers access the common multisite’s wp-admin which defaults to one of the user’s URL (ie http://bob.wpmulisite.dev where Bob is the developer who initially set up the multisite environment.
I tried overriding the urls by defining them in custom wp-configs per developer but that didn’t go far as the WP_HOME and WP_SITEURL are ignored in WP multisite.

I don’t like the idea of having to work with db dumps, replacing urls in the dump and importing it back into a database for each user because I fear that the process will soon lead to problems and it will inflict too much merging, diff-ing etc. However, I may be wrong and I’m absolutely open to any suggestions.

I’ve outlined my main problem above, if you find it unclear I’ll be happy in rephrasing it. At this point I’m not very sure of what level of detail you might be interested in so just ask me and I’ll dig in.


Get this bounty!!!

#StackBounty: #magento-1.9 #database #magento1.9.3 #cash-on-delivery #pincodes How to add more pincodes in Cash on Delivery restriction…

Bounty: 50

I am using custom module Cash on delivery restriction by zipcodes for Magento 1.9.3.7

enter image description here
I am having an issue to add pincodes more than 10 thousand. This module only accepts 8 thousand pincodes.

here is Observer class

<?php
class Mfp_Cod_Model_Observer {
public function getCashOnDelvery(Varien_Event_Observer $observer) {
        $event           = $observer->getEvent();
        $method          = $event->getMethodInstance();
        $result          = $event->getResult();
        $isModuleEnable = Mage::getStoreConfig('cod/cod/enable');

        if($isModuleEnable) {

            if($method->getCode() == 'phoenix_cashondelivery' ){

                $quote = Mage::getSingleton('checkout/cart')->getQuote();
                $add = $quote->getShippingAddress();
                $postcode = $add->getData('postcode');

                $comparisonMode = Mage::getStoreConfig('cod/cod/mode');
                $zipCodes = Mage::getStoreConfig('cod/cod/zipcode');
                $isExist = false;

                if(trim($zipCodes) == '') {             
                    $result->isAvailable = true;
                } else {    

                    if(strpos($zipCodes, $postcode) !==  false) {
                        $isExist = true;
                    }


                    if($isExist != true) {

                        $zipCodesArray = explode(',', nl2br($zipCodes));
                        if(count($elementLineArray) > 1) {
                            foreach($zipCodesArray as $codzipLine) {
                                $elementLineArray = explode('-', $codzipLine);
                                if(count($elementLineArray) == 2 && ( $postcode >= $elementLineArray[0] && $postcode <= $elementLineArray[1] )) {
                                    $isExist = true;
                                    break;
                                } else if($postcode == $codzipLine) {
                                    $isExist = true;
                                    break;
                                }
                            }
                        }
                    }

                    $returnValue = '';
                        $returnValue = ($isExist)?true:false;

                    $result->isAvailable = $returnValue;

                }   

            } 
        }   
      }
    }

can anyone help me to resolve this issue. is there any limitation for data saving. How can I add more than 10 thousand zipcodes?


Get this bounty!!!

#StackBounty: #magento-1.9 #database #ce-1.9.0.1 #ce-1.9.1.0 #magento1.9.3 How to add more pincodes in Cash on Delivery restriction mod…

Bounty: 50

I am using custom module Cash on delivery restriction by zipcodes for magento 1.9.3.7

enter image description here
I am having issue to add pincode more than 10 thousand. This module only excepts 8 thousand pincodes.

here is Observer class

public function getCashOnDelvery(Varien_Event_Observer $observer) {
    $event           = $observer->getEvent();
    $method          = $event->getMethodInstance();
    $result          = $event->getResult();
    $isModuleEnable = Mage::getStoreConfig('cod/cod/enable');

    if($isModuleEnable) {

        if($method->getCode() == 'phoenix_cashondelivery' ){

            $quote = Mage::getSingleton('checkout/cart')->getQuote();
            $add = $quote->getShippingAddress();
            $postcode = $add->getData('postcode');

            $comparisonMode = Mage::getStoreConfig('cod/cod/mode');
            $zipCodes = Mage::getStoreConfig('cod/cod/zipcode');
            $isExist = false;

            if(trim($zipCodes) == '') {             
                $result->isAvailable = true;
            } else {    

                if(strpos($zipCodes, $postcode) !==  false) {
                    $isExist = true;
                }


                if($isExist != true) {

                    $zipCodesArray = explode(',', nl2br($zipCodes));
                    if(count($elementLineArray) > 1) {
                        foreach($zipCodesArray as $codzipLine) {
                            $elementLineArray = explode('-', $codzipLine);
                            if(count($elementLineArray) == 2 && ( $postcode >= $elementLineArray[0] && $postcode <= $elementLineArray[1] )) {
                                $isExist = true;
                                break;
                            } else if($postcode == $codzipLine) {
                                $isExist = true;
                                break;
                            }
                        }
                    }
                }

                $returnValue = '';
                    $returnValue = ($isExist)?true:false;

                $result->isAvailable = $returnValue;

            }   

        } 
    }   
}

}

can anyone help me to resolve this issue. is there any limitation for data saving. How can I add more than 10 thousand zipcodes?


Get this bounty!!!

#StackBounty: #database #sql-server #odbc Link to Skyward Database to MS SQL Server

Bounty: 50

I am trying to connect Skyward Database( Progress Database ) to MS SQL Server. What am I doing to connect?
First Setup an ODBC connection to the Progress database in the ODBC Administrator in the System DSN. ( In System DSN, I am selecting ODBC driver for SQL server ). During setup, I am entering IP address on the server name. After that, I test my connection, it gives an error: Connection was not establishing.
I don’t know why is not connecting I am entering hostname correctly.

Please help me to connect skyward database and one more thing skyward is hosted on another server which is cloud-based.


Get this bounty!!!

#StackBounty: #magento-2.1 #database #upgrade #magento2.2.2 Upgrading M2.1.9 to Magento 2.2.2 is very slow

Bounty: 50

We are trying to upgrade a Magento 2.1.9 site to Magento 2.2.2
We have more than 130k products and the process seems to be stuck for more than 3 hours on

Upgrading data..
Module 'Magento_Backend':
Module 'Magento_Catalog':

I used xdebug to profile what process takes so long and apparently problem is:

if (version_compare($context->getVersion(), '2.2.2') < 0) {
            $this->upgradeWebsiteAttributes->upgrade($setup);
        }

Having many products and many attributes the process takes ages. Actually it’s still stuck there so it’s been 3 days. The database size it’s 47GB


Get this bounty!!!