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 Willy

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

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