Mixing info from different tables

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.

Hi Claire,

Thanks for that, I have attached the php file that currently displays a summary listing of all the businesses in the "business_directory" table, in addition to this table we have four additional tables;

serviced_accommodation

self_catering

camp_and_caravan

holiday_property

which we would like to try to include if possible with the "more info" button opening up the detail page for whichever listing is selected.

Hope this is what you were wanting.

Thanks again for your help.

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?

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

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

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.