Basic PHP development with SQL

PHP/SQL guide!

Well as you've probably figured I'm new to this community, so I decided one of my first posts should be contributing something quite huge. This tutorial will show you how to make good use of PHP and SQL.

Now bare in mind I'm not perfect so my conventions in some cases in regards to the language itself may be off but that isn't really a major thing right now.

Requirements:

• phpmyadmin
• SQL
• PHP
• A webserver (Just run it on localhost etc)
  
  
  
  

Now you may be thinking that's a lot but we can simply download XAMP for windows, or LAMP for Linux (there are countless things we could use such as lighthttpd but for this tutorial we'll stick to LAMP/XAMP). You can download the things you need here:

• XAMP (Windows only) - http://www.apachefriends.org/en/xampp-windows.html
• LAMP (Linux) - http://www.apachefriends.org/en/xampp-linux.html
  
  

The installation process is self explanatory. First of all I'm going to be covering the basics of PHP and SQL (I'm aware there is already a basics guide but I don't mind writing another to avoid confusion).

PHP

Firstly all PHP code should be in this frame:

Code:

<?php
// PHP code here
?>

PHP stands for Hypertext Preprocessor it is a web development languages which is used widely on the Internet. It has the basic fundamentals which all languages have such as variables and so forth.

Code:

$test = 1;

This little snippet would assign the var test to equal 1. Most things we do in regards to setting variables and so on begin with a dollar sign. If we wanted to change the value of the variable 'test' we could do:

Code:

$test = 2;

Now test no longer equals 1, it equals 2. So if we printed out $test it would give us '2' because that is the variables value. That may seem pretty damn pointless to you if you're a beginer but I can assure you it isn't, variables can be the stepping stones of a great program and you need to use them.

Another thing which is widely used in languages is statements, it can be an if statement, an else statement and so on. They are another stepping stone to a good PHP program. For example (baring in mind what we just learned):

Code:

$test = 1;
$testagain = 5;

if ($test > $testagain) {
// do something
}

This is an if statement, it outlines if something is going to happen then what should the action be. This code is saying "if the variable test is higher than the variable testagain then do whatever it says underneath it but not past the brackets".

Code:

if (conditions) { // the conditions of the statement
// do what is here
} // this is the end of the statement

So for example:

Code:

$dogs = 500;
$cats = 450;

if ($dogs > $cats) {
echo "There are more dogs than cats!";
} else if ($dogs < $cats) {
echo "There are more cats than dogs!";
}  // I forgot to add. Echo is PHPs way of printing out things e.g echo "Hi!"; would print hi!

This code brings us on to another statement, the else if statement. This works similar to an if statement.

Code:

if (condition) {
// do something
} else if (other condition) {
// do something
}

another use of it is:

Code:

if (condition) {
// do if condition is true
} else {
// if the first condition isn't true it does what is here no matter what
}

The else basically says "if the first condition isn't true, then no matter what we do what's under the else." it's not an easy thing to explain but I do try my best. I feel that is all you will need to know in regards of the basics.

Just some quick facts:

A string is for letters, abcdefghijklmnopqrstuvwxyz etc.
Ints are for numbers 12345678910 etc.. (up to a huge amount)

SQL

SQL is something we're going to be using along side PHP to create our basic program. SQL/PHP are used in forums and all over the web. This forum will use SQL/PHP together.

We will be using SQL databases and SQL queries with our PHP. Basic SQL syntax includes:

Code:

CREATE
DROP
TABLE
INSERT
WHERE

and so on, we're not looking to use tons of SQL so we don't really want to know everything just what we're going to use! An example of a query would be:

Code:

CREATE DATABASE tom b

This would create the database 'tom b'. As you can see it's pretty much English. I don't want to go in depth on SQL because it's really easy!

Creating our basic program!

Now that we know the basics we're going to make a basic program. So, what will our program do? It will get a users input, and save it to a database.

You may think that sounds complicated, but it really isn't. I suggest you google if you're having issues with phpmyadmin because it's really simple to setup and many guides exist.

First of all, go to localhost/phpmyadmin and login you can either create a database manually or query it (google if you can't navigate phpmyadmin it's basically right in front of you).

The first thing we're going to do is create a user input field.

Code:

 <form action="sql.php" method="post">
  Your name: <input type="text" name="name" />
  Your age:<input type="text" name="age" />
 <input type="submit" />
 </form>

This is a PHP form. Once the user has supplied the data it will go to the file sql.php:

Code:

 <form action="sql.php" method="post">

it's method is POST. There are two fields it will prompt the user to input into:

Code:

  Your name: <input type="text" name="name" />
  Your age:<input type="text" name="age" />

Their name, and their age. It will look like this:



except it'll have Your name: and so on next to it. Credits to Google images for the picture. Now that we have a submission form it looks nice but it doesn't actually do anything.

All it does is lead to sql.php so we're going to have to edit that file (make it if you haven't made it yet, ensure it's in the same directory). Now the PHP begins!

The basic frame of our file is this:

Code:

<?php

?>

All code goes between those tags, first of all we're going to have to grab the data the user inputted into the form. It's fairly basic.

Code:

<?php
$username = $_POST["name"];
$userage = $_POST["age"];
?>

Te function:

Code:

$_POST["age"];

grabs the data the user inputted into the age field we did before. It knows which one it is because of the 'age'.

Code:

name="age" />

part of our input field. So if we changed it to:

Code:

name="chicken" />

we would have to do:

Code:

$_POST["chicken"];

So now whenever we print out "$username or $userage" it'll give us the data they inputted into our submission form. So now we're going to develop of that.

Our code upto now is:

input.php:

Code:

 <form action="sql.php" method="post">
  Your name: <input type="text" name="name" />
  Your age:<input type="text" name="age" />
 <input type="submit" />
 </form>

sql.php:

Code:

<?php
$username = $_POST["name"];
$userage = $_POST["age"];
?>

Now we want to do something with the information we've got from the user, which has now been assigned to two variables. We want to save it to a database.

The first thing we need to do is use a built in PHP function which allows us to connect to a database.

Code:

mysql_connect("localhost","username","password"); // password for your phpmyadmin login

A good example of how to do this securely from w3schools is this:

Code:

<?php
 $con = mysql_connect("localhost","mysql_user","mysql_pwd");
 if (!$con)
   {
   die('Could not connect: ' . mysql_error());
   }

 // some code

 mysql_close($con);
 ?>

I think this is probably the best way of doing it I've seen yet. I think the code is pretty self explanatory. It outputs an SQL error, and closes the connection if something goes wrong so is good for fixing and problems and so on.

So now we've connected to our database our code looks like this (sql.php):

sql.php:

Code:

<?php
$username = $_POST["name"];
$userage = $_POST["age"];
$con = mysql_connect("localhost","root","abc123");
 if (!$con)
   {
   die('Could not connect: ' . mysql_error());
   }



 mysql_close($con);
?>

This will now connect us to the database, so we can start adding MYSQL queries and so on. We want to create some tables inside our database for my example I'm going to be using this layout:

Database name: database
tables: database
rows: username, userage

If you're unsure on how to work phpmyadmin please use Google, there are millions of guides. I'm going to write one myself later on. Now we're going to use PHP/SQL together to make a query.

Code:

<?php
$username = $_POST["name"];
$userage = $_POST["age"];
$con = mysql_connect("localhost","root","abc123");
 if (!$con)
   {
   die('Could not connect: ' . mysql_error());
   }

mysql_select_db("guide",$con);
mysql_query("INSERT INTO data (username, userage)
VALUES('$_POST[name]', '$_POST[age]')");

?>

This is what our finished query will look like, it looks complex to the beginer but I can assure you it is very simple. I'll break the code down to explain it to you:

Code:

mysql_select_db("guide",$con); // this selects the database we're using
mysql_query("INSERT INTO data (username, userage) //this basically tells us where we're inserting into
VALUES('$_POST[name]', '$_POST[age]')"); // these are the values of our inserts
?>

Code:

mysql_query("INSERT INTO data (username, userage) //this basically tells us where we're inserting into
VALUES('$_POST[name]', '$_POST[age]')"); // these are the values of our inserts
?>

This part may be hard to understand for some

mysql_query("INSERT INTO data (username, userage)

is basically the SQL the "INSERT INTO data" is the part which is telling SQL what table we want to insert our data into. "data" being the name of the table. So if our table name was 'tom b' it would be

mysql_query("INSERT INTO tom b (username, userage)

INSERT INTO is basic SQL syntax as mention before. The:

VALUES('$_POST[name]', '$_POST[age]')");

part is the values which are inserted into the rows. You could use the variables we made but I've just gone ahead and used the POST function to insert the data into the table.

Code:

<?php
$username = $_POST["name"];
$userage = $_POST["age"];
$con = mysql_connect("localhost","root","abc123");
 if (!$con)
   {
   die('Could not connect: ' . mysql_error());
   }

mysql_select_db("guide",$con);
mysql_query("INSERT INTO data (username, userage)
VALUES('$_POST[name]', '$_POST[age]')");

?>

This code now saves the data submitted by the user into a database.

The users input is now saved! This is one of my first posts on this community and I'm not the best explainer in the world! I'll make an update of others guides I'm writing shortly here:

• PHP good methods
• PHP loops
  
  

I may of made some mistakes as it is a long post so please do notify me, thanks.

 

The guide is definitially layed out well. but I'm kinda lost with what PHP is exactly used for.

 

PHP in the loosest sense is used to create dynamic web pages as opposed to static html.

Consider the following

A user logs in to your site, and you want to great them personally.

Rather than creating an html page for every users to point with the following

Code:

Hello Liam

You could create a single PHP page with the following

PHP:

<?php

$username = $_GET['username']; 

echo 'Hello $username';
?>

$_GET is a way of retreiving data from the query string. So in my example when a use logs in they would be redirected to the following page.

Code:

http://mypage.com/welcome.php?username=liam

so by using the $_GET global variable, and converting it to a local variable you can actually welcome anyone to the site just by passing it in your URL.

Code:

http://mysite.com/welcome.php?username=geoff

Would print

Welcome Geoff.

Lets look a little more in depth. Take the page you are reading. The query string will look somthing like /viewthread.php?t=95014164.

What this means is instead of having a single html page for every thread on this forum (which is about 9 million pages) there is one viewthread.php page and the number passed in the query string tells php which thread to look for. IN the back ground PHP then collates all the information about the thread and enters it into the page using variables.

Does that make sense! Im not the best writer

 

Thanks for very good information on s basic php development.

 

SQL server is widely used with PHP. PHP is open source programming language and SQL provides you the correct type of balance. It is totally free but also built with lots of features.