Go4Expert

Go4Expert (http://www.go4expert.com/)
-   PHP (http://www.go4expert.com/forums/php/)
-   -   Problem in SQL Statement (http://www.go4expert.com/forums/sql-statement-t25756/)

newphpcoder 11May2011 12:56

Problem in SQL Statement
 
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 Code:

<?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($lnameENT_QUOTES); ?> </td>
    <td><?php echo htmlentities($fnameENT_QUOTES);?> </td>
    <td><?php echo htmlentities($dateENT_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
PHP Code:

<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.:confused:

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

Thank you

pein87 12May2011 08:30

Re: Problem in SQL Statement
 
the COUNT() function only excepts on parameter or * and the key word DISTINCT with that one parameter. The parameter is the column name. Your trying to use this function on more then on column and it does not allow more then one column as its param. Since your using this on one table just use

PHP Code:

$totalResults "SELECT COUNT(*) AS ['total'] FROM machine_problem_rhoda"

You can add conditionals to to see if something is not right like register date etc...

for example if you have registration you could get the number of people who joined this year

PHP Code:

$totalResults "SELECT COUNT(*) FROM machine_problem_rhoda WHERE YEAR(registration) <> '2011'"

If your using what I wrote as the config feel free to change it,add to it etc... That was simply a demo so you could get the overall idea of what needs to be done. I still recommend using the propel CRUD for this or maybe just using codeignitor framework for RAD(rapid application development) purposes.

underground_devil 14May2011 10:25

Re: Problem in SQL Statement
 
if problem is not solved then you can send me mail at ugdevil@gmail.com


All times are GMT +5.5. The time now is 08:58.