Php and Mysql for real beginner

Get Started
1

Hi

I’me less than two weeks old in this programming business but with a lot of online help have designed the basics of my first two pages of a website, with HTML and CSS. Now I need to start thinking of the “Submit” button for registry and understand that the first job is to design a MySQL table. Where do I start? I have EditPlus which does not seem to help a lot. Are there any useful tools around? Any good instructions on how to design a simple table?

Meanwhile, I have discovered how to set up a table through phpMyAdmin. I suppose that I have to assign atributes to the various lines and marry them with the fields in the webpage (I.e. text, integer, etc) Is that it?

What comes after? php code to upload to where?

Thank you

qim

2

If you are using windows, a good place to start would be by installing wamp: http://www.wampserver.com/en/ If you’re on mac, try Mamp: http://www.mamp.info/en/index.html

These installations will set you up with a way to run php on your local machine, without having to upload to a webserver while you are learning.

A good place for learning is nettuts: http://nettuts.com , have a search on there for some beginner/basic tutorials, and take it from there.

Also, beware any tutorials that use the mysql_ functions (they are deprecated and should not be used), or any tutorials that output html directly from php like this:


 echo '<h1>This is some html</h1>';

Don’t do that… it’ll lead to code that is hard to maintain very quickly. If you see either of these things in any tutorials you find, avoid them.

3

Thanks aaarrrghh

I’mtrying to understand the steps in order: I guess first I should tidy up my webpage (HTML/CSS) to mirror the database at the server. Can you help me there?

Then, I should write the script that will link the two. That will have to come later when I learn something about the code (php?)

Thank you

qim

4

Not quite sure what you mean about the css/html and database thing. Html and css are used to create the presentation layer for your site. The database is independent of any code, but you can use php to pull information out of the database, modify information within it, and display it to your website to your users.

This should help you http://blog.themeforest.net/screencasts/diving-into-php-video-series/

I’d recommend going through these videos, one at a time.

Also - some extra advice - a lot of people will recommend w3schools as a resource. Ignore these people. W3schools is a terrible resource that you would do well to avoid. If you follow the tutorial videos I’ve linked you to here, and make sure to ignore any tutorial with mysql_* functions in it and any tutorial that recommends echoing html from php, you’ll be on the right track.

5

Sorry for the confusion.

i already have a webpage with the rgistration form made with HTML and CSS. I also set upo a databse at the server. I expect that for both to talk to each other through the eventual script they must have the same b´names, length, type, etc, and have a different format to the one I used just for display. If it is not like this then I am on the wrong track altogether.

qim

6

Ah, no it doesn’t quite work like that.

You use php as the middle man here, and php will basically pass the data from each respective field on your web page to the database. The names of tables and fields in your database do not have to match anything on the front end of the website itself.

Let’s say you have a field in your database called “username”, and on the website you have an input field with a name of say “user_name_to_use”, in php you’d do something like this:



$username = $_POST['user_name_to_use'];

//now whatever was typed into the user_name_to_use field on the website is in the $username value in php

//You'd then be able write some database code in php to link the $username variable in php to the 'username' field in your database.

Note: I’ve not written the actual db stuff, because you can find that in the above tutorial, and another important thing is that I’ve done nothing with security here - in reality you should write some code to make sure the $username variable contains “safe” data, but again, this is something you can learn using the above link. The point here is that php takes the data used on the webpage and you are then free to do whatever you want with it. There is no hard link to the stuff in your html and your underlying database.

7

Thanks. I will do some studying first.

qim

8

Could I ask you a quick question?

When you see something like $name = $_POST[‘user’]; which belongs to the HTML form and which to the server database? I take ‘name’ is form and ‘user’ database. is taht correct?

Thanks

9

Ok, well, when you post from a form in html, the <form> element has an attribute called method. It has two possibilities: “post” and “get”

Eg:

<form method=“post” action=“”>
//form stuff goes here
</form>

or

<form method=“get” action=“”>
//form stuff goes here
</form>

When you click the submit button this data is then sent to your script in such a way that you can retrieve it. Get data ends up in the url, which is why you sometimes see urls like this: www.mywebsite.com/?username=jeff&favorite_car=nissan

Post data, on the other hand, is not visible in the url itself.

You can pick up the data passed form the form in php using two special superglobals - $_POST and $_GET. You can grab any element by taking the html name attribute of the element and addressing it that way.

So if in the <form> element, you had method=“post”, and an input with a name=“my_username”, you can access that data in php like this:


$myVariable = $_POST['my_username'];

The code I showed you before doesn’t do anything to the database at that point - it just grabs the data and stores it in a php variable.

I’d recommend going through those videos I linked you to above. This stuff will all be explained in there.

10

Thanks

I got some code online and am trying to change it around to match my page fields. Hope it works…

11

Do you think this is a good start (before I lose too much time in a goose chase)?

<?php
 
//STEP 1 Connect To Database
 
$connect = mysql_connect("HOST","USER","PASSWORD");
 if (!$connect)
 {
 die("MySQL could not connect!");
 }
 
$DB = mysql_select_db('pintotou_agents');
 
if(!$DB)
 {
 die("My SQL could not select Database!");
 }
 
//STEP 2 Declare Variables
 
$Username = $_POST['Username'];
$Re_Username = $_POST['Username'];


$Password = $_POST['Password'];
$Re_Password = $_POST['re-Password'];


$Agency_name = $_POST['re-Password'];
$IATA_number = $_POST['re-Password'];
$City = $_POST['re-password'];

$Website = $_POST['re-Password'];
$Country = $_POST['re-Password'];

$Email = $_POST['Email'];
$Email1 = "@";
$Email_Check = strpos($Email,$Email1);


$Person_responsible = $_POST['Person'];
$Position_in_Agency = $_POST['Position'];
$Telephone = $_POST['Telephone'];

$Fax = $_POST['Fax'];


$Date = $_POST['date'];


 
//STEP 3 Check To See If All Information Is Correct
 
if($Username == "")
 {
 die("Opps! You don't enter a username!");
 }

 if($Username == "" || $Re_Username == "")
 {
 die("Opps! You didn't enter one of your passwords!");
 }

if($Password == "" || $Re_Password == "")
 {
 die("Opps! You didn't enter one of your passwords!");
 }

 if($Password != $Re_Password)
 {
 die("Ouch! Your passwords don't match! Try again.");
 }

if($Agency_name == "")
 {
 die("Opps! You never entered in your birth year!");
 }
 

 
if($Email_Check === false)
 {
 die("Opps! That's not an email!");
 }
 
//STEP 4 Insert Information Into MySQL Database
 
if(!mysql_query("INSERT INTO users (email, username, password, birth)
 VALUES ('$Email', '$Username', '$Password', '$Birth')"))
 {
 die("We could not register you due to a mysql error (Contact the website owner if this continues to happen.)");
 }
 
?>
12
  • The mysql_* extension has been deprecated as of the latest version of php will will likely be removed in the next version. You should now use either the mysqli_* extension or PDO
  • NEVER EVER trust any data submitted by the user, no matter how well you know them, any incoming data should be treated as unsafe and sanitized eg is it of the correct length and correct type, etc.
  • When sending any query to the database server you should use prepared statements, as it stands that code is vulnerable to SQL injection

There are a number of books aimed at beginners to PHP and MySQL, one is called PHP & MySQL Novice to Ninja, 5th Edition (Author: Kevin Yank, Publisher: SitePoint)

13

Hi

Thank you fot the advixce which I accept.
Please, note that my server, JustHost, uses MySQL databases.

However, at this moment I am not building a working webpage. I simply need something that looks like a webpage. Later, I can change, update, look at all the security issues. My question was whether that code might be a start, and if it would work. It is also my way of learning what it is all about in the little time that I have available.

Meanwhile. I found some other code which I have been looking at. If I were to try it, what code would I have to put in the “Submit” button for this code to be called and executed?

Any help would be welcome

Thank you

qim

<?php

//STEP 1 Connect To Database

$connect = mysql_connect(“HOST”,“USER”,“PASSWORD”);
if (!$connect)
{
die(“MySQL could not connect!”);
}

$DB = mysql_select_db(‘pintotou_agents’);

if(!$DB)
{
die(“My SQL could not select Database!”);
}

// STEP 2

$uname = $_POST[‘username’];
$pword = $_POST[‘password’];

$uname = htmlspecialchars($uname);
$pword = htmlspecialchars($pword);
$uLength = strlen($uname);
$pLength = strlen($pword);

if ($uLength >= 10 && $uLength <= 20) {

$errorMessage = “”;

}
else {

$errorMessage = $errorMessage . “Username must be between 10 and 20 characters” . “<BR>”;

}

if ($pLength >= 8 && $pLength <= 16) {

$errorMessage = “”;

}
else {

$errorMessage = $errorMessage . “Password must be between 8 and 16 characters” . “<BR>”;

}
if ($errorMessage == “”) {

}

$user_name = “root”;
$pass_word = “”;
$database = “login”;
$server = “127.0.0.1”;

$db_handle = mysql_connect($server, $user_name, $pass_word);
$db_found = mysql_select_db($database, $db_handle);

if ($db_found) {

}
$SQL = “SELECT * FROM login WHERE L1 = $uname”;
$result = mysql_query($SQL);
$num_rows = mysql_num_rows($result);

if ($num_rows > 0) {

$errorMessage = “Username already taken”;

}
else {

$SQL = “INSERT INTO login (L1, L2) VALUES ($uname, $pword)”;

$result = mysql_query($SQL);

mysql_close($db_handle);

header (“Location: page1.php”);

14

No, it’s not a good start.

As I said to you, and someone else said above, you should disregard ANY tutorial with ANY mysql_* functions in it. You’ve gone right ahead and found some really badly written code by ignoring what I said.

I’ve told you what you should do.

Be patient, go ahead and watch those videos I linked you to, and disregard the code you’ve just pasted (it’s crap code and not a good starting point).

15

I have watched the videos. But as I said this is not a working site. I have already written a robots.txt to stop Google to find it. All I need is to get a feeling of how things work.

Forgetting the code that I sent you, could you help me with the line (s) I have to add around the submit button to link to the code that will eventually be written?

Thank you

16

The queries you’re using are deprecated (http://php.net/manual/en/function.mysql-query.php) We’re now using PDO or MySQLi as the main DB drivers.

  1. The above
  2. You’re not even hashing your passwords plaintext? Give me 5 seconds and you’re database has been wiped
  3. Posting and renaming variables - just a waste of time, space & bandwidth
  4. Pulling out everything from your users table just to check if the username exists? Bad idea as you’re also pulling out the plaintext password

I’ve edited it, but not tested it so let me know.


<?php

//STEP 1 Connect To Database

$dbHost = "localhost";
$dbUser = "user";
$dbPassword = "pass";
$dbDatabase = "database";

//Connect

$dbconn = new PDO('mysql:host='.$dbHost.';dbname='.$dbName, $dbUser, $dbPassword);

//Test connection
try
   {
   $dbPDO = new PDO('mysql:host='.$dbHost.';dbname='.$dbName, $dbUser, $dbPassword);
   $dbPDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   }
catch  (PDOException $e)
   {
    echo "Error!: " . $e->getMessage() . "
";
    die();
   }


// STEP 2

//check for errors
   //Note that we're not converting the variable, there's no need just a waste of time, space and bandwidth
$uLength = strlen($_POST['username']);
$pLength = strlen($_POST['password']);

if ($uLength >= 10 && $uLength <= 20) {

$errorMessage = "";

}
else {

$errorMessage = $errorMessage . "Username must be between 10 and 20 characters" . "<BR>";

}

if ($pLength >= 8 && $pLength <= 16) {

$errorMessage = "";

}
else {

$errorMessage = $errorMessage . "Password must be between 8 and 16 characters" . "<BR>";

}
if ($errorMessage == "") {

}


//You were selecting the everything, very insecure, this is more secure. Using PDO we don't need to escape the variables as the PDO script handles it for us. We also only need to check if the username exists so no need to waste bandwidth selecting *

$sth = $dbconn->prepare("SELECT username FROM login WHERE L1 = :uname");


 if ($sth->execute(array("uname" => $_POST["uname"]))){
 	//User exists show error
 	$errorMessage = "Username already taken";
 }else { //User does not exist, insert new user
 	$sth = $dbconn->prepare("INSERT INTO login (L1, L2) VALUES (:uname, :pword)");
	$params = array(uname => $_POST[uname], pword => $_POST[pword]);
	$sth->execute($params);

 	$dbconn = null;

 	header ("Location: page1.php");
 }


?>
17

Thank you very much

I have to go out now, but will work on it tonight when I get back,

Thank you again

qim

18

I agree with everything except point 3. It makes sense to take a variable out of the $_POST superglobal - this wastes literally no time and certainly doesn’t waste any bandwidth. It’s the right way to do it.

Edit: It literally takes up no more bandwidth at all (absolutely none), and would save you time actually. Each time you want to refer to the variable after that point, you can refer to $username instead of $_POST[‘username’]. It also means if you call any functions on the variable, the effect of the function is maintained - so for example if you did something like $username = ucfirst($_POST[‘username’]);, the $username variable would continue having a capital first letter.

It’s easier to maintain your variables, actually saves you time, takes up no more bandwidth and just makes much more sense.

I’ll also note that while your code was an improvement, it’s still quite messy, procedural and not perfect. It’s better though, and as a starting point, other than point 3 that I’ve already mentioned, it’s not too bad.

19

Hi, and again tahnk you very much

I’m having problems getting to work. I placed it in a new folder in the root of public_html and called it from www.mydomain/NewFolder and it does not work. Ithink it has to do with my passwords as I got totally confused as to which are necessary.

$dbHost = “localhost”;
$dbUser = “user”;
$dbPassword = “pass”;
$dbDatabase = “database”;

Host is the IP address of JustHost and I got it right
User confuses me. When I set up the database I entered only one user, that is me. I know the password, but wonder if it this user you refer to
Password; which password? for cPanel, which happens to be the same as for phpMyAdmin; or for MySQL databases? or password for user (me)?
Database: again I’m not sure. The database I created was called pinto_agents, but by the time I set up the fields there appeared anohter name,c capitalized, Agents, so I guess the database is called pinto_agents/Agents.

Here goes the error log without the passwords

[25-Mar-2013 16:09:02] PHP Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY000] [1129] Host ‘just21.justhost.com’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’’ in /home3/pintotou/public_html/MySQLTester2/connection2.php:12
Stack trace:
#0 /home3/pintotou/public_html/MySQLTester2/connection2.php(12): PDO->__construct(‘mysql:host=173…’, ‘', '’)
#1 {main}
thrown in /home3/pintotou/public_html/MySQLTester2/connection2.php on line 12
[25-Mar-2013 16:09:06] PHP Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY000] [1129] Host ‘just21.justhost.com’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’’ in /home3/pintotou/public_html/MySQLTester2/connection2.php:12
Stack trace:
#0 /home3/pintotou/public_html/MySQLTester2/connection2.php(12): PDO->__construct(‘mysql:host=173…’, ‘', '’)
#1 {main}
thrown in /home3/pintotou/public_html/MySQLTester2/connection2.php on line 12
[25-Mar-2013 16:10:53] PHP Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY000] [1129] Host ‘just21.justhost.com’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’’ in /home3/pintotou/public_html/MySQLTester2/connection2.php:12
Stack trace:
#0 /home3/pintotou/public_html/MySQLTester2/connection2.php(12): PDO->__construct(‘mysql:host=173…’, ‘', '’)
#1 {main}
thrown in /home3/pintotou/public_html/MySQLTester2/connection2.php on line 12
[25-Mar-2013 16:12:30] PHP Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY000] [1129] Host ‘just21.justhost.com’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’’ in /home3/pintotou/public_html/MySQLTester2/connection2.php:12
Stack trace:
#0 /home3/pintotou/public_html/MySQLTester2/connection2.php(12): PDO->__construct(‘mysql:host=173…’, ‘', '’)
#1 {main}
thrown in /home3/pintotou/public_html/MySQLTester2/connection2.php on line 12
[25-Mar-2013 16:23:08] PHP Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY000] [1129] Host ‘just21.justhost.com’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’’ in /home3/pintotou/public_html/MySQLTester2/connection2.php:12
Stack trace:
#0 /home3/pintotou/public_html/MySQLTester2/connection2.php(12): PDO->__construct(‘mysql:host=173…’, ‘', '’)
#1 {main}
thrown in /home3/pintotou/public_html/MySQLTester2/connection2.php on line 12

20

You are doing things the hard way by not using Sitepoint’s beginner-friendly book on PHP MySQL. I bought an earlier version of their book and it got me up and running after several months of study and practice. Beware of shortcuts that teach you bad habits. They are hard to identify and break once you keep using them.