1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Problem in SQL Statement

Discussion in 'PHP' started by newphpcoder, May 11, 2011.

  1. newphpcoder

    newphpcoder New Member

    Joined:
    Sep 24, 2010
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Web Programmer
    Location:
    Philippines
    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:
    <?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:
    <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. pein87

    pein87 New Member

    Joined:
    Aug 6, 2010
    Messages:
    173
    Likes Received:
    47
    Trophy Points:
    0
    Occupation:
    Web Dev
    Location:
    Limbo
    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:
    $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:
    $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.
     
  3. underground_devil

    underground_devil New Member

    Joined:
    Jan 24, 2011
    Messages:
    36
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    PHP developer ,trainer
    Location:
    Punjab

Share This Page