[Resolved] Using Array Data From MySQL In Web Pages?

Deadlast99

I have been working on this problem for the last 2 days and have not been able to figure it out!

I am trying to take data received from a form that is saved in my MySQL database and display it on another page using html.

Here is the first php script (get_data_picks.php) I use to pull my data from the database:

<?php
// open the connection
$conn = mysql_connect("host","database","password");

// pick the database to use
mysql_select_db("database",$conn);

// create the SQL statement
$sql = "SELECT * FROM wkly_forms WHERE (week = '01')";

// execute the SQL statement
$result = mysql_query($sql, $conn) or die(mysql_error());

//go through each row in the result set and get data
while ($newArray = mysql_fetch_array($result)) {
// give a name to the fields
$wkly_forms_name = $newArray['name'];
$wkly_forms_gm_01 = $newArray['gm_01'];
$wkly_forms_gm_02 = $newArray['gm_02'];
$wkly_forms_gm_03 = $newArray['gm_03'];
$wkly_forms_gm_04 = $newArray['gm_04'];
$wkly_forms_gm_05 = $newArray['gm_05'];
$wkly_forms_gm_06 = $newArray['gm_06'];
$wkly_forms_gm_07 = $newArray['gm_07'];
$wkly_forms_gm_08 = $newArray['gm_08'];
$wkly_forms_gm_09 = $newArray['gm_09'];
$wkly_forms_gm_10 = $newArray['gm_10'];
$wkly_forms_gm_11 = $newArray['gm_11'];
$wkly_forms_gm_12 = $newArray['gm_12'];
$wkly_forms_gm_13 = $newArray['gm_13'];
$wkly_forms_gm_14 = $newArray['gm_14'];
$wkly_forms_gm_15 = $newArray['gm_15'];
$wkly_forms_gm_16 = $newArray['gm_16'];
$wkly_forms_tot_pts = $newArray['tot_pts'];
$wkly_forms_week = $newArray['week'];
}
?>

If I echo the variables directly inside this script, I get a complete list of all the records in the wkly_forms table that have '01' in the 'week' column.

However, If I try include this script in the following page, the last record meeting the criteria is always displayed:

<?php
include("get_data_picks.php");
?>
<html>
<head>
<title>Week 01 Summary</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

This is only one table row from from my web page. On the actual page, I would like to repeat this row for each record in the database that meets the criteria, "WHERE (week = '1').

I realize that there is a major flaw in this coding because the printf statements are run after the loop has gone through all the records and only the last record is being refferenced, but I am completely ignorant on how I would correct it.

Is there a way to put the html inside the loop? Is there a way to specify which record in the array I wish to print? Any guidance or reccomendations would be GREATLY APPRECIATED!!

Thanks,
Deadlast99

P.S. Sorry about the code not being formatted correctly, I'm still trying to figure out how to use the site.

tsinka

To have highlighted code simply put [ PHP] and [ /PHP] around the code (without the spaces between [ P and [ /)

Thomas

tsinka

Hi,

in general you can do something like this:

<html>
  <head>
    <title>the title</title>
  </head>
  <body>
<?PHP
// open the connection 
$conn = mysql_connect("host","database","password"); 

// pick the database to use 
mysql_select_db("database",$conn); 

// create the SQL statement 
$sql = "SELECT * FROM wkly_forms WHERE (week = '01')"; 

// execute the SQL statement 
$result = mysql_query($sql, $conn) or die(mysql_error()); 

echo "<table>\n";

while ($newArray = mysql_fetch_array($result)) { 
  echo <<<EOHTML

  <tr> 
    <td bordercolor="#FFCC00">
      <b>{$newArray['name']}</b>
    </td> 

EOHTML;

  for ($cnt=1;$cnt<=16;$cnt++) {
    $idx = sprintf("gm_%02d",$cnt);
    echo <<<EOHTML

<td bordercolor="#33FF00" align="center"> 
  <img src="/weekly_summary/images/small_icons/{$newArray[$idx]}.gif">
</td> 

EOHTML;
  }

  echo <<<EOHTML

<td bordercolor="#FFCC00" align="center"> 
  <b>{$newArray['tot_pts']}</b>
</td> 
  </tr>

EOHTML;

}
?>
    </table>
  </body>
</html>

I hope I have no errors in my code ...
Make sure that there are no whitespaces at the end of the line echo <<<EOHTML; and EOHTML; because that would cause an error.

Thomas

paulnaj

If you look carefully, you will notice that you are OVERWRITING each variable on every pass of the while loop.

Take, for example, the variable $wkly_forms_name . You can only store one value in this variable.

while ($newArray = mysql_fetch_array($result)) {
$wkly_forms_name = $newArray['name'];
... (other assignments)
}

Thus, after the loop has ended $wkly_forms_name contains the LAST value assigned to it.

You could solve this in anumber of ways ... here's one ... it might work for you ...

while ($newArray = mysql_fetch_array($result)) {
$wkly_forms_name[] = $newArray['name'];
... (other assignments)
}

... this will store an array of names.

Another way would be to build the HTML output in the actual loop.

See how you get on.

Paul.

Deadlast99

tsinka,

The code you supplied was EXACTLY what I was looking for. I was able to use the code you supplied exactly as it was written to accomplish what I wanted to do . However, being the 'PHP Rookie' that I am, I have to admit that I do not completely understand it. Could you explain <<<EOHTML and EOHTML, or recommend where I could read up on it.

Thanks again for your help!

Deadlast99

tsinka

Glad to hear that 🙂

This is called "heredoc" syntax. The advantage of this syntax is that you don't have to mess around with e.g. esacping double quotes. It is especially useful if you have large blocks of HTML code with a few PHP variables in it

You could also split the php block and then use <?=$variable?> or <?=functionname($var)?>:

<?PHP
  $a="A";
  $b="B";
?>
<p><?=$a?></p>
<p><?=$b?></p>
<?PHP
  // do some other stuff
?>

You can find info about it "here".

Thomas

Deadlast99

tsinka,

Thanks again for the help and also the info on the "heredoc" syntax! For me, discovering this site was like winning the lottery! I've read hundreds and hundreds of pages of text and not learned nearly as much as I have on this site! I can't thank you enough for ending my frustration!

Thanks,
Deadlast99