Mixing info from different tables

8 posts by 3 authors in: Forums > CMS Builder
Last Post: June 30, 2014   (RSS)

By willydoit - June 27, 2014

Hi all,

On our site we provide advertising facilities for local businesses, we have a general businesses table and we also have separate tables for self catering accommodation, serviced accommodation, holiday parks etc due to the information which needs to be presented to the viewer and able to be searched on being quite different for each type of accommodation.

Our general business section lists businesses of varying types and has a search facility to show just certain types of business.

What I would like to be able to do though is include all the records from within each of different accommodation tables as well as the businesses in the general business table so that it produces a summary information list  of all our advertisers regardless of which table the information is in.

I have two questions, a) is it possible b) how difficult would it be.

If it is possible I can see that I would have to tidy up certain elements of my tables to make the field naming structure the same across all tables which brings me to question three, is it possible to rename fields that already contain data without losing or risking corrupting any data.

For example if in the serviced accommodation table the email field is "client_email" and in my self catering accommodation table the email field is just "email" can I simply go in and modify the field in one of the tables and change the field name to match the other table? 

It would be better for me on a number of levels if common fields across the different tables had the same field name even if I cannot have a list/search page that combines all the tables so any help with this answer would be appreciated.

Thanks in advance,

By claire - June 27, 2014

Hi there

Renaming a field should be fine, though I would definitely recommend making a backup of your database before doing anything major to it.

If you want info from multiple tables, the easiest way to do it is to get the records from the general business table, then get the records from each individual other table, and use PHP loops to match them up when you want to display them to the user. Basically you'd be running the getRecords function a couple of times until you have everything you need.

If you're not sure how to do this, just attach your viewer file here and let me know the names of the various different tables.

--------------------

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By claire - June 27, 2014

Okay, here's a sample of how this might work:

$business_directoryRecordsFeatured_ids = array();
foreach ($business_directoryRecordsFeatured as $bdr) {
    $business_directoryRecordsFeatured_ids[] = $brd['num'];
}

list($featured_extraInfo, $featured_extraInfoMeta) = getRecords(array(
    'tableName' => 'serviced_accommodation',
    'where' => 'businessNum in (' . implode(',',$business_directoryRecordsFeatured_ids) . ')'
));

I'd need some more info in this case, such as the name of the business number field (I'm calling it businessNum).

What this does is take the $business_directoryRecordsFeatured array, pull out all the num fields into their own array called $business_directoryRecordsFeatured_ids, and then give that array to the getRecords function to get the relevant records from the serviced_accommodation table. So now you have an array of info from the extra table to work with, and there's a few different ways to get it onto the page with the right record - probably just loop through it again and check for the businessNum, or something like that.

Does this make sense?

--------------------

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By willydoit - June 30, 2014

Hi Claire

Thanks for that, it's all starting to look a bit complicated for something which isnt an urgent need, may look into this further later but am also wondering whether Chris' show/hide dependent fields plugin would enable me to have just one table and then show different fields to be completed depending upon the business category chosen.

At the moment, the only reason I have my listings in different tables is due to having fields relating to services provided which are specific to each accommodation type. ie the services provided by a serviced accommodation such as hotel would be different to those provided by a self catering establishment.

I am wondering if as an  example the user/advertiser selects "Serviced Accommodation" as their business type when entering their information that the cmsb information entry page would then display a different data input form than it would if they selected "Self catering Accommodation"

Is that what the show/hide dependent fields plugin is for? If it is, is there a simple way to merge data from one table into another without having to manually copy all the information over?

Thanks

By claire - June 30, 2014

Hi Willy

I'll ask around here, see if there's an easy way to do it. I suspect not, unfortunately.

--------------------

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

By gregThomas - June 30, 2014 - edited: June 30, 2014

Hi Willydoit,

I think combining the various sections into one might be the way to go. You could write a script to combine all of the data into one table, you could use mysql_select to get all of the records from one section:

$allRecords = mysql_select('old_table');

Then you could cycle through all the records, and add the relevant fields to the new combined data table using the mysql_insert function:

foreach($allRecords as $record){
  $insertData = array(
    'new_field1' => $record['old_field1'],
    'new_field2' => $record['old_field2'],
    'new_field3' => $record['old_field3'],
    'new_field4' => $record['old_field4'],
    'new_field5' => $record['old_field5']
  );
  mysql_insert('new_table', $insertData);
}

I've had a look at the show/hide dependent fields plus plugin, and that should do what you're looking for. You could have a list field as the first field on the page. The user would use this to select what type of accommodation record it is (hotel, farm, etc). Then the appropriate fields would be shown or hidden. You could also add separators to group fields for certain types of accommodation together. 

Let me know if you have any questions.

Cheers!

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By willydoit - June 30, 2014

Hi Greg,

Thanks for that, it is all a bit beyond my abilities at the moment, however having just one table for all the businesses would make life a whole lot simpler. I need to have a closer look at my tables and map the similarities and see where the differences lie. We are in the middle of a huge rewrite of the site at the moment so now would be a good time in one respect but on the other hand we are already behind schedule with the rewrite to the point that I have been in the office before 4:30 am to after till late evening most days for the last couple of weeks. All of which leaves you a bit brain dead.

all our databases are live at the moment which means that any conversion would have to be a fast turnaround, I think creating a duplicate database to work with the newly designed site would therefore be the way to go. We can copy the existing databases and import them into a cmsb installation on the test domain and work on that one while leaving the existing live site intact.

If we tidied up the existing tables so that all the field names matched and created  some sort of list advising which additional fields were required for which business types, how big a job would it be to have you guys merge the existing tables into a single database working along the lines outlined in the earlier post?

we also run some scripts that you wrote specifically for us for automatic account creation in the different tables after payments etc so we need to see how they would be affected, I suspect they would become redundant for the most part.

Because we run the website concerned at a virtual loss due to it being provided by ourselves as a community website to support the town budgets are tight, we do therefore need a pretty firm idea of expenditure before we can consider our next move.

I will take this whole post and send it as a support request to start the ball rolling.

Thanks again.