Menu
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:
[table]
[tr]
[td][b]name[/b][/td]
[td][b]alias[/b][/td]
[td][b]extra[/b][/td]
[/tr]
[tr]
[td]Class[/td]
[td]class[/td]
[td]options[Dancercise=Dancercise,Abs / Mega Abs Blast=Abs / Mega Abs Blast,exercise 3 = exercise 3, exercise 4 = exercise 4][/td]
[/tr]
[tr]
[td]Centre[/td]
[td]centre[/td]
[td]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][/td]
[/tr]
[tr]
[td]Day[/td]
[td]day[/td]
[td]options[Monday=Monday,Tuesday=Tuesday,Wednesday=Wednesday,Thursday=Thursday,Friday=Friday, Saturday=Saturday,Sunday=Sunday][/td]
[/tr]
[tr]
[td]Start Time[/td]
[td]startime[/td]
[td]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][/td]
[/tr]
[tr]
[td]End Time[/td]
[td]endtime[/td]
[td]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][/td]
[/tr]
[/table]
I have got so far with creating the form within my script:
[code=php]
/**
* 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