Getting file name from database

Quote

Post by SkeR » Thu Oct 26, 2017 5:49 pm

I am a beginner in PHP and MySQL and for that reason i have a hard time taking a specific value from a database.

So far i managed to create a table and upload files into the database via the admin side of my opencart 3.0.2.0. Everything is working fine and all the files are uploaded sucessfuly to the database.

After that i have managed to relate each file to a particular product and store that in the databse table product_to_mp3 which contains only the product_id and the mp3_id

Now what I am trying to achieve in the storefront section is to find the filename(i want to display its value) for the particular mp3 file by comparing and checking matching mp3_id for product_id in the table product_to_mp3 and get the filename for that mp3_id from the table mp3

P.S All of the tables are with the standart Prefix OC_

Here is the code i have added to catalog/controller/product/product.php:

Code: Select all

			$mp3names = $this->model_catalog_product->getProductMP3s($this->request->get['product_id']);

foreach ($mp3names as $mp3name) {
				$data['mp3s'][] = array(
					'name'        => $mp3name['filename'],	
				);				
}

Here is the code i have added to catalog/model/catalog/product.php:

Code: Select all

public function getProductMP3s($mp3_id) {
		$product_data = array();
		$query = $this->db->query("SELECT filename FROM " . DB_PREFIX . "mp3 m LEFT JOIN " . DB_PREFIX . "product p ON (m.mp3_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_mp3 p2m ON (p.product_id = p2m.product_id)");
			foreach ($query->rows as $result) {
			$product_data[$result['filename']] = $this->getProduct($result['filename']);
		}
		return $product_data;
		}
Here is the code i have added to catalog/view/mytheme/product/product.twig:

Code: Select all

 <div id="mp3filename">{{ mp3s.filename }}</div>

I would be very thankfull if you even just give me guidance on how i could solve that.

Thank you in advance
SkeR
Newbie
Posts
4
Joined
Thu Oct 26, 2017 3:15 pm
Top

Re: Getting file name from database

Quote

Post by straightlight » Thu Oct 26, 2017 10:24 pm

SkeR wrote: ↑
Thu Oct 26, 2017 5:49 pm
 I am a beginner in PHP and MySQL and for that reason i have a hard time taking a specific value from a database.

So far i managed to create a table and upload files into the database via the admin side of my opencart 3.0.2.0. Everything is working fine and all the files are uploaded sucessfuly to the database.

After that i have managed to relate each file to a particular product and store that in the databse table product_to_mp3 which contains only the product_id and the mp3_id

Now what I am trying to achieve in the storefront section is to find the filename(i want to display its value) for the particular mp3 file by comparing and checking matching mp3_id for product_id in the table product_to_mp3 and get the filename for that mp3_id from the table mp3

P.S All of the tables are with the standart Prefix OC_

Here is the code i have added to catalog/controller/product/product.php:

Code: Select all

			$mp3names = $this->model_catalog_product->getProductMP3s($this->request->get['product_id']);

foreach ($mp3names as $mp3name) {
				$data['mp3s'][] = array(
					'name'        => $mp3name['filename'],	
				);				
}

Here is the code i have added to catalog/model/catalog/product.php:

Code: Select all

public function getProductMP3s($mp3_id) {
		$product_data = array();
		$query = $this->db->query("SELECT filename FROM " . DB_PREFIX . "mp3 m LEFT JOIN " . DB_PREFIX . "product p ON (m.mp3_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_mp3 p2m ON (p.product_id = p2m.product_id)");
			foreach ($query->rows as $result) {
			$product_data[$result['filename']] = $this->getProduct($result['filename']);
		}
		return $product_data;
		}
Here is the code i have added to catalog/view/mytheme/product/product.twig:

Code: Select all

 <div id="mp3filename">{{ mp3s.filename }}</div>

I would be very thankfull if you even just give me guidance on how i could solve that.

Thank you in advance
The model query statement would be incorrect. The filename field would also need to include a prefix in case other tables may contain the same field name with different values for any reasons. The m.mp3_id logically would not match the p.product_id from the JOIN table. You'd rather need to create a product_id field in the MP3 table.

As for the TWIG file, that would also be incorrect. Since you created an array from the controller file with the key name: mp3s, you'd need to initiate a for loop statement in the TWIG file. Then, since it is a loop statement, rather than the use of brackets, you'd simply use something like: mp3.filename inside the loop.

Code: Select all

{% if mp3s %}
{% for mp3 in mp3s %}
    mp3.filename
{% endfor %}
{% endif %}
for instance.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester

straightlight
Legendary Member
Posts
22525
Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Top

Re: Getting file name from database

Quote

Post by SkeR » Fri Oct 27, 2017 9:41 pm

Thank you very much for your guidance.

I have reworked the code in catalog/model/catalog/product.php to this and mainly the quert section :

Code: Select all

public function getProductMP3s($mp3_id) {
		$product_data = array();

		$query = $this->db->query("SELECT filename FROM " . DB_PREFIX . "mp3 m LEFT JOIN " . DB_PREFIX . "product_to_mp3 p2m ON (p2m.mp3_id = m.mp3_id) LEFT JOIN " . DB_PREFIX . "product p ON (p.product_id = p2m.product_id) WHERE p.product_id = p2m.product_id ");

			foreach ($query->rows as $result) {
			$product_data[$result['filename']] = $this->getProduct($result['filename']);
		}
		return $product_data;
		}
Even though i still think there is something wrong here but i can not figure it out :(

In the file catalog/controller/product/product.php the code is pretty much the same as i mentioned before:

Code: Select all

$mp3names = $this->model_catalog_product->getProductMP3s($this->request->get['product_id']);
          
   foreach ($mp3names as $mp3name) {
				$data['mp3s'][] = array(
					'name'        => $mp3name['filename'],	
				);				
             }
In in the catalog/view/theme/mytheme/product/product.twig file i have done what you have advised me but i am not sure if that is right.

Code: Select all

{% if mp3s %}
{% for mp3 in mp3s %}
  <div id="mp3filename"> {{mp3.name}}</div>
{% endfor %}
{% endif %}

It would be great if you could have a look at the code again for me and give me more guidance please
Thank you in advance
SkeR
Newbie
Posts
4
Joined
Thu Oct 26, 2017 3:15 pm
Top

Re: Getting file name from database

Quote

Post by straightlight » Fri Oct 27, 2017 9:56 pm

The coding of the model is not so bad but the logic to it is flaw. It should rather be:

Code: Select all

public function getProductMP3s($product_id) {
		$product_data = array();

		$query = $this->db->query("SELECT `m`.`filename` FROM `" . DB_PREFIX . "mp3` `m` LEFT JOIN `" . DB_PREFIX . "product_to_mp3` `p2m` ON (`p2m`.product_id` = `m`.`product_id`) LEFT JOIN `" . DB_PREFIX . "product` `p` ON (`p`.`product_id` = `p2m`.`product_id`) LEFT JOIN `" . DB_PREFIX . "product_description` `pd` ON (`pd`.`product_id` = `p`.`product_id`) LEFT JOIN `" . DB_PREFIX . "product_to_store` `p2s` `p2s` ON (`p2s`.`product_id` = `pd`.`product_id`) WHERE `p`.`product_id` = `p2m`.`product_id` AND `pd`.`language_id` = '" . (int)$this->config->get('config_language_id') . "' AND `p2s`.`store_id` = '" . (int)$this->config->get('config_store_id') . "'");

			foreach ($query->rows as $result) {
			$product_data[$result['filename']] = $this->getProduct($result['filename']);
		}
		return $product_data;
		}
Also ensure to create your relationship entity by creating a new product_id field in your mp3 and product_to_mp3 table. All three tables needs to match, in this case.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester

straightlight
Legendary Member
Posts
22525
Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Top
Post Reply
Return to “Extensions Support”
Who is online

Users browsing this forum: No registered users and 5 guests