PHP - Generating An Array From Mysql Table

Hi, I'm trying to make a dynamic html table to contain the mysql data that is generated via php. I'm trying to display a user's friends in a table of two columns and however many rows, but can't seem to figure out what is needed to make this work.

Here's my code as it stands:
Code: [Select]
<?php
//Begin mysql query
$sql = "SELECT * FROM friends WHERE username = '{$_GET['username']}' AND status = 'Active' ORDER BY friends_with ASC";
$result = mysql_query($sql);
$count = mysql_num_rows($result);

$sql_2 = "SELECT * FROM friends WHERE friends_with = '{$_GET['username']}' AND status = 'Active' ORDER BY username ASC";
$result_2 = mysql_query($sql_2);
$count_2 = mysql_num_rows($result_2);

while ($row = mysql_fetch_array($result)) {
                                                          echo $row["friendswith"] . "<br>";
                                                }
while ($row_2 = mysql_fetch_array($result_2)) {
                                                          echo $row_2["username"] . "<br>";
                                                }
?>

The above simply outputs all records of a user's friends (their usernames) in alphabetical order. The question of how I'd generate a new row each time a certain amount of columns have been met, however, is beyond me. Anyone know of any helpful resources that may solve my problem? Thanks in advance =)

Hello everyone,

Sorry if this has been answered but if it has I can't find it anywhere. So, from the begining then.  Lets say I had a member table and in it I wanted to store what their top 3 interests are.  Their$ row has all the usual things to identify them userID and password etc.. and I had a further 3 columns which were labled top3_1 top3_2 & top3_3 to put each of their interests in from a post form.

If instead I wanted to store this data as a PHP Array instead (using 1 column instead of 3) is there a way to store it as readable data when you open the PHPmyadmin?

At the moment all it says is array and when I call it back to the browser (say on a page where they could review and update their interests) it displays 'a' as top3_01 'r' as top3_02 and 'r' as top3_03 (in each putting what would be 'array' as it appears in the table if there were 5 results. Does anyone know what I mean?

For example -

If we had a form which collected the top 3 interests to put in a table called users,

Code: [Select]
<form action="back_to_same_page_for_processing.php" method="post" enctype="multipart/form-data">
   
    <input name="top3_01" type="text" value="enter interest number 1 here" />
    <input name="top3_02" type="text" value="enter interest number 2 here" />
    <input name="top3_03" type="text" value="enter interest number 3 here" />
    <input type="submit" name="update_button" value=" Save and Update! " />

   
    </form> // If my quick code example for this form is not correct dont worry its not the point im getting at :)
And they put 'bowling' in top3_01, 'running' in top3_02 and 'diving' in top3_03 and we catch that on the same page with some PHP at the top -->

Code: [Select]
        if (isset($_POST)['update_button']) {
   
    $top3_01 = $_POST['top3_01']; // i.e, 'bowling' changing POST vars to local vars
    $top3_02 = $_POST['top3_02']; // i.e, 'running'
    $top3_03 = $_POST['top3_03']; // i.e, 'diving'

With me so far? If I had a table which had 3 columns (1 for each interest) I could put something like -

  Code: [Select]
   include('connect_msql.php');
   
    mysql_query("Select * FROM users WHERE id='$id' AND blah blah blah");
   
    mysql_query("UPDATE users SET top3_01='$top3_01', top3_02='$top3_02', top3_03='$top3_03' WHERE id='$id'");


And hopefully if ive got it right, it will put them each in their own little column. Easy enough huh? But heres the thing, I want to put all these into an array to be stored in the 1 column (say called 'top3') and whats more have them clearly readable in PHPmyadmin and editable from there yet still be able to be called back an rendered on page when requested.

Continuing the example then, assuming ive changed the table for the 'top3' column instead of individual colums,  I could put something like this -


Code: [Select]
            if (isset($_POST)['update_button']) {
       
        $top3_01 = $_POST['top3_01']; // i.e, 'bowling' changing POST vars to local vars
        $top3_02 = $_POST['top3_02']; // i.e, 'running'
        $top3_03 = $_POST['top3_03']; // i.e, 'diving'
   
    $top3_array = array($top3_01,$top3_02,$top3_03);
   
    include('connect_msql.php');
   
    mysql_query("UPDATE members SET top3='$top3_array' WHERE id='$id' AND blah blah blah");
But it will appear in the column as 'Array' and when its called for using a query it will render the literal string. a r r in each field instead.  Now I know you can use the 'serialize()' & 'unserialize()' funtcions but it makes the entry in the database practically unreadable. Is there a way to make it readable and editable without having to create a content management system?

If so please let me know and I'll be your friend forever, lol, ok maybe not but I'd really appreciate the help anyways.  The other thing is, If you can do this or something like it, how am I to add entries to that array to go back into the data base?

I hope ive explained myself enough here, but if not say so and I'll have another go.  Thanks very much people,  L-PLate (P.s if I sort this out on my own ill post it all here)

As a complete newbie, I'm not sure Ive got the terminology right in my subject header. But this is what I am trying to do.

I have a soccer stats site with data entered into each of numerous 'seasons', accessible via a drop-down menu, as he
http://www.fleethistory.co.uk/stats/matches.php

Now regardless of what season I select, the URL will stay the same as above. What I want to do is have users go direct to a particular season from a link without having to use the drop-down. How would I generate a unique URL for each of the seasons listed in the drop-down?

Don't know if you need the PHP code, but here it is just in case:



Code: [Select]
<?php
/*
***************************************************************************
*   tplSoccerStats
*   -----------------------------------------------------------------------
*   Copyright: TPL Design
*   email:     info@tpl-design.com
*   www:       www.tpl-design.com/tplsoccerstats
***************************************************************************
*/

// Starts a new session or resumes the current one.
session_start();

// Get db host, db username, db password, and db name from admin/user.php
// (each team has its own db).
include('admin/user.php');

// Establish db connection. Select appropriate team db.
$connection = mysql_connect("$host", "$user", "$password") or die(mysql_error());
mysql_select_db("$txt_db_name", $connection) or die(mysql_error());

// Get preferences stored in db.
$sql = "SELECT * FROM tplss_preferences WHERE ID = '0'";
$pref = mysql_query($sql, $connection) or die(mysql_error());
$pdata = mysql_fetch_array($pref);
mysql_free_result($pref);

// Include php preferences.
include('preferences.inc');

// Use session defaults if available, otherwise set them from database values.
if ((!isSet($_SESSION['defaultseasonid_tplss']))    ||
    (!isSet($_SESSION['defaultmatchtypeid_tplss'])) ||
    (!isSet($_SESSION['defaultlanguage_tplss'])))    {

    $_SESSION['defaultseasonid_tplss'] = $pdata['DefaultSeasonID'];
    $_SESSION['defaultmatchtypeid_tplss'] = $pdata['DefaultMatchTypeID'];
    $_SESSION['defaultlanguage_tplss'] = $pdata['DefaultLanguage'];
}
$defaultseasonid = $_SESSION['defaultseasonid_tplss'];
$defaultmatchtypeid = $_SESSION['defaultmatchtypeid_tplss'];
$defaultlanguage = $_SESSION['defaultlanguage_tplss'];

// Include language based global vars, in our case english, e.g. $txt_preview = 'Preview'
include('language.inc');

// Include pafc banner and menu bar.
include('header.php');

// Start an html form.
echo '<form method="post" action="change.php">' . "\n";

// Print images at top of form if we have them (we don't).
$image_url = "images/header.jpg";
$image_url2 = "images/header.gif";
if(file_exists($image_url) || file_exists($image_url2))
{
  echo '<table align="center" width="' . $tb_width . '" cellspacing="0" cellpadding="0" border="0">' . "\n";
  echo "<tr>\n";
  echo '<td align="center">' . "\n";

  if (file_exists($image_url)) {
    echo '<img src="' . $image_url . '" ALT=""><br>' . "\n";
  }
  elseif (file_exists($image_url2)) {
    echo '<img src="' . $image_url2 . '" ALT="">' . "\n";
  }
  echo "</td></tr></table>\n\n";
}

?>

<!-- Print change bar table -->
<table align="center" width="<?php echo $tb_width ?>" cellspacing="0" cellpadding="0" border="0" bgcolor="<?php echo $border_c ?>">
<tr>
<td>
<table width="100%" cellspacing="1" cellpadding="5" border="0">
<tr>
<td bgcolor="<?php echo $inside_c ?>" align="center">
<?= $txt_change ?>:
<select name="season">
<option value="0"><?= $txt_all ?></option>
<?php
$sql = "SELECT * FROM tplss_seasonnames WHERE SeasonPublish = '1' ORDER BY SeasonName DESC";
$get_seasons = mysql_query($sql, $connection) or die(mysql_error());
while($data = mysql_fetch_array($get_seasons))
{
  if($data['SeasonID'] == $defaultseasonid)
    echo '<option value="' . $data['SeasonID'] . '" SELECTED>' . $data['SeasonName'] . "</option>\n";
  else
    echo '<option value="' . $data['SeasonID'] . '">' . $data['SeasonName'] . "</option>\n";
}
mysql_free_result($get_seasons);
?>
</select>&nbsp;or Competition:&nbsp;
<select name="matchtype">
<option value="0"><?= $txt_all ?></option>
<?php
$sql = "SELECT * FROM tplss_matchtypes ORDER BY MatchTypeName";
$get_types = mysql_query($sql, $connection) or die(mysql_error());
while($data = mysql_fetch_array($get_types))
{
  if($data['MatchTypeID'] == $defaultmatchtypeid)
    echo '<option value="' . $data['MatchTypeID'] . '" SELECTED>' . $data['MatchTypeName'] . "</option>\n";
  else
    echo '<option value="' . $data['MatchTypeID'] . '">' . $data['MatchTypeName'] . "</option>\n";
}
mysql_free_result($get_types);
?>
</select> <input type="submit" name="submit" value="Select">

</td>
</tr>
</table>
</td>
</tr>
</table>

<!-- Print outer fixture tables -->
<table align="center" width="<?php echo $tb_width ?>" cellspacing="0" cellpadding="0" border="0" bgcolor="<?php echo $border_c ?>">
<tr>
<td>
<table width="100%" cellspacing="1" cellpadding="5" border="0">
<tr>
<td bgcolor="<?php echo $inside_c ?>" align="center">

<!-- Print inner fixture table -->
<table width="<?= $tb2_width ?>%" cellspacing="0" cellpadding="4" border="0">

<tr>
<td align="left" valign="middle" bgcolor="#D01818">
<font color="#FFffff"><b>Date</b></font>
</td>

<td align="center" valign="middle" bgcolor="#D01818">
<font color="#FFffff"><b>H/A</b></font>
</td>

<td align="left" valign="middle" bgcolor="#D01818">
<font color="#FFffff"><b>Opponent</b></font>
</td>

<td align="left" valign="middle" bgcolor="#D01818">
<font color="#FFffff"><b>Competition</b></font>
</td>

<td align="center" valign="middle" bgcolor="#D01818" colspan="2">
<font color="#FFffff"><b>Result</b></font>
</td>

<td align="center" valign="middle" bgcolor="#D01818">
<font color="#FFffff"><b>Att</b></font>
</td>

<td align="center" valign="middle" bgcolor="#D01818">
<font color="#FFffff"><b>Details</b></font>
</td>
</tr>

<?php
// Construct db query to get fixtures from database.
$selectClause = "SELECT
                 M.MatchID AS id,
         M.MatchAdditionalType AS additype,
         O.OpponentName AS opponent,
         O.OpponentID AS oppid,
         M.MatchGoals AS goals,
         M.MatchGoalsOpponent AS goals_opponent,
         M.MatchPenaltyGoals AS penalty_goals,
         M.MatchPenaltyGoalsOpponent AS penalty_goals_opponent,
         M.MatchOvertime AS overtime,
         M.MatchPenaltyShootout AS penalty_shootout,
         DATE_FORMAT(M.MatchDateTime, '%M %Y') AS month,
         DATE_FORMAT(M.MatchDateTime, '%a %e') AS date,
         DATE_FORMAT(M.MatchDateTime, '%H:%i') AS time,
         M.MatchPlaceID AS place,
                 M.MatchAttendance AS att,
         M.MatchPublish AS publish,
         MT.MatchTypeName AS typename,
         P.PreviewText AS prewtext
         FROM ( tplss_matches M, tplss_matchtypes MT, tplss_opponents O )
         LEFT OUTER JOIN tplss_previews P ON M.MatchID = P.PreviewMatchID ";

if (($defaultseasonid != 00) && ($defaultmatchtypeid != 0)) {
  $whereClause = "WHERE M.MatchTypeID = '$defaultmatchtypeid'
                  AND M.MatchSeasonID = '$defaultseasonid'
                  AND M.MatchTypeID = MT.MatchTypeID
          AND M.MatchOpponent = O.OpponentID ";
} elseif (($defaultseasonid == 0) && ($defaultmatchtypeid != 0)) {
  $whereClause = "WHERE M.MatchTypeID = '$defaultmatchtypeid'
          AND M.MatchTypeID = MT.MatchTypeID
          AND M.MatchOpponent = O.OpponentID ";
} elseif (($defaultseasonid != 0) && ($defaultmatchtypeid == 0)) {
  $whereClause = "WHERE M.MatchSeasonID = '$defaultseasonid'
          AND M.MatchTypeID = MT.MatchTypeID
          AND M.MatchOpponent = O.OpponentID ";
} elseif (($defaultseasonid == 0) && ($defaultmatchtypeid == 0)) {
  $whereClause = "WHERE M.MatchTypeID = MT.MatchTypeID
          AND M.MatchOpponent = O.OpponentID ";
}

$orderByClause = "ORDER BY M.MatchDateTime";

// Execute query.
$sql = $selectClause . $whereClause . $orderByClause;
$get_matches = mysql_query($sql, $connection) or die(mysql_error());

// Loop round fixtures (which come back from database in date order).
while($data = mysql_fetch_array($get_matches))
{
  // Print a month header row each time we hit a new month.
  if ($data['month'] <> $lastMonth) {
    echo "<tr>\n";
    echo '<td align="left" valign="middle" bgcolor="#7c0606" colspan="9">';
    echo '<font color="#FFffff"><b>' . $data['month'] . "</b></font>";
    echo "</td>\n";
    echo "</tr>\n\n";
    $lastMonth = $data['month'];
  }

  // Assign home/away based vars.
  if ($data['place'] == 1) {
    $placeBg = $bg4;
    $venue = "H";
  } else {
    $placeBg = $bg3;
    $venue = "A";
  }

  // Print date and venue.
  echo"<tr>\n";
  echo '<td align="left" valign="middle" bgcolor="' . $placeBg . '">';
  echo $data['date'];
  echo "</td>\n";

  echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '">';
  echo $venue;
  echo "</td>\n";

  // Print opponent team name (as a link if possible).
  echo '<td align="left" valign="middle" bgcolor="' . $placeBg . '">';
  if ($data['oppid'] == 1) {
    echo '$data[opponent]';
  } else {
    echo '<a href="opponent.php?opp=' . $data['oppid'] . '">' . $data['opponent'] . "</a>";
  }
  echo "</td>\n";

  // Print competition type.
  echo '<td align="left" valign="middle" bgcolor="' . $placeBg . '">';
  echo $data['typename'];
  if ($data['additype'] != '') {
    echo " / " . $data['additype'];
  }
  echo "</td>\n";

  // Print result, attendance and match report.
  if ($data['goals'] == NULL || $data['goals_opponent'] == NULL) {
    // No goals recorded - match can't have been played yet - print empty fields.
    echo '<td bgcolor="' . $placeBg . '">&nbsp;</td>' . "\n";
    echo '<td bgcolor="' . $placeBg . '">&nbsp;</td>' . "\n";
    echo '<td bgcolor="' . $placeBg . '">&nbsp;</td>' . "\n";
    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '">';
    if ($data['prewtext'] == '') {
      echo "&nbsp;";
    } else {
      echo '<a href="preview.php?id=' . $data['id'] . '">' . $txt_preview . "</a>";
    }
    echo "</td>\n";
  } else {
    // Goals recorded - figure out result and score - print required fields.
    if ($data['penalty_goals'] == NULL || $data['penalty_goals_opponent'] == NULL) {
      if ($data['goals'] > $data['goals_opponent'])
$result = '<img src="images/win.jpg">';

      elseif ($data['goals'] < $data['goals_opponent'])
$result = '<img src="images/lose.jpg">';
      else
$result = '<img src="images/draw.jpg">';
      $score = $data['goals'] . " - " . $data['goals_opponent'];
    } else {
      if ($data['penalty_goals'] > $data['penalty_goals_opponent'])
$result = "<b>W</b>";
      else
$result = "L";
      $score = $data['goals'] . " - " . $data['goals_opponent'] . " (" . $data['penalty_goals'] . " - " . $data['penalty_goals_opponent'] . ")";
    }
    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '">' . $result . "</td>\n";
    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '">' . $score . "</td>\n";
    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '">' . $data['att'] . "</td>\n";
    if($data['publish'] == 1)
    {
    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '"><a href="matchdetails.php?id=' . $data['id'] . '">Yes</td></a></td>' . "\n";
    }
    else
    {
    echo '<td align="center" valign="middle" bgcolor="' . $placeBg . '"> </td></a></td>' . "\n";
    }
  }

  echo "</tr>\n\n";
}

// Free resultset.
mysql_free_result($get_matches);

?>
</table>

</td>
</tr>
</table>
</td>
</tr>
</table>
<?php
// Print tpl soccers stats message.
include('bottom.txt');
?>
</form>

<?php
// Finish off html.
include('footer.php');
?>

I am creating a drink recipe site.

So far I have created the mysql table and the page where i can enter in the drink name ingredients ect. and it inserts into the table.

Could some one point me in the right direction where when I make an entry into this table a new page is created from the content.

How would I go about doing this?