[SOLVED] Cannot retrieve the data from database

[sashi34u]

Hi  all,

 

I am trying to run a query to retrieve data from mysql database and display it in a webpage in a table. The query is running fine, but the the data is not appearing on the webpage. All I see is a webpage with few empty cells.

 

The echo command starting from line 42 is something like this

echo "<tr>\n

      <td>$building</td>\n

      <td>$parking_lot</td>\n      

      <td>$month</td>\n 

      <td>$day</td>\n

      <td>$occupancy</td>\n

      <td>$empty_spaces</td>\n

      <td>$special_days</td>\n

      </tr>\n";

 

The variables I have used here ie., $building, $parking_lot etc are nothing but the column names in the mysql database ie., building, parking_lot etc. I hope I did not mess up in that sense. But, I do not know the reason for the non appearance of the data.

 

I am posting the whole code below. Also I am inserting a screenshot of the display in the webpage. If anyone has any answer please let me know.

 

Thanks

Sashi

 

 

 

<?php 

// Connects to your Database 
$host="********";
$user="*******";
$password="*******";
$dbname="*********";
$cxn=mysqli_connect($host, $user, $password, $dbname) ;
if (!$cxn=mysqli_connect($host, $user, $password, $dbname))
{
	$error=mysqli_error($cxn);
	echo "$error";
	die();
}
else
{
	echo "Connection established successfully";
}

$sql="SELECT * FROM occupancy";
$data = mysqli_query($cxn,$sql); 
if (!$data=mysqli_query($cxn,$sql))
{
	$error=mysqli_error($cxn);
	echo "$error";
	die();
}
else
{
	echo "<br>";
	echo "Query sent successfully";
}


echo "<br>";
echo "<h1> OCCUPANCY <h1>";
echo "<table cellspacing='0'>";
echo "<tr><td colspan='7'><hr /></td></tr>";
while ($row=mysqli_fetch_array($data))
{
	extract($row);
	echo "<tr>\n
	      <td>$building</td>\n
	      <td>$parking_lot</td>\n		      	
	      <td>$month</td>\n   
	      <td>$day</td>\n
	      <td>$occupancy</td>\n
	      <td>$empty_spaces</td>\n			
	      <td>$special_days</td>\n
	      </tr>\n";
	echo "<tr><td colspan='7'><hr /></td></tr>\n";
}
echo "</table>\n";


?>

 

 

[corbin]

print_r($row) and make sure the column names match exactly.

[sashi34u]

Yes, the column names match perfectly. Print_r would show us the structure and the values present in a database. But I would like to fit the output into a defined. With respect to that, do you see any changes in the code?

[corbin]

Hrmmm, if the column names match the variable names exactly, there shouldn't be an error.  (It's usually considered bad form to use extra on row results by the way.... you run the risk of a variable being overwritten later if another column is added or something.)

 

 

Do you get any errors?  I'm not sure mysqli_fetch_array is a function.

[sashi34u]

Hi corbin,

 

I got the reply from somewhere else. I am posting it here so that you can have a look. Let me know if it makes sense. I did not get any errors. All I see is a page with the heading occupancy and then several vertical cells. But the suggestion pasted below solved the problem. Thanks for all your help.

 

I believe your problem is that the mysqli_fetch_array function on line 39

returns an enumerated array and the extract function on line 41 takes the

values in an array and assigns it to an associative array based on the

keys.  Since the keys in an enumerated array are numbers, those are the

variable names.  I believe you will have an easier time if you do the

following:

 

while ($row=mysqli_fetch_array($data))
  {
      $building = $row[0];
      $parking_lot = $row[1];
      $month = $row[2];
      $day = $row[3];
      $occupancy = $row[4];
      $empty_spaces = $row[5];
      $special_days = $row[6];
      echo "<tr>\n
            <td>$building</td>\n
            <td>$parking_lot</td>\n
<td>$month</td>\n               <td>$day</td>\n
            <td>$occupancy</td>\n
            <td>$empty_spaces</td>\n
<td>$special_days</td>\n
            </tr>\n";
      echo "<tr><td colspan='7'><hr /></td></tr>\n";
  }

Now it is important in the order with which the fields are returned from the

database since that will affect the row values when you are assigning them

to variables (ie- if the day is returned first in the array it should be "

$day = $row[0]" )

 

[corbin]

Ahhh, so mysqli_fetch_array does exist...

 

 

Anyway, glad you figured it out ;p.