[Legolas]

Hello [username].
I decided to start a big series of Php tutorials.
In this very analitic tutorial, I will show you how you can use PHP to write code and use commands to connect to a Mysql database, add records , edit them, remove records and finally how to display them.

___________________________________________________________________________________________________________


Connecting to the database

PHP Code:

<?php function dbconnect() { // Connection to the Server $db_connect = mysql_connect("localhost", "db_user", "db_password") or die ("Failed to connect to the server"); // If use utf8 encoding and it does not display something correctly. // mysql_query("SET NAMES 'utf8'", $db_connect); // Database Selection $db_select = mysql_select_db("db_name", $db_connect) or die ("Failed to select the database"); } ?>

With the following, we connect to mysql :

PHP Code:

mysql_connect("localhost", "db_user", "db_password")

At the end, we select the database name :

PHP Code:

$db_select = mysql_select_db("db_name", $db_connect)

It helps us to have the above function in a separate file, and whenever we need it, we import it. So, if we want to change for example the password or user, we do it in this file (dbconn.php).
To import the file of the function in another php file, we use the include() command as follows:

PHP Code:

<?php // Importing the file with the function include(“dbconn.php”); // We call the Function dbconnect();

Commands

PHP Code:

// Disconnection from the Server /mysql_close($db_connect); ?>

Although it is not necessary to do disconnection, it would be good, if we do not forget this.

___________________________________________________________________________________________________________


Adding and updating records in a MySql database

PHP Code:

<?php // Importing the file with the function include(“dbconn.php”); // We call the function dbconnect(); … // Disconnection from the Server /mysql_close($db_connect); ?>

Then, with the following code, we declare what we want to assign and where.

PHP Code:

// Adding Data $qAdd = mysql_query("INSERT INTO clients (id, name, surname) VALUES ('', 'Legolas', 'Greenleaf')") or die (There was a problem importing the data.")); if ($qAdd) { echo "The Data has been added!"; }

While, with the following we declare what we want to change/edit.

PHP Code:

// Updating Data $qUpdate=mysql_query("UPDATE clients SET name = 'name', surname = 'surname' WHERE id='1' "); if ($qUpdate) { echo "The Data has been updated!"; }

So, the add.php file takes the following form :

PHP Code:

<?php // Importing the file with the function include(“dbconn.php”); // We call the function dbconnect(); // Adding Data $qAdd = mysql_query("INSERT INTO clients (id, name, surname) VALUES ('', 'Legolas', 'Greenleaf')") or die ("There was a problem adding the data.")); if ($qAdd) { echo "The Data has been added!"; } // Disconnection from the Server /mysql_close($db_connect); ?>

While, the update.php the following:

PHP Code:

<?php // Importing the file with the function include(“dbconn.php”); // We call the function dbconnect(); // Updating Data $qUpdate=mysql_query("UPDATE clients SET name = 'name', surname = 'surname' WHERE id='1' "); if ($qUpdate) { echo "The Data has been updated!"; } // Disconnection from the Server /mysql_close($db_connect); ?>

___________________________________________________________________________________________________________


Displaying records from a MySql Database

At first, we create an index.php file and we call the function, to connect to the database, which we created before.

PHP Code:

<?php // Importing the file with the function include(“dbconn.php”); // We call the Function dbconnect(); … // Disconnection from the Server /mysql_close($db_connect); ?>

Then, with the following code, we extract the results out of our database.

PHP Code:

// Query the database $q=mysql_query("SELECT * FROM clients "); $num = mysql_num_rows($q); // If there are Results if ($num > 0 ) { $i=0; while ($i < $num) { // We create names for the variables $name = mysql_result($q,$i,"name"); $surname = mysql_result($q,$i,"surname"); // Displaying Variables echo "Name: $name<br />"; echo "Surname: $surname <br />"; // If there are not Results ++$i; } } else { echo "They have not been registered data for you yet."; }

So, this file takes the following form.

PHP Code:

<?php // Importing the file with the function include(“dbconn.php”); // We call the Function dbconnect(); // Query the database $q=mysql_query("SELECT * FROM clients "); $num = mysql_num_rows($q); // If there are Results if ($num > 0 ) { $i=0; while ($i < $num) { // We create names for the variables $name = mysql_result($q,$i,"name"); $surname = mysql_result($q,$i,"surname"); // Displaying Variables echo "Name: $name<br />"; echo "Surname: $surname <br />"; // If there are not Results ++$i; } } else { echo "They have not been registered data for you yet."; } // Disconnection from the Server /mysql_close($db_connect); ?>

We can include/comprise different criteria in our search, such as if the name is "Legolas" .
This is done by adding WHERE name=’Legolas’ to the mysql query.

PHP Code:

$q=mysql_query("SELECT * FROM clients WHERE name=’Legolas’");

___________________________________________________________________________________________________________


Deleting records in a MySql Database

At first, we create a delete.php file and we call the function, to connect to the database, which we created before.

PHP Code:

<?php // Importing the file with the function include(“dbconn.php”); // We call the function dbconnect(); … // Disconnection from the Server /mysql_close($db_connect); ?>

Then, with the following code, we declare what we want to delete.

PHP Code:

// Removing Data $qRemove = mysql_query("DELETE FROM clients WHERE id='1' "); if ($qRemove) { echo "The Data has been removed!"; } // So, the file takes the following form. <?php // Importing the file with the function include(“dbconn.php”); // We call the function dbconnect(); // Removing Data $qRemove = mysql_query("DELETE FROM clients WHERE id='1' "); if ($qRemove) { echo "The Data has been removed!"; } // Disconnection from the Server /mysql_close($db_connect); ?>

___________________________________________________________________________________________________________



I hope you enjoyed this.
More Php tutorials are coming soon.

Legolas

[Psycho_Coder]

Why do you use the old mysql_connect(), its vulnerable and also the PHP community discourages using this, its there just for legacy support. Use Mysqli instead as that is better. I suppose you know this already but still making it with mysqli would have been better.

using include() is not good instead using include_once is much better imo.

This more or less CRUD application you made is often a very common example, and a simple Google search will fetch even better results with more explanation tutorials than the one you posted here. So whats the use of such a tutorial ?

Also I don't think calling this thread even a tutorial would be correct since all you did was posting the code and hence using a source code prefix would have been much better.

It is often a good practice to create a config.inc.php file or something and then define the parameters using define() and then use require_once(), example :-

define("DB_HOST", "localhost");
define("DB_USER", "root");
define("DB_PASS", "********");

etc. etc.

Conclusion:-

You worked to make it better but still made the tutorial lack in several ways.

If you think that we should teach the users from basic then believe me, people who will join this site most of them don't read tutorials like this if they are not detailed or some new techniques or tricks are covered. They will always choose something which is much more informative so that their time is not wasted. But if you can cover things that requires more Googling then a user will surely read your thread as they can get more information in one thread.

If you decide to make them in smaller parts then thats useless too since if something can be compiled together as one and if they are linked then it should remain linked.

If it was meant for other better and regular users then probably they would do the same as above and also probably a lot of good members here already knew these simple things.

So, to sum up I would say that the thread should have been much more detailed and should have covered tricks or traps.


Thank you,
Sincerely,
Psycho_Coder.

[Ex094]

I've many time emphasized users to use mysqli instead of mysql but they seem to be lazy in solving the small errors encountered because they haven't used it.. Your tutorial will be useless for new devs because you are using an old and unsecured means of connecting to your database that might lead to it's exploitation by some skid. Good efforts but use mysqli

[Legolas]

I've many time emphasized users to use mysqli instead of mysql but they seem to be lazy in solving the small errors encountered because they haven't used it.. Your tutorial will be useless for new devs because you are using an old and unsecured means of connecting to your database that might lead to it's exploitation by some skid. Good efforts but use mysqli

Thank you for the response ! :Smile: I really appreciate it.

Yes, my tutorial is a bit old and has the the " unsecured way ". However, i wanted to start the series from something which is easy.
But, as i said on the first line, i will continue the Php tutorial series. And yes! I should use mysqli and i will do it. :Thumbs-Up:

P.S. I appreciate that you recognize my effort.

[Ex094]

1 more suggestion would be less usage of the "SELECT *" instead you should type the name of all the rows from which you want to extract the data because when you carry out the "SELECT *" it does more load on the server than "SELECT row1, row2, row3" cuz it knows which rows to fetch... I read it somewhere and it was very useful

[Anima Templi]

I have pretty much all the same things to say as above, BUT I'll recommend you use PDO for the db integration instead of mysqli. Why this? PDO provides single interface across multiple databases. That means you can use multiple DB without using mysql_query for mysql, mssql_query for MS sql etc. Just use something like $db->query("INSERT INTO..."). No matter what DB driver you are using.

http://php.net/manual/en/intro.pdo.php

[zomgwtfbbq]

I have pretty much all the same things to say as above, BUT I'll recommend you use PDO for the db integration instead of mysqli. Why this? PDO provides single interface across multiple databases. That means you can use multiple DB without using mysql_query for mysql, mssql_query for MS sql etc. Just use something like $db->query("INSERT INTO..."). No matter what DB driver you are using.

http://php.net/manual/en/intro.pdo.php

Yes totally agree, I always use PDO. Here's a good read about preventing sql injections when using prepared statements.
http://stackoverflow.com/questions/13409...-injection.

Thanks Legolas, you put a lot of effort in your post. :Thumbs-Up:

[Legolas]

I have pretty much all the same things to say as above, BUT I'll recommend you use PDO for the db integration instead of mysqli. Why this? PDO provides single interface across multiple databases. That means you can use multiple DB without using mysql_query for mysql, mssql_query for MS sql etc. Just use something like $db->query("INSERT INTO..."). No matter what DB driver you are using.

http://php.net/manual/en/intro.pdo.php

That really was a helpful and qualitative reply. :Thumbs-Up: Yes. I am going to use PDO.
PDO has a better API. It has some good features, which protect your code from SQL-injection attacks. Also, yes, your code is more safe. So, that's true that PDO is better choice than MySql way. ( than mysql functions )


Thank you ! :Smile:
______________________________________________________________________________________________________________

@zomgwtfbbq ,
Thank you too ! :Smile: :Thumbs-Up: I appreciate that you recognize my effort.

I will check it.

[waghcwb]

Oh, thanks for it. It will be useful.