Search code examples
phpmysqlalter

edit button to mysql

I got my database to work, and I can add my data and get return on it again. That is perfect, because it is the first time that I get it to work, and it opend up a lot of possibilies. So my next project here, is to ALTER my table in MySQL with a button. Until now it looks like this:

Image

I can add a date, day, fromtime and totime. But I would like to have the possibility to change fx the day, if I make a mistake when I add the values to my database. I started on making an edit button in the right hand side. So overtime I make a new row, there will come a new edit button. But does anybody know how I can asign my button to the ALTER TABLE query? Or maybe a hint how to do it?

Best Regards to all From Mads

EDITED CODE:

I have made the primary key in the database p_id. I also get a return from the p_id

  <html>

<head>
    <link rel="stylesheet" type="text/css" href="css/arrangeTables.css">

</head>

<body>

    <form method="post">
        <h3>Add your worktime to database</h3><br>
            <input type="date" name="date"><br><br>
            <select name="day">
                <option value="Mandag">Mandag</option>
                <option value="Tirsdag">Tirsdag</option>
                <option value="Onsdag">Onsdag</option>
                <option value="Torsdag">Torsdag</option>
                <option value="Fredag">Fredag</option>
                <option value="Lørdag">Lørdag</option>
                <option value="Søndag">Søndag</option>
            </select>
            <input type="time" name="fromtime">
            <input type="time" name="totime">
            <input type="submit" value="submit"><br><br>

    </form>


</body>

<?php
$username = "root";
$password = "root";
$hostname = "127.0.0.1:3306"; 

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password) 
 or die("Unable to connect to MySQL");
echo "Connected to MySQL<br><br>";

//select a database to work with
$selected = mysql_select_db("danskebank",$dbhandle) 
  or die("Could not select any database");

// Insert to database
$date = $_POST['date'];
$day = $_POST['day'];
$fromtime = $_POST['fromtime'];
$totime = $_POST['totime'];

$sql = "INSERT INTO addWorkTime(date, day, fromtime, totime) VALUES('$date', '$day', '$fromtime', 'totime')";

$result = mysql_query($sql);

//Return records from database
$result = mysql_query("SELECT date, day, fromtime, totime FROM addWorkTime");
?>



    <!-- Return from the database -->
    <h3>Return from database:</h3><br>

  <!-- headers -->
        <tr>
            <th class="column1">Date</th>
            <th class="column2">Day</th>
            <th class="column3">From</th>
            <th class="column4">To</th>
        </tr>

        <!-- Now a row for each new set of data, here you probably need to
        loop through some data set you retrieve from the database -->
        <?php while($row = mysql_fetch_array($result)): ?>
        <table>
          <tr>
            <td class="resultcolumn4"><?php echo $row{'p_id'};?></td>
            <td class="resultcolumn1"><?php echo $row{'date'};?><br></td>
            <td class="resultcolumn2"><?php echo $row{'day'};?></td>
            <td class="resultcolumn3"><?php echo $row{'fromtime'};?></td>
            <td class="resultcolumn4"><?php echo $row{'totime'};?></td>
            <td><a href='link_to_the_add_or_edit?id='.<?php $row['id'] ?></td>

            <?php

            $id=$_GET['id'];

            echo '<input type="hidden" name="name_of_hidden_input" value='.$id.'>';
            //and the rest of the form

                if($_GET['submit']){

                    //Some mysql injection prevention first
                    update danskebank SET date=['?'] where id= $_GET['name_of_hidden_input']
                }
            ?>
          </tr>
          <?php endwhile; ?>
    </table>
</html>
Solution

  • To edit the specific row, you would need a Primary key in your mysql table. For example you call it: id. Now you would need to get the id from the table as well: SELECT id, date, day, fromtime, totime FROM addWorkTime

    Use the $row['id']; in the while loop and replace the : <input type="button" value="Edit"> to: <a href='link_to_the_add_or_edit?id='.<?php $row['id'] ?> now your url will look like: link_to_the_add_or_edit?id=1 and you can use: $_GET['id'] on the link_to_the_add_or_edit page. Now when you're on that page, you make sure you remember that id(SESSIONS) so you can use it on the submit action when you fill in the values. Example of session:

    session_start();
$_SESSION['id']=$_GET['id'];

    on the link_to_the_add_or_edit page. After this you can update the row you want like this(when you submit something):

    update danskebank SET date=['?'] where id= $_SESSION['id']

    EDIT(regarding DarkBee's comment):

    Instead of using sessions here, you can also store the $_GET['id'] in a hidden field like this:

    $id=$_GET['id'];

echo '<input type="hidden" name="name_of_hidden_input" value='.$id.'>';
//and the rest of the form

if($_GET['submit']){

   //Some mysql injection prevention first

   update danskebank SET date=['?'] where id= $_GET['name_of_hidden_input']
}

    and in the query use: $_GET['name_of_hidden_input'];