#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2018
    Posts
    8
    Rep Power
    0

    SQL statement where offset value increases in increments - help!


    Hi,

    Thanks for taking a look at this post.

    I'm trying to combine a tutorial I've successfully followed to allow my search results to display in batches of 9 rows
    - with the next 9 loading from my sql database when the end of my page is reached.

    The tutorial which I followed uses some ajax code to create variables for the offset and limit values as follows:

    Code:
    <script type="text/javascript">
         <!--make the ajax call when page loads-->
    	$(document).ready(function()
    	{
    		 var flag = 0;
    		 
    		 <!--pass the two parameters, offset and limit-->	
    		 $.ajax({
    			 
    				type: "GET",
    				url: "get_data.php",
    				data: {
    					'offset':0,
    					'limit':3
    					  },
    				success:function(data){
    					$('body').append(data);
    					flag += 3;
    				}
    			 
    				});
    				//Every time when we scroll we check the current value of scrollbar 
    				//and if it has reached the bottom of the page
    				$(window).scroll(function(){
    					if($(window).scrollTop()>= $(document).height() - $(window).height()){
    				//this is what happens at the bottom - same ajax function but we now want to offset by+=3 everytime
    				//so above we create a variable and increase by three whenver the ajax call is successful		
    						
    						 $.ajax({
    			 
    						type: "GET",
    						url: "get_data.php",
    						data: {
    							'offset':flag,
    							'limit':3
    							  },
    						success:function(data){
    							$('body').append(data);
    							flag += 3;
    						}
    					 
    						});
    						
    						
    					}
    				});
    	});
    
    	</script>
    The code redirects to get_data.php where it gets the value of limit and offset and then applies it to string variables $limit and $offset
    which are then used in a mysql statement:

    PHP Code:
    <?php

        
    if(isset($_GET['offset']) && isset($_GET['limit']))
        {
            
            
    $limit $_GET['limit'];
            
    $offset $_GET['offset'];
            
    //creating a connection
            
    $connection mysqli_connect('localhost''root''password''blog');
            
            
    //creating an sql
            
    $data_sql "SELECT * FROM posts LIMIT {$limit} OFFSET {$offset}";
            
            
    //running sql to get dataset
            
    $data mysqli_query($connection$data_sql);
            
            
                    
    //Testing for errors
                    
    if($data === false)
                    {
                      echo 
    "<b>Query failed!!</b><br />\n<b>Query:</b> {$data_sql}<br />\n<b>Error:</b> " mysqli_error($connection);
                    exit();
                    }
                    
    //
            
        
            
    while($row mysqli_fetch_array($data))
            {
                echo 
    '<div class="blog-post"><h1>'.$row['id'].'</h1><p>'.$row['post'].'</p></div>';
            }
        
        
        }
            
    ?>
    I have tried to edit this so that all the code appears on only one page, where the information pulled from the database is displayed.
    Also, the data displayed is also that specified by the parameters passed in the search box in the previous page.

    I removed the GET statements and declared the variable just before the SQL statement (see code below) but I am getting an error stating
    Query: SELECT * FROM teachers_table LIMIT 9 OFFSET offset
    Error: Undeclared variable: offset


    I'm not sure if this is because I declared the variable in ajax?

    My amended code effort is below. Many thanks for reading....

    PHP Code:
    <html>
        <head>
        <title>Load More</title>
        <script type="text/javascript" src="jquery.js"></script>
        <script type="text/javascript">
        
         <!--make the ajax call when page loads-->
        $(document).ready(function()
        {
             var flag = 0;
             
             <!--pass the two parameters, offset and limit-->    
             $.ajax({
                 
                    //type: "GET",
                    url: "mini_profiles.php",
                    data: {
                        'offset':0,
                        'limit':9
                          },
                    success:function(data){
                        $('body').append(data);
                        flag += 9;
                    }
                 
                    });
                    //Every time when we scroll we check the current value of scrollbar 
                    //and if it has reached the bottom of the page
                    $(window).scroll(function(){
                        if($(window).scrollTop()>= $(document).height() - $(window).height()){
                    //this is what happens at the bottom - same ajax function but we now want to offset by+=3 everytime
                    //so above we create a variable and increase by three whenver the ajax call is successful        
                            
                             $.ajax({
                 
                            //type: "GET",
                            url: "mini_profiles.php", //this is the ajax function calling the get_data.php
                            data: {
                                'offset':flag,
                                'limit':9
                                  },
                            success:function(data){
                                $('body').append(data);
                                flag += 9;
                            }
                         
                            });
                            
                            
                        }
                    });
        });

        </script>
        </head>
        <body>
        

        <div class="container">
        
                        
        
        
        
                     <?php
                      
                      $language   
    = isset($_POST['language'  ]) ? $_POST['language'  ] : null//These are the values sent through from the simple search box on the homepage
                      
    $prefecture = isset($_POST['prefecture']) ? $_POST['prefecture'] : null;
                      
    $vid        = isset($_POST['vid'       ]) ? $_POST['vid'       ] : null;
                      
    $photo      = isset($_POST['photo'     ]) ? $_POST['photo'     ] : null;
                      
                      
                      
    $whereClause = []; //we are creating an empty array and calling it 'where clause'
                      
                      //The 'mysqli_real_escape_string()' function escapes special characters in a string for use in an SQL statement.
                      //It provides a level of protection against SQL Injection
                      
                      
    if ($language) {  
                         
    $whereClause[] = 'language = "'mysqli_real_escape_string($db_connection$language) .'"';  //if a value for language has been passed in then add it to the array
                     
    }
                     
                     if (
    $prefecture) {
                         
    $whereClause[] = 'prefecture = "'mysqli_real_escape_string($db_connection$prefecture) .'"';
                     }
                     
                     if (
    $photo) {
                         
    $whereClause[] = 'photo != ""';
                     }
                      
                      if (
    $vid) {
                         
    $whereClause[] = 'vid != ""';
                     }
                     
                     if (empty(
    $whereClause)) {
                         
    $whereClause[] = '1';
                     }
                     
                     
    ?>
               
             
             
             
        
                    <?php

                    
    //if(isset($_GET['offset']) && isset($_GET['limit']))
                    //{
                        
                        
    $limit 'limit';
                        
    $offset 'offset';
                                
                        
    //creating an sql
                        //$data_sql = " SELECT * FROM teachers_table WHERE ". join(' AND ', $whereClause) ."  ";  //works on its own
                        
    $data_sql "SELECT * FROM teachers_table LIMIT 9 OFFSET {$offset}";  //isn't working
                        
                        //$data_sql = "SELECT * FROM teachers_table LIMIT {$limit} OFFSET {$offset} WHERE ". join(' AND ', $whereClause) ."  ";
                        //$data_sql = "SELECT * FROM teachers_table  WHERE ". join(' AND ', $whereClause) ." LIMIT {$limit} OFFSET {$offset}  ";
                        //running sql to get dataset
                        
    $data mysqli_query($db_connection$data_sql);
                        
                    
    ?>
                        
                    
                    <?php
                    
    //Testing for errors
                    
    if($data === false)
                    {
                      echo 
    "<b>Query failed!!</b><br />\n<b>Query:</b> {$data_sql}<br />\n<b>Error:</b> " mysqli_error($db_connection);
                    exit();
                    }
                    
                    
    //
                    
    ?> 
                        
                        
                        
                        
                        <div class="row">        
                    
                                <?php while($teacher mysqli_fetch_array($data))
                                {
    ?> 
                            

                                                       [I]Display info here[/I]

                            <?php ?> 
                                     
                        </div>                 
                            
                        
                        
                     <?php //} ?> 
        
        
        
        
        </div>    

        



        
        </div><!-- container ends-->                    
    </section>

    </body>
    </html>
    Any advice would be much appreciated.
    Many thanks!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    134
    Rep Power
    65
    You have replies on one of the other help forums stating what you need to do to get the code to work together, and it involves more work then just sticking the two pieces of code together in one file and posting it on different help forums.

IMN logo majestic logo threadwatch logo seochat tools logo