Arduino: SQLite3, PHP, Very basic script

RensD · January 5, 2014

Hello all,

Introducing myself

My name is Rens and I'm from the Netherlands. I'm completely new at PHP.

Since I'm absolutely not new at programming, I reckoned I would be able to handle this.

However...... I seem to have chewed more off than I can swallow.......

This will be an extensive post

Since I see a lot of questions on this forum (and all other forums by the way) after a first post has been made, I am trying to give you all information here, so you get a good picture of what I have done en what the problems are. Doing so, this has become a pretty big post, but I hope you guys appreciate this.

What am I trying to accomplish

I am running a PHP script on an Arduino. This is on Linux OpenWRT.

This device is smaller than a deck of cards and contains for instance a full web server and much more.

The website I need is just a maintenance site for the database.

One customer, the owner of the device (so login needed), can enter or alter his email address here, and can enter or alter a list of devices and their location. Data is stored in a SQLite3 database. All other functionality is handled in Python, so out of this scope.

The website should look somewhat like this concept:

The script grew and grew and grew.......

The script I'm using started out very basic, but since I ran into problems, I added a lot of debug information to find out what is wrong.

Also I tried to put information into separate variables. Not necessary, I know.... but I was trying to figure things out.

This is the script I'm posting here however in it's complete form, so I can clarify that the trouble is.

(Please see the script at the bottom of this post.)

Screens-hots of what happens

Some things work, some things don't work.

Basically: Entering new stuff works. Modifying or deleting stuff does not.

Handling the email address

New database, completely empty. First time opening the website.

I enter a new email address and press enter.

All looks fine. The email address has been entered and is shown in the inputbox.

However.... Looking the the database:

The email address has been entered TWO times.........?

WHY?

Altering the email address is a different story.

I enter a modified email address.

After pressing 'enter': nothing gas changed
All is back to 'new@email.com' end has not been changed into 'modified@email.com'.

Why are there TWO entries in the $email_array? Element [0] and element ..........?

And looking in the database, now there are 4 email entries.

And for every time I handle the email address, two more entries will ben made.

Handling the devices

If I enter a NEW device, with of without location, all works well.

Here I have entered 3 new devices:

All seems fine.

Looking in the database:

Yep.... All is fine.

Except I made an user 'oops' in the location.

So, lets modify the location from 'Location oops' into a better sounding location 'Location 3'.

Pressing the button.....

Nope...... Nothing has changed. As you can see in the debug information, the UPDATE command has been triggered, but no value for 'device_id' and 'location 'has been send in the $_POST. How come?
Nothing has been changed in the database as well off cause.

Okay...... Than, let's just delete the oops.

Pressing the delete button:

And here the debug information shows the DELETE command has been issued as to be expected, but again no 'device_id' in the $_POST.

So..... My question

How come, I do not get my information.

What am I missing please?

Can't be that much wrong I think..... but since this is my first attempt at PHP ever, I'm missing the obious I think.

Would you please, please direct me into the right direction?

Friendly greetings
Rens

O.... the script......

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <title>Device maintenance</title>
    </head>
    <body>
        <FONT FACE="courier">
        Debug status!<br>

        <!-- For debugging only -->
        <?php echo "In body: _POST "; print_r($_POST); echo "<br>"; ?>
        __________________________________________________
        <p><p>
        <?-- End of debugging -->

        <?php
            // Connect to the database
            $db = new SQLite3("/mnt/devices.db");

            // Get the email address
            $db->query("CREATE TABLE IF NOT EXISTS email(email varchar(100))");
            $db->query("CREATE TABLE IF NOT EXISTS devices(device_id varchar(20) NOT NULL, location varchar(50))");

            $results = $db->query("SELECT * FROM email");
            $email_array = $results->fetchArray();
            $email = $email_array['email'];
        ?>
        <!-- For debugging only -->
        <?php echo "In body after fetchArray: email_array "; print_r($email_array); echo "<br>"; ?>
        <?php
            if ($email == ""){
                if ($_POST['new_email'] > ""){
                    $email = $_POST['new_email'];
                }
            }
        ?>
        <?php echo "In body: email = " . $email ?>
        <p><p>
        <?-- End of debugging -->

        <!-- Present the current email address and enable changing it. -->
        <form method="post" action="#">
            <input type="text" name="new_email" style="width: 320px" value="<?php echo $email; ?>" />
            <input type="submit" value="Wijzig e-mail" />
        </form>
        After form email, email = <br>
        <pre> <?php echo $email; ?> </pre>

        <!-- Show a table with the attached devices, also make a form of it so the locations can be changed -->
        <form method="post" action="#">
            <table>
                <thead>
                    <tr>
                        <th>ID</th>
                        <th>Locatie</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                        echo "In tbody, _POST = ";
                        print_r ($_POST);
                        echo "<br>";
                        echo "In tbody, _device = ";
                        print_r($device);
                        echo "<br>";
                        echo "In tbody, _email = ";
                        print_r($email);
                        echo "<br>";

                        // Check if form was submitted
                        if(isset($_POST)) {
                            $query = "";

                            // Lets not check if there is anything in the database. Just replace.
                            if (isset($_POST['new_email'])){
                                // Update or Insert the emailaddres.
                                $query = "INSERT OR REPLACE INTO email (email) VALUES ('{$email}');";
                                echo $query;
                                @$db->exec($query);

                                // Remove the old email address (if any)
                                //$query = "DELETE FROM email ; ";
                                //echo $query;
                                //echo "<BR>";
                                //@$db->exec($query);

                                // Put in the new email address
                                //$query = "INSERT INTO email VALUES ('{$email}');";
                                //echo $query;
                                //echo "<BR>";
                                //@$db->exec($query);
                            }
                                                                                                                                                                                                                                            

                            // Check if new device was added or a device was modified
                            if(isset($_POST["new_device"])) {
                                // Insert a new device into the table
                                $query ="INSERT INTO devices('device_id', 'location') values('{$_POST["device_id"]}', '{$_POST["device_location"]}')";
                                echo $query;
                            } else if(isset($_POST["modify_device"])) {
                                // Add a query to update a device
                                $query = "UPDATE devices SET location = '{$_POST["device_location"]}' WHERE device_id = '{$_POST["device_id"]}'";
                                echo $query;
                            } else if(isset($_POST["delete_device"])) {
                                // Add a query to delete a device
                                $query = "DELETE FROM devices WHERE device_id = '{$_POST["device_id"]}'";
                                echo $query;
                            }
                            @$db->exec($query);
                        }

                        $results = $db->query("SELECT * FROM devices");

                        // -- For debugging only --
                        print "After handling posts, _device = ";
                        print_r($device);
                        echo "<BR>";
                        // -- End of debugging --

                        while ($device = $results->fetchArray(SQLITE3_ASSOC)) {
                    ?>
                    <tr>
                        <td>
                            <?php echo $device["device_id"]; ?>
                            <input type="hidden" name="device_id" value="<?php echo $device["device_id"]; ?>" />
                        </td>
                        <td>
                            <input type="text" name="device_location" value="<?php echo $device["location"]; ?>" />
                        </td>
                        <td>
                            <input type="submit" name="modify_device" value="Modify Device" />
                        </td>
                        <td>
                            <input type="submit" name="delete_device" value="Delete device" />
                        </td>
                    </tr>
                    <?php
                        }
                    ?>

                    </tbody>
                    <tfoot>
                        <tr>
                            <td>
                                <input type="text" name="device_id" />
                            </td>
                            <td>
                                <input type="text" name="device_location" />
                            </td>
                            <td>
                                <input type="submit" name="new_device" value="New device" />
                            </td>
                        </tr>
                    </tfoot>
                </table>
            </form>

            <!--End table with devices-->
        </FONT>
    </body>
</html>

Edited January 5, 2014 by RensD

mac_gyver · January 5, 2014

for the email, some of your logic is foo-bar -

$results = $db->query("SELECT * FROM email");
$email_array = $results->fetchArray();
$email = $email_array['email'];
?>
<!-- For debugging only -->
<?php echo "In body after fetchArray: email_array "; print_r($email_array); echo "<br>"; ?>
<?php
if ($email == ""){
if ($_POST['new_email'] > ""){
$email = $_POST['new_email'];
}
}
?>

the code above queries for ALL the rows from the email table, retrieves the first row (assuming at least one row exists) and sets the $email variable from the first row. it only uses the $_POST['new_email'] if there are no rows in the email table or the email address in the first row is empty. which brings us to your code trying to insert the email -

if (isset($_POST['new_email'])){
// Update or Insert the emailaddres.
$query = "INSERT OR REPLACE INTO email (email) VALUES ('{$email}');";
echo $query;
@$db->exec($query);

// Remove the old email address (if any)
//$query = "DELETE FROM email ; ";
//echo $query;
//echo "<BR>";
//@$db->exec($query);

// Put in the new email address
//$query = "INSERT INTO email VALUES ('{$email}');";
//echo $query;
//echo "<BR>";
//@$db->exec($query);

this code uses whatever is in the $email variable, which will only be from $_POST['new_email'] if the email table is empty or if the first row in the email table has an empty email column.

ALL the logic related to handling the form submission for $_POST['new_email'] needs to be within the - if (isset($_POST['new_email'])){ ...... } block of code.

also, you should enforce uniqueness of things like email addresses in your database table by assigning a unique key to the column (not sure if sqlite supports this) or in your code by querying for the value before trying to insert/update it.

lastly, most of your symptoms 'sound' like the browser is requesting the page twice. when you are observing empty submitted data, it's because the last of the two requests doesn't have any submitted data. in general. you need to validate that there is data before using it, i.e, required/expected data is at least not empty.

RensD · January 6, 2014

@mac_gyver
Thanks for the quick reply man.
Much appreciated.

I have found out where my email issue came from.

Programming Ad-Hoc normally creates spaghetti, and so did I, hence introducing an execute command just after the "INSERT OR REPLACE" command and an other one after all other statements, resulting is entering the new email address twice. :-)

Inserting and modifying the email address work all fine now. Thanks for the hints.

Second issue

Now my list of devices.

How do I make the $_POST contain the device_id and/or new_location after pressing one of the buttons?

Would you please boot me into gear as well on this?

Friendly greetings,
Rens

mac_gyver · January 6, 2014

i looked (more) at your device code. the form you are producing is repeating the same set of name='...' attributes for each device. only the last value for any name='...' is what is being submitted, which is empty due to the 'add device' set of form fields at the end of the form.

because your intent is to only modify/delete one value at a time, you should produce a separate form for each row on the display.

currently, with one form, pressing ANY of the submit buttons will submit all the values in the form. if you intended to modify/delete all the fields at once, you would have just one form and use array names for the form fields, with the device id as the array key, then just have one submit button.

RensD · January 6, 2014

Victory!

Yes.... That did the trick.
Now I can handle the email actions correctly, and I can handle the device actions correctely.

My (seemingly) last problem is how to refresh the site in a proper way.

Refreshing in a proper way

What I mean is: I, for instance, delete a device........

The device is removed from the database, but the screen tells me it is still there.

Refreshing the screen does solve this (redundant display) issue.

What is the best way to solve this please?

RensD · January 6, 2014

we have a winner!

Ah.... Solved that.....

What I did before was:

1: Build the page

2: Handle POST_requests

As simple as it can be, now I do:
1: Handle POS_Requests

2: Build the page.

and all works well. :-)

I must say I'm quite happy with my very first PHP-page ever.

It has been quite a struggle the last few days, but I learned a lot.

Thank you for helping me as you did.

Friendly greetings
Rens.

(Edit to add the code as it is now.)

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Device maintenance</title>
  </head>
  <body>
    <FONT FACE="courier">
    <?php
      // Connect to the database
      $db = new SQLite3("/mnt/devices.db");
    ?>

    <!--Handle _POST activity-->
    <?php
      // Check if form was submitted
      if(isset($_POST)) {
        $query = "";
          
        // Lets not check if there is anything in the database. Just replace.
        if (isset($_POST['new_email'])){
          // Clear the current emailaddres.
          $query = "DELETE FROM email;";
          @$db->exec($query);
            
          // Insert the new emailaddress
          $query = "INSERT INTO email (email) VALUES ('{$_POST['new_email']}');";
          @$db->exec($query);
        }
        
        // Check if new device was added or a device was modified
          if(isset($_POST["new_device"])) {
          // Insert a new device into the table
          $query ="INSERT INTO devices('device_id', 'location') values('{$_POST["device_id"]}', '{$_POST["device_location"]}')";
          @$db->exec($query);
        } else if(isset($_POST["modify_device"])) {
          // Add a query to update a device
          $query = "UPDATE devices SET location = '{$_POST["device_location"]}' WHERE device_id = '{$_POST["device_id"]}'";
          @$db->exec($query);
        } else if(isset($_POST["delete_device"])) {
          // Add a query to delete a device
          $query = "DELETE FROM devices WHERE device_id = '{$_POST["device_id"]}'";
          @$db->exec($query);
        }
      }
    ?>
    <!-- End of handling POST activities-->
      
    <!-- Present the current email address in a form and enable changing it. -->
    <?php
      // Get the result from the database
      $query  = "SELECT * FROM email";
      $result = $db->query($query);

      $email_array = $result->fetchArray(SQLITE3_ASSOC);
      $email = $email_array['email'];
    ?>

    <!-- Present the read emailaddress in an inputbox and enable alteration  -->
    Uw meldingen worden gestuurd naar het volgende emailadres<BR>
    <form method="post" action="#">
      <input type="text" name="new_email" style="width: 320px" value="<?php echo $email; ?>" />
      <input type="submit" value="Wijzig e-mail" />
    </form>
    <!-- End of presenting email-->
    
    <!-- Show the (rebuild) table with the attached devices -->
    <table>
      <thead>
        <P><BR>
        De volgende apparaten zijn bekend
        <tr>
          <th>ID</th>
          <th>Locatie</th>
        </tr>
      </thead>
      <tbody>
        <?php
          $results = $db->query("SELECT * FROM devices");
          while ($device = $results->fetchArray(SQLITE3_ASSOC)) {
        ?>
        <tr>
          <!-- Each row is a form, so each POST contains the correct values of that row -->
          <form method="post" action="#">
            <td>
              <?php echo $device["device_id"]; ?>
              <input type="hidden" name="device_id" value="<?php echo $device["device_id"]; ?>" />
            </td>
            <td>
              <input type="text" name="device_location" value="<?php echo $device["location"]; ?>" />
            </td>
            <td>
              <input type="submit" name="modify_device" value="Modify Device" />
            </td>
            <td>
              <input type="submit" name="delete_device" value="Delete device" />
            </td>
          </form>
        </tr>
        <?php
          }
        ?>
      </tbody>

      <tfoot>
        <tr>
          <form method="post" action="#">
            <td>
              <input type="text" name="device_id" />
            </td>
            <td>
              <input type="text" name="device_location" />
            </td>
            <td>
              <input type="submit" name="new_device" value="New device" />
            </td>
          </form>
        </tr>
      </tfoot>
    </table>

    <!--End table with devices-->
    </FONT>
  </body>
</html>

Edited January 6, 2014 by RensD

Sign In

Arduino: SQLite3, PHP, Very basic script

Recommended Posts

RensD

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

RensD

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

RensD

Link to comment

Share on other sites

RensD

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information