8 Replies Latest reply on Jan 12, 2009 1:01 AM by Newsgroup_User

    php/sql prob

    malcster2 Level 1
      i have a little problem with this site, http://www.alexandraco.org/paid.php
      it is a ajax page, and this seems to work fine. the only problem is, when the page is originally loaded, there is an error message, for a split second. it works fine after that. it anoying me, as i have built several other pages similar to this that works fine.

      here is the php code(which is an include)


      <?php

      $server = "localhost";
      $user = "xxxx";
      $password = "xxxxx";
      $database = "xxx";

      $db_handle = mysql_connect($server, $user, $password);
      $db_found = mysql_select_db($database, $db_handle);
      $oby = $_GET['$sortpost'];



      $sql = "select * from customers order by ".$oby;
      $result = mysql_query($sql);
      $count=mysql_num_rows($result);

      echo "Records: $count </br>";

      ?>

      <table id="custid" name="custtable" align="left" cellspacing="5px;" border="1" >
      <th align="center">invoice</th><th align="center">title</th><th align="center">fname</th><th align="center">sname</th><th align="center">company</th><th align="center">amount</th><th align="center">paid</th><th align="center">date paid</th>
      <?php



      while ($db_field = mysql_fetch_assoc($result))
      {
      print("<tr align = 'center'><td>".$db_field['invoiceno']."</td><td>".$db_field['title']."</td><td>".$db_fiel d['fname']."</td><td>".$db_field['sname']."<td>".$db_field['company']."</td><td>".$db_fiel d['amount']."</td><td>".$db_field['paid']."</td><td>".$db_field['datepaid']."</td></tr>");

      }
      echo("</table>");



      ?>



      also, when i originally load the page, a value is passed to the sql to sort by a default field, so this is not the problem. hope you can help

      malc
        • 1. Re: php/sql prob
          Ben M Adobe Community Professional
          What error are you seeing? I seem to be loading the page fine although I might recommend making the font size of your table a little larger because it is a strain to try to read.
          • 2. Re: php/sql prob
            Level 7
            .oO(malcster2)

            >i have a little problem with this site, http://www.alexandraco.org/paid.php
            > it is a ajax page, and this seems to work fine. the only problem is, when the
            >page is originally loaded, there is an error message, for a split second.

            Even after that the error message is still there in the original HTML
            code received from the server. It's just not visible anymore on screen,
            because the part it is in was replaced by your AJAX call. Without JS it
            would be visible all the time.

            >it
            >works fine after that. it anoying me, as i have built several other pages
            >similar to this that works fine.
            >
            > here is the php code(which is an include)
            >
            >
            > <?php
            >
            > $server = "localhost";
            > $user = "xxxx";
            > $password = "xxxxx";
            > $database = "xxx";
            >
            > $db_handle = mysql_connect($server, $user, $password);
            > $db_found = mysql_select_db($database, $db_handle);
            > $oby = $_GET['$sortpost'];

            On first call $_GET['$sortpost'] is not set, so $oby will be empty and
            the following query will fail.

            You definitely need a correct PHP configuration and some error checking.
            First make sure that on your development machine error_reporting is set
            to E_ALL|E_STRICT in the php.ini. Then on the first page request you
            should receive an E_NOTICE error. On the real server errors should be
            turned off (display_errors = 0), because they might reveal too much
            internal informations about your system and DB to some bad guys. For
            example it shows where your includes are stored and even allows to call
            them directly ... not good.

            After that's done check with isset() if $_GET['$sortpost'] exists and
            add some error checking to your MySQL calls as well. There are a dozen
            things that can go wrong when querying a DB, so you have to check that
            you really got the result you expect (if any at all) before you proceed.

            Finally read about SQL injection and how to prevent it. Your code is
            vulnerable, because you use a user-submitted value ($_GET['$sortpost'])
            directly in a query without any validation. With the given informations
            it's possible for an attacker to run almost any arbitrary query on your
            server!

            > $sql = "select * from customers order by ".$oby;

            You should explicitly name all columns you want to receive. SELECT * is
            just bad[tm] and should be used only for testing or debugging, but not
            in production code.

            > while ($db_field = mysql_fetch_assoc($result))
            > {
            > print("<tr align =
            >'center'><td>".$db_field['invoiceno']."</td><td>".$db_field['title']."</td><td>"
            >.$db_field['fname']."</td><td>".$db_field['sname']."<td>".$db_field['company']."
            ></td><td>".$db_field['amount']."</td><td>".$db_field['paid']."</td><td>".$db_fie
            >ld['datepaid']."</td></tr>");

            Just a suggestion: You might want to have a look a printf()/vprintf()
            to keep such code a bit more readable (using vprintf() would require a
            proper SQL query with all required fields mentioned in the correct
            order). See the manual for more details.

            > also, when i originally load the page, a value is passed to the sql to sort by
            >a default field, so this is not the problem. hope you can help

            The error happens before this.

            HTH
            Micha
            • 3. Re: php/sql prob
              malcster2 Level 1
              thanks for the advice michael, but i'm the only one who is going to be viewing this page. it is going to be password protected as well.

              on initial loading of the page, i'm setting the $oby variable by default, so sort by invoice is being done by default, so there is no problem there

              i believe the problem is with the count query, but as i say, i have near identical sites that run perfectly. and the count part works fine after the initial error
              • 4. Re: php/sql prob
                Level 7
                .oO(malcster2)

                > on initial loading of the page, i'm setting the $oby variable by default, so
                >sort by invoice is being done by default, so there is no problem there

                On the first request the $_GET['$sortpost'] variable doesn't exist, so
                this line will cause a notice (assuming proper configuration):

                $oby = $_GET['$sortpost'];

                This also causes the query to become invalid, which MySQL would tell you
                if you would ask for it (error checking, as already mentioned).

                What really initializes the $oby variable is the _second_ request, done
                by your AJAX script. Try it without JS and you will see the error
                message on screen.

                Micha
                • 5. Re: php/sql prob
                  malcster2 Level 1
                  <script>
                  callAjax('invoiceno');
                  </script>

                  i'm using the above code when the main page is loaded, so the ajax script is kicking in automatically. i have tested this, and it is definitly the case.

                  the error message is reference this piece of code: $count=mysql_num_rows($result);

                  • 6. Re: php/sql prob
                    malcster2 Level 1
                    now sorted, the main page didn't like me using a php include. just removed the include(....) bit, did the trick
                    • 7. Re: php/sql prob
                      Level 7
                      .oO(malcster2)

                      ><script>
                      > callAjax('invoiceno');
                      > </script>
                      >
                      > i'm using the above code when the main page is loaded, so the ajax script is
                      >kicking in automatically. i have tested this, and it is definitly the case.

                      Yes, and when is this script called? Immediately _after_ the page was
                      requested from the server for the first time! Think logically: The
                      browser has to load the page before he even knows that there's some AJAX
                      to execute. After that the AJAX triggers _another_ request.

                      Have a look at your server's logfiles - you will find _two_ requests.
                      The first one does not have the $_GET['$sortpost'], which causes the
                      error. The second request from the AJAX has this parameter and works.

                      Try it without JS as already suggested, then you may see it more
                      clearly. It's not an AJAX issue, but simply missing initializing and
                      error checking in your script.

                      > the error message is reference this piece of code:
                      >$count=mysql_num_rows($result);

                      Correct, because the query fails, because there's an empty $oby
                      variable, because there's no $_GET['$sortpost'] parameter on the first
                      request. How often do I have to repeat this until you believe it? ;)

                      Micha
                      • 8. Re: php/sql prob
                        Level 7
                        .oO(malcster2)

                        >now sorted, the main page didn't like me using a php include. just
                        >removed the include(....) bit, did the trick

                        That was surely not the reason and doesn't fix the problem, believe me.
                        But if you think it's OK now - good.

                        Micha