Page 1 of 1

Problem with PHP code.

Posted: Sun Jul 24, 2011 1:24 pm
by klops12
Hello!
I need help with PHP code.
OK, I took resource code from Flash-db site and there is GueastBook V2.
I made two text inputs and one text output. The problem is that I can't get back entries from my SQL database?
PHP code:

Code: Select all

<?
   $DBhost = "sql306.byethost2.com";   // Database Server
   $DBuser = "b2_8264108";            // Database User
   $DBpass = "**********";            // Database Pass
   $DBName = "b2_8264108_GuestBook2";            // Database Name
   $table = "guestbook";             // Database Table
   $table1 = "guestbook1";             // Database Table
   $numComments = 10;       // Number of Comments per page
   
   // Connect to mySQL Server
   $DBConn = mysql_connect($DBhost,$DBuser,$DBpass) or die("Error in GuestBook Application: " . mysql_error());
   // Select mySQL Database
   mysql_select_db($DBName, $DBConn) or die("Error in GuestBook Application: " . mysql_error());

// Part Two - Choose what action to perform
   $action = $_GET['action'];
   
   switch($action) {
      case 'read' :
       // Fetch all comments from database table
       $sql = 'SELECT * FROM `' . $table . '`';
       $allComments = mysql_query($sql, $DBConn) or die("Error in GuestBook Application: " . mysql_error());
       $numallComments = mysql_num_rows($allComments);
       // Fetch page-wise comments from database table
       $sql .= ' ORDER BY `time` DESC LIMIT ' . $_GET['NumLow'] . ', ' . $numComments;
       $fewComments = mysql_query($sql, $DBConn) or die("Error in GuestBook Application: " . mysql_error());
       $numfewComments = mysql_num_rows($fewComments);
       // Generate Output for Flash to Read
       print '&totalEntries=' . $numallComments . '&';
       print "&entries=";   
       
       if($numallComments == 0) {
          print "No entries in the guestbook, as yet..";
       } else { 
          while ($array = mysql_fetch_array($fewComments)) {
            $name = mysql_result($fewComments, $i, 'name');
            $email = mysql_result($fewComments, $i, 'email');
            $comments = mysql_result($fewComments, $i, 'comments');
            $time = mysql_result($fewComments, $i, 'time');
            
            print '<b>Name: </b>' . $name . '<br><b>Email: </b>' . $email . '<br><b>Comments: </b>' . $comments . '<br><i>Date: ' . $time . '</i><br><br>';
            $i++;
          }
      }
      // Print this only when there aren't any more entries..
      if($_GET['NumLow'] > $numallComments) {
         print 'No More Entries!&';
      }
      break;
       
     case 'write' :
        // Recieve Variables From Flash
       $name = ereg_replace("&", "%26", $_POST['yourname']);
       $email = ereg_replace("&", "%26", $_POST['youremail']);
       $comments = ereg_replace("&", "%26", $_POST['yourcomments']);
       $submit = $_POST['submit'];
           
       // Current system date in yyyy-mm-dd format
       $submitted_on = date ("Y-m-d H:i:s",time());
              
       // Check if its submitted from Flash
       if($submit == 'Yes'){
       // Insert the data into the mysql table
       $sql = 'INSERT INTO ' . $table . 
                ' (`ID`, 
               `name`, 
               `email`, 
               `comments`, 
               `time`
              ) 
              VALUES 
              (\'\','
               . '\'' . $name . '\',' 
               . '\'' . $email . '\',' 
               . '\'' . $comments . '\',' 
               . '\'' . $submitted_on . '\'
               )';
       $insert = mysql_query($sql, $DBConn) or die("Error in GuestBook Application: " . mysql_error());
       
       // If you want your script to send email to both you and the guest, uncomment the following lines of code
       // Email Script Begin
      
 
       print "&gb_status=Thank you for signing my guestbook.&done=yes&";
       return;
       }
       print "&_root.write.gb_status=Error!&";
       break;
   }
   // Connect to mySQL Server
   $DBConn = mysql_connect($DBhost,$DBuser,$DBpass) or die("Error in GuestBook Application: " . mysql_error());
   // Select mySQL Database
   mysql_select_db($DBName, $DBConn) or die("Error in GuestBook Application: " . mysql_error());

// Part Two - Choose what action to perform
   $action = $_GET['action'];
   
   switch($action) {
      case 'read' :
       // Fetch all comments from database table
       $sql = 'SELECT * FROM `' . $table1 . '`';
       $allComments = mysql_query($sql, $DBConn) or die("Error in GuestBook Application: " . mysql_error());
       $numallComments = mysql_num_rows($allComments);
       // Fetch page-wise comments from database table
       $sql .= ' ORDER BY `time` DESC LIMIT ' . $_GET['NumLow'] . ', ' . $numComments;
       $fewComments = mysql_query($sql, $DBConn) or die("Error in GuestBook Application: " . mysql_error());
       $numfewComments = mysql_num_rows($fewComments);
       // Generate Output for Flash to Read
       print '&totalEntries1=' . $numallComments . '&';
       print "<br>&entries1=";   
       
       if($numallComments == 0) {
          print "No entries in the guestbook, as yet..";
       } else { 
          while ($array = mysql_fetch_array($fewComments)) {
            $name = mysql_result($fewComments, $i, 'name');
            $email = mysql_result($fewComments, $i, 'email');
            $comments = mysql_result($fewComments, $i, 'comments');
            $time = mysql_result($fewComments, $i, 'time');
            
            print '<b>Name: </b>' . $name . '<br><b>Email: </b>' . $email . '<br><b>Comments: </b>' . $comments . '<br><i>Date: ' . $time . '</i><br><br>';
            $i++;
          }
      }
      // Print this only when there aren't any more entries..
      if($_GET['NumLow'] > $numallComments) {
         print 'No More Entries!&';
      }
      break;
       
     case 'write' :
        // Recieve Variables From Flash
       $name = ereg_replace("&", "%26", $_POST['yourname1']);
       $email = ereg_replace("&", "%26", $_POST['youremail1']);
       $comments = ereg_replace("&", "%26", $_POST['yourcomments1']);
       $submit = $_POST['submit1'];
           
       // Current system date in yyyy-mm-dd format
       $submitted_on = date ("Y-m-d H:i:s",time());
              
       // Check if its submitted from Flash
       if($submit == 'Yes'){
       // Insert the data into the mysql table
       $sql = 'INSERT INTO ' . $table1 . 
                ' (`ID`, 
               `name`, 
               `email`, 
               `comments`, 
               `time`
              ) 
              VALUES 
              (\'\','
               . '\'' . $name . '\',' 
               . '\'' . $email . '\',' 
               . '\'' . $comments . '\',' 
               . '\'' . $submitted_on . '\'
               )';
       $insert = mysql_query($sql, $DBConn) or die("Error in GuestBook Application: " . mysql_error());
       
       // If you want your script to send email to both you and the guest, uncomment the following lines of code
       // Email Script Begin
             
       print "&gb_status1=Thank you for signing my guestbook.&done=yes&";
       return;
       }
       print "&_root.write.gb_status1=Error!&";
       break;
   }

?>
Maybe someone can help me with PHP code, cause I am newbie to PHP?
P.S.http://www.avsb1.byethost2.com
If You need addditional information, ask and I will try to respond quick as I can.

Re: Problem with PHP code.

Posted: Sun Jul 24, 2011 2:15 pm
by califdon
When you post PHP code, please use the PHP Code button to enclose your code within Syntax tags for legible display. I have done this for you, above.

It's risky to directly include $_GET or $_POST variables in SQL statements as you have done with $_GET['NumLow'] in several SQL statements. You should first "cleanse" them, usually by using the PHP function mysql_real_escape_string(), assigning the cleansed value to a variable that can safely be used in an SQL statement. This also allows you to set a default value if none is present.

With respect to your problem, are you sure that there is a value for $_GET['NumLow']? It's easy to check by looking at the URL in the browser's Location bar.

Also, you need to initialize $i to 0 just before your while() loop. But, better yet, you should use one of the mysql_fetch_...() functions instead of mysql_result() in this situation, which will be at least 4 times faster, since one database fetch will collect all the fields of a row, and you won't have to deal with the $i syntax at all. I would probably do it like this:

Code: Select all

       while ($row = mysql_fetch_array($fewComments)) {
            $name = $row['name'];
            $email = $row['email'];
            $comments = $row['comments'];
            $time = $row['time'];

            print "<b>Name:</b> $name";
            print "<b>Email:</b> $email";
            print "<b>Comments:</b> $comments";
            print "<em>Date: $time</em> $time";
            }
Sorry I had to omit the line breaks, but it seems this forum software interprets them, rather than printing them in a quote box or code box.

Re: Problem with PHP code.

Posted: Mon Jul 25, 2011 2:07 pm
by klops12
Thank You Califdon, I edited your code example in mine, the result is better, but I can't see next entries only first 10 and I would like to get entries according to time they have submited, is that possible?
P.S. Maybe here is some one who knows Flash and tell me what is the problem with textArea (text output), it is not scrollable and when I push button "next" and then "previuos", textArea gets broken even more... :(

Re: Problem with PHP code.

Posted: Mon Jul 25, 2011 2:24 pm
by califdon
klops12 wrote:Thank You Califdon, I edited your code example in mine, the result is better, but I can't see next entries only first 10 and I would like to get entries according to time they have submited, is that possible?
P.S. Maybe here is some one who knows Flash and tell me what is the problem with textArea (text output), it is not scrollable and when I push button "next" and then "previuos", textArea gets broken even more... :(
What value are you assigning to $numComments? That determines how many records to return. You are already sorting by the `time` field. I can't help you with Flash.

Re: Problem with PHP code.

Posted: Sun Jul 31, 2011 1:44 pm
by klops12
Yeah it is sorting by time, but problem is that there is two tables and they are shown one after other.
$numComments = 10.

Re: Problem with PHP code.

Posted: Sun Jul 31, 2011 2:44 pm
by califdon
klops12 wrote:Yeah it is sorting by time, but problem is that there is two tables and they are shown one after other.
$numComments = 10.
I'm afraid I don't understand. You have a Select query that retrieves records from just one table at a time. What other table is involved????????

Re: Problem with PHP code.

Posted: Mon Aug 01, 2011 9:58 am
by klops12
THere are two tables:

$table = "guestbook"; // Database Table
$table1 = "guestbook1"; // Database Table

Re: Problem with PHP code.

Posted: Mon Aug 01, 2011 1:47 pm
by califdon
klops12 wrote:I can't see next entries only first 10 and I would like to get entries according to time they have submited...
klops12 wrote:$numComments = 10
Does that answer your question? This code was written to paginate the results, 10 rows per page. Then the user is expected to click on a button that requests the next 10 rows, etc. Is that what you want?

It appears that you have used a fragment of code and tried to duplicate it to apply to 2 different tables, but that is unlikely to work. What are you attempting to do? Do you want to merge the results from 2 different tables? If so, that is done with a Union query, not with 2 independent queries. You would need to revise essentially all of this code to achieve merged results. If you can explain what you want to do, maybe we can show you how to do it.

Re: Problem with PHP code.

Posted: Mon Aug 01, 2011 2:10 pm
by klops12
Califdon you read my thoughts :)
You are abolutely right, I need to get next 10 after pushing button "Next" and yes, I need to get merged query from both tables :)

Re: Problem with PHP code.

Posted: Mon Aug 01, 2011 2:30 pm
by califdon
There's a serious question about why you have 2 different tables containing what appears to be the same or similar data. That's usually a red flag warning sign that the database is designed improperly, although there can be circumstances that might make it unavoidable, such as if the data is coming from quite different sources. But given that situation, and assuming that you want to merge the data, that is done with a single Union query. Read http://www.mysqltutorial.org/sql-union-mysql.aspx to understand how it works. You absolutely shouldn't open a database connection, run a query, close the connection, open it up again and run another query, etc. You only need to open the connection once and run one Union query. The ORDER BY `time` clause will apply to the combined results of the UNION query. In PHP, you don't need to close the database connection, since that is automatically done by PHP when the script terminates.