Forum Stats

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

Discussions

Categories

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?

· Share on TwitterShare on Facebook

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
    · Share on TwitterShare on Facebook
This discussion has been closed.