/    Sign up×
Community /Pin to ProfileBookmark

Problem in SQL Statement

Copy linkTweet thisAlerts:
May 11.2011

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:

[code=php]
<?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>
[/code]

and when I edit it

[code=php]
<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>
[/code]

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

to post a comment
PHP

2 Comments(s) ↴

Copy linkTweet thisAlerts:
@OctoberWindMay 11.2011 — [code=php]
SELECT COUNT(Emp_ID, Last_Name, First_Name, Birthday) FROM machine_problem_rhoda
[/code]

This is an invalid (or poorly constructed) query. Remove the COUNT() and see if that returns the result set you're looking for.

COUNT is usually used in combination with a 'GROUP BY' statement. If you just need the total number of rows returned, you can use the mysql_num_rows() function.

[code=php]
SELECT Emp_ID, Last_Name, First_Name, Birthday FROM machine_problem_rhoda;
[/code]
Copy linkTweet thisAlerts:
@rhodaroseauthorMay 11.2011 — I resolved that issue and now I have problem in sorting of my data.

here is my machine1.php
[code=php]
<?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 ******** the value range.
}
else
{
$numrows = 0;//or false -
}



//$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', 'one_list', 'first_list');
$tpl->set_var(array('sort' => $sort));
$tpl->parse('first_list', 'one_list', true);*/




$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'],
'sort' => $sort
));
$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>-->


<?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');*/

?>
[/code]

and this is my machine1.html
[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>Machine 1</title>
</head>

<body>

<table border="1" cellpadding="1" cellspacing="1">

<tr>
<td><a href="machine1.php?sort={sort}&field_name=Emp_ID">Employee Id</a></td>
<td><a href="machine1.php?sort={sort}&field_name=Last_Name">Last Name</a></td>
<td><a href="machine1.php?sort={sort}&field_name=First_Name">First Name</a></td>
<td><a href="machine1.php?sort={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={Emp_ID} ?>'>Edit</a> <a href='delete.php?id={Emp_ID}' onClick="return confirm('Are you sure you want to delete?')">Delete</a></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> -->
</tr>
<!-- END block_list -->
</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;
</body>
</html>
[/CODE]

It only sort ASC and when I click the hyperlink it did not sort in DESC
×

Success!

Help @rhodarose spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 6.1,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @meenaratha,
tipped: article
amount: 1000 SATS,

tipper: @meenaratha,
tipped: article
amount: 1000 SATS,

tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,
)...