[RESOLVED] Populate a Dynamic Form with MSQL Database

bf2017

Bear with me. I'm somewhat new to php and MySQL.

On my website I have a manufacturer dropdown that has a dynamic search. It uses Chosen for its submit.

Here is the .tpl code for the form:

{
$Id: menu_manufacturers.tpl,v 1.1.2.2 2010/12/09 14:00:53 aim Exp $
vim: set ts=2 sw=2 sts=2 et:
}

{capture name=menu}

<form method="get" action="manufacturers.php">
<select name="manufacturerid" onchange="this.form.submit()" class="chzn-select-manuf" style="width:100%" >
<option value="0"{if $manufacturerid eq "0"} selected{/if}>{$lng.lbl_manufacturers}...</option>
{section name=mid loop=$manufacturers_menu }
<option value="{$manufacturers_menu[mid].manufacturerid}"{if $manufacturerid eq $manufacturers_menu[mid].manufacturerid} selected{/if}>{$manufacturers_menu[mid].manufacturer}</option>
{/section}
{if $show_other_manufacturers}
<option value="0"{if $manufacturerid eq "0"} selected{/if}>{$lng.lbl_other_manufacturers}</option>
{else}
<option value="0"{if $manufacturerid eq "0"} selected{/if}>{$lng.lbl_all} {$lng.lbl_manufacturers}...</option>
{/if}
</select>
</form>

{/capture}
{include file="customer/menu_dialog.tpl" title=$lng.lbl_manufacturers content=$smarty.capture.menu additional_class="menu-manufacturers"}

The manuafcturers table has these fields:

ManufacturerID, Manufacturer, avail, artist_only.

Right now, the form only populates with those manufacturers where avail="Y" (its a Y or N field). I need it to populate only those manufacturers where avail="Y' AND artist_only="Y" (another Y or N field). It populates from a .php file that gets the manufacturer list to use in the loop. However, that .php file is encrypted so I can't see the database query it uses. So I need to make my own .php file with my own query.

That query is the part that I can't figure out. I also don't really understand the line of code:

or what it does.

Any help?

If you'd like to see the dropdown it's here:

www.inkedboutique.com/home.php?shopkey=123

Click on a product and then you'll see the Brands dropdown on the left.

niroshan

Hi bf2017,

what this.form.submit() will does is, it submit the form when ever you select a manufacture from the manufacture drop down.

<form method="get" action="manufacturers.php">

So I think the easiest way to hack this code is (since manufacturers.php file is encrypted ),

backup the original manufacturers.php
create a PHP file called manufacturers.php and put it exact location where original manufacturers.php is at.
create your own query to fetch the data from the database
populate the array $manufacturers_men with relevant data. $manufacturers_men should have following data,

a) manufacturerid
b) manufacturer

make sure you have values for the following variables as well,
a) $lng.lbl_manufacturers
b) $show_other_manufacturers
c) $lng.lbl_other_manufacturers
d) $lng.lbl_all
e) $lng.lbl_manufacturers

Hope this helps...

best regards,
niroshan

bf2017

Thanks for the reply niroshan.

Questions...

First here is the code with tags so it's easier to read.

{*
$Id: menu_manufacturers.tpl,v 1.1.2.2 2010/12/09 14:00:53 aim Exp $
vim: set ts=2 sw=2 sts=2 et:
*}

<script type="text/javascript" src="include/chosen.jquery.min.js">
</script>


{capture name=menu}



<form method="get" action="manufacturers.php">
<select name="manufacturerid" onchange="this.form.submit()" class="chzn-select-manuf" style="width:100%" >
<option value="0"{if $manufacturerid eq "0"} selected{/if}>{$lng.lbl_manufacturers}...</option>
{section name=mid loop=$manufacturers_menu }
<option value="{$manufacturers_menu[mid].manufacturerid}"{if $manufacturerid eq $manufacturers_menu[mid].manufacturerid} selected{/if}>{$manufacturers_menu[mid].manufacturer}</option>
{/section}
{if $show_other_manufacturers}
<option value="0"{if $manufacturerid eq "0"} selected{/if}>{$lng.lbl_other_manufacturers}</option>
{else}
<option value="0"{if $manufacturerid eq "0"} selected{/if}>{$lng.lbl_all} {$lng.lbl_manufacturers}...</option>
{/if}
</select>
</form>

{/capture}
{include file="customer/menu_dialog.tpl" title=$lng.lbl_manufacturers content=$smarty.capture.menu additional_class="menu-manufacturers"}

So I already have variables for

a) $lng.lbl_manufacturers
b) $show_other_manufacturers
c) $lng.lbl_other_manufacturers
d) $lng.lbl_all
e) $lng.lbl_manufacturers

The manufacturers.php isn't encrypted. The file that queries the table to get the list of manufacturers is the encrypted php file. (Correct?)

I have tried to make my own php file to query the list of manufacturers, however, I don't think my syntax is right because I keep getting a SQL error and no manufacturers are displayed in the dropdown.

So can somebody show me a query that will list the manufacturers complete with the strings in the above code and show me where to place those strings in the form section from above?

OR...if there is an easier way to code this form, show me a query and make a completely new dropdown .tpl to do the same thing as the above .tpl.

The manufacturers table has these fields:
manufacturerid
manufacturer
url
descr
order
provider
avail
artist_only

I need a query that will only display those manufacturers WHERE avail="Y' AND artist_only="Y".

Thanks in advance.

niroshan

Hi bf2017,

Can you post the code in 'manufacturers.php'?

bf2017

I finally found where the manufacturers query resides, but I still have a question.

This is the code that retrieves the manufacturers list:

function func_get_manufacturers_list($avail_only = false, $limit = 0) {
    global $shop_language, $sql_tbl;

$query = "SELECT $sql_tbl[manufacturers].*, IFNULL($sql_tbl[manufacturers_lng].manufacturer,"
    . " $sql_tbl[manufacturers].manufacturer) AS manufacturer, IFNULL($sql_tbl[manufacturers_lng].descr,"
    . " $sql_tbl[manufacturers].descr) AS descr FROM $sql_tbl[manufacturers]"
    . ($avail_only ? ' USE INDEX (avail)' : '')
    . " LEFT JOIN $sql_tbl[manufacturers_lng] ON $sql_tbl[manufacturers].manufacturerid = $sql_tbl[manufacturers_lng].manufacturerid"
    . " AND $sql_tbl[manufacturers_lng].code = '$shop_language'"
    . ($avail_only ? " WHERE avail = 'Y'" : '')
    . " ORDER BY orderby, manufacturer"
    . ($limit > 0 ? ' LIMIT ' . $limit : '');

return func_query($query);
}

I want it to retrieve only those manufacturers WHERE avail="Y" AND artist_only="Y". I know I need to add something like $artist_only = false in the first line of the code and also something like . ($artist_only ? " WHERE artist_only = 'Y'" : '') down below in the query so it looks something like this:

function func_get_manufacturers_list($avail_only = false, $artist_only=false, $limit = 0) {
    global $shop_language, $sql_tbl;

$query = "SELECT $sql_tbl[manufacturers].*, IFNULL($sql_tbl[manufacturers_lng].manufacturer,"
    . " $sql_tbl[manufacturers].manufacturer) AS manufacturer, IFNULL($sql_tbl[manufacturers_lng].descr,"
    . " $sql_tbl[manufacturers].descr) AS descr FROM $sql_tbl[manufacturers]"
    . ($avail_only ? ' USE INDEX (avail)' : '')
    . " LEFT JOIN $sql_tbl[manufacturers_lng] ON $sql_tbl[manufacturers].manufacturerid = $sql_tbl[manufacturers_lng].manufacturerid"
    . " AND $sql_tbl[manufacturers_lng].code = '$shop_language'"
    . ($avail_only ? " WHERE avail = 'Y'" : '')
    . ($artist_only ? " WHERE artist_only = 'Y'" : '')
    . " ORDER BY orderby, manufacturer"
    . ($limit > 0 ? ' LIMIT ' . $limit : '');

return func_query($query);
}

But I can't get my syntax right.

Help with the proper syntax and code?

niroshan

HI bf2017,
yeah your right Its better to add $artist_only=false as a parameter to the function, so that it gave more flexibility.

Try this,

function func_get_manufacturers_list($avail_only = false, $artist_only=false, $limit = 0) { 
    global $shop_language, $sql_tbl; 

$whereClause = '';	
$whereClause = $avail_only ? " WHERE avail = 'Y'" : '';

if($artist_only)
	$whereClause = $whereClause == '' ? " WHERE artist_only = 'Y'" : " AND artist_only = 'Y'";	

$query = "SELECT $sql_tbl[manufacturers].*, IFNULL($sql_tbl[manufacturers_lng].manufacturer," 
    . " $sql_tbl[manufacturers].manufacturer) AS manufacturer, IFNULL($sql_tbl[manufacturers_lng].descr," 
    . " $sql_tbl[manufacturers].descr) AS descr FROM $sql_tbl[manufacturers]" 
    . ($avail_only ? ' USE INDEX (avail)' : '') 
    . " LEFT JOIN $sql_tbl[manufacturers_lng] ON $sql_tbl[manufacturers].manufacturerid = $sql_tbl[manufacturers_lng].manufacturerid" 
    . " AND $sql_tbl[manufacturers_lng].code = '$shop_language'" 
    . $whereClause
    . " ORDER BY orderby, manufacturer" 
    . ($limit > 0 ? ' LIMIT ' . $limit : ''); 

return func_query($query); 
}

Regards,
Niroshan

bf2017

Beautiful! That did the trick. Thanks so much!