Hey everyone,
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..
Thanks to everyone that shared ideas and helped out!!! I am very much appreciative!!!

