SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help I have an database error!

    This is the error

    <b>Fatal error</b>: SQL in <b>C:\wamp\www\nyhungry\index3.php</b> on line <b>438</b><br />
    Coming from this line


    PHP Code:
    <?php  $sql 'SELECT * FROM '$arrRestaurants' LIMIT $offset, $rowsperpage';
    $result mysql_query($sql) or trigger_error("SQL"E_USER_ERROR); //line 438?>

  2. #2
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It would be much better to put some useful information into error message, instead of just a single word "SQL".
    Use
    PHP Code:
    $result mysql_query($sql) or trigger_error(mysql_error()." ".$sql); ?> 
    instead
    And you will have everything you need to solve this problem

  3. #3
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have change the function you have given to me and now the results are



    Notice: 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 '$offset, $rowsperpage' at line 1 in C:\wamp\www\nyhungry\index3.php on line 450

    Warning: Invalid argument supplied for foreach() in C:\wamp\www\nyhungry\index3.php on line 452
    [1] 2 3 > >>
    PHP Code:
    <?php
     $sql 
    'SELECT * FROM '$arrRestaurants' LIMIT $offset, $rowsperpage';
    $result mysql_query($sql) or trigger_error(mysql_error()); // line 450
    $i 1;
    foreach(
    $result as $arrRestaurant) { //  line 452
    ?>

  4. #4
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now we can see the problem is in the PHP string syntax.
    Do you know the difference between " ' quotes? If no, I strongly suggest to look at this manual entry: http://php.net/types.string
    (this is most important part for the beginners in my opinion)

  5. #5
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,526
    Mentioned
    83 Post(s)
    Tagged
    4 Thread(s)
    What he's trying to say is that single quotes do not expand the variables within them.

    PHP Code:
    ' LIMIT $offset, $rowsperpage'// LIMIT $offset, $rowsperpage
    " LIMIT $offset$rowsperpage"// LIMIT 100, 30
    ' LIMIT '.$offset.', '.$rowsperpage// LIMIT 100, 30 
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  6. #6
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,011
    Mentioned
    56 Post(s)
    Tagged
    0 Thread(s)
    Complicated variable expressions can be placed in braces rather than using the connotation operator if desired.

    Example
    PHP Code:
    echo "LIMIT {$sql->limits['start']} {$sql->limits['end']}"
    will echo the values of the limits property array of the object $sql.

  7. #7
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,526
    Mentioned
    83 Post(s)
    Tagged
    4 Thread(s)
    Except that I think the start and end values of LIMIT need to be separated by a comma.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,048
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    If $arrRestaurants is an array then you will need to implode it or convert it to a string in some other fashion before embedding it in a string.

  9. #9
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The solution was to use the $arrRestaurant variable inside the array_slice function together and get rid of the query of the pagination script and use the query conditions inside the $arrRestaurant variable to populate the data.
    So instead of calling a query to populate
    PHP Code:
    <?php
     $sql 
    'SELECT * FROM '$arrRestaurants' LIMIT $offset, $rowsperpage';
    $result mysql_query($sql) or trigger_error(mysql_error()); // line 450?>
    The array_slice function was the solution

    PHP Code:
    $arrDisplay array_slice($arrRestaurants,$offset,$rowsperpage); 
    Whole code with the pagination
    PHP Code:
    <?php else if (!empty($arrRestaurants)){ ?>
      <div id="holders"><img src="images/smallholdersleft.gif" alt="holders" /></div>
    <!--<dvi id="holders1"><img src="images/holders1.gif" alt="holders" /></div>-->
    <div id="header2">
      <fieldset class="header2field"><img src="images/Restaurantsresults.gif" alt="Restaurants Results"/></fieldset>
        </div>
      <div id="container4">
      <div class="wrap">
         <?php // print search query
    //if(!empty($strSQL)) { 
    //printf('<p>%s</p>',$strSQL);
     // find out how many rows are in the table 
    $sql "SELECT COUNT(*) FROM restaurants";
    $result mysql_query($sql) or trigger_error(mysql_error());
    $r mysql_fetch_row($result);
    $numrows $r[0];

    // number of rows to show per page
    $rowsperpage 6;
    // find out total pages
    $totalpages ceil($numrows $rowsperpage);

    // get the current page or set a default
    if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
       
    // cast var as int
       
    $currentpage = (int) $_GET['currentpage'];
    } else {
       
    // default page num
       
    $currentpage 1;
    // end if

    // if current page is greater than total pages...
    if ($currentpage $totalpages) {
       
    // set current page to last page
       
    $currentpage $totalpages;
    // end if
    // if current page is less than first page...
    if ($currentpage 1) {
       
    // set current page to first page
       
    $currentpage 1;
    // end if

    // the offset of the list, based on current page 
    $offset = ($currentpage 1) * $rowsperpage;

    // get the info from the db 
    $arrDisplay array_slice($arrRestaurants,$offset,$rowsperpage);


    $i 1;
    foreach(
    $arrDisplay as $arrRestaurant) {

      echo 
    "<div class=\"shoeinfo1\">
       <img src=\"images/spacer.gif\" alt=\"spacer\" class=\"spacer2\" />
          <h2 class=\"infohead\">"
    $arrRestaurant['restaurantname'] . "</h2>
          <div class=\"pic\"><img class=\"line\" src= "
    $arrRestaurant['image'] ." alt=\"picture\" width=\"100%\" height=\"100%\" /></div>

          <h5> Rating:</h5><h4>"
    ;
            
            
            
    $ratingData Rating::OutputRating($arrRestaurant['restaurantname']);
          
          if (
    Error::HasErrors())
          {
            echo 
    Error::ShowErrorMessages();
            
    Error::ClearErrors();
          }
          else
          {
            echo 
    $ratingData;
          }  
          echo
    "</h4> 
            <h3>Description:</h3>
            <div id=\"description\"><p>"
    .$arrRestaurant['description']." </p></div> 
            <div class=\"suabe2\">Address:<span class=\"suabe\">"
    .$arrRestaurant['address']."</span></div>
            <div id=\"state\">State:<span class=\"suabe\">"
    $arrRestaurant['state']. "</span></div>
               <h6>Zip:<span class=\"suabe\">"
    $arrRestaurant['zip'] . "</span></h6>
        <p><a href=\"#\">More</a></p></div>
        
    "
    ;
    $i++; 
    if (
    $i && $i == 
    {
      echo 
    "<div class=\"clearer\"></div>";

    }
    }
    /******  build the pagination links ******/
    // range of num links to show
    $range 2;

    // if not on page 1, don't show back links
    if ($currentpage 1) {
       
    // show << link to go back to page 1
       
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
       
    // get previous page num
       
    $prevpage $currentpage 1;
       
    // show < link to go back to 1 page
       
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
    // end if 

    // loop to show links to range of pages around current page
    for ($x = ($currentpage $range); $x < (($currentpage $range) + 1); $x++) {
       
    // if it's a valid page number...
       
    if (($x 0) && ($x <= $totalpages)) {
          
    // if we're on current page...
          
    if ($x == $currentpage) {
             
    // 'highlight' it but don't make a link
             
    echo " [<b>$x</b>] ";
          
    // if not current page...
          
    } else {
             
    // make it a link
             
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
          } 
    // end else
       
    // end if 
    // end for
                     
    // if not on last page, show forward and last page links        
    if ($currentpage != $totalpages) {
       
    // get next page
       
    $nextpage $currentpage 1;
        
    // echo forward link for next page 
       
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
       
    // echo forward link for lastpage
       
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
    // end if
    }

    ?>

    </div>
    </div>

  10. #10
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oddz,

    each of the page in the pagination is set up to display 5 set of row in other words 5 rows per page. When the user click in the second page to see the second 5 set of rows or iterations then the pagination script is set to {$_SERVER['PHP_SELF']} to the same page. Now since the html form action is set to
    PHP Code:
    action="<?php echo $_SERVER['PHP_SELF']; ?>
    it conflicts.

    When a user click in one of the numbers in the pagination script other than the default one then the script display the initial page which is the form and the recommeded restaurants but not the second set of rows.

    PHP Code:
    <?php // Build search query and embed filters
        
    $strSQL sprintf(
        
    'SELECT
        
              r.restaurants_id
              ,r.restaurantname
              ,r.image
              ,r.description
              ,r.address
              ,r.zip
              ,r.state
           FROM
              restaurants r
             %s
             %s
             %s'
         
    ,$boolIncludeZipCodes === true?'INNER JOIN restaurants_to_zip_codes rz ON r.restaurants_id = rz.restaurants_id ':''
         
    ,empty($arrSQLFilters)?'':' WHERE '.implode(' AND ',$arrSQLFilters)  
         ,
    $boolIncludeZipCodes === true?'GROUP BY r.restaurants_id':''
        
    );
     
        
    // Run search query    
        
    $arrResult mysql_query($strSQL) or die("Cannot execute:"mysql_error());
        
        while(
    $arrRow mysql_fetch_assoc($arrResult)) {
            
    $arrRestaurants[] = $arrRow;
        }
        

    ?>
    <div id="wrapper">

    <form name="frmSearch" class="abajo" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
        <fieldset class="primero">
            <legend class="primerosub">Search For a Restaurant</legend>
            
            <fieldset class="segundo1" style="border-color:#FFFFFF" >  
                <legend>Location Details</legend>  
                <ol>
                    <li class="restaurants-name">
                        <label for="restaurants-name">Name</label>
                        <input type="text" name="frmSearch[name]" value="<?php echo $strName?>" id="restaurants-name" class="text" />
                    </li>
                    <li class="restaurants-zipcode">
                        <label for="restaurants-zipcode">Zip</label>
                        <input type="text" name="frmSearch[zipcode]" value="<?php echo $strZipCode?>" maxlength="5" id="restaurants-zipcode" class="text" />
                    </li>
                    <?php if(!empty($arrStates)) { ?>
                        <li class="restaurants-state">
                            <label for="restaurants-state">State</label>
                            <select name="frmSearch[state]" id="restaurants-state" style="width:40px;">
                                <?php
                                
    foreach($arrStates as $strStateAbb) {
                                    
    printf(
                                        
    '<option value="%s"%s>%s</option>'
                                        
    ,$strStateAbb
                                        
    ,strcmp($strState,$strStateAbb) == 0?' selected="selected"':''
                                        
    ,strcmp($strStateAbb,'')==0?'--':$strStateAbb
                                    
    );
                                }    
                                
    ?>
                            </select>
                        </li>
                    <?php ?>
                    <?php if(!empty($arrRestaurantsFoodTypes)) { ?>
                        <li class="restaurants-food-types">
                            <label for="restaurants-food-types">Food Type</label>
                            <select name="frmSearch[food_types][]" id="restaurants-food-types">
                                <?php
                                
    foreach($arrRestaurantsFoodTypes['values'] as $intIndex=>$intFoodTypesId) {
                                    
    printf(
                                        
    '<option value="%s"%s>%s</option>'
                                        
    ,$intFoodTypesId
                                        
    ,in_array($intFoodTypesId,$arrFoodTypes)?' selected="selected"':''
                                        
    ,$arrRestaurantsFoodTypes['output'][$intIndex]
                                    );
                                }    
                                
    ?>
                            </select>
                        </li>
                    <?php ?>
                    <ol class="resetsearch">
                    <li>
                        <input type="submit" value="Submit" name="frmSearch[submit]">
                    </li>
                    <li>
                        <input type="submit" value="reset" name="frmSearch[reset]">
                    </li>
                    </ol>
                </ol>
            </fieldset>
            <fieldset class="tercero" style="">
                <legend>Services</legend>
                
                <?php if(!empty($arrRestaurantsOfferings)) { ?>
                <ol>
                    <li class="restaurants-offerings">
                        <?php
                        
    foreach($arrRestaurantsOfferings as $arrRestaurantsOffering) {
                            
    printf(                    
                                
    '<input type="checkbox" name="frmSearch[offerings][]" value="%u" id="restaurants-offerings-%u"%s />
                                <span class="checkboxes23">%s</span><br />'                               
                                
    ,$arrRestaurantsOffering['restaurant_offerings_id']
                                ,
    $arrRestaurantsOffering['restaurant_offerings_id']
                                ,
    in_array($arrRestaurantsOffering['restaurant_offerings_id'],$arrOfferings)?' checked="checked"':''
                                
    ,$arrRestaurantsOffering['name']
                            );
                        }
                    
    ?>
                    </li>
                </ol>
                <?php ?>
            </fieldset>
        </fieldset>
    </form>
    </div>
    <div id="videowrapper"> 
    <fieldset class="videofield">
    <object width="380" height="265">
    <param name="movie" value="videoplayer5.swf">
    <embed src="videos/vaideoplayer5.swf" width="380" height="240">
    </embed>
    </object>
    </fieldset>
    </div>

    <?php if(empty($arrRestaurants)){
    echo 
    '<div id="holders"><img src="images/smallholdersleft.gif" alt="holders" /></div>
    <!--<dvi id="holders1"><img src="images/holders1.gif" alt="holders" /></div>-->
    <div id="header2">
      <fieldset class="header2field"><img src="images/RecommendedRestaurants.gif" alt="Recommended Restaurants"/></fieldset>
        </div>
    <div id="container4">
      <div class="wrap"> '
    ;
          
      

      
    $query "SELECT restaurants.state, restaurants.address ,restaurants.zip, restaurants.description, restaurants.restaurantname, restaurants.image, restaurants.restaurants_id
    FROM restaurants
    WHERE
    restaurants_id IN ( 1, 2, 3, 4)"


    $result mysql_query($query$connection);
    $i 1;

     while (
    $content mysql_fetch_array($result)) {
    echo 
    "<div class=\"shoeinfo1\">
       <img src=\"images/spacer.gif\" alt=\"spacer\" class=\"spacer2\" />
          <h2 class=\"infohead\">"
    $content['restaurantname'] . "</h2>
          <div class=\"pic\"><img class=\"line\" src= "
    $content['image'] ." alt=\"picture\" width=\"100%\" height=\"100%\" /></div>

          <h5> Rating:</h5><h4>"
    ;
            
            
            
    $ratingData Rating::OutputRating($content['restaurantname']);
          
          if (
    Error::HasErrors())
          {
            echo 
    Error::ShowErrorMessages();
            
    Error::ClearErrors();
          }
          else
          {
            echo 
    $ratingData;
          }  
          echo
    "</h4> 
            <h3>Description:</h3>
            <div id=\"description\"><p>"
    .$content['description']." </p></div> 
            <div class=\"suabe2\">Address:<span class=\"suabe\">"
    .$content['address']."</span></div>
            <div id=\"state\">State:<span class=\"suabe\">"
    $content['state']. "</span></div>
               <h6>Zip:<span class=\"suabe\">"
    $content['zip'] . "</span></h6>
        <p><a href=\"#\">More</a></p></div>
        
    "
    ;
    $i++; 
    if (
    $i && $i == 
    {
      echo 
    "<div class=\"clearer\"></div>";

    }
    }
     
    echo 
    '</div>
     </div>'
    ;    
     
        
       } 
     else if (!empty(
    $arrRestaurants)){ ?>
      <div id="holders"><img src="images/smallholdersleft.gif" alt="holders" /></div>
    <!--<dvi id="holders1"><img src="images/holders1.gif" alt="holders" /></div>-->
    <div id="header2">
      <fieldset class="header2field"><img src="images/Restaurantsresults.gif" alt="Restaurants Results"/></fieldset>
        </div>
      <div id="container4">
      <div class="wrap">
         <?php // print search query
    //if(!empty($strSQL)) { 
    //printf('<p>%s</p>',$strSQL);
     // find out how many rows are in the table 
    $sql "SELECT COUNT(*) FROM restaurants";
    $result mysql_query($sql) or trigger_error(mysql_error());
    $r mysql_fetch_row($result);
    $numrows $r[0];

    // number of rows to show per page
    $rowsperpage 6;
    // find out total pages
    $totalpages ceil($numrows $rowsperpage);

    // get the current page or set a default
    if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
       
    // cast var as int
       
    $currentpage = (int) $_GET['currentpage'];
    } else {
       
    // default page num
       
    $currentpage 1;
    // end if

    // if current page is greater than total pages...
    if ($currentpage $totalpages) {
       
    // set current page to last page
       
    $currentpage $totalpages;
    // end if
    // if current page is less than first page...
    if ($currentpage 1) {
       
    // set current page to first page
       
    $currentpage 1;
    // end if

    // the offset of the list, based on current page 
    $offset = ($currentpage 1) * $rowsperpage;

    // get the info from the db 
    $arrDisplay array_slice($arrRestaurants,$offset,$rowsperpage);


    $i 1;
    foreach(
    $arrDisplay as $arrRestaurant) {

      echo 
    "<div class=\"shoeinfo1\">
       <img src=\"images/spacer.gif\" alt=\"spacer\" class=\"spacer2\" />
          <h2 class=\"infohead\">"
    $arrRestaurant['restaurantname'] . "</h2>
          <div class=\"pic\"><img class=\"line\" src= "
    $arrRestaurant['image'] ." alt=\"picture\" width=\"100%\" height=\"100%\" /></div>

          <h5> Rating:</h5><h4>"
    ;
            
            
            
    $ratingData Rating::OutputRating($arrRestaurant['restaurantname']);
          
          if (
    Error::HasErrors())
          {
            echo 
    Error::ShowErrorMessages();
            
    Error::ClearErrors();
          }
          else
          {
            echo 
    $ratingData;
          }  
          echo
    "</h4> 
            <h3>Description:</h3>
            <div id=\"description\"><p>"
    .$arrRestaurant['description']." </p></div> 
            <div class=\"suabe2\">Address:<span class=\"suabe\">"
    .$arrRestaurant['address']."</span></div>
            <div id=\"state\">State:<span class=\"suabe\">"
    $arrRestaurant['state']. "</span></div>
               <h6>Zip:<span class=\"suabe\">"
    $arrRestaurant['zip'] . "</span></h6>
        <p><a href=\"#\">More</a></p></div>
        
    "
    ;
    $i++; 
    if (
    $i && $i == 
    {
      echo 
    "<div class=\"clearer\"></div>";

    }
    }
    /******  build the pagination links ******/
    // range of num links to show
    $range 2;

    // if not on page 1, don't show back links
    if ($currentpage 1) {
       
    // show << link to go back to page 1
       
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
       
    // get previous page num
       
    $prevpage $currentpage 1;
       
    // show < link to go back to 1 page
       
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
    // end if 

    // loop to show links to range of pages around current page
    for ($x = ($currentpage $range); $x < (($currentpage $range) + 1); $x++) {
       
    // if it's a valid page number...
       
    if (($x 0) && ($x <= $totalpages)) {
          
    // if we're on current page...
          
    if ($x == $currentpage) {
             
    // 'highlight' it but don't make a link
             
    echo " [<b>$x</b>] ";
          
    // if not current page...
          
    } else {
             
    // make it a link
             
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
          } 
    // end else
       
    // end if 
    // end for
                     
    // if not on last page, show forward and last page links        
    if ($currentpage != $totalpages) {
       
    // get next page
       
    $nextpage $currentpage 1;
        
    // echo forward link for next page 
       
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
       
    // echo forward link for lastpage
       
    echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
    // end if
    }

    ?>

    </div>
    </div>
    Beside this approach I have been trying to change the form action to action="restaurantsdisplay.php?variables, variables=[zip], variable=[state] etc... and pass all this variables to restaurantsdisplay.php and then populate them in the html frame below in restaurantsdisplay.php

    PHP Code:
    <?php $i 1;
    foreach(
    $arrDisplay as $arrRestaurant) {

      echo 
    "<div class=\"shoeinfo1\">
       <img src=\"images/spacer.gif\" alt=\"spacer\" class=\"spacer2\" />
          <h2 class=\"infohead\">"
    $arrRestaurant['restaurantname'] . "</h2>
          <div class=\"pic\"><img class=\"line\" src= "
    $arrRestaurant['image'] ." alt=\"picture\" width=\"100%\" height=\"100%\" /></div>

          <h5> Rating:</h5><h4>"
    ;
            
            
            
    $ratingData Rating::OutputRating($arrRestaurant['restaurantname']);
          
          if (
    Error::HasErrors())
          {
            echo 
    Error::ShowErrorMessages();
            
    Error::ClearErrors();
          }
          else
          {
            echo 
    $ratingData;
          }  
          echo
    "</h4> 
            <h3>Description:</h3>
            <div id=\"description\"><p>"
    .$arrRestaurant['description']." </p></div> 
            <div class=\"suabe2\">Address:<span class=\"suabe\">"
    .$arrRestaurant['address']."</span></div>
            <div id=\"state\">State:<span class=\"suabe\">"
    $arrRestaurant['state']. "</span></div>
               <h6>Zip:<span class=\"suabe\">"
    $arrRestaurant['zip'] . "</span></h6>
        <p><a href=\"#\">More</a></p></div>
        
    "
    ;
    $i++; 
    if (
    $i && $i == 
    {
      echo 
    "<div class=\"clearer\"></div>";

    }
    }
    ?>


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
  •