mysql_fetch_array() Error

**tearsfall** · 09-04-2008 11:20 PM

First,

I never figured out what I did wrong the other day with my simple web application: the simple guest book. I removed the form where users add guest book entries. I'm using myPhpAdmin to add new rows to the table for testing purposes. Once I figure out what's going on then I will continue with the form.

my guestbook's address: http://tearsfall.pcriot.com/guestbook.php

First, my guest book's table structure and each attributes defining feautres:

guestbook (guestID,guest_name,guest_message)

guestID: int(4), not null, auto increment, primary key
guest_name: varchar(19), not null
guest_message: varchar(255), not null

Second, my database_connection.php (so that you will understand that I'm trying to make my site with OOP).

Code:

<?php

  class DatabaseConnection {
  
    private $connection_resource;
    private $hostname;
    private $username;
    private $password;
    private $database_name;
      
    function __construct($hostname,$username,$password,$database_name) { 
      $this->hostname = $hostname;
      $this->username = $username;
      $this->password = $password;
      $this->database_name = $database_name;
    }
    
    public function connect() {
      $this->connection_resource = mysql_connect($this->hostname,
                                                 $this->username,
                                                 $this->password);
      $this->connection_resource += mysql_select_db($this->database_name);                                                 
    }
      
    public function disconnect() {
      mysql_close($this->connection_resource);
    }
    
  }
 
?>

guestbook.php

Code:

    <?php
      
      require "database_connection.php"; 
      $myconnection = new DatabaseConnection('localhost','tearsfal_admin','*********','tearsfal_forums');
      $myconnection->connect();
           
      $result = mysql_query("SELECT * FROM guestbook") or die(mysql_error());
       
      if(mysql_fetch_array($result) < 1) {
        echo "No entries.";
      }
      else {
        while($row = mysql_fetch_array($result)) {
        echo $row['guestID'] . " " . $row['guest_name'] . "<br />" . $row['guest_message'] . "<br /><br />";

        }
      }  
      
    ?>

OKay, my guestbook.php is bringing up all the rows following the first row. I don't know why the first row is not being included on my guestbook.php. I will show you the code that I know is in question with my database_connection.php and my guestbook.php files, respectively.

database_connection.php: these lines appear to be okay, but maybe someone with more PHP experience can point something out to me

Code:

    public function connect() {
      $this->connection_resource = mysql_connect($this->hostname,
                                                 $this->username,
                                                 $this->password);
      $this->connection_resource += mysql_select_db($this->database_name);                                                 
    }

guestbook.php

Code:

     
      if(mysql_fetch_array($result) < 1) {
        echo "No entries.";
      }
      else {
        while($row = mysql_fetch_array($result)) {
        echo $row['guestID'] . " " . $row['guest_name'] . "<br />" . $row['guest_message'] . "<br /><br />";

        }

That's all that I have pressing on my mind. Every bit of help I receive will be great.

TearsFall

**phpasks** · 09-05-2008 06:09 AM

You have MYSQL 5 version & you have used mysql 4 function

better way you can used mysql 5 database function

PHP Code:


<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = "SELECT Name, CountryCode FROM City ORDER by ID LIMIT 3";
$result = $mysqli->query($query);

/* numeric array */
$row = $result->fetch_array(MYSQLI_NUM);
printf ("%s (%s)\n", $row[0], $row[1]);

/* associative array */
$row = $result->fetch_array(MYSQLI_ASSOC);
printf ("%s (%s)\n", $row["Name"], $row["CountryCode"]);

/* associative and numeric array */
$row = $result->fetch_array(MYSQLI_BOTH);
printf ("%s (%s)\n", $row[0], $row["CountryCode"]);

/* free result set */
$result->close();

/* close connection */
$mysqli->close();
?>

**tearsfall** · 09-07-2008 10:54 PM

Here is an excerpt from my original post in this topic:

guestbook.php

Code:

    <?php
      
      require "database_connection.php"; 
      $myconnection = new DatabaseConnection('localhost','tearsfal_admin','*********','tearsfal_forums');
      $myconnection->connect();
           
      $result = mysql_query("SELECT * FROM guestbook") or die(mysql_error());
       
      if(mysql_fetch_array($result) < 1) {
        echo "No entries.";
      }
      else {
        while($row = mysql_fetch_array($result)) {
        echo $row['guestID'] . " " . $row['guest_name'] . "<br />" . $row['guest_message'] . "<br /><br />";

        }
      }  
      
    ?>

I figured out what I was doing wrong. Because I figured out what I was doing wrong I decided to make a mini-tutorial for others who may have the same problem in the future with this type of thing. I didn't really comprehend mysql_fetch_array or mysql_fetch_assoc, but now that I do I'm going to explain it so that everyone can benefit from it.

Before I explain the differences between mysql_fetch_array and mysql_fetch_assoc, I want you guys to see where I made my mistake:

Code:

      if(mysql_fetch_array($result) < 1) {
        echo "No entries.";
      }
      else {
        while($row = mysql_fetch_array($result)) {
        echo $row['guestID'] . " " . $row['guest_name'] . "<br />" . $row['guest_message'] . "<br /><br />";

        }
      }

The bolded areas are mistakes. I will explain why after you see the corrected code:

Code:

if(mysql_num_rows($result) < 1) {
     echo "No entries found in the database.";
}

while($row = mysql_fetch_assoc($result)) {
     // Stuff here
}

First, I tried to use an if conditional statement to see if the array that is returned by mysql_fetch_array is less than 1. That condition checked the array's size. I wrote a conditional statement to see if the array had less than 3 items stored in the array. I wanted to see if the array happened to be empty not if it had less than 3 items within the array. So, instead I had to check the number of rows returned by the query, if there is less than 1 row or not. Thus, my while statement was "never" being executed.

To understand why my while statement was incorrectly written, you have to consider the following:

The differences between mysql_fetch_array and mysql_fetch_assoc are understood like this. The function mysql_fetch_array gathers information from the database and collects it in an array, and the mysql database colums are referenced by an index number.

For example, in my database: (using mysql_fetch_array)

guestID -> index # 0
guest_name -> index # 1
guest_message -> index # 2

For those columns to be displayed in my guestbook, I would have had to do something like this:

Code:

while($row = mysql_fetch_array($result)) {
   echo $row[0] . " " . $row[1] . " " . $row[2];
}

I chose not to use mysql_fetch_array, because using an index number is slightly more confusing than using the column names. I decided to go with mysql_fetch_assoc for the following reasons:

(1) mysql_fetch_assoc associates the array with the column names.
(2) Instead of using numbers to select fields from a table, you can actually use the field names, which seems much simplier.
(3) mysql_fetch_assoc results are smaller than mysql_fetch_array results

Code:

while($row = mysql_fetch_assoc($result)) {
     echo $row['guestID'] . " " . $row['guest_name'] . " " . $row['guest_message'];
}

If you want to experiment with the output and learn the differences between mysql_fetch_array and mysql_fetch_assoc for yourself ... try the following code:

Code:

$query = "SELECT * FROM table";   // Or whatever query you'd like to test with

$result = mysql_query($result);
$result_array = mysql_fetch_array($result);
$result_assoc = mysql_fetch_assoc($result);

//  Print the difference between fetch_array and fetch_assoc

print_r($result_array);
echo "<br />";
print_r($result_assoc);

The output from mysql_fetch_array will look like this: (spaced for easier reading)

array ( 0 => 1
guestID => 1
1 => (guest name here)
guest_name = > (guest name here)
2 => (guest message here)
guest_message => (guest_message here)
)

The output from mysql_fetch_assoc will look like this: (spaced for easier reading)

array ( guestID => 1
guest_name => (guest name here)
guest_message => (guest message here)
)

So, there you have it. I hope that some of you find this helpful in the future.

TearsFall

**sunils** · 09-08-2008 06:48 AM

You can post that tutorial in tutorial section and it will be helpful for other members. If you post here, it wont be visible for other.. make a tutorial of this and remove the content from here( if possible by you or tell me which things are to be removed.).. and if the problem is solved please close the thread.

**rima123** · 09-08-2008 08:29 AM

tearsfall... resort to keep the coding simple... just to connect to db u've written WORLD DOOM BUTTON function and then u yes using php4 in php5... so welcome to world of php5 buddy