The MP4 video format is compatible with the iPhone, meaning you should be able to play video files without needing any conversion or re-encoding. For many situations, the native Video application on the iPhone will be fine for watching an MP4 file, whether on your phone or your computer. However, several independently developed apps can give added functionality.
The most basic way of watching an MP4 video on your iPhone is to add it to the iTunes library on your computer, sync it to your iPhone, then open it using the Videos app. Remember that iTunes can re-encode video files to MP4 format if necessary. For added convenience, you can switch on an option called "Home Sharing" in the settings menu of iTunes on your computer and the settings menu on your iPhone. Once you've done this, you'll be able to stream videos from your computer to the iPhone (without having to put them on your iPhone) as long as both devices are using the same local network. You'll find the available videos in the "Shared" section of the Videos app.
Video of the Day
The Air Video apps let you set up software on your computer to make your content available to any iOS device on your local network. You can also make it available to any iOS device that has an Internet connection, even if it's not on the same network as your computer; a PIN code stops other people from connecting. Most MP4 videos will stream directly to your iPhone; other videos are re-encoded "on the fly" by your computer, so you may get a brief delay before playback starts. The app is available in a free version and a paid "HD" version; the latter has an improved interface, lets you easily copy videos to your device (without needing to use iTunes) and has better support for high-resolution videos.
This app is similar to Air Video in many ways, streaming video to your iOS device and converting it if necessary. One difference is that it uses a Windows-style folder system rather than a menu system for searching your media library. Another is that you don't necessarily need to install extra software on your computer; the app automatically detects shared folders on your computer. Like Air Video, PlayerXtreme uses a free/paid model; unlike Air Video, you'll need the paid version to stream any content.
VLC for iOS
This is the iPhone version of a media player of the same name available for Windows PCs. As with Air Video and Player Xtreme, VLC for iOS can stream MP4 video directly from your computer and can convert incompatible files in real time. VLC supports multiple core processors on your computer, which lets it work with higher-resolution files with fewer glitches or delays. It lets you transfer files from your computer to iPhone over Wi-Fi, though you'll need an account with the Dropbox service to use this.