That worked great. Thanks!

Paul

Yeah, I've already checked for that, it's there. I don't want to hijack this thread, and it looks like Dave has a solution for you. So I have another look and start anew thread if I can;t shift the error.

Thanks

Paul

I am also having a similar issue which I am having problems pinning down, although it is my own custom warning using the CMSB error logging function:

E_USER_NOTICE: Requested URL not found: favicon.ico

I'll follow this thread to see if there are any clues, and will try to look at again. If I find anything I'll let you know.

Paul

I have been trying to filter records using the where option, but seem to have hit a wall. I just don't seem to be able to get any results back and have an empty array.

I have an editor list of images. In there I have a drop down for 'Product' (to associate the image with a product) which is populated by using the "Get options from database (advanced)", as I have an editor list of products to easily add and create new products.

// load detail record from 'images'
  list($imagesRecords, $imagesMetaData) = getRecords(array(
    'tableName'   => 'images',
    'where' => "'product:label' = 'Hats and Scarfs'",
    'loadUploads' => true,
    'allowSearch' => false,
  ));

Any suggestions on where I am going wrong?

Thanks

Paul

Paul

I was wondering if Interactive Tools has any plans to officially support micro-sites / multisites  running powered by one CMSB install like Drupal. For example, a company with multiple stores that either have their own URL or sub-domain, that need to be able to update their own content, but is controlled by head office.

Thanks!

Paul

That said, you could always invest some time and learn to write plugins for yourself, which is something I have started doing over the last few weeks.

Sometimes when you learn something it's good to go back to the beginning and review the process. The data is actually being passed to me as a CSV, and I remembered I bought the CSV import plug-in ages ago, so after upgrading CMSB to the latest version (you guys deserve the ££ after all your help!), I created a brand new table that contains all the bike info in various columns, then using what Daryl taught me, I managed to modify the queries etc so that I have the correct drop down lists showing the right info dependant on selection.

Thanks guys, I've been working on this project day and night since last week, and your assistance has been fantastic.

Paul

Now I get it! Great overview!

So that works for small lists however I am dealing with 119 makes and 842 models and the list changes frequently. Now I understand the method you showed me in the previous post, I realise that this is not practical due to the sheer size of the data.

I will explore today to see if there is a way to match each Model with the relevant make because of the ID structure. For example:

Peugeot is MB-230
Peugeot Blaster is MB-230-1

Any pointers on this will be welcome. If I work it out before then I'll post if here for others to see.

Thanks for the reply. Much appreciated.

To explain our setup in more detail, we have a Used Bike Section. In that section we currently have 2 lists. The first list is a list of all Makes (MAKE). The second list is the full list of all models for all makes(MODEL).

The data structure is such that the list of models could be associated with the relevant make. So for example:

MAKE (List):
MB-230|Peugeot

MODEL (List):
MB-230-0|Other
MB-230-1|Blaster
MB-230-2|Buxy
MB-230-3|Elyseo
MB-230-4|Elystar
MB-230-5|Fox
MB-230-6|Geo
MB-230-7|Geopolis
MB-230-8|JetC-Tech
MB-230-9|JetForce
MB-230-10|Looxor
MB-230-11|Ludix
MB-230-12|Metal-X
MB-230-13|Satelis
MB-230-14|Speedfight
MB-230-15|SV
MB-230-16|TKR
MB-230-17|Trekker
MB-230-18|V-Clic
MB-230-19|Vivacity
MB-230-20|X-Fight
MB-230-21|XPS
MB-230-22|XR6

So I was thinking that we would need an additional list called FILTERED MODELS that is populated based on the chosen MAKE, to make choosing the correct model easier instead of going through the massive list of models for all makes.

I have attached some images as well of the current setup:

I have tried to make sense of your suggestion but struggling with it as MySQL is not my strong point and I only have to deal with CMSB every now and again.

If you previous suggestion still stands I will keep trying to make sense of it. However any further advise would be very welcome!

By the way I have tried looking at the other post you linked too, but Iam unable to view images in other posts. Anyone else got this problem?

Thanks again!

Paul

I have a large drop down of a series of bike MAKES that looks like this:

A-1|Aprilia
A-2|Benelli
A-3|BMW

...and so on

And I have a very large list of MODELS related to the MAKES listed above (The example below shows some of the Aprilia bike models:

A-1-1|125Strada
A-1-2|AF1
A-1-3|Amico

...and so on

I am trying to filter the list of bike models based on the selection made in the bike MAKES dropdown.

So I created another list called FILTERED MODELS, set it to "Get Options From MySQL Query" and configured it to use Advanced Filter: Refresh list when this field changes: MAKES

I am now trying to write the SQL query to only show the related MODELS to the chosen MAKE, but with little success.

Am I on the right path? Any advice welcome!

Thanks!

EDIT: I've tried to highlight the key names of what I am talking about. It looks like I am shouting lol! Also here is an image to clarify where I am doing this:

So, this is the OLD BROKEN code:

<?php $firstTable = true;?>
<?php foreach (getSchemaTables() as $tableName) :?>
  <?php if(strpos($tableName, "_main")): ?>
    <?php $schema = loadSchema($tableName); ?>
    <?php if(@$schema['bike_stock']): ?>
      <?php if($firstTable) {
        $firstTable = false;
        }
        else {
        echo "UNION";
      }
      ?>
SELECT @i:=@i+1 AS "Column Number", myquery.* FROM (
      SELECT `subdomain_name` as "Subdomains List", `dealer_title` as "Dealer Title" FROM `<?php echo $TABLE_PREFIX;?><?php echo $tableName;?>`
WHERE bike_stock = '1'
) myquery, (SELECT @i:=0) myrowcounter

    <?php endif; ?>
  <?php endif ?>
<?php endforeach ?>

and here is the CURRENT FIXED Code:

<?php $firstTable = true;?>
<?php foreach (getSchemaTables() as $tableName) :?>
  <?php if(strpos($tableName, "_main")): ?>
    <?php $schema = loadSchema($tableName); ?>
    <?php if(@$schema['bike_stock']): ?>
      <?php if($firstTable) {
        $firstTable = false;
        }
        else {
        echo "UNION";
      }
      ?>

SELECT @i:=@i+1 AS "Column Number", myquery.* FROM (
      SELECT `subdomain_name` as "Subdomains List", `dealer_title` as "Dealer Title" FROM `<?php echo $TABLE_PREFIX;?><?php echo $tableName;?>`
WHERE bike_stock = '1'
) myquery, (SELECT @i:=0) myrowcounter

    <?php endif; ?>
  <?php endif ?>
<?php endforeach ?>

So, the only difference is the new empty line before the first SELECT. That's it. Can someone explain this please? So much time wasted because of a missing empty line. I kind of feel robbed but glad it's fixed.

Thought I would share just in case someone else falls foul of this.

Yep, I also googled for a number of days, and used my experts exchange account to try and get to the bottom of this. I tried the exact same method as you kindly posted below.

It works on very simple queries, but seems to start failing when union etc, comes into it.

So far the code has advanced to this stage but still not working. I might just have to give up on this one. Anyway this is what it looks like at the moment after discussing elsewhere and working on it:

<?php
$firstTable = true;
foreach (getSchemaTables() as $tableName):
if(strpos($tableName, "_main")):
$schema = loadSchema($tableName);
    
if(isset($schema['name'])):

if($firstTable) {
$firstTable = false;
} else {
echo " UNION";
}

printf("SELECT @ROW := @ROW + 1 AS rowNum, name as `Name` FROM `%s`, (SELECT @ROW := 0) r;", $TABLE_PREFIX . $tableName);

endif;
endif;
endforeach;
?>

Anyway your help is appreciated as I know that this is not really a CMSB problem, but I was just hoping that someone else might have found themselves in a similar situation and worked it out.

I am wondering if there is a way to have a column number for each result from a MySQL query in Report Builder. For example:

1 | Bob

2| Jane

3| David

Here is an example of my current Query:

<?php $firstTable = true;?>
<?php foreach (getSchemaTables() as $tableName) :?>
  <?php if(strpos($tableName, "_main")): ?>
    <?php $schema = loadSchema($tableName); ?>
    <?php if(@$schema['name']): ?>
      <?php if($firstTable) {
        $firstTable = false;
        }
        else {
        echo "UNION";
      }
      ?>

      SELECT `name` as "Name", FROM `<?php echo $TABLE_PREFIX;?><?php echo $tableName;?>`

    <?php endif; ?>
  <?php endif ?>
<?php endforeach ?>

Thanks!

No problem! All working, thanks for all the help. I'm off to look at it closer now to try and understand it fully.

Just checked again and the same results.

Thanks for the help so far!

I think I see what you are doing. However I get the followig error:

Notice: Undefined variable: table in /homepages/16/d311219399/htdocs/national/cms-hub/lib/common.php(466) : eval()'d code on line 4 loadSchema: no tableName specified!

I have a series of tables and need to find tables that end with '_main' and display the table name + the contents of a field called  'phone' which is in the _main tables.

I've tried all sorts today but with little luck.

I am able to find only tables with a field called 'phone' but I cannot output the field value.

Here is my code so far:

<?php $firstTable = true;?>
<?php foreach (getSchemaTables() as $tableName) :?>
<?php if (strpos($tableName, "_main")): ?>
<?php if ($firstTable) {
$firstTable = false;
}
else {
echo "UNION";
}
?>

SELECT "<?php echo $tableName;?>" as "Phone Numbers" FROM `<?php echo $TABLE_PREFIX;?><?php echo $tableName;?>`
WHERE phone

<?php endif ?>
<?php endforeach ?>

Any and all pointers in the right direction welcome!

Thanks.

Is there a way to add a row number to the result to be able to quickly see how many there are?

Many thanks for your time and patience on this, and sorry for explaining it so bad initially ;)

Paul

I am working inside the Report Builder Plugin for CMSB. Your suggestion is not compatible with the plugin but thank you anyway :)

I have researched this and I am wondering if what I want to do is even possible. Maybe your way is the only way Greg.

I know how to find all tables ending with _main: 

select * from Information_schema.tables where table_name like '%_main'

I can also search a specific table and get MySQL to only show is sachs_dealer = true;

select * from bobs_bikes_main where sachs_dealer = 1;


But I can't seem to be able to search for dealers ending in _main AND where the field sachs_dealer = 1

So the output I am looking for is:

Dealer Main         |     Sachs Dealer

Bobs Bikes          |     True

Mean Machines  |     True

Does anyone have any suggestions?