An update... I was so close to giving up, but I have managed to get the system working!!
The issue was the IP address I was connecting to in Node-RED. All the advice and tutorials given said to use the same IP address as the raspberry pi which is 192.168.0.100 and then port 1883. Then I looked at which IP address mosquitto was listening on port 127.0.0.1 so i put this into the mqtt node and alas, it worked. I don't know what that IP is but thank god it worked haha
Here's how it all looks now after all that. I've got it set up to automatically turn the water pump on when the soil moisture goes below 20% and then turn off above 80%. I also added automated a sms to send one for every hour the system is showing a level below 20%, just as a fail-safe. And I can manually turn on the pump if that was to happen..
I'm a bit confused and surprised by your response as the IP address 127.0.0.1 with port 1883 means - use the Mosquitto broker on the local machine (which I assume is on IP 192.168.0.100).
The small test flow I posted above used 127.0.0.1 (as I didn't know the IP address of your RPi). If you had tried that flow it would have shown if the local broker was working or not. As @Colin said - once the test flow is working you can change the IP address for another broker - it could on another machine on your home network or even a remote broker.
Anyway, glad you have got it working and receiving data - you can now have fun creating a dashboard.
