1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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

Discussion in 'PHP' started by jpmad4it, Sep 20, 2012.

  1. #1
    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]name[/td]
    [td]alias[/td]
    [td]extra[/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:

    
    /**
     * 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);
    PHP:
    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
     
    jpmad4it, Sep 20, 2012 IP