Forum Stats

  • 3,826,774 Users
  • 2,260,707 Discussions
  • 7,897,072 Comments

Discussions

Reg: Displaying database result as per user query from smarty.

Madhu.149
Madhu.149 Member Posts: 190
edited Oct 16, 2013 5:40PM in PHP

Hello All,

I am developing an user interactive sql site, where user can input sql queries and fetch results in the application.

I need to get the result set from the database as per user query and display in the form of a table.

php:

<?php
include_once('tiki-setup.php');


$res_set = array();
  $conn=oci_connect("hr","hr","localhost/XE");
  $c_err = oci_error($conn);
  $c_err1 = htmlentities($c_err['message']);
  $smarty->assign('con_err', $c_err1);
  // check if the form has been submitted. If it has, process the form and save it to the database
  if (isset($_POST['submit']))
  {
$sqlid = $_POST['sqlid'];
$smarty->assign("sqltext", $sqlid);
  // get form data, making sure it is valid
  $sqlid = $_POST['sqlid'];
  $query=$sqlid;
  $stid = oci_parse($conn, $query);
  $res = oci_execute($stid);
  if (!$res) {
    $e = oci_error($stid);  // For oci_execute errors pass the statement handle
  $e1 = htmlentities($e['message']);

$smarty->assign('err', $e1);
}
else
{
  while ($rows = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
  $res_set[] = $rows;
  }
  $smarty->assign('result', $res_set);
  
  }


}
$smarty->assign('mid', 'hello_world.tpl');
$smarty->display('template_new.tpl');




  ?>

Smarty:

<table style="border-collapse:collapse;">
   {foreach from=$result item=row}
    <tr style="background-color: {cycle values="#eeeeee,#d0d0d0"}">
  {if $row.DEPARTMENT_ID and $row.FIRST_NAME and $row.SALARY}
  <td>{$row.DEPARTMENT_ID}</td>
  <td>{$row.FIRST_NAME}</td>
  <td>{$row.SALARY}</td>
  {else}
        <td>{$row.EMPLOYEE_ID}</td>
  <td>{$row.FIRST_NAME}</td>
  <td>{$row.LAST_NAME} </td>
  <td>{$row.EMAIL}</td>
  <td>{$row.PHONE_NUMBER}</td>
  <td>{$row.HIRE_DATE}</td>
  <td>{$row.JOB_ID}</td>
  <td>{$row.SALARY}</td>
  <td>{$row.COMMISSION_PCT}</td>
  <td>{$row.MANAGER_ID}</td>
  <td>{$row.DEPARTMENT_ID}</td>
  {/if}
    </tr>
    {/foreach}
  </table>
  <div style="color:red;">
  {$err}
  </div>
  <div style="color:red;">
  {$con_err}
  </div>

Without smarty I can get the result with the below code..

 print '<table border="1">';
   while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
  print '<tr>';
    foreach ($row as $item) {
  print '<td>'.($item?htmlentities($item):' ').'</td>';
    }
    print '</tr>';
   }
   print '</table>';

But i need to do with smarty?

How to transfer the "print" code to smarty

How is this possible?

Answers

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,648 Employee

    I believe "twig" is the template system of the moment.  But if you do want Smarty, I have a very old example that may or may not help, depending what your problem is.

    emp.php:

    <?php
    
    // NAME
    //   emp.php
    // List employees
    
    require("Smarty.class.php");
    
    $c = oci_connect("hr", "welcome", "localhost/xe");
    
    // List all employees
    function do_query($c)
    {
      $sql = "select first_name, last_name from employees order by employee_id";
      $s = oci_parse($c, $sql);
      oci_execute($s);
      $r = oci_fetch_all($s, $results, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
      if ($r === false) {
        die;
      }
      return($results);
    }
    
    $smarty = new Smarty;
    //$smarty->caching = false;
    $smarty->compile_check = true;
    //$smarty->debugging = true;
    
    $emp = do_query($c);
    $smarty->assign('emp', $emp);
    $smarty->display('emp.tpl.php');
    
    ?>
    

    emp.tpl.php:

    {* 
      NAME
        emp.tpl.php
      DESCRIPTION
        Smarty template to show employees list.
    *}
    
    {include file="empheader.tpl.php" title="Employees"}
    
    {if $emp}
      <table border="1">
      <tr>
        <th>First Name</th>
        <th>Last Name</th>
      </tr>
      {section name=i loop=$emp}
      <tr>
        <td>{$emp[i].FIRST_NAME|default:" "}</td>
        <td>{$emp[i].LAST_NAME|default:" "}</td>
      </tr>
      {/section}
      </table>
    {else}
      <p>No Rows.</p>
    {/if}
    
    {include file="empfooter.tpl.php"}
    

    empheader.tpl.php:

    {* 
      NAME
        empheader.tpl.php
      DESCRIPTION
        Smarty template: common page header file for Employee System
    *}
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
           "http://www.w3.org/TR/html4/loose.dtd">
    <HTML>
    <HEAD>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1">
    <LINK REL="stylesheet" TYPE="text/css"  HREF="templates/empstyle.css" />
    <TITLE>{$title|default:"Employee System"}</TITLE>
    </HEAD>
    <BODY>
    <H1>{$title|default:"Employee System"}</H1>
    

    empfooter.tpl.php:

    {* 
      NAME
        empfooter.tpl.php
      DESCRIPTION
        Smarty template: common page footer file for Employee System
    *}
    <!-- <p>{$smarty.now|date_format:"%Y-%m-%d %H:%M:%S"}</p> -->
    <hr>
    <p>Employee System v1.0</p>
    </BODY>
    </HTML>
    
    Christopher Jones-Oracle
This discussion has been closed.