Solved

PHP/Smarty : How to fetch data from mysql to and Option or List Menu

Posted on 2008-10-18
7
1,974 Views
Last Modified: 2013-12-13
by using smarty,

how to fetch data from mysql and show it to an option list.


i wan to select to display the

field "pack_name" from table "package"

inside pack_name field have several row as below

gold
silver
pro
free

how do i display those to option list?
thanks for you help.
PHP :
 
	$sql2    = "SELECT * FROM package";
	$rs2  = $conn->execute($sql2);
	        $pack = $rs2->getrows();
	
	$sql    = "SELECT * FROM signup WHERE UID = '" .$UID. "' LIMIT 1";
    $rs     = $conn->execute($sql);
	
    if ( mysql_affected_rows() == 1 )
        $user = $rs->getrows();
    else
        $err = 'This user does not exist! Invalid user ID?';
 
 
 
 
 
 
 
 
 
 
 
HTML Template :
 
<label for="Package">Package: </label>
          <select name="package">
            {section name=i loop=$pack.pack_name}
            <option value="{$pack.pack_name[i]}">{$pack.pack_name[i]}</option>
            {/section}
                                                                          </select>

Open in new window

0
Comment
Question by:alicca
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 6

Expert Comment

by:thebradnetwork
ID: 22751022
This is a search and display script in PHP...you will need a HTML search form and if you need that let me know.
           <?php
 
 
            // declare sting variable and set value to link to theform.html
            
 
            // connect to database and select database 
 
            $con = mysql_connect("localhost","username","password");
            if (!$con)
            {
                  die('Could not connect: ' . mysql_error());
            }
 
            mysql_select_db("directory", $con);
 
            $name = 'name';
            if (isset($_REQUEST['name']))
            {
                  $name = trim($_REQUEST['name']);
            } else {
                  echo "Sorry, no search criteria.";
 
                  exit;
            }
 
            // contains the string input by the user on the name.html page
 
            $query = "SELECT * FROM package '".mysql_real_escape_string($name)."%'";
 
            $result = mysql_query($query) or die( "Problem executing: ". $query . " " . mysql_error() );
 
            // if no results are returned from the query, give error message and exit
            if (mysql_num_rows($result) == 0) {
                  echo "Sorry, no matching results.";
 
                  exit;
            }
 
            while ($row = mysql_fetch_assoc($result)) {
            
 
 
		  echo "<b>Package Name: </b>";
                  echo $row["package_name"] ;
                  echo  "&nbsp;";
 
            }
            ?>
      </BODY>
</HTML>

Open in new window

0
 
LVL 6

Expert Comment

by:thebradnetwork
ID: 22751025
also your row will need to be named package_name
0
 
LVL 2

Author Comment

by:alicca
ID: 22751041
i need this work on smarty to show menu/List

anyway thanks
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:alicca
ID: 22751060
i know how to display thing in php, i dont know how to display variable from php to smarty templete.

some 1 please help
0
 
LVL 25

Expert Comment

by:Marcus Bointon
ID: 22758042
Have you tried reading the Smarty docs? This is pretty simple stuff.

In your PHP script you need to assign the list of values to your smarty instance:

$smarty->assign('pack', $pack);

Then the contents of the $pack variable will be available inside your template.

The easiest way to build a select menu is to the the Smarty html_options plugin:

http://www.smarty.net/manual/en/language.function.html.options.php
0
 
LVL 2

Author Comment

by:alicca
ID: 22760402
thanks Squinky.

i got what you mean.. but cant make my script run..
nothing was shown except the

-- none --



see my code, if i did something wrongly?

if i use

{html_options options=$pack}

it show the whole thing inside table in a long list.

TPL :
 
  <fieldset>
        <legend>Package Management</legend>
            <label for="Package">Package: </label>
            <select name="package">
             <option value='null'>-- none --</option>
             {html_options options=$pack.pack_name }
                                                                           </select>
            <br>
            <label for="gender"></label>
<br>
        </fieldset>
 
 
=====================================================================
 
PHP:
 
 
    
	$sql2    = "SELECT pack_name FROM package";
	$rs2  = $conn->execute($sql2);
	        $pack = $rs2->getrows();
	
	$sql    = "SELECT * FROM signup WHERE UID = '" .$UID. "' LIMIT 1";
    $rs     = $conn->execute($sql);
	
    if ( mysql_affected_rows() == 1 )
        $user = $rs->getrows();
    else
        $err = 'This user does not exist! Invalid user ID?';
}
 
STemplate::assign('pack', $pack);
STemplate::assign('user', $user);
STemplate::assign('countries', $countries);
?>

Open in new window

0
 
LVL 25

Accepted Solution

by:
Marcus Bointon earned 500 total points
ID: 22760963
The item that you pass for options needs to be a simple associative array (where the key name is used as the value attribute and the value is use for the option tag content), and what you're giving it is probably a bit more than that. If $pack is an array of records, then $pack.pack_name is probably an invalid index, hence the empty select.

Preprocess the pack list like this:

$packnames = array();
foreach($pack as $item) {
$packnames[$pack['id']] = $pack['pack_name'];
}
$smarty->assign('packnames' , $packnames);

That should then work in the html_options tag (you may need to tweak field names).
0

Featured Post

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question