Problem in SQL Statement

rhodarose · May 11, 2011, 7:23am

Good day!

When I change my code to separate html and php code and I used template and config which is not my code . I got a lot of problems and error. And sad to say I have no rights to edit or change the code in template and config sender and also I cannot dispplay those code here in forum.

here is my old code of machine1:


<?php
session_start(); 
if(empty($_SESSION['logged_in'])) {
    header('Location:index.php');
    die();
}
?>
<html>
<head>
<meta>
</head>
 <body>
 <form name="machine1" action="machine1.php" method="post">
 
 <p>
  <?php
 
   $sort = "ASC";
  $data_sort = "Emp_ID";
  
  if(isset($_GET['sorting']))
	{
		if($_GET['sorting'] == 'ASC'){
			$sort = "DESC";
		}
		else{
			$sort = "ASC";
		}
	}
	
	
	if (isset($_GET['field_name'])) {
		if($_GET['field_name']  == 'Emp_ID'){
			$data_sort = "Emp_ID";
		}
		elseif($_GET['field_name'] == 'Last_Name'){
			$data_sort = "Last_Name";
		}
		elseif($_GET['field_name'] == 'First_Name'){
			$data_sort = "First_Name";
		}
		elseif($_GET['field_name'] == 'Birthday'){
			$data_sort = "Birthday";
		}
	}
 ?>
 
 <table border="1">
   <tr>
 <td><a href="machine1.php?sorting=<?php echo $sort; ?>&field_name=Emp_ID">Emp ID</a></td>
 <td><a href="machine1.php?sorting=<?php echo $sort; ?>&field_name=Last_Name">Last Name</a></td>
 <td><a href="machine1.php?sorting=<?php echo $sort; ?>&field_name=First_Name">First Name</a></td>
 <td><a href="machine1.php?sorting=<?php echo $sort; ?>&field_name=Birthday">Birthday</a></td>
 <td>Option</td>
 </tr>
 
<?php 
include 'connection.php';

if (isset($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} else {
   $pageno = 1;
} 
$query = "SELECT count(*) FROM tbl_machine1";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];

$rows_per_page = 5;
$lastpage      = ceil($numrows/$rows_per_page);

$pageno = (int)$pageno;
if ($pageno > $lastpage) {
   $pageno = $lastpage;
} 
if ($pageno < 1) {
   $pageno = 1;
} 

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;



$query = "SELECT * FROM tbl_machine1 ORDER BY $data_sort $sort $limit";
$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);


while($info = mysql_fetch_array( $result ))
{
	$emp_id = $info['Emp_ID'];
	$lname = $info['Last_Name'];
	$fname = $info['First_Name'];
	$bday = $info['Birthday'];
	$date = date('d-m-Y', strtotime($bday));
	?>
	<tr>
	<td><?php echo $emp_id;?> </td>
    <td><?php echo htmlentities($lname, ENT_QUOTES); ?> </td>
    <td><?php echo htmlentities($fname, ENT_QUOTES);?> </td>
    <td><?php echo htmlentities($date, ENT_QUOTES);?> </td>
   	<td><a href = 'edit.php?id=<?php echo $emp_id; ?>'>Edit</a> <a href='delete.php?id=<?php echo $emp_id; ?>' onClick="return confirm('Are you sure you want to delete?')">Delete</a></td>
	</tr>
    <?php
}
?>
	</table>
     <A HREF="javascript:void(0)" onClick="window.open('add.php','welcome','width=300,height=200')">
<input type="button" name="add" value="ADD"> </A>&nbsp;&nbsp;

<?php

 if(isset($_GET['sorting']))
	{
		if($_GET['sorting'] == 'ASC'){
			$sort = "ASC";
		}
		else{
			$sort = "DESC";
		}
	}
				
if ($pageno == 1) {
   echo " FIRST PREV ";
} else {
 ?>
 <a href="machine1.php?pageno=1&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">FIRST</a>
<?php
   $prevpage = $pageno-1;
?>
   <a href="machine1.php?pageno=<?php echo $prevpage;?>&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">PREV</a>
 
<?php
}
echo " ( Page $pageno of $lastpage ) ";

if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;

 ?>
 
	<a href="machine1.php?pageno=<?php echo $nextpage; ?>&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">NEXT</a>
   	<a href="machine1.php?pageno=<?php echo $lastpage; ?>&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">LAST</a> 
 <?php
}

?>
 </body>
 </html>

and when I edit it


<html>
<head>
<meta>

</head>

<body>
<?php

error_reporting(E_ERROR | E_WARNING | E_PARSE);
include('includes/config.sender.php');
include('includes/template.inc');


/*Sorting of Data*/
 $sort = "ASC";
  $data_sort = "Emp_ID";
  
  if(isset($_GET['sorting']))
	{
		if($_GET['sorting'] == 'ASC'){
			$sort = "DESC";
		}
		else{
			$sort = "ASC";
		}
	}
	
	
	if (isset($_GET['field_name'])) {
		if($_GET['field_name']  == 'Emp_ID'){
			$data_sort = "Emp_ID";
		}
		elseif($_GET['field_name'] == 'Last_Name'){
			$data_sort = "Last_Name";
		}
		elseif($_GET['field_name'] == 'First_Name'){
			$data_sort = "First_Name";
		}
		elseif($_GET['field_name'] == 'Birthday'){
			$data_sort = "Birthday";
		}
	}
	
	
/*Pagination, Sorting and Limit*/

if (isset($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} else {
   $pageno = 1;
} 
//$query = "SELECT count(*) FROM tbl_machine1";

$sql_select = "SELECT COUNT(
					Emp_ID,
					Last_Name,
					First_Name,
					Birthday)
			   FROM
					machine_problem_rhoda";
					
$result = $_DB->opendb($sql_select);

//var_dump($sql_select);


//$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
//$query_data = mysql_fetch_row($result);
//$numrows = $query_data[0];
$numrows = $result[0];

$rows_per_page = 5;
$lastpage      = ceil($numrows/$rows_per_page);

$pageno = (int)$pageno;
if ($pageno > $lastpage) {
   $pageno = $lastpage;
} 
if ($pageno < 1) {
   $pageno = 1;
} 

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;



$sql_select = "SELECT
					Emp_ID,
					Last_Name,
					First_Name,
					Birthday
				FROM
					machine_problem_rhoda
				ORDER BY $data_sort $sort $limit
				";
$rows = $_DB->opendb($sql_select);

//$query = "SELECT * FROM tbl_machine1 ORDER BY $data_sort $sort $limit";
//$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);


//while($info = mysql_fetch_array( $result ))

$tpl->set_block('handle', 'block_list', 'tag_list');
foreach($rows as $row) {
	$tpl->set_var(array('id'=> $row['Emp_ID'],
						'lastname' => $row['Last_Name'],
						'firstname' => $row['First_Name'],
						'birthday' => $row['Birthday'],
	));
	$tpl->parse('tag_list', 'block_list', true);
}
/*while($info = countdata( $result ))
{
	$emp_id = $info['Emp_ID'];
	$lname = $info['Last_Name'];
	$fname = $info['First_Name'];
	$bday = $info['Birthday'];
	$date = date('d-m-Y', strtotime($bday));
	?>
	<tr>
	<td><?php echo $emp_id;?> </td>
    <td><?php echo htmlentities($lname, ENT_QUOTES); ?> </td>
    <td><?php echo htmlentities($fname, ENT_QUOTES);?> </td>
    <td><?php echo htmlentities($date, ENT_QUOTES);?> </td>
   	<td><a href = 'edit.php?id=<?php echo $emp_id; ?>'>Edit</a> <a href='delete.php?id=<?php echo $emp_id; ?>' onClick="return confirm('Are you sure you want to delete?')">Delete</a></td>
	</tr>
    <?php
}*/
?>
	<!--</table>-->
     <A HREF="javascript:void(0)" onClick="window.open('add.php','welcome','width=300,height=200')">
<input type="button" name="add" value="ADD"> </A>&nbsp;&nbsp;

<?php

 if(isset($_GET['sorting']))
	{
		if($_GET['sorting'] == 'ASC'){
			$sort = "ASC";
		}
		else{
			$sort = "DESC";
		}
	}
				
if ($pageno == 1) {
   echo " FIRST PREV ";
} else {
 ?>
 <a href="machine1.php?pageno=1&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">FIRST</a>
<?php
   $prevpage = $pageno-1;
?>
   <a href="machine1.php?pageno=<?php echo $prevpage;?>&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">PREV</a>
 
<?php
}
echo " ( Page $pageno of $lastpage ) ";

if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;

 ?>
 
	<a href="machine1.php?pageno=<?php echo $nextpage; ?>&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">NEXT</a>
   	<a href="machine1.php?pageno=<?php echo $lastpage; ?>&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">LAST</a> 
 <?php
}

?>





<?php


/*$sql_select = "SELECT
					Emp_ID,
					Last_Name,
					First_Name,
					Birthday
			   FROM
					machine_problem_rhoda";
					
$rows = $_DB->opendb($sql_select);*/


$tpl = new Template('.', 'keep');
$tpl->set_file(array('handle' => 'html/machine1.html'));

/*$tpl->set_block('handle', 'block_list', 'tag_list');
foreach($rows as $row) {
	$tpl->set_var(array('id'=> $row['Emp_ID'],
						'lastname' => $row['Last_Name'],
						'firstname' => $row['First_Name'],
						'birthday' => $row['Birthday'],
	));
	$tpl->parse('tag_list', 'block_list', true);
}*/


$tpl->parse('handle', array('handle'));
$tpl->p('handle');

?>
</body>
</html>

and I got this error:

SELECT COUNT(
Emp_ID,
Last_Name,
First_Name,
Birthday)
FROM
machine_problem_rhoda
query error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’
Last_Name,
First_Name,
Birthday)
FROM
machine_pr’ at line 2

I tried to search in internet regarding this error and I cant find the answer to sove my problem.

I hope somebody can help me…because I dont have any companion only forum

Thank you

DarthGuido · May 11, 2011, 7:42am


COUNT(
Emp_ID,
Last_Name,
First_Name,
Birthday)

As far as I know, you can’t put multiple column names inside the COUNT() function.

rhodarose · May 11, 2011, 7:50am

uhmmm…I really don’t know…uhm what should i do in my code?

DarthGuido · May 11, 2011, 7:55am

You did this before

SELECT count(*) FROM tbl_machine1

Why did you change that? What are you trying to achieve with the change you made?

rhodarose · May 11, 2011, 7:56am

When i tried to used this code:


$sql_select = "SELECT COUNT(*)
				FROM 
					machine_problem_rhoda";
					
$result = $_DB->opendb($sql_select);

I got this fatal error:

Fatal error: Unsupported operand types in /opt/zeva/releases/ZEVA.sandbox/machine_problem/rhoda/machine1.php on line 84

line 84 is


$lastpage      = ceil($numrows/$rows_per_page);

rhodarose · May 11, 2011, 7:59am

I change this because my boss told me avoid using * in select statement:(

DarthGuido · May 11, 2011, 8:01am

Do some echo’s, var_dump’s, print_r’s, whatever you need, to check the values of the variables involved.

rhodarose · May 11, 2011, 8:04am

I used this code


$sql_select = "SELECT COUNT(*)
				FROM 
					machine_problem_rhoda";
					
$result = $_DB->opendb($sql_select);

print_r($result);

and the output is
Array ( [0] => Array ( [COUNT(*)] => 20 ) )

but still the fatal error is still there.

DarthGuido · May 11, 2011, 8:12am

So,

$result contains

Array ( [0] => Array ( [COUNT(*)] => 20 ) )

and

$numrows = $result[0];

What do you think $numrows contains?

rhodarose · May 11, 2011, 9:02am

Honestly sir my code in paging is I got only in internet sir, so I have no idea in
$numrows = $query_data[0];
and I change it to

$numrows = $result[0]; because of the function that i used.and also because
this code $query_data = mysql_fetch_row($result); i was removed because in opendb mysql_fetch_array was used.

rhodarose · May 11, 2011, 9:10am

I revised my code:


$sql_select = "SELECT COUNT(*) as count
				FROM 
					machine_problem_rhoda";
					
$result['count'] = $_DB->opendb($sql_select);

echo "<pre>";
print_r($result);
echo "</pre>";

and the result is
Array
(
[count] => Array
(
[0] => Array
(
[count] => 20
)

)

now I got an error
Fatal error: Call to a member function set_block() on a non-object in /opt/zeva/releases/ZEVA.sandbox/machine_problem/rhoda/machine1.php on line 119

here is my code:


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?php
/*session_start(); 
if(empty($_SESSION['logged_in'])) {
    header('Location:index.php');
    die();
}*/

error_reporting(E_ERROR | E_WARNING | E_PARSE);
include('includes/config.sender.php');
include('includes/template.inc');


/*Sorting of Data*/
 $sort = "ASC";
  $data_sort = "Emp_ID";
  
  if(isset($_GET['sorting']))
	{
		if($_GET['sorting'] == 'ASC'){
			$sort = "DESC";
		}
		else{
			$sort = "ASC";
		}
	}
	
	
	if (isset($_GET['field_name'])) {
		if($_GET['field_name']  == 'Emp_ID'){
			$data_sort = "Emp_ID";
		}
		elseif($_GET['field_name'] == 'Last_Name'){
			$data_sort = "Last_Name";
		}
		elseif($_GET['field_name'] == 'First_Name'){
			$data_sort = "First_Name";
		}
		elseif($_GET['field_name'] == 'Birthday'){
			$data_sort = "Birthday";
		}
	}
	
	
/*Pagination, Sorting and Limit*/

if (isset($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} else {
   $pageno = 1;
} 
//$query = "SELECT count(*) FROM tbl_machine1";

/*$sql_select = "SELECT COUNT(
					Emp_ID,
					Last_Name,
					First_Name,
					Birthday)
			   FROM
					machine_problem_rhoda";*/
					
$sql_select = "SELECT COUNT(*) as count
				FROM 
					machine_problem_rhoda";
					
$result['count'] = $_DB->opendb($sql_select);

echo "<pre>";
print_r($result);
echo "</pre>";

//var_dump($sql_select);


//$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
//$query_data = mysql_fetch_row($result);
//$numrows = $query_data[0];
$numrows = $result[0];

$rows_per_page = 5;
$lastpage      = ceil($numrows/$rows_per_page);

$pageno = (int)$pageno;
if ($pageno > $lastpage) {
   $pageno = $lastpage;
} 
if ($pageno < 1) {
   $pageno = 1;
} 

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;



$sql_select = "SELECT
					Emp_ID,
					Last_Name,
					First_Name,
					Birthday
				FROM
					machine_problem_rhoda
				ORDER BY $data_sort $sort $limit
				";
$rows = $_DB->opendb($sql_select);

//$query = "SELECT * FROM tbl_machine1 ORDER BY $data_sort $sort $limit";
//$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);


//while($info = mysql_fetch_array( $result ))

$tpl->set_block('handle', 'block_list', 'tag_list');
foreach($rows as $row) {
	$tpl->set_var(array('id'=> $row['Emp_ID'],
						'lastname' => $row['Last_Name'],
						'firstname' => $row['First_Name'],
						'birthday' => $row['Birthday'],
	));
	$tpl->parse('tag_list', 'block_list', true);
}
/*while($info = countdata( $result ))
{
	$emp_id = $info['Emp_ID'];
	$lname = $info['Last_Name'];
	$fname = $info['First_Name'];
	$bday = $info['Birthday'];
	$date = date('d-m-Y', strtotime($bday));
	?>
	<tr>
	<td><?php echo $emp_id;?> </td>
    <td><?php echo htmlentities($lname, ENT_QUOTES); ?> </td>
    <td><?php echo htmlentities($fname, ENT_QUOTES);?> </td>
    <td><?php echo htmlentities($date, ENT_QUOTES);?> </td>
   	<td><a href = 'edit.php?id=<?php echo $emp_id; ?>'>Edit</a> <a href='delete.php?id=<?php echo $emp_id; ?>' onClick="return confirm('Are you sure you want to delete?')">Delete</a></td>
	</tr>
    <?php
}*/
?>
	<!--</table>-->
     <A HREF="javascript:void(0)" onClick="window.open('add.php','welcome','width=300,height=200')">
<input type="button" name="add" value="ADD"> </A>&nbsp;&nbsp;

<?php

 if(isset($_GET['sorting']))
	{
		if($_GET['sorting'] == 'ASC'){
			$sort = "ASC";
		}
		else{
			$sort = "DESC";
		}
	}
				
if ($pageno == 1) {
   echo " FIRST PREV ";
} else {
 ?>
 <a href="machine1.php?pageno=1&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">FIRST</a>
<?php
   $prevpage = $pageno-1;
?>
   <a href="machine1.php?pageno=<?php echo $prevpage;?>&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">PREV</a>
 
<?php
}
echo " ( Page $pageno of $lastpage ) ";

if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;

 ?>
 
	<a href="machine1.php?pageno=<?php echo $nextpage; ?>&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">NEXT</a>
   	<a href="machine1.php?pageno=<?php echo $lastpage; ?>&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">LAST</a> 
 <?php
}

?>





<?php


/*$sql_select = "SELECT
					Emp_ID,
					Last_Name,
					First_Name,
					Birthday
			   FROM
					machine_problem_rhoda";
					
$rows = $_DB->opendb($sql_select);*/


$tpl = new Template('.', 'keep');
$tpl->set_file(array('handle' => 'html/machine1.html'));

/*$tpl->set_block('handle', 'block_list', 'tag_list');
foreach($rows as $row) {
	$tpl->set_var(array('id'=> $row['Emp_ID'],
						'lastname' => $row['Last_Name'],
						'firstname' => $row['First_Name'],
						'birthday' => $row['Birthday'],
	));
	$tpl->parse('tag_list', 'block_list', true);
}*/


$tpl->parse('handle', array('handle'));
$tpl->p('handle');

?>
</body>
</html>

and here is my machine1.html


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Machine 1</title>
</head>

<body>
Welcome {username}!
<table border="1" cellpadding="1" cellspacing="1">
    <tr>
    	<td><a href="machine1.php?sorting=<?php echo $sort; ?>&field_name=Emp_ID">Employee Id</a></td>
    	<td><a href="machine1.php?sorting=<?php echo $sort; ?>&field_name=Last_Name">Last Name</a></td>
        <td><a href="machine1.php?sorting=<?php echo $sort; ?>&field_name=First_Name">First Name</a></td>
        <td><a href="machine1.php?sorting=<?php echo $sort; ?>&field_name=Birthday">Birthday</a></td>
    </tr>
	<!-- BEGIN block_list -->
	<tr>
		<td>{id}</td>
		<td>{lastname}</td>
		<td>{firstname}</td>
		<td>{birthday}</td>
	</tr>
	<!-- END block_list -->
</table>
{pagination}
</body>
</html>

what the error mean?

Thank you for your help

DarthGuido · May 11, 2011, 9:12am

I know you have to change it because you have to use opendb.
What I mean is, $result[0] will give you the value of the first element of the $result array, which is

Array ( [COUNT(*)] => 20 )

another array!

What you want is the value 20.

The first thing you should do is change your query a bit, giving an alias name to the COUNT(*) column. This will make things easier and clearer down the road:


$sql_select = "
  SELECT COUNT(*) AS numrows
  FROM machine_problem_rhoda
";

As you’ll see when you test it, $result now will contain the following:

Array ( [0] => Array ( [numrows] => 20 ) )

To get that value 20, you have to get the ‘numrows’ element from the first (0) element from $results:


$numrows = $result[0]['numrows'];
echo "numrows : $numrows";

rhodarose · May 11, 2011, 9:19am

guido2004:

I know you have to change it because you have to use opendb.
What I mean is, $result[0] will give you the value of the first element of the $result array, which is
Array ( [COUNT(*)] => 20 )
another array!

What you want is the value 20.

The first thing you should do is change your query a bit, giving an alias name to the COUNT(*) column. This will make things easier and clearer down the road:
$sql_select = "
  SELECT COUNT(*) AS numrows
  FROM machine_problem_rhoda
";
As you’ll see when you test it, $result now will contain the following:
Array ( [0] => Array ( [numrows] => 20 ) ) 
To get that value 20, you have to get the ‘numrows’ element from the first (0) element from $results:
$numrows = $result[0]['numrows'];
echo "numrows : $numrows";

I revised my code as what you said


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?php
/*session_start(); 
if(empty($_SESSION['logged_in'])) {
    header('Location:index.php');
    die();
}*/

error_reporting(E_ERROR | E_WARNING | E_PARSE);
include('includes/config.sender.php');
include('includes/template.inc');


/*Sorting of Data*/
 $sort = "ASC";
  $data_sort = "Emp_ID";
  
  if(isset($_GET['sorting']))
	{
		if($_GET['sorting'] == 'ASC'){
			$sort = "DESC";
		}
		else{
			$sort = "ASC";
		}
	}
	
	
	if (isset($_GET['field_name'])) {
		if($_GET['field_name']  == 'Emp_ID'){
			$data_sort = "Emp_ID";
		}
		elseif($_GET['field_name'] == 'Last_Name'){
			$data_sort = "Last_Name";
		}
		elseif($_GET['field_name'] == 'First_Name'){
			$data_sort = "First_Name";
		}
		elseif($_GET['field_name'] == 'Birthday'){
			$data_sort = "Birthday";
		}
	}
	
	
/*Pagination, Sorting and Limit*/

if (isset($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} else {
   $pageno = 1;
} 
//$query = "SELECT count(*) FROM tbl_machine1";

/*$sql_select = "SELECT COUNT(
					Emp_ID,
					Last_Name,
					First_Name,
					Birthday)
			   FROM
					machine_problem_rhoda";*/
					
$sql_select = "SELECT COUNT(*) as numrows
				FROM 
					machine_problem_rhoda";
					
$result['count'] = $_DB->opendb($sql_select);

echo "<pre>";
print_r($result);
echo "</pre>";


$numrows = $result[0]['numrows'];
echo "numrows : $numrows";
//var_dump($sql_select);


//$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
//$query_data = mysql_fetch_row($result);
//$numrows = $query_data[0];
//$numrows = $result[0];

$rows_per_page = 5;
$lastpage      = ceil($numrows/$rows_per_page);

$pageno = (int)$pageno;
if ($pageno > $lastpage) {
   $pageno = $lastpage;
} 
if ($pageno < 1) {
   $pageno = 1;
} 

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;



$sql_select = "SELECT
					Emp_ID,
					Last_Name,
					First_Name,
					Birthday
				FROM
					machine_problem_rhoda
				ORDER BY $data_sort $sort $limit
				";
$rows = $_DB->opendb($sql_select);

//$query = "SELECT * FROM tbl_machine1 ORDER BY $data_sort $sort $limit";
//$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);


//while($info = mysql_fetch_array( $result ))

$tpl->set_block('handle', 'block_list', 'tag_list');
foreach($rows as $row) {
	$tpl->set_var(array('id'=> $row['Emp_ID'],
						'lastname' => $row['Last_Name'],
						'firstname' => $row['First_Name'],
						'birthday' => $row['Birthday'],
	));
	$tpl->parse('tag_list', 'block_list', true);
}
/*while($info = countdata( $result ))
{
	$emp_id = $info['Emp_ID'];
	$lname = $info['Last_Name'];
	$fname = $info['First_Name'];
	$bday = $info['Birthday'];
	$date = date('d-m-Y', strtotime($bday));
	?>
	<tr>
	<td><?php echo $emp_id;?> </td>
    <td><?php echo htmlentities($lname, ENT_QUOTES); ?> </td>
    <td><?php echo htmlentities($fname, ENT_QUOTES);?> </td>
    <td><?php echo htmlentities($date, ENT_QUOTES);?> </td>
   	<td><a href = 'edit.php?id=<?php echo $emp_id; ?>'>Edit</a> <a href='delete.php?id=<?php echo $emp_id; ?>' onClick="return confirm('Are you sure you want to delete?')">Delete</a></td>
	</tr>
    <?php
}*/
?>
	<!--</table>-->
     <A HREF="javascript:void(0)" onClick="window.open('add.php','welcome','width=300,height=200')">
<input type="button" name="add" value="ADD"> </A>&nbsp;&nbsp;

<?php

 if(isset($_GET['sorting']))
	{
		if($_GET['sorting'] == 'ASC'){
			$sort = "ASC";
		}
		else{
			$sort = "DESC";
		}
	}
				
if ($pageno == 1) {
   echo " FIRST PREV ";
} else {
 ?>
 <a href="machine1.php?pageno=1&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">FIRST</a>
<?php
   $prevpage = $pageno-1;
?>
   <a href="machine1.php?pageno=<?php echo $prevpage;?>&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">PREV</a>
 
<?php
}
echo " ( Page $pageno of $lastpage ) ";

if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;

 ?>
 
	<a href="machine1.php?pageno=<?php echo $nextpage; ?>&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">NEXT</a>
   	<a href="machine1.php?pageno=<?php echo $lastpage; ?>&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">LAST</a> 
 <?php
}

?>





<?php


/*$sql_select = "SELECT
					Emp_ID,
					Last_Name,
					First_Name,
					Birthday
			   FROM
					machine_problem_rhoda";
					
$rows = $_DB->opendb($sql_select);*/


$tpl = new Template('.', 'keep');
$tpl->set_file(array('handle' => 'html/machine1.html'));

/*$tpl->set_block('handle', 'block_list', 'tag_list');
foreach($rows as $row) {
	$tpl->set_var(array('id'=> $row['Emp_ID'],
						'lastname' => $row['Last_Name'],
						'firstname' => $row['First_Name'],
						'birthday' => $row['Birthday'],
	));
	$tpl->parse('tag_list', 'block_list', true);
}*/


$tpl->parse('handle', array('handle'));
$tpl->p('handle');

?>
</body>
</html>

and the output is:

Array
(
[count] => Array
(
[0] => Array
(
[numrows] => 20
)

)

numrows :
Fatal error: Call to a member function set_block() on a non-object in /opt/zeva/releases/ZEVA.sandbox/machine_problem/rhoda/machine1.php on line 122:(

DarthGuido · May 11, 2011, 9:29am

$result[‘count’] = $_DB->opendb($sql_select);

should be

$result = $_DB->opendb($sql_select);

rhodarose · May 11, 2011, 9:35am

I revised my code as you suggested:


$sql_select = "SELECT COUNT(*) as numrows

                FROM 

                    machine_problem_rhoda";

                    

$result = $_DB->opendb($sql_select);



echo "<pre>";

print_r($result);

echo "</pre>";





$numrows = $result[0]['numrows'];

echo "numrows : $numrows";

and the result is:

Array
(
[0] => Array
(
[numrows] => 20
)

)

numrows : 20
Fatal error: Call to a member function set_block() on a non-object in /opt/zeva/releases/ZEVA.sandbox/machine_problem/rhoda/machine1.php on line 148

DarthGuido · May 11, 2011, 9:49am

what is line 148?

rhodarose · May 11, 2011, 10:06am


$sql_select = "SELECT
					Emp_ID,
					Last_Name,
					First_Name,
					Birthday
				FROM
					machine_problem_rhoda
				ORDER BY $data_sort $sort $limit
				";
$rows = $_DB->opendb($sql_select);
//this is line 148
$tpl->set_block('handle', 'block_list', 'tag_list');
foreach($rows as $row) {
	$tpl->set_var(array('id'=> $row['Emp_ID'],
						'lastname' => $row['Last_Name'],
						'firstname' => $row['First_Name'],
						'birthday' => $row['Birthday'],
	));
	$tpl->parse('tag_list', 'block_list', true);
}

DarthGuido · May 11, 2011, 10:25am

Ok, did you define $tpl anywhere?

rhodarose · May 11, 2011, 10:37am

now my data in my database was view, now my problem is my sorting , it is only sort ASC and when I tried to click again the hyperlink it did not sort to DESC.

here is my code machine1.php


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?php
/*session_start(); 
if(empty($_SESSION['logged_in'])) {
    header('Location:index.php');
    die();
}*/

error_reporting(E_ERROR | E_WARNING | E_PARSE);
include('includes/config.sender.php');
include('includes/template.inc');


/*Sorting of Data*/
 $sort = "ASC";
  $data_sort = "Emp_ID";
  
  if(isset($_GET['sorting']))
	{
		if($_GET['sorting'] == 'ASC'){
			$sort = "DESC";
		}
		else{
			$sort = "ASC";
		}
	}
	
	
	if (isset($_GET['field_name'])) {
		if($_GET['field_name']  == 'Emp_ID'){
			$data_sort = "Emp_ID";
		}
		elseif($_GET['field_name'] == 'Last_Name'){
			$data_sort = "Last_Name";
		}
		elseif($_GET['field_name'] == 'First_Name'){
			$data_sort = "First_Name";
		}
		elseif($_GET['field_name'] == 'Birthday'){
			$data_sort = "Birthday";
		}
	}
	
	
/*Pagination, Sorting and Limit*/

if (isset($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} else {
   $pageno = 1;
} 
//$query = "SELECT count(*) FROM tbl_machine1";

/*$sql_select = "SELECT COUNT(
					Emp_ID,
					Last_Name,
					First_Name,
					Birthday)
			   FROM
					machine_problem_rhoda";*/
					
/*$sql_select = "SELECT COUNT(*) as numrows
				FROM 
					machine_problem_rhoda";
					
$result = $_DB->opendb($sql_select);

echo "<pre>";
print_r($result);
echo "</pre>";


$numrows = $result['count'];*/
//var_dump($sql_select);



$sql_select = "SELECT COUNT(*) as numrows 
                FROM  
                    machine_problem_rhoda"; 
$result = $_DB->opendb($sql_select); 

if(isset($result[0]['numrows'])) //check if result returned a numrows
{
   $numrows = (int)$result[0]['numrows'];     //typecast, to clearify the value range.
}
else
{
   $numrows = 0;//or false - anything that suits your applogic by (what if no users)
}



//$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);
//$query_data = mysql_fetch_row($result);
//$numrows = $query_data[0];
//$numrows = $result[0];

/*$sql_select = "SELECT COUNT(*) as numrows

                FROM 

                    machine_problem_rhoda";

                    

$result = $_DB->opendb($sql_select);



echo "<pre>";

print_r($result);

echo "</pre>";





$numrows = $result[0]['numrows'];

echo "numrows : $numrows";*/





$rows_per_page = 5;
$lastpage      = ceil($numrows/$rows_per_page);

$pageno = (int)$pageno;
if ($pageno > $lastpage) {
   $pageno = $lastpage;
} 
if ($pageno < 1) {
   $pageno = 1;
} 

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;



$sql_select = "SELECT
					Emp_ID,
					Last_Name,
					First_Name,
					Birthday
				FROM
					machine_problem_rhoda
				ORDER BY $data_sort $sort $limit
				";
$rows = $_DB->opendb($sql_select);

//$query = "SELECT * FROM tbl_machine1 ORDER BY $data_sort $sort $limit";
//$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR);


//while($info = mysql_fetch_array( $result ))


$tpl = new Template('.', 'keep');
$tpl->set_file(array('handle' => 'html/machine1.html'));


$tpl->set_block('handle', 'block_list', 'tag_list');
foreach($rows as $row) {
	$tpl->set_var(array('id'=> $row['Emp_ID'],
						'lastname' => $row['Last_Name'],
						'firstname' => $row['First_Name'],
						'birthday' => $row['Birthday'],
	));
	$tpl->parse('tag_list', 'block_list', true);
}

$tpl->parse('handle', array('handle'));
$tpl->p('handle');
/*while($info = countdata( $result ))
{
	$emp_id = $info['Emp_ID'];
	$lname = $info['Last_Name'];
	$fname = $info['First_Name'];
	$bday = $info['Birthday'];
	$date = date('d-m-Y', strtotime($bday));
	?>
	<tr>
	<td><?php echo $emp_id;?> </td>
    <td><?php echo htmlentities($lname, ENT_QUOTES); ?> </td>
    <td><?php echo htmlentities($fname, ENT_QUOTES);?> </td>
    <td><?php echo htmlentities($date, ENT_QUOTES);?> </td>
   	<td><a href = 'edit.php?id=<?php echo $emp_id; ?>'>Edit</a> <a href='delete.php?id=<?php echo $emp_id; ?>' onClick="return confirm('Are you sure you want to delete?')">Delete</a></td>
	</tr>
    <?php
}*/
?>
	<!--</table>-->
     <A HREF="javascript:void(0)" onClick="window.open('add.php','welcome','width=300,height=200')">
<input type="button" name="add" value="ADD"> </A>&nbsp;&nbsp;

<?php

 if(isset($_GET['sorting']))
	{
		if($_GET['sorting'] == 'ASC'){
			$sort = "ASC";
		}
		else{
			$sort = "DESC";
		}
	}
				
if ($pageno == 1) {
   echo " FIRST PREV ";
} else {
 ?>
 <a href="machine1.php?pageno=1&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">FIRST</a>
<?php
   $prevpage = $pageno-1;
?>
   <a href="machine1.php?pageno=<?php echo $prevpage;?>&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">PREV</a>
 
<?php
}
echo " ( Page $pageno of $lastpage ) ";

if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;

 ?>
 
	<a href="machine1.php?pageno=<?php echo $nextpage; ?>&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">NEXT</a>
   	<a href="machine1.php?pageno=<?php echo $lastpage; ?>&field_name=<?php echo $data_sort; ?>&sorting=<?php echo $sort; ?>">LAST</a> 
 <?php
}

?>





<?php


/*$sql_select = "SELECT
					Emp_ID,
					Last_Name,
					First_Name,
					Birthday
			   FROM
					machine_problem_rhoda";
					
$rows = $_DB->opendb($sql_select);*/


/*$tpl = new Template('.', 'keep');
$tpl->set_file(array('handle' => 'html/machine1.html'));

$tpl->set_block('handle', 'block_list', 'tag_list');
foreach($rows as $row) {
	$tpl->set_var(array('id'=> $row['Emp_ID'],
						'lastname' => $row['Last_Name'],
						'firstname' => $row['First_Name'],
						'birthday' => $row['Birthday'],
	));
	$tpl->parse('tag_list', 'block_list', true);
}


$tpl->parse('handle', array('handle'));
$tpl->p('handle');*/

?>
</body>
</html>

and here is my machine1.html


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Machine 1</title>
</head>

<body>
Welcome {username}!
<table border="1" cellpadding="1" cellspacing="1">
    <tr>
    	<td><a href="machine1.php?sorting=<?php echo $sort; ?>&field_name=Emp_ID">Employee Id</a></td>
    	<td><a href="machine1.php?sorting=<?php echo $sort; ?>&field_name=Last_Name">Last Name</a></td>
        <td><a href="machine1.php?sorting=<?php echo $sort; ?>&field_name=First_Name">First Name</a></td>
        <td><a href="machine1.php?sorting=<?php echo $sort; ?>&field_name=Birthday">Birthday</a></td>
        <td>Option</td>
    </tr>
	<!-- BEGIN block_list -->
	<tr>
		<td>{id}</td>
		<td>{lastname}</td>
		<td>{firstname}</td>
		<td>{birthday}</td>
        <td><a href = 'edit.php?id=<?php echo $emp_id; ?>'>Edit</a> <a href='delete.php?id=<?php echo $emp_id; ?>' onClick="return confirm('Are you sure you want to delete?')">Delete</a></td>
	</tr>
	<!-- END block_list -->
</table>
{pagination}
</body>
</html>

DarthGuido · May 11, 2011, 10:45am

Do some debugging: add some echo’s in your code to see what happens when you want to sort. What part of the code is executed, what values do the variables have?

Try to understand why your code isn’t doing what you want it to do.