External Database Queries

[crashpaddesign]

I am writing a custom block module that has a standard mySQL query function that retreives recent posts from a Wordpress database. I am successfully able to query and get back a result from the WP database, but the query function seems to break all blocks that contain queries after my block. (e.g. my links block below the WP recents posts no longer appears). Any ideas why the conflict when doing external database queries?

Example: http://woolistic.crashpaddesign.com/ (at the bottom there should be a series of 'footer links' pulled from the default prestashop 'blocklink' module.)

Any help would be greatly appreciated!

here is my code function to query the WP database (that is working):

   private function getBlogPosts($limit) {

       // DB Config
       $server     = "xxxxxx";
       $username     = "xxxxxx";
       $password     = "xxxxxx";
       $database     = "xxxxxx";

       mysql_connect($server, $username, $password);

       @mysql_select_db($database) or die("Unable to select database");

       $query = "SELECT post_title, post_name 
                   FROM wp_posts 
                   WHERE post_type = 'post' AND post_status = 'publish' 
                   ORDER BY post_date DESC LIMIT $limit"; 

       $result = mysql_query($query) or die(mysql_error());

       $posts = array();

       if (mysql_num_rows($result)) {
           $i = 0;
           while ($post = mysql_fetch_assoc($result)) {
               $posts[$i]['post_name'] = $post['post_name'];
               $posts[$i]['post_title'] = stripslashes($post['post_title']);
               $i++;
           }        
       }

       mysql_free_result($result);

       return $posts;

   }

[ezakimak]

I am not sure , but I think you should instantiate a new database conection for your external dabatabse queries

[MrBaseball34]

I am not sure , but I think you should instantiate a new database conection for your external dabatabse queries

Isn't that what this code does?

mysql_connect($server, $username, $password);

He is not using an already connected instance.

[Rhapsody]

I'm looking to do similar with code that will do the following:

 

1. when a user logs into prestashop, also log into a mysql database on another server

2. Using the email address of the person logged in, search for a record in the external database

3. If the email address exists in the external database, get the values for 5 fields stored with that record and save them so they can be integrated to customize a purchase. The filed names are hull (text), name (text), m2012 (text), sail (text), fleet (text), USSailing (text), forumprofile (text), URL.id (number unique to record), tsp (text), membertype (text), yachtclub (text)

4. If the email address doesn't exist in the external database, leave the variable blank

 

Once these variables are stored, I would like to be able to do the following:

1. Display them as fields filled in as "Customized Fields" defined for a product in the BO (e.g the external pulled data populates these fields)

[bellini13]

hey there,

 

is it only when they log in? what happens if they have logged in on a previous visit, and they now come back to the site, and do not have to log in again, because they have an existing cookie with a valid session.

 

the reason I ask is because you should be able to extend the AuthController class to do the first 4 things.

 

I'm confused on the backoffice requirement. You are pulling the information based on a customer email address, but you want to store the information at a product level, which is not customer based. so for example...

if 5 customers log in, and you store the data 5 times. then a backoffice user logs in and looks at 'product 1', which of the 5 external data would you display? all of them?

[Rhapsody]

Thanks for the reply. I was thinking that the data is only valid for a session when the person logs in. That is for two reasons, the first as you describe as it is only valid for that person. The second reason is the data should be pulled from the external database for that session so if it was updated on the external database, fresh data is pulled.

 

I had considered merging the databases statically, but this would not ensure that current data was stored. I'm open to ideas though.

[bellini13]

perhaps describe what you are looking to accomplish. i think you are stating 'how' you are trying to accomplish something, but didn't describe 'what' you are trying to accomplish.

 

perhaps if i understand the 'what', it will make more sense to me and a solution will become more clear.

[Rhapsody]

Fair comment!

 

I want to use Prestashop for regatta registration. We have another database that is used for organizational membership registration. In this database we record data such as the name of the person's boat, the hull and sail numbers, their membership number for US Sailing, and if the membership is valid for the current year. This external database is mysql but the front end uses a set of cold fusion scripts that are integrated in our website.

 

On the prestashop side, when someone registers for a regatta, we require some of the same information on regatta registration that has been stored in the external database. While we could just include "Custom Fields" with mandatory text entries, it seems to be redundant since we have the data already available elsewhere. This feature to populate fields would be a convenience to the user, and preclude having to manually check the entries typed in.

 

Once a person is registered for a regatta, we export the registrations in CSV format. The data is used to populate "scratch sheets" that list the regatta entrants and is also read into an open source Java based scoring program that the race committee uses to score the regatta finishes.

 

Additional Prestashop uses include the ordering of merchandise sold with a regatta. We will have hats and shirts made with the person's boat name embroidered on the merchandise. Pulling the boat name from the external database makes the process easier.

 

Here is a web link to the publicly available data on the database. There is more information stored that we do not show to the public.

 

Is this more understandable now?

 

ps - this is not the same Prestashop site you did the PDF membership card for

[bellini13]

ok, making sense.

 

so instead of pulling the data during the login and storing it, how about when a purchase is made, override the order controller to pull the data for 'this' customer on demand, and then store it along with the order information, perhaps in a new prestashop table, with the order number referenced.

 

then you could override the adminorder backoffice tab to show the information in a new section within the order detail? even display on invoicing if you so choose

[Rhapsody]

I like that approach better than what I was thinking! So here is what I beleive it would require:

1. add new table to prestashop database

2. Override to OrderController.php

3. AdminOrders.php in tabs directory - is there a way to override this or do I modify core code?

4. PDF.php - override to display data pulled for order

5. Update to my theme .tpl files to pull data and display in the user's password & preferences. I'd probably include a link there back to the cold fusion scripts so they could update the external database info if necessary.

 

Am I missing anything?

[bellini13]

you should also create a new custom class that would extend the ObjectModel, that would represent the data in the new table. you could look at Customer.php for an example. this is a more complicated way to query/update this information, but it abstracts all the mysql query stuff from your code.

 

as for AdminOrders.php, there is no override capability per say. instead you would make a copy of the AdminOrders.php, make your changes, and place it in a folder under the modules folder, let's call the folder "myadminorder".

 

then in the back office you would go to the employees | tab section. edit the order entry, and enter "myadminorder" in the Module field. This tells prestashop to use the custom tab in the module folder named myadminorder.

 

not sure I followed the theme changes, but in order to show the data in the theme, your controller for that page would need to query the data, and place it in a smarty variable so your template can display it.

[Rhapsody]

Thanks - I'll play with this and see what I come up with using these suggestions.