Reading a field from a database table

[DigiBooks]

Hi all.

I thought I was beginning to get to grips with reading from tables but clearly need more learning!!!!

In catalog\includes\templates\template_default\templates\tpl_product_info_display.p hp I simply want to check that the download file exists before the customer is given the option of adding it to their cart - or deliver a Missing File message if it doesn't exist. I know how to check the file existence once I have the zip filename. So just to start in my simple one step at a time I have added these lines to ensure that I can obtain the filename:

<?php
$test_product_number =(int)$_GET['products_id'];
echo 'Product Number is: ' . $test_product_number;

$zipfile_name = $db->Execute("select products_attributes_filename from " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " where products_attributes_id='" . $test_product_number . "'");
echo $zipfile_name;

?>

$test_product_number is working fine but it all falls over on the $db->Execute statement.

The page displays the product, product image, description and Add To Cart button at this point followed by:

Product Number is: 1865 (or whatever no. it is)


then stops delivering the rest of the page down to the bottom and omitting the right-hand side bar.

With certain tests using variations on the execute statement I have seen at the bottom of the page: Warning an error has occurred.

Obviously my simple understanding of reading a field from a table is incorrect.

I know that in the products table the products_id will correspond to the same number in the products_attributes_downloads table where the zip filename is also stored.

Please could somebody advise what I am doing wrong?

Many thanks in advance

[mc12345678]

Ok, so there's issues with writing the code and there also is an issue with the concept of database data retrieval.

First things first. While the associated FAQ offers some different explanations and guidance, the central concept is the same regardless. For example you have described a partial blank screen occurring as well as receiving a warning an error occurred type message. These are covered by the following two FAQs and by further investigation you'll be able to at least more succinctly ask a question about the issue that is occurring.

http://www.zen-cart.com/content.php?124-blank-page
https://www.zen-cart.com/content.php...-and-try-again

Then with regards to the database structure. As it appears you understand, there is a simple products_id which uniquely identifies the main product in question. Then when dealing with attributes (such as a download file) there is a sort of build-up of data that is employed in ZC. There are a list of option names, and a list of option values (that are assigned to an option name) and then a combination (if it exists) of an option name/option value being assigned as an overall attribute to a product. But, then a download file has some additional information to "manage" that is not a part of a standard attribute. There is, as discovered, the filename, but also other things like how many times the file should be permitted to be downloaded, for how long it can be downloaded, etc... this information, once the product is purchased, is stored in an orders related table and modified as necessary for the specific order.

Anyways, the part that needs to be understood/further adapted is the relationship of the products_id to the file's filename.
So, I recommend taking a look at the database table structure of products_attributes in addition to products_attributes_download to see how the two tables sort of "fit" together and how it is possible to get the filename from knowing the products_id. Further, you will also want to consider the possibility that there may be more than one file associated with the product (either because the one file was too large to serve as a single download and needs to be split across multiple, or perhaps that there are multiple versions/styles of the same content or of course some combination).

I leave that part of the database query to further discovery, but there is also the issue of the code to handle the result.

The $db->Execute method returns a variable that has fields in it. Now in ZC 1.5.5 and above the returned variable is manageable more as an array than previously designed, but that is for a later rewrite of the code seeing as still using ZC 1.5.1.

The result of $zipfile_name is not literally just the filename but is made of other database related information among which is the value that was sought in the database.

For one, $zipfile_name->EOF provides an indicator that at the moment of use, the current record is the "end-of-file" or the last record. This means that at the moment of asking there is no data to be reviewed from the query either because the query returned no data or as the query results have been accessed/reviewed ($zipfile_name->MoveNext()), now at the last record.

To see how many records were returned one can use $zipfile_name->RecordCount().

Assuming that after the execute query has been run and that we are not at the end-of-file, then to access say the products_attributes_filename field's data returned by the query, the variable form is: $zipfile_name->fields['products_attributes_filename']. The field name between the single quotes relates back to the query in the section between the select statement and the from statement. It is possible to alias a field to another name and it would be that alias that would need to be used.

So in the above instead of directly echoing $zipfile_name (which is an array of sorts and would be part of why the page errored out) would echo $zipfile_name->fields['products_attributes_filename'];

But... Let's talk a little about sanitization... in your first query/statement you cast $_GET['products_id'] to an integer. Good. One, no product can have a zero products_id so if the first "value" or character is anything other than a number then casting it to an integer will cause it to be zero and subsequent results will not have much meaning. The second thing is that at least that statement can not be further "modified" by sql injection (modifying the value of the parameter products_id to cause a system response that could be otherwise interpreted or provide some amount of configuration information.) The reason I sort of bring this up is that the sql query itself uses a variable that has already been "made" to be sanitized and while it appears to be immediately adjacent to the assignment, could be separated by a lot of code and therefore could either be purposefully or accidentally modified away from just the integer that was expected. Therefore it is wise to again cast the value within the sql to its expected value type (int). Then there is a bit of a query structure that has become a bit more sensitive in newer versions (might as well start now even though using a store that is 5+ years old in design). Since whatever "id" is being looked up is expected to be an integer, such a value is presented to a field without any single or double quote once all php substitutions have been made. Meaning a statement like this:

select products_id from products where products_id = 618;

Would be the select query statement one would put into say phpmyadmin to obtain the result of the products_id that had the given products_id... (not your typical query per se, but could be used to identify the presence of the products_id by evaluating the result. A number of other ways to do the same thing as well.)

Anyways, the php related query, building one thing at a time could be made as so when replacing the generic table name:

"select products_id from " . TABLE_PRODUCTS . " where products_id = 618"

Then by substituting $products_id as cast to an integer:

"select products_id from " . TABLE_PRODUCTS . " where products_id = ". (int)$products_id

Notice how there is not the single quote around the $products_id? It's unnecessary and actually causes additional unnecessary processing.

Anyways, while your solution is not directly provided here, perhaps you can enjoy the self-discovery through using some of the information provided here. It's not 100% explanatory and there are some pitfalls that can be experienced if haven't applied some of the concepts fully. Might I suggest looking through the ZC code for similar simple table/field lookups and see how it may be handled.

Also, as a final thing (for this particular post), hopefully it is also understood that perhaps the "best" place to address file existence is when the attributes are being generated/looked up such as in includes/modules/YOUR_TEMPLATE/attributes.php. It may or may not provide useful information or a way to do something different depending on what else is to happen or be needed.

[DigiBooks]

Ok, so there's issues with writing the code and there also is an issue with the concept of database data retrieval.

First things first. While the associated FAQ offers some different explanations and guidance, the central concept is the same regardless. For example you have described a partial blank screen occurring as well as receiving a warning an error occurred type message. These are covered by the following two FAQs and by further investigation you'll be able to at least more succinctly ask a question about the issue that is occurring.

http://www.zen-cart.com/content.php?124-blank-page
https://www.zen-cart.com/content.php...-and-try-again

Then with regards to the database structure. As it appears you understand, there is a simple products_id which uniquely identifies the main product in question. Then when dealing with attributes (such as a download file) there is a sort of build-up of data that is employed in ZC. There are a list of option names, and a list of option values (that are assigned to an option name) and then a combination (if it exists) of an option name/option value being assigned as an overall attribute to a product. But, then a download file has some additional information to "manage" that is not a part of a standard attribute. There is, as discovered, the filename, but also other things like how many times the file should be permitted to be downloaded, for how long it can be downloaded, etc... this information, once the product is purchased, is stored in an orders related table and modified as necessary for the specific order.

Anyways, the part that needs to be understood/further adapted is the relationship of the products_id to the file's filename.
So, I recommend taking a look at the database table structure of products_attributes in addition to products_attributes_download to see how the two tables sort of "fit" together and how it is possible to get the filename from knowing the products_id. Further, you will also want to consider the possibility that there may be more than one file associated with the product (either because the one file was too large to serve as a single download and needs to be split across multiple, or perhaps that there are multiple versions/styles of the same content or of course some combination).

I leave that part of the database query to further discovery, but there is also the issue of the code to handle the result.

The $db->Execute method returns a variable that has fields in it. Now in ZC 1.5.5 and above the returned variable is manageable more as an array than previously designed, but that is for a later rewrite of the code seeing as still using ZC 1.5.1.

The result of $zipfile_name is not literally just the filename but is made of other database related information among which is the value that was sought in the database.

For one, $zipfile_name->EOF provides an indicator that at the moment of use, the current record is the "end-of-file" or the last record. This means that at the moment of asking there is no data to be reviewed from the query either because the query returned no data or as the query results have been accessed/reviewed ($zipfile_name->MoveNext()), now at the last record.

To see how many records were returned one can use $zipfile_name->RecordCount().

Assuming that after the execute query has been run and that we are not at the end-of-file, then to access say the products_attributes_filename field's data returned by the query, the variable form is: $zipfile_name->fields['products_attributes_filename']. The field name between the single quotes relates back to the query in the section between the select statement and the from statement. It is possible to alias a field to another name and it would be that alias that would need to be used.

So in the above instead of directly echoing $zipfile_name (which is an array of sorts and would be part of why the page errored out) would echo $zipfile_name->fields['products_attributes_filename'];

But... Let's talk a little about sanitization... in your first query/statement you cast $_GET['products_id'] to an integer. Good. One, no product can have a zero products_id so if the first "value" or character is anything other than a number then casting it to an integer will cause it to be zero and subsequent results will not have much meaning. The second thing is that at least that statement can not be further "modified" by sql injection (modifying the value of the parameter products_id to cause a system response that could be otherwise interpreted or provide some amount of configuration information.) The reason I sort of bring this up is that the sql query itself uses a variable that has already been "made" to be sanitized and while it appears to be immediately adjacent to the assignment, could be separated by a lot of code and therefore could either be purposefully or accidentally modified away from just the integer that was expected. Therefore it is wise to again cast the value within the sql to its expected value type (int). Then there is a bit of a query structure that has become a bit more sensitive in newer versions (might as well start now even though using a store that is 5+ years old in design). Since whatever "id" is being looked up is expected to be an integer, such a value is presented to a field without any single or double quote once all php substitutions have been made. Meaning a statement like this:

select products_id from products where products_id = 618;

Would be the select query statement one would put into say phpmyadmin to obtain the result of the products_id that had the given products_id... (not your typical query per se, but could be used to identify the presence of the products_id by evaluating the result. A number of other ways to do the same thing as well.)

Anyways, the php related query, building one thing at a time could be made as so when replacing the generic table name:

"select products_id from " . TABLE_PRODUCTS . " where products_id = 618"

Then by substituting $products_id as cast to an integer:

"select products_id from " . TABLE_PRODUCTS . " where products_id = ". (int)$products_id

Notice how there is not the single quote around the $products_id? It's unnecessary and actually causes additional unnecessary processing.

Anyways, while your solution is not directly provided here, perhaps you can enjoy the self-discovery through using some of the information provided here. It's not 100% explanatory and there are some pitfalls that can be experienced if haven't applied some of the concepts fully. Might I suggest looking through the ZC code for similar simple table/field lookups and see how it may be handled.

Also, as a final thing (for this particular post), hopefully it is also understood that perhaps the "best" place to address file existence is when the attributes are being generated/looked up such as in includes/modules/YOUR_TEMPLATE/attributes.php. It may or may not provide useful information or a way to do something different depending on what else is to happen or be needed.

Thanks mc12345678

I always love your responses but having read it over and over it just made me more confused!!

As a bit of background: the first reason I am looking to do this file existence check is because my Admin do not always check and see that the downloadable file is present (and I am guilty of this too!!)

The second reason is because we do not "sell" products. Access to our systems are by annual membership only and once enrolled members have unlimited downloads. We have developed an external membership login to our website but it still requires a member to login to Zencart in order to download anything which is what we are dispensing with.

So instead of adding a product to the cart we want a simple download file button hence checking that the zip file exists.

I have finally worked out that the products_id in products does not always match the products_attributes_id in the products_attributes_download table.

The only way to get to this is to check the current products_id against the products_attributes table and extract the products_attribute_id and then seek the products_attribute_id in the products_attributes_download table in order to find the zip filename for downloading.

What a convoluted path!

Now I still confess that I am not a PHP expert but I have written many systems in other languages which use an ID as a lookup whatever table that may exist in and that ID is static. I'm sure there is a reason for programming ZenCart the way it is but I just don't get it!

Bottom line is ... members have to use our external access system to get to Zencart otherwise they are forced back to the main website to login. They have paid a subscription controlled by that access system so a shopping cart is far too complex as they have to use a different login in Zencart in order to download their selections.

Our alternative is to switch off customer logins and give direct downloads based on the zip file being present.

In order to monitor downloads we just add a link through a new downloads.php process which will give us a CSV file to analyze

[Design75]

As a bit of background: the first reason I am looking to do this file existence check is because my Admin do not always check and see that the downloadable file is present (and I am guilty of this too!!

I solved this problem in the Cittins module, by creating a drop down, so you can only add a file that is already present in the downloads directory. It is pretty simple to add this to the standard attributes controller.

[DigiBooks]

I solved this problem in the Cittins module, by creating a drop down, so you can only add a file that is already present in the downloads directory. It is pretty simple to add this to the standard attributes controller.

Thanks Design75

Great idea but my intention is to do away with attributes entirely which are really slow on the server. Just add the filename as part of the add product process and check it exists at Admin level as well.

I know this will be a highly bespoked version of Zencart but we don't "sell" so versions above 151 will not benefit us but serve as a great basis for delivering goods. And with the new downloads monitor we will be able to get around the issues of the upcoming GDPR regulations by not recording IP addresses or users downloading products

[lat9]

Here's the SQL query you were asking for, generating a list of download filenames for the product identified by $_GET['products_id']:

$download_files = $db->Execute(
    SELECT pad.products_attributes_filename AS filename
      FROM " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " pad
           INNER JOIN products_attributes pa
                ON pa.products_attributes_id = pad.products_attributes_id
               AND pa.products_id = " . (int)$_GET['products_id']
);
while (!$download_files->EOF) {
    $current_filename = $download_files->fields['filename'];
    // Do what you need to verify the file's existence
    $download_files->MoveNext();
}

Since the products_attributes_download table doesn't include a products_id, the query needs to look up the attributes defined for that products_id and use the associated products_attributes_id as the "key" to that look-up.