SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Location
    Port Harcourt
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Complex sql, How do I do it?

    I am developing an application which records students' school fees in a table named 'dailyincome'. I have two categories of student, those in the junior secondary school 'jss' and senior secondary school 'sss'. I have two other tables 'jss' and 'sss' housing the information about the two categories of students. I want a situation that whenever incomeList.php is loaded in the browser, it access the database and list out the payee (student's) full name, amount paid, date of payment. The link between 'dailyincome' table and 'jss & sss' is that they both has a column 'payersID' which should reference and uniquely identify the full details of the payer and display it on the page. My whole page is now below as I'm trying to get information from dailyincome and jss tables presently though I want to get information from both jss and sss tables and identify them within the dailyincome table.


    PHP Code:

    <?php 



    //connect to the database
        
    include_once ('DatabaseManager.php'); 
        
    $conn DatabaseManager::getConnection();
        
    //get the function
        
    include_once ('function.php');

            
    $page = (int) (!isset($_GET["page"]) ? $_GET["page"]);
            
    $limit 20;
            
    $startpoint = ($page $limit) - $limit;
            
            
    //to make pagination
            
    $statement "SELECT dailyincome.id, paymentType, amountPaidInFigure, paymentDate, jss.firstName AS firstName, jss.lastName AS lastName, jss.id FROM dailyincome, jss WHERE jss.payersID = dailyincome.payersID";


    require_once 
    '../classes/Config.php';
     
    $result = new ECConfig();
    $result2 = new ECConfig();
    $result3 = new ECConfig();
    $result->getSiteName();
    $result2->getSiteNickName();
    $result3->getCurrency();
    ?>

    <!DOCTYPE html>
    <!--[if IE 8]>                  <html class="no-js lt-ie9" lang="en" > <![endif]-->
    <!--[if gt IE 8]><!--> <html class="no-js" lang="en" > <!--<![endif]-->

    <head>
        <meta charset="utf-8" />
      <meta name="viewport" content="width=device-width" />
      <title><?php echo $result->getSiteName(); ?>  | Finance</title>
      <link href="../css/normalize.css" rel="stylesheet" />
      <link href="../css/foundation.css" rel="stylesheet" />
      <link href="../css/pagination.css" rel="stylesheet" type="text/css" />
      <link href="../css/B_blue.css" rel="stylesheet" type="text/css" />
      <link href="../main.css" rel="stylesheet" type="text/css" media="all">
      <script src="../js/vendor/custom.modernizr.js"></script>


    </head>
    <body>
    <?php $query $conn->query("{$statement} ORDER BY jss.id DESC LIMIT {$startpoint} , {$limit}"); ?>
    <?php $dailyIncomeRegister 
    $conn->query("{$statement}"); 
           
    $dailyIncome $dailyIncomeRegister->rowCount(); ?>
         <div class="contain-to-grid">                    
                        <div class="top-bar text-center">
                        
                            <h3 class="me1"><?php echo $result->getSiteName(); ?></h3>
                            <h5 class="subheader"><?php echo $result->getSiteNickName(); ?></h5>
                        
        </div>
          </div>
          
     <!-- The part above takes care of the banner region -->
          
          <div class="row">
          <div class="breadcrumbs">
          <div class="large-7 columns">
            <a href="../homepage.php">Main Home</a> || <a href="index.php">Finance</a> 
            </div>
            <div class="large-5 columns">
            You are logged in as <?php if(isset($_SESSION['userName'])) echo $_SESSION['userName']; ?> || Log Out <a href="index.php?action=logout"><img src="../img/quit.png" alt="Log Out" /></a> 
            </div>
          </div> 
          </div>  
          
          <br/>   
          <div class="row">  
            <div class="large-9 columns">
            <?php include 'incomeConsole.php'?>
            </div>
            
            <div class="large-3 columns">
            <?php include 'mainmenu.php'?>
            </div>
            
            </div><!--closing tag for the first row -->
            
            <div class="row">
            <div class="large-12 columns">
              <?php if(isset($query)): ?>
            <p class="searchResult">      
            <?php
              
    if(isset($dailyIncome)) {
              if(
    $dailyIncome == 0)
              {
                   echo 
    'There is no record of any daily income in the database.';
              }
              if(
    $dailyIncome == 1)
              {
                  echo 
    'There is 1 record of daily income in the database.';
              }
              if(
    $dailyIncome 1)
              {
                  echo 
    "There are $dailyIncome records of daily income in the database.";
              }
              }
              
    ?>
              </p>
             <div class="large-3 columns panel"><h5>Payee's Name</h5></div>
             <div class="large-3 columns panel"><h5>Amount Paid</h5></div>
             <div class="large-2 columns panel"><h5>Payment Date</h5></div>
             <div class="large-4 columns panel"><h5>Action</h5></div>
                    
             <?php foreach($query as $i=>$dailyIncome): ?>
                    
                    <div class="large-3 columns"><?php echo ($dailyIncome['lastName']); ?> <?php echo ($dailyIncome['firstName']); ?> <?php echo ($dailyIncome['id']); ?></div>
                    <div class="large-3 columns"><?php echo $result->getCurrency(); ?><?php echo htmlentities(number_format($dailyIncome['amountPaidInFigure']), 2); ?></div>
                    <div class="large-2 columns"><?php echo (date($dailyIncome['paymentDate'])); ?></div>
                    <div class="large-4 columns">
                     <form action="index.php" method="post">
                    <div>
                      <input type="hidden" name="id" value="<?php  echo htmlentities($dailyIncome['id']); ?>"/>
                      <input type="submit" name="action" value="Edit Income" class="button myconsole2 radius" />
                      <input type="submit" name="action" value="Delete Income" onclick="return confirm('Delete This Income?')" class="button myconsole2 radius" />
                    </div>
                  
                  </form>
                    </div>
                 <hr/>
                 <?php endforeach; ?>
              

            <?php endif; ?>
            <div class="large-12 columns paginate"><?php echo pagination($statement$limit$page); ?></div>
            
            </div>
            
         </div>
         
       
            
    <br/>
    <br/>
    <br/>
    <!-- Footer -->
    <div class="wrapper row2">
      <div class="row">&nbsp;
      <br/>
       </div>
       </div> 
    <div class="wrapper row4">
      <div class="row">
      <div class="large-12 columns text-center">
        <p> &copy; <?php echo date('Y'); ?>School Manager  Licensed to <?php echo $result->getSiteName(); ?>, All Rights Reserved.</p>
       
      </div>
      
      </div>
    </div>

    </body>
    </html>
    I keep getting this error message:

    Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 'SELECT dailyincome.id, paymentType, amountPaidInFigure, paymentDate, jss.fir' at line 1' in C:\xampp\htdocs\finance\function.php:9 Stack trace: #0 C:\xampp\htdocs\finance\function.php(9): PDO->query('SELECT COUNT(*)...') #1 C:\xampp\htdocs\finance\incomeList.php(137): pagination('SELECT dailyinc...', 20, 1) #2 {main} thrown in C:\xampp\htdocs\finance\function.php on line 9.


    Now, function.php is to help in paginating my result and its working fine for all other pages where results need to be paginated. Function.php is shown below:

    PHP Code:
    <?php

       
       
    function pagination($query$per_page 10,$page 1$url '?'){ 
       include_once (
    'DatabaseManager.php'); 
       
    $conn DatabaseManager::getConnection();
               
            
    $query "SELECT COUNT(*) as `num` FROM {$query}";
            foreach(
    $conn->query($query) as $row);
            
    $total $row['num'];
            
    $adjacents "2"

            
    $page = ($page == $page);  
            
    $start = ($page 1) * $per_page;                                
            
            
    $prev $page 1;                            
            
    $next $page 1;
            
    $lastpage ceil($total/$per_page);
            
    $lpm1 $lastpage 1;
            
            
    $pagination "";
            if(
    $lastpage 1)
            {    
                
    $pagination .= "<ul class='pagination'>";
                        
    $pagination .= "<li class='details'>Page $page of $lastpage</li>";
                if (
    $lastpage + ($adjacents 2))
                {    
                    for (
    $counter 1$counter <= $lastpage$counter++)
                    {
                        if (
    $counter == $page)
                            
    $pagination.= "<li><a class='current'>$counter</a></li>";
                        else
                            
    $pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";                    
                    }
                }
                elseif(
    $lastpage + ($adjacents 2))
                {
                    if(
    $page + ($adjacents 2))        
                    {
                        for (
    $counter 1$counter + ($adjacents 2); $counter++)
                        {
                            if (
    $counter == $page)
                                
    $pagination.= "<li><a class='current'>$counter</a></li>";
                            else
                                
    $pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";                    
                        }
                        
    $pagination.= "<li class='dot'>...</li>";
                        
    $pagination.= "<li><a href='{$url}page=$lpm1'>$lpm1</a></li>";
                        
    $pagination.= "<li><a href='{$url}page=$lastpage'>$lastpage</a></li>";        
                    }
                    elseif(
    $lastpage - ($adjacents 2) > $page && $page > ($adjacents 2))
                    {
                        
    $pagination.= "<li><a href='{$url}page=1'>1</a></li>";
                        
    $pagination.= "<li><a href='{$url}page=2'>2</a></li>";
                        
    $pagination.= "<li class='dot'>...</li>";
                        for (
    $counter $page $adjacents$counter <= $page $adjacents$counter++)
                        {
                            if (
    $counter == $page)
                                
    $pagination.= "<li><a class='current'>$counter</a></li>";
                            else
                                
    $pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";                    
                        }
                        
    $pagination.= "<li class='dot'>..</li>";
                        
    $pagination.= "<li><a href='{$url}page=$lpm1'>$lpm1</a></li>";
                        
    $pagination.= "<li><a href='{$url}page=$lastpage'>$lastpage</a></li>";        
                    }
                    else
                    {
                        
    $pagination.= "<li><a href='{$url}page=1'>1</a></li>";
                        
    $pagination.= "<li><a href='{$url}page=2'>2</a></li>";
                        
    $pagination.= "<li class='dot'>..</li>";
                        for (
    $counter $lastpage - (+ ($adjacents 2)); $counter <= $lastpage$counter++)
                        {
                            if (
    $counter == $page)
                                
    $pagination.= "<li><a class='current'>$counter</a></li>";
                            else
                                
    $pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";                    
                        }
                    }
                }
                
                if (
    $page $counter 1){ 
                    
    $pagination.= "<li><a href='{$url}page=$next'>Next</a></li>";
                    
    $pagination.= "<li><a href='{$url}page=$lastpage'>Last</a></li>";
                }else{
                    
    $pagination.= "<li><a class='current'>Next</a></li>";
                    
    $pagination.= "<li><a class='current'>Last</a></li>";
                }
                
    $pagination.= "</ul>\n";        
            }
        
        
            return 
    $pagination;
        } 
    ?>
    I want this page to work and more importantly, I want to get list of students who have paid from both jss and sss tables and cross reference it with dailyincome table and have the result displayed and paginated if necessary, how do I get it to work? I understand the issue is with my sql syntax. please I need help. Thanks.

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,812
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    Place a var_dump($query); after your $query = "SELECT COUNT(*) as `num` FROM {$query}"; line in pagination.

    Take that output and paste it in phpMyAdmin and see if it works. Chances are it doesn't and you need to modify how that pagination query gets built.
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  3. #3
    SitePoint Evangelist bronze trophy
    Join Date
    Feb 2013
    Posts
    504
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)
    Not real sure about your table structure but you would list the TABLE and FIELD for each requested field and do some sort of JOIN to get multiple tables. I'm thinking the jss in this case would be the primary table to call for student names then LEFT JOIN dailyincome to get any matching income data. If dailyincome should be primary then you'd want to switch those around. You use ON to link the tables by id fields. Something like this.
    PHP Code:
     $statement "SELECT 
     dailyincome.id, 
     dailyincome.paymentType, 
     dailyincome.amountPaidInFigure, 
     dailyincome.paymentDate, 
     jss.firstName AS firstName, 
     jss.lastName AS lastName, 
     jss.id 
     FROM jss
     LEFT JOIN dailyincome 
     ON dailyincome.payersID = jss.payersID"

    I would test the query as a stand-along version FIRST to check than result is as expected before dealing with pagination.

  4. #4
    SitePoint Evangelist bronze trophy
    Join Date
    Feb 2013
    Posts
    504
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)
    Not sure how far you're into things but you MIGHT consider having ONE table for students and a field to identify class, e.g. jss,sss.

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Location
    Port Harcourt
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Drummin, I will test out your solution and I think you're right about the single table thing as it will reduce my headache. I will let you know when I'm through.

  6. #6
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Location
    Port Harcourt
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cpradio, I tried out the line of code and it didn't make any difference. I guess I didn't use it right. Drummin, the solution you gave me worked but I'm having problem with function.php which is supposed to create the pagination. Thanks guys for your help so far, how do I get to the bottom of this?

  7. #7
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Location
    Port Harcourt
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Cpradio, I will test it out and get back to you.

  8. #8
    SitePoint Evangelist bronze trophy
    Join Date
    Feb 2013
    Posts
    504
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)
    There are so many such as Cpradio and Fretburner that know so much more than me, but I'm glad I could help. Thanks!

  9. #9
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Location
    Port Harcourt
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cpradio is surely a Big Boss. He's helped me out on many occasions. Fretburner is another great guy definitely! Seeing his number of posts within 4 months! Cpradio, Fretburner, Drummin, one more thing please. I want a situation whereby if there is nothing in the dailyincome table, the conditions I set in my main page:
    Code:
    <?php
              if(isset($dailyIncome)) {
              if($dailyIncome == 0)
              {
                   echo 'There is no record of any daily income in the database.';
              }
              if($dailyIncome == 1)
              {
                  echo 'There is 1 record of daily income in the database.';
              }
              if($dailyIncome > 1)
              {
                  echo "There are $dailyIncome records of daily income in the database.";
              }
              }
              ?>
    displays the right information i.e, There is no record of any daily income in the database. You know presently the mysql code accesses both jss and dailyincome tables and even if there is nothing in dailyincome table and there is something in jss, it'll display one of the messages above. I've tried using
    Code:
    style="display: <?php if(isset($dailyIncome['amountPaidInFigure'])) echo 'none'; ?>"
    in the div containg the table that shows my result but I'm not getting the desired result. Presently, I have two rows in jss table and the conditional statement I stated up shows that I have two records in the database which display 0 where ($dailyIncome['amountPaidInFigure']) should have displayed the right amount whenever I call the page. What do I do please?

  10. #10
    SitePoint Mentor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,257
    Mentioned
    32 Post(s)
    Tagged
    5 Thread(s)
    I think what you want to do is follow Drummin's earlier suggestion of reversing the join condition to make dailyincome the primary table. That way, if there are no daily income records, your query will return an empty resultset.

  11. #11
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Location
    Port Harcourt
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot Fretburner, Drummings suggestion worked well for my purpose.


Bookmarks

Posting Permissions

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