Pico W writing into MySQL db?

Support Raspberry Pi
23

Hi!
In the forum topic ‘Pico W writing into MySQl db?’, the code supplied by @Fractal shows how to write data from a Raspberry Pi Pico W into a MySQL database on another machine, using a combination of urequests and php.

Would it be possible to get a sample of the complementary code (urequests and php) which would enable the PIco W to read data from a MySQL database.

2 Likes
24

Hi Frederick,

I’m not Fractal, so I won’t be of quite as much help here, but I can help you pick it apart a bit.

There are a few parts to this:

The part that runs on the Pico

This part performs that HTTP request, and puts what the server responds with in the variable r. You shouldn’t need to modify this to my knowledge

The part that decides the query

You’ll have to brush up on your SQL for this, here’s the query that runs against the database.

The part that runs the query

This bit runs the query according to the $conn and $sql variables you set up earlier. You shouldn’t need to change this bit, just the stuff around it, so that the result (data returned from part 2) gets passed back over HTTP. I’m a bit unsure about this bit. PHP docs should help here:
PHP: mysqli::query - Manual

All the best with your project, and let me know if you need anything clarified.

1 Like
25

Hi Frederick,

Sorry I missed your earlier post. It is easy for a Pico W to read data from a MySQL database.

The first step is for the Pico W to run a PHP script on the server e.g.

r = urequests.get(url = 'https://www.myserver.com/MySQLread.php')

The PHP script reads the data from the MySQL database and echos it back as text in response to the urequests.get(). I have used comma delimited (CSV) and JSON format and they both work. The Python script can then decode the variable r CSV or JSON data back to data as necessary.

If the PHP script knows what to do e.g. read the last 10 entries all good. If not then urequests.get() will need to pass the request as a parameter e.g.

number = '10'  # must be text
r = urequests.get(url = 'https://www.myserver.com/MySQLread.php?readings='+number)

If you need any code snippets let me know.

1 Like
26

Fractal thanks for the quick reply. That has got me pointed in the right direction, but I have one question.

Having made a mysqli_query($conn, $sql) statement in the PHP script, how do the results of the query get passed back to the ‘r’ variable on the Pico W (what you have decribed as ‘echos it back’).
Is there a need for some form of ‘return’ statement at the end of the PHP script?

1 Like
27

Hi Frederick
A warning I am a hobbyist rather than a programmer and getting code to do what I want is a struggle. Rather than go into a complex description it may be best to give you an example which you can study. The Pico W has a RTC and my example sets the time to UTC. So on the server a PHP script called timestamp.php echos the UTC time stamp. I have used JSON since this is about as complex as it gets. Here is the code for timestamp.php.

<? 
date_default_timezone_set("UTC");
$myObj = new stdClass();
$myObj->year = intval(date(Y));
$myObj->month = intval(date(n));
$myObj->day = intval(date(j));
$myObj->dayofweek = intval(date(N));
$myObj->hours = intval(date(G));
$myObj->mins = intval(date(i));
$myObj->secs = intval(date(s));
$myObj->subseconds = 0;
$myJSON = json_encode($myObj);
echo $myJSON;
?>

Here is the Python code to run on the Pico W. To simplify it I have not included code to connect to WiFi. With http protocol there is the request and the response which contains the echo JSON data.

# The below code assumes the Pico W has a WiFi connection.
import network
import urequests
import json
import machine
import wifi
from time import sleep

def set_datetime():
    try:
        r = urequests.get("http://www.myserver.com/timestamp.php")
        print("r.text: " + str(r.text))
        j = json.loads(r.text)
        rtc.datetime((j["year"], j["month"], j["day"], j["dayofweek"], j["hours"], j["mins"], j["secs"], j["subseconds"]))
        print(rtc.datetime())
    except:
        print("urequest date/time error")        
rtc = machine.RTC()
set_datetime()

The code shows how to extract the JSON data. The print statements in the function output UTC as JSON and a Python Tuple.

r.text: {"year":2023,"month":12,"day":2,"dayofweek":6,"hours":5,"mins":20,"secs":12,"subseconds":0}
(2023, 12, 2, 5, 5, 20, 12, 0)

For PHP/MySQL I use W3Schools as a reference e.g.

Good luck

2 Likes
28

While JSON is the rigorous approach handling multiple rows of data if you only wanted a row of data without the data descriptor you should be able to PHP echo the Python Tuple viz

(2023, 12, 2, 5, 5, 20, 12, 0)

This would considerably simplify the code at both ends.

2 Likes
29

Fractal,
With the assistance of your last response I am now reading data on the remote MySQL database to the Pico W. The point I was previously missing was that the ‘echo’ or ‘print’ statement at the end of the PHP script transferred the results of the SQL query to the urequests on the Pico.

Thanks for your help on this.

1 Like
30

Hello all,
I am currently in my planning phase for something similar to this. My pico 2W needs to send data to my MySQL server. Just asking if @Fractal 's way which seems to look like it works (similar to my case) is still a good idea. I am also willing to take some new inspiration if anyone has got some new ideas.
Thanks
Ali

31

Hey @SyedAliRaza290321,

While I don’t have personal experience with MySQL, as far as I can tell @Fractal’s solution should still work, and judging by the fact that no one returned to the thread, it probably works pretty well.

Curious to see if others have any other recommendations.

32

Note the solution I suggested is not theoretical. It is being used to send data from weather stations to a central server and has been working successfully for some time. I would also be interested to learn of any alternative methods.

1 Like