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:

[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,...etc., etc.,...,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,,...etc., etc.,...,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:

/**
 * 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

[Weedpacket]

I can see an error in the script; using [man]mysql_error[/man] would show you what the problem is.

[jpmad4it]

Thanks. Is it mssql? That should be mysql. I've fixed that but nothing gives?

[Weedpacket]

What did [man]mysql_error[/man] say?

[johanafm]

First off, if you ever need to use some kind of formatting that would make the page 5000px wide, please manually insert line breaks. It's annoying as hell having to scroll left and right all the time. Usually I reread my post before submitting it for real to fix any misstakes, but personally I won't bother this time. Culprit: your table's start and end times, most likely since there are
no whitespaces.

jpmad4it;11013333 wrote:

Thanks. Is it mssql? That should be mysql. I've fixed that but nothing gives?

And apart from Weedpacket's question, whenever you "fix" a problem and it either doens't go away or introduces a new one,
post the changes you made. It's impossible for us to guess what you "fixed" when you didn't actually fix it.

Some remarks which are slightly off topic from your specific question but relate to your code in case you want the advice

jpmad4it;11013281 wrote:

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

While SQL server may refer to any SQL database server, it's the name of Microsoft's product and as such people will expect that you are talking about SQL Server, not just any database.

To make things clear, I'd write either "Couldn't connect to MySQL Server" or preferably plain and simple "Couldn't connect to database". The user doesn't need to know anything about your platform, and a potential hacker definitely shouldn't be given knowledge about what security holes may exist...

jpmad4it;11013281 wrote:

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);

While off topic from what you asked, I'd still like to point out that you should change what data you assign to $data. The only thing you should assign to this variable is $rowdata, not the entire option string: "<option ... </option>". The reason is that the presentation should be handled in your templates instead of being coupled with your program logic. I.e.

$data = array();
while($row = mssql_fetch_array($result)) {
	# In my opinion, ucfirst(strtolower()) also belongs in your template, since it's about presentation
	# and nothing else.
	$data[] = $row['extra'];

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

and then in the template...

<select>
{foreach from=$type item=d}
	<option value="$d">{$d|strtolower|ucfirst}</option>
{/foreach}
</select>

<!-- should you ever want to handle this in some other way in the browser,
	you'd simply change the html code in your template, whithout touching the underlying logic -->
Stupid example perhaps, but the principles are there
<h3>List of options</h3>
{foreach from=$type item=d}
	<div>{$d|strtolower|ucfirst}</div>
{/foreach}

Also note that <option>string</option> is the same as writing <option value="string">string</option>, so unless you actually need the value to be case sensitive, you'd be better off just cutting the value part out.