Error log says table doesn't exist... but it does.

[Joseph M]

I'm getting the following error in my logs (ellipsis in place of directory names):

PHP Fatal error: 1146:Table '... .media_format' doesn't exist :: select format_id, format_name
from media_format order by format_name ==> (as called by) /home/.../public_html/.../.../includes/modules/product_music/collect_info.php on line 96 <== in /home/.../public_html/.../includes/classes/db/mysql/query_factory.php on line 171

However the table clearly exists in my database with both referenced fields (format_id and format_name) in it.
In my Admin Extras, the field also appears with all values present but the text "TABLE_HEADING_MEDIA_FORMAT" appears at the top of the field values list, and "TEXT_DISPLAY_NUMBER_OF_FORMAT" appears below the list. In addition, the new field appears twice in the Extras drop-down menu.
Store product pages show the error warning: WARNING: An Error occurred, please refresh the page and try again.

There is one place in the database where it appears to be missing. That is in Table: product_music_extra where I see:
Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Edit Drop Drop PRIMARY BTREE Yes No products_id 604 A No
Edit Edit Drop Drop idx_music_genre_id_zen BTREE No No music_genre_id 20 A No
Edit Edit Drop Drop idx_artists_id_zen BTREE No No artists_id 120 A No
Edit Edit Drop Drop idx_record_company_id_zen BTREE No No record_company_id 46 A No

Had this working briefly, but messed it up somehow. It appears I may have dropped a column in another table. (?) But I can't be sure as I am relatively new to working with databases and phpMyAdmin. Is there a ZC or database expert here who can recognize this behavior and tell me what I may have deleted (or added) to cause this?

[carlwhat]

in:

/home/.../public_html/.../.../includes/modules/product_music/collect_info.php

on line 96 is a call to the database. that sql statement is wrong.

to figure out exactly the sql statement, you should output it to the screen, and then try running that sql statement using phpMyadmin.

if you post the code from that above script, above and including line 96, someone could help.

if the table is there, then your code must be wrong.

best.

[Joseph M]

you should output it to the screen, and then try running that sql statement using phpMyadmin.

Still new to this and not sure how to do that.

Oddly, lines 86-108 don't even reference the problematic table:

(int)$_SESSION['languages_id'] . "'");

      $pInfo->updateObjectInfo($product->fields);
    } elseif (zen_not_null($_POST)) {
      $pInfo->updateObjectInfo($_POST);
      $products_name = $_POST['products_name'];
      $products_description = $_POST['products_description'];
      $products_url = $_POST['products_url'];
    }

    $category_lookup = $db->Execute("select *
                              from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd
                              where c.categories_id ='" . (int)$current_category_id . "'
                              and c.categories_id = cd.categories_id
                              and cd.language_id = '" . (int)$_SESSION['languages_id'] . "'");
    if (!$category_lookup->EOF) {
      $cInfo = new objectInfo($category_lookup->fields);
    } else {
      $cInfo = new objectInfo(array());
    }

There is some code before that does. Starting at line 66 which lists all of the Music specific fields:

pe.artists_id,pe.format_id,pe.record_company_id,pe.music_genre_id,
                                      p.products_quantity_order_min, p.products_quantity_order_units, p.products_priced_by_attribute,
                                      p.product_is_free, p.product_is_call, p.products_quantity_mixed,
                                      p.product_is_always_free_shipping, p.products_qty_box_status, p.products_quantity_order_max,
                                      p.products_sort_order,
                                      p.products_discount_type, p.products_discount_type_from,
                                      p.products_price_sorter, p.master_categories_id
                              from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd,
                                   " . TABLE_PRODUCT_MUSIC_EXTRA . " pe
                              where p.products_id = '" . (int)$_GET['pID'] . "'
                              and p.products_id = pd.products_id and

And some after as well (110-130):

    $artists_array = array(array('id' => '', 'text' => TEXT_NONE));
    $artists = $db->Execute("select artists_id, artists_name
                                   from " . TABLE_RECORD_ARTISTS . " order by artists_name");
    while (!$artists->EOF) {
      $artists_array[] = array('id' => $artists->fields['artists_id'],
                                     'text' => $artists->fields['artists_name']);
      $artists->MoveNext();
    }

    $format_array = array(array('id' => '', 'text' => TEXT_NONE));
    $format = $db->Execute("select format_id, format_name
                                   from " . TABLE_MEDIA_FORMAT . " order by format_name");
    while (!$format->EOF) {
      $format_array[] = array('id' => $format->fields['format_id'],
                                     'text' => $format->fields['format_name']);
      $format->MoveNext();
    }

[mc12345678]

Are you sure in your database that the table media_format exists as compared to the field that got added to the products table that is instead called MEDIA_FORMAT as referenced in this post: https://www.zen-cart.com/showthread....40#post1348440?

[carlwhat]

few things:

• while you may be new, good job putting your code in bbcode tags!
• mc has a point... are you sure you created the table? what happens if you put the following statement into phpMyadmin:
  

  select format_id, format_name from media_format order by format_name

• that is what php is complaining about in the error log.
• line numbers are what the line numbers are. you have either modified the script since the time of the error log, or you are referencing the wrong script, or you have inaccurately quoted the error log. i have never seen an error log be wrong about where the error is.

[Joseph M]

Are you sure in your database that the table media_format exists as compared to the field that got added to the products table that is instead called MEDIA_FORMAT as referenced in this post: https://www.zen-cart.com/showthread....40#post1348440?

Yes, absolutely sure. I can view the table with all of its associated columns in phpMyAdmin:
Table: media_format
format_id format_name format_image date_added last_modified

I also have the required table media_format_info in the database with associated columns:
format_id languages_id format_url url_clicked date_last_click (though I don't see any data in it yet).

The products table doesn't appear to have any Music product specific field references in it (including artist, record company, nor genre).

[Joseph M]

what happens if you put the following statement into phpMyadmin:

select format_id, format_name from media_format order by format_name

It returns a complete list of my various media formats and associated ID numbers.

[*] line numbers are what the line numbers are. you have either modified the script since the time of the error log, or you are referencing the wrong script, or you have inaccurately quoted the error log. i have never seen an error log be wrong about where the error is.

Very possible that I have changed the script since the time of the error. Or that I looked at the wrong one (though I typically delete them after viewing). I just produced a fresh error and log and it is referencing a different file. However even after confirming I have the right file and script there are still line discrepancies:

PHP Fatal error: 1146:Table '... .TABLE_MEDIA_FORMAT' doesn't exist :: select * from TABLE_MEDIA_FORMAT
where format_id = '0' ==> (as called by) /home/.../public_html/.../includes/modules/pages/product_music_info/main_template_vars_product_type.php on line 45

Lines 39-63 from /includes/modules/pages/product_music_info/main_template_vars_product_type.php:

    $sql = "select * from " . TABLE_RECORD_ARTISTS . "
            where artists_id = '" . $music_extras->fields['artists_id'] . "'";

    $artist = $db->Execute($sql);

    $sql = "select * from " . TABLE_RECORD_ARTISTS_INFO . "
            where artists_id = '" . $music_extras->fields['artists_id'] . "'
            and languages_id = '" . (int)$_SESSION['languages_id'] . "'";

    $artist_info = $db->Execute($sql);

    $sql = "select * from " . TABLE_MEDIA_FORMAT . "
            where format_id = '" . $music_extras->fields['format_id'] . "'";

    $format = $db->Execute($sql);

    $sql = "select * from " . TABLE_MEDIA_FORMAT_INFO . "
            where format_id = '" . $music_extras->fields['format_id'] . "'
            and languages_id = '" . (int)$_SESSION['languages_id'] . "'";

    $format_info = $db->Execute($sql);

Line 45 is:

    $sql = "select * from " . TABLE_RECORD_ARTISTS_INFO . "

This is the new error log:

#4 queryFactory->Execute() called at [/home/.../public_html/.../includes/modules/pages/product_music_info/main_template_vars_product_type.php:45]
#5 include_once(/home/.../public_html/.../includes/modules/pages/product_music_info/main_template_vars_product_type.php) called at [/home/.../public_html/.../includes/modules/pages/product_music_info/main_template_vars.php:133]
#6 require(/home/.../public_html/.../includes/modules/pages/product_music_info/main_template_vars.php) called at [/home/.../public_html/.../includes/templates/CUSTOM/common/tpl_main_page.php:125]
#7 require(/home/.../public_html/.../includes/templates/CUSTOM/common/tpl_main_page.php) called at [/home/.../public_html/.../index.php:97]

[15-Aug-2018 23:36:28 America/New_York] PHP Fatal error: 1146:Table '... .TABLE_MEDIA_FORMAT' doesn't exist :: select * from TABLE_MEDIA_FORMAT
where format_id = '0' ==> (as called by) /home/.../public_html/.../includes/modules/pages/product_music_info/main_template_vars_product_type.php on line 45

I did find an apparent typo (a missing "t")at line 101:

zen_href_link(FILENAME_REDIRECT, 'action=music_artist&artists_id=' .

Still, a completely different field. And repairing it didn't fix the error problem.
I'm going to review the other files mentioned in the error log. Perhaps they can offer a hint to the problem.

[carlwhat]

this error log indicates that the define for TABLE_MEDIA_FORMAT does not exist. this is NOT the table in the database. TABLE_MEDIA_FORMAT is a constant that requires a definition; which refers to the table in the database.

we covered that in another thread.

[Joseph M]

As a beginner, finding the solution to this problem was too confusing. I reverted to a known working backup from before attempting to write this 'Format Mod.' Then I re-uploaded my changes one file at a time, checking the admin and store sides of my ZC, and watching for error logs generated every step of the way.. Each file worked without producing any logs.

They still produced some quirks. At one point I had a double "Format" option in my Admin>Extras drop-down. I solved this by finding it in the database and deleted a line from the Table: admin_pages which was an unnecessary duplicate.

I also had a problem with my Admin>Extras>Media Formats page heading and display number (at the bottom) which respectively read:
TABLE_HEADING_MEDIA_FORMAT
and
TEXT_DISPLAY_NUMBER_OF_FORMAT

I found these in the media_format.php file made for this mod (modeled directly off of record_artists.php).
Two defines had typos, each reading FORMATS instead of FORMAT.
After fixing the last two annoying problems, my ZC is running error log free. For the Admin side at least, this is complete.

I can't say what caused the problems mentioned at the beginning of this thread. But it was having backups (make them frequently), uploading my mod files one at a time, and checking each for error logs and site behavior that led me to getting a complete Format field addition in my admin. That and the kind help of carlwhat and mc12345678. Many thanks. I learned a lot from your suggestions.