Help: Select and format data from DB, and output in Smarty tag for CMS?

[jpmad4it]

Hi all,

 

I have a form that I need to create using data that is held in a MySQL database. I need to select the data, format it and output the form inputs in a smarty tag.

 

Here is the data structure:

 

 

   

     

name

     

alias

     

extra

   

   

     

Class

     

class

     

options[Dancercise=Dancercise,Abs / Mega Abs Blast=Abs / Mega Abs Blast,exercise 3 = exercise 3, exercise 4 = exercise 4]

   

   

     

Centre

     

centre

     

options[sports centre 1=Sports centre 1,Sports centre 2=Sports centre 2,Sports centre 3=Sports centre 3,Sports centre 4=Sports centre 4]

   

   

     

Day

     

day

     

options[Monday=Monday,Tuesday=Tuesday,Wednesday=Wednesday,Thursday=Thursday,Friday=Friday, Saturday=Saturday,Sunday=Sunday]

   

   

     

Start Time

     

startime

     

options[06:00=06:00,06:15=06:15,06:30=06:30,06:45=06:45,07:00=07:00,07:15=07:15,07:30=07:30,07:45=07:45,08:00=08:00,08:15=08:15,08:30=08:30,08:45=08:45,09:00=09:00,09:15=09:15,09:30=09:30,09:45=09:45,10:00=10:00,10:15=10:15,10:30=10:30,10:45=10:45,11:00=11:00,11:15=11:15,11:30=11:30,11:45=11:45,12:00=12:00,12:15=12:15,12:30=12:30,12:45=12:45,13:00=13:00,13:15=13:15,13:30=13:30,13:45=13:45,14:00=14:00,14:15=14:15,14:30=14:30,14:45=14:45,15:00=15:00,15:15=15:15,15:30=15:30,15:45=15:45,16:00=16:00,16:15=16:15,16:30=16:30,16:45=16:45,17:00=17:00,17:15=17:15,17:30=17:30,17:45=17:45,18:00=18:00,18:15=18:15,18:30=18:30,18:45=18:45,19:00=19:00,19:15=19:15,19:30=19:30,19:45=19:45,20:00=20:00,20:15=20:15,20:30=20:30,20:45=20:45,21:00=21:00,21:15=21:15,21:30=21:30,21:45=21:45,22:00=22:00,22:15=22:15,22:30=22:30]

   

   

     

End Time

     

endtime

     

options[06:00=06:00,06:15=06:15,06:30=06:30,06:45=06:45,07:00=07:00,07:15=07:15,07:30=07:30,07:45=07:45,08:00=08:00,08:15=08:15,08:30=08:30,08:45=08:45,09:00=09:00,09:15=09:15,09:30=09:30,09:45=09:45,10:00=10:00,10:15=10:15,10:30=10:30,10:45=10:45,11:00=11:00,11:15=11:15,11:30=11:30,11:45=11:45,12:00=12:00,12:15=12:15,12:30=12:30,12:45=12:45,13:00=13:00,13:15=13:15,13:30=13:30,13:45=13:45,14:00=14:00,14:15=14:15,14:30=14:30,14:45=14:45,15:00=15:00,15:15=15:15,15:30=15:30,15:45=15:45,16:00=16:00,16:15=16:15,16:30=16:30,16:45=16:45,17:00=17:00,17:15=17:15,17:30=17:30,17:45=17:45,18:00=18:00,18:15=18:15,18:30=18:30,18:45=18:45,19:00=19:00,19:15=19:15,19:30=19:30,19:45=19:45,20:00=20:00,20:15=20:15,20:30=20:30,20:45=20:45,21:00=21:00,21:15=21:15,21:30=21:30,21:45=21:45,22:00=22:00,22:15=22:15,22:30=22:30]

   

 

 

I have got so far with creating the form within my script:

 

/**
* PHP Database Connection
*
* Allows a CMS website to connect to the MYSQL
* Database to return live data for Interactive
* Search fields.
*
* Returns result as a smarty variable.
*

*
*/

$server = "mp.server.ip";
$u 		= "user";
$p 		= "pass";
$db 	= "db";
$field  = "extra";

// Connect
$connection = mysql_connect($server, $u, $p)
or die("Couldn’t connect to SQL Server on $server");

// Select Database
$selected = mysql_select_db($db, $connection)
or die("Couldn’t open database $db");

// Field to select
$query  = "SELECT $field";

// Table and conditions should generally remain the same, barring chain ID.
$query .= "FROM cms_module_listit2cloneinteractivesearch_fielddef";
$query .= "WHERE alias = 'class'";

// Execute the SQL Query
$result = mysql_query($query);

// Build select options from results
$data = '';

while($row = mssql_fetch_array($result)) {
$rowdata = $row['extra'];
$data .= "<option value=\"$rowdata\">" . ucwords(strtolower($rowdata)) . "</option>";
//echo "<option value=\"$rowdata\">" . ucwords(strtolower($rowdata)) . "</option>";
}

$smarty->assign('type', $data);

//close the connection
mysql_close($connection);

 

This doesn't seem to do anything though. I don't know if there is an error somewhere in my script, or whether it is freaking out at the data that is contained in the "extra" field because I am not doing anything with it to get it into the options correctly.

 

Can anyone help me figure this out? I am basically trying to create select drop-downs for all of the fields, and output the data in "extra" as the <option value=""> for each field.

 

Any help will be greatly appreciated.

 

Kind regards

Jp