Thread: [PHP/MySQL] MySQL > PHP Connecting and Retrieving Information [PHP/MySQL]

Results 1 to 3 of 3
  1. #1 [PHP/MySQL] MySQL > PHP Connecting and Retrieving Information [PHP/MySQL] 
    puts "Hi "+name+
    Ruby's Avatar
    Join Date
    Apr 2012
    Age
    30
    Posts
    346
    Thanks given
    42
    Thanks received
    135
    Rep Power
    66
    Hello, Rune-Server.

    This tutorial will be taking us another step down the line of PHP into connecting to a database and retrieving information from the tables and columns inside that database, so this will cover all aspects of PHP/SQL connections. This is not a tutorial on SQL, however I will include some useful statements that you can use. Without further ado, my MySQL > PHP Connections tutorial.

    N.B - Please note that I expect you to have a database already set up and ready to use, this is only for connections to your tables.

    [hr]

    Part 1 - Populating your MySQL table.
    First of all, you want to add a table and add fields to your table, this is done through the use of an SQL script. I realise this can seem a little daunting, but it's not hard. As usual, I'll give you the code and then I'll talk through it after:
    Code:
    CREATE TABLE IF NOT EXISTS `tut` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `title` varchar(50) NOT NULL,
      `text` text NOT NULL,
      `date` varchar(25) NOT NULL,
      PRIMARY KEY (`id`)
    )
    So, your first line of your script is more or less telling MySQL to create the table "tut" if it doesn't already exist. Your next line is then defining the row "id" this is an integer, which can be up to 11 characters long. It is a blank value, so it isn't null, because there's a difference between empty and a blank value and nothing. A blank value is something, where if there is no value, it is nothing. This is also auto incrementing which means when every new record is added, it gives it a new id, 1 higher than the previous id. You then create the row of "title" this is declared as a varchar holding up to 50 characters, a varchar is a variable-length string. Again, a blank value. You then create the row "text" this is declared as text, which is self explanatory and is again, a blank value therefore not null. Your final row is the date, this is a varchar (variable-length string) holding up to 25 characters, again a not null value since it is blank and not nothing. We're then declaring our primary key as the row of "id". This is more used for your most vital tables, as somewhat of a search process.

    Now that you've done that, you want to click "insert" and then insert the values you wish each of these to hold. For example;

    Title - Tutorial!
    Text - Welcome to Ruby's tutorial!
    Date - 3/9/12

    The ID isn't needed since it's auto incrementing.

    If you didn't understand that, have a run back through it a few times until you do.

    Moving swiftly on to our next port of call; connecting, opening and closing through PHP.

    So, after creating and understanding our table, here is the PHP part. We'll use three very simple functions here; mysql_connect(), mysql_select_db(), mysql_close(). These aren't hard to remember, but if you aren't good at remembering functions such as this, write them down somewhere or bookmark this tutorial for easy access. As usual I'll give you the coding then I'll talk you through it.
    Code:
    $username = "yourmysqlusername";
    $password = "yourmysqlpassword";
    $database = "yourprefix_tut";
    $server = "localhost"; //99% of the time this is localhost, if it isn't, you'll know what it is.
    
    $connect = mysql_connect($server, $username, $password);
    
    $found = mysql_select_db($database, $connect);
    
    if ($found) {
    echo 'database located!';
    mysql_close($connect);
    } else {
    echo 'database could not be located, rewrite your code';
    }
    This above code is really simple to understand so I won't go into too much detail. All you're doing is declaring 4 variables holding information on your database. $username, $password are your username and password. $database is the database you wish to select and $server is the server on which your database is hosted; usually localhost. We now declare the variable $connect this holds the function mysql_connect();, which now has $server, $username and $password in it, all the information you need to connect. We then declare $found for the database being found, this is where we make use of the mysql_select_db(); function, inside this function we have $database which is the database we want to connect to and we include $connect for good measures. We then start an if statement saying that if $found works and we find the database and manage to connect, it echoes some code saying we managed it. If we didn't manage to connect it tells us to re-write our code. However, it should work 100% since I just tested it two minutes ago.

    Part 3 - Selecting data from the database.

    Now that you've connected, we can now start to retrieve information from the database that you populated with the record from the first part.

    Now, for this we will be using our script we created two minutes ago with some new code added in. I'll post the new script and then I'll talk you through it again, as usual.
    Code:
    $username = "yourmysqlusername";
    $password = "yourmysqlpassword";
    $database = "yourprefix_tut";
    $server = "localhost"; //99% of the time this is localhost, if it isn't, you'll know what it is.
    
    $connect = mysql_connect($server, $username, $password);
    
    $found = mysql_select_db($database, $connect);
    
    if ($found) {
    
    $SQL = "SELECT * FROM yourprefix_tut";
    $result = mysql_query($SQL);
    
    while ($dbrow = mysql_fetch_assoc($result)) {
    echo $dbrow['ID'] . "<br />";
    echo $dbrow['title'] . "<br />";
    echo $dbrow['text'] . "<br />";
    echo $dbrow['date'] . "<br />";
    }
    mysql_close($connect);
    } else {
    echo 'database could not be located, rewrite your code';
    }
    Okay, so here's the break down. Everything has been explained above apart from this code:
    Code:
    $SQL = "SELECT * FROM yourprefix_tut";
    $result = mysql_query($SQL);
    
    while ($dbrow = mysql_fetch_assoc($result)) {
    echo $dbrow['ID'] . "<br />";
    echo $dbrow['title'] . "<br />";
    echo $dbrow['text'] . "<br />";
    echo $dbrow['date'] . "<br />";
    }
    So this is all I'll explain. First off, we're declaring $SQL which contains an sql query using the SELECT function in SQL. This also has a "*" in it, this just means all, the FROM is then asking from which database you wanted it loaded. We're then doing $result as our sql query, using a new function "mysql_query();" which now contains the $SQL variable which has all the information the query needs to run. We then start a while loop, if you haven't read my previous tutorials there's one about loops in there. We're declaring the new variable $dbrow which equals the data returned by $result from the SQL query. We're then echoing $dbrow and it's actual rows, e.g `text` and `title` as ['text'] and ['title']. If you have read my arrays tutorial, you'll realise this information has been returned in an array! After these is just simple line breaks, remember your " . " or it won't work, see previous tutorials as to why.

    So you have now successfully wrote a database, created a table, added rows to a table, added records to that table, wrote a PHP script to access that database and then echoed the results you added in to that table earlier, happy? Good.

    We're now finished with this tutorial.

    Until then,
    Ruby.
    Professional website developer
    Almost a decade experience
    Bespoke website templates
    CMS & forum themes
    Future proof & secure scripts
    We should chat...
    Reply With Quote  
     

  2. #2  
    Adobe

    Dreamweaver's Avatar
    Join Date
    Jan 2010
    Posts
    1,066
    Thanks given
    38
    Thanks received
    149
    Rep Power
    246
    Nice tut, but you should have just made one big post, instead of like 20 dif ones...
    Reply With Quote  
     

  3. #3  
    Officially Retired

    Huey's Avatar
    Join Date
    Jan 2008
    Age
    22
    Posts
    16,478
    Thanks given
    3,385
    Thanks received
    7,727
    Rep Power
    5000
    Quote Originally Posted by Sicria View Post
    Nice tut, but you should have just made one big post, instead of like 20 dif ones...

    Rather not, Rather him have created multiple threads on different tuts then cramming them into one thread making the thread massive and unattractive.
    Attached image
    Listen children don't become this guy.
    Quote Originally Posted by Owner Spikey View Post
    Why can I attack lower level npc's in a matter of a mouse hover but for a higher level npc the only choice to attack is by right clicking option attack?

    Reply With Quote  
     


Thread Information
Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


User Tag List

Similar Threads

  1. My first steps into PHP and mySQL
    By Icedice in forum Website Development
    Replies: 7
    Last Post: 03-01-2012, 10:37 PM
  2. php - mysql
    By minutes in forum Application Development
    Replies: 5
    Last Post: 01-15-2012, 03:57 AM
  3. PHP MySQL help?
    By Plasma in forum Application Development
    Replies: 1
    Last Post: 10-14-2011, 08:08 AM
  4. Help (PHP, MYSQL)
    By Adimral Slee in forum Help
    Replies: 2
    Last Post: 06-22-2009, 08:29 PM
  5. [PHP] Connecting to MySql
    By Harvey in forum Application Development
    Replies: 9
    Last Post: 01-24-2009, 12:26 PM
Posting Permissions
  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •