Query to form help

Hi guys, im new to this forum but having looked at the topics and level of help given i think it will become my new home… Anyway enough of that for now, here is my problem…

Im trying to query my database to show the rows in question on screen but i want the ability to edit the rows too. For example when the row is displayed it will have a tick box to say authorised or not and i want to be able to tick it and update the relevant field.

This is how the query is displayed but i cant figure out how to get the tick boxes working.

This is how its done so far

<?php 
  $query = "SELECT * FROM clock_status , staff
  WHERE auth = 0 and clock_status.user_id = staff.staff_number
  and DATE(date_time) = DATE(NOW()) ORDER BY date_time ASC";
  // (YEARWEEK(date_time)=YEARWEEK(CURRENT_TIMESTAMP)
   $result = mysql_query($query);
    echo "<table border='1'>
    <tr>
    <th>Name</th>
    <th>Site</th>
    <th>Clocked In</th>
    <th>Status</th>
    <th>Clocked Out</th>
    <th>Sickness</th>
    <th>Holiday</th>
    <th>Auth ?</th>
    ";
                                                                                                
    while ($row = mysql_fetch_assoc($result)) {
    echo "<tr>";
    echo "<form id=\\"auth\\" action=\\"manager_amend.php\\" method=\\"post\\">";
    echo "<td>" .$row['name']."</td>";
    echo "<td>" .$row['location']."</td>";
    echo "<td>" .$row['date_time']."</td>";
      if ($row['status'] == "1") {
        echo "<td>" ."Clocked In"."</td>";
      } else {
        echo "<td>" ."Clocked Out"."</td>";
      }
        echo "<td>" .$row['amend_time']."</td>";
        echo "<td>" ."<input type=\\"checkbox\\" name=\\"sickness\\" value=\\"yes\\">" . "</td>";
        echo "<td>" ."<input type=\\"checkbox\\" name=\\"holiday\\" value=\\"yes\\">" . "</td>";
        echo "<td>" ."<input type=\\"checkbox\\" name=\\"auth\\" value=\\"yes\\">" . "</td>";
      }
        echo "</tr>";
        echo "</table>";                                                                        
?>
<br />
<input type="submit" name="submit" value="Accept">
</form>

A point in the right direction would be greatly appreciated…

Thanks,
Mark.

So for each row that is selected, you’d like to be able to edit each row? Make the value of the check box the primary key. On the next page pull up the $_POST array and do a for each{} with the relevant fields to edit the data. Might be easier to simply make a text link to an “edit.php” that would edit just one entry at a time.

Since PHP is server side processing, you will probably need to use some JAVASCRIPT in order to do what you want, that is unless you want to select a row, click a button and then have another form designed to do the editing.

If you want to have another form, that is an easy thing as well, just have the tick box value be the row ID and pass that to the new edit form, do the database retrieval of the data into the applicable fields, have the user edit them, and have another button to do the update.

Hi guys thanks for the replies.

Im not sure i can make the value of the check box the primary key as i already have an ID field in the DB as a primary key. Can i have more than one primary key?

As for using JavaScript, id rather not have to if i can help it because of 2 reasons, 1, JavaScript could be disabled on the browser, 2, i don’t understand JavaScript…

I wouldn’t mind having a second page to edit the users but wondered if there was a simpler way as there could be potentially 100 or more entries per day from that query.

The main issue with this code is that you open multiple forms, never close it and only have one submit button.

If you want to be able to edit each row separately - create a separate form for each record with it’s own submit button. Pass id of that record in a hidden field:

echo "<input type='hidden' name='id' value='".$row['id']."'>";

If you want to edit all rows together you only need one form. You’ll need to loop through the whole form then building update SQL query for each row.

Hi, thanks there phprunner, I like the second option best as it would be a logistical nightmare if each entry had its own form.

As you have probably guessed i am new to php and have only been using it for a week so please bear with me. Could you possibly give me an example of how i would loop it.

Also when you said i open multiple forms and never close it, i take it you dont mean the /form tag at the very bottom?

Thanks again for the help and sorry for the silly questions.

This should work better … as far ass I understand


<?php 
  $query = "SELECT * FROM clock_status , staff
  WHERE auth = 0 and clock_status.user_id = staff.staff_number
  and DATE(date_time) = DATE(NOW()) ORDER BY date_time ASC";
  // (YEARWEEK(date_time)=YEARWEEK(CURRENT_TIMESTAMP)
   $result = mysql_query($query);
    echo "<form id=\\"auth\\" action=\\"manager_amend.php\\" method=\\"post\\">
<table border='1'>
    <tr>
    <th>Name</th>
    <th>Site</th>
    <th>Clocked In</th>
    <th>Status</th>
    <th>Clocked Out</th>
    <th>Sickness</th>
    <th>Holiday</th>
    <th>Auth ?</th>
	</tr>
    ";
                                                                                                
    while ($row = mysql_fetch_assoc($result)) {
    echo "<tr>";
    echo "<td>" .$row['name']."</td>";
    echo "<td>" .$row['location']."</td>";
    echo "<td>" .$row['date_time']."</td>";
      if ($row['status'] == "1") {
        echo "<td>" ."Clocked In"."</td>";
      } else {
        echo "<td>" ."Clocked Out"."</td>";
      }
        echo "<td>" .$row['amend_time']."</td>";
        echo "<td>" ."<input type=\\"checkbox\\" name=\\"sickness\\" value=\\"yes\\">" . "</td>";
        echo "<td>" ."<input type=\\"checkbox\\" name=\\"holiday\\" value=\\"yes\\">" . "</td>";
        echo "<td>" ."<input type=\\"checkbox\\" name=\\"auth\\" value=\\"yes\\">" . "</td>";
    echo "</tr>";
}                                                                  
?>
</table>
<input type="submit" name="submit" value="Accept">
</form>

Also, it is better to use single quote instead of double quote when echoing as when using double quote, it tells php to look for variable and it is useless in the current case.

As turb pointed out you needed to move the opening <form> tag outside of while loop.

Guys again i cant thank you enough.

Ill make the suggested changes tomorrow and see how i get on.

Thanks again.

Ok, ive made quite a bit of change to this page with a view of making it easier to use and design.

So basically im wondering now if there is any way i can pass some of the data from the database query to a URL id.

for example…

<?php echo $row['id'] : ?>

<= as fetched from the query

then i want that ‘id’ value passed to a link something like

<a href="www.mysite.com?id=$id">Link</a> 

Is it possible to achieve this?

Thanks in advance.

Use

<a href="http://www.mysite.com?id=<?php echo $id;?>">Link</a>

or

echo '<a href="http://www.mysite.com?id=$id">Link</a>';

Following wont work:

echo '<a href="http://www.mysite.com?id=$id">Link</a>';

Use

echo '<a href="http://www.mysite.com?id=' . $id . '">Link</a>';

or

echo "<a href=\\"http://www.mysite.com?id=$id\\">Link</a>";