Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts

    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

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,865
    Thanks
    160
    Thanked 2,224 Times in 2,211 Posts
    Code:
    COUNT(
                        Emp_ID,
                        Last_Name,
                        First_Name,
                        Birthday)
    What's your intention behind that code? You can't have multiple columns inside aggregate function, as you've. See http://dev.mysql.com/doc/refman/5.0/...functions.html
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by abduraooft View Post
    What's your intention behind that code? You can't have multiple columns inside aggregate function, as you've. See http://dev.mysql.com/doc/refman/5.0/...functions.html
    before that is code is like this:
    PHP Code:
    $query "SELECT count(*) FROM machine_problem_rhoda"
    and now I change it as what my boss said
    PHP Code:
    $sql_select "SELECT COUNT(
                        Emp_ID,
                        Last_Name,
                        First_Name,
                        Birthday)
                   FROM
                        machine_problem_rhoda"
    ;
                        
    $result $_DB->opendb($sql_select); 
    he told me that its better to put all the field name rather than * because * is slow.

  • #4
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    When i tried to used this code:
    PHP 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
    PHP Code:
    $lastpage      ceil($numrows/$rows_per_page); 
    :(

  • #5
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,865
    Thanks
    160
    Thanked 2,224 Times in 2,211 Posts
    he told me that its better to put all the field name rather than * because * is slow.
    It seems like you've misinterpreted what your boss said!

    He might have told about the usage of SELECT * and not with any aggregate function.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #6
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by abduraooft View Post
    It seems like you've misinterpreted what your boss said!

    He might have told about the usage of SELECT * and not with any aggregate function.

    Uhmmmm...Now i used * on my count and now i don't know how can I resolved the fatal error:(

  • #7
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    I revised my code:

    PHP 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:
    PHP 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
    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>
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •