How do u query DB in loop, and change field name?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

How do u query DB in loop, and change field name?

Post by simonmlewis »

This produces rows of a table, and each row echos firstname1, lastname1..... firstname2, lastname2.... and so on.

When the administrator goes to EDIT a record, I want to use the same table, because they may want to ADD a user (so I would need to keep the addition fields (goes up to 14), but enter the firstname and lastnames of those in firstname1, lastname1.... as they are entered in the database fieldnames of the same.

I started, but am stuck, as I need to do something like:

Code: Select all

SELECT * FROM diary WHERE bookingid = '$bookingid' AND firstname&count <> NULL...
And as it goes over and over, it goes firstname1, firstname2......
Otherwise, I will have to perhaps produce 14 of these $result2 queries!!

Here's the code as it stands now.

Code: Select all

      echo "<tr><td><input type='text' name='firstname$counter'";
      
      $result2 = mysql_query ("SELECT * FROM diary WHERE bookingid = '$bookingid'")or die(mysql_error());
      while ($row2 = mysql_fetch_object($result2))
        {
        if ($row->firstname1
        } mysql_free_result($result2);
      
      echo "></td><td><input type='text' name='lastname$counter'></td></tr>";
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do u query DB in loop, and change field name?

Post by simonmlewis »

Cracked it.

Code: Select all

$result2 = mysql_query ("SELECT firstname, lastname FROM diary WHERE bookingid = '$bookingid'")or die(mysql_error());
      $num_rows2 = mysql_num_rows($result2);
      $rowcount = 1;
      while ($row2 = mysql_fetch_object($result2))
        {
        if ($rowcount <= 14)
          {
          echo "<tr><td><input type='text' name='firstname$counter' value='$row2->firstname $rowcount'></td>
        <td><input type='text' name='lastname$counter' value='$row2->lastname'></td></tr>";
          $rowcount = $rowcount + 1;
          }
        }
        
    $calc = 14 - $rowcount;
    $counter = 1;
    $calc = $calc + 1;
    while ($counter < $calc)
      {
      echo "<tr><td><input type='text' name='firstname$counter'></td>
        <td><input type='text' name='lastname$counter'></td></tr>";
      $counter = $counter + 1;
      }
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply