Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Day 13 - MySQL - Become PHP Expert in 30 days

  1. #1
    Join Date
    Sep 2003
    Posts
    3,040

    Default Day 13 - MySQL - Become PHP Expert in 30 days

    MySQL

    MySQL is very popular database used for storing data in web sites.

    Why Use Database ?

    Database make it easy to store and retrieve data.

    SQL (Standard Query Language) is used to retrieve data from database. SQL is a common language used by many database programs.

    How to connect to MySQL Server ?

    To connect to a MySQL server, you need to know IP Address of the MySQL server, Database name, user name and password.

    Tables

    Before we can store data in a database, we need to create table.

    If Database is a folder, tables are like files. Only tables can store real data. In database, we can't save random data, we create multiple tables for type of data.

    For example, to store students information, we create students table. A table have muliple fields, in case of students table

    * name
    * standard
    * address
    * score
    * age

    etc..

    Out Test Database

    You can use following database for next few lessons.

    Code:
    MySQL Server IP: 127.0.0.1
    Database: fwhphp_db
    User: fwhphp_user
    Password: k5BJRaX6SFbs
    As you can see, we have a very complicated password, so it is secure to post it on a public form :)

    It is very important we don't show our database login details to public, but in this case, it is not a big issue as we don't have anything important in it and we can delete it any time.

    Let's Connect to MySQL Server

    We use MySQLi (MySQL Improved) function to connect to MySQL server. This is the new recommended method.

    day_13_mysql_connect.php

    PHP Code:
    <?php

    $db_server 
    '127.0.0.1';
    $db_user '';
    $db_password '';
    $db_name '';

    $mysqli = new mysqli($db_server$db_user$db_password$db_name);

    if (
    $mysqli->connect_errno) {
        echo 
    'Connect failed: ' $mysqli->connect_error;
        exit();
    } else {
        echo 
    '<h1>Success! Welcome to MySQL database server.</h1>';
    }
    In above code, you need to set value for following variables.

    PHP Code:
    $db_user '';
    $db_password '';
    $db_name ''
    Try with wrong user name, password and db name and see what error message you get. You should be able to read such error message and understand why that error occurred. So simulate error conditions by using wrong values for the variables.

    Reading Data From MySQL

    To start, i created a table with some sample data.

    Name of the table is "chat", it have 3 fields.

    * id (numeric field)
    * name (text field)
    * message (text field)

    day_13_mysql_read.php

    PHP Code:
    <?php

    $db_server 
    '127.0.0.1';
    $db_user 'fwhphp_user';
    $db_password 'k5BJRaX6SFbs';
    $db_name 'fwhphp_db';

    $mysqli = new mysqli($db_server$db_user$db_password$db_name);

    if (
    $mysqli->connect_errno) {
        echo 
    'Connect failed: ' $mysqli->connect_error;
        exit();
    }

    $sql 'SELECT * FROM `chat`';
    $result $mysqli->query($sql);

    echo 
    '<p>MySQL have returned ' $result->num_rows ' records.</p>';

    echo 
    '<br>';
    echo 
    '<hr>';

    while (
    $chat $result->fetch_object()) {
        echo 
    '<b>' .  $chat->name '</b> says : ' $chat->message;
        echo 
    '<br>';
        echo 
    '<hr>';
    }
    Tomorrow we will see how to store data into a table.

    EDIT: Due to today PHP downgrade on server48, my previous example code did not work, so i have to use while() loop. While loop allow you to read all records from MySQL, it is somewhat like foreach.




    Become PHP Expert in 30 days
    FreeMarriage.com - Free Online Matrimonial
    FlashWebHost.com - Professional Web Hosting, Designing.

  2. #2
    Join Date
    Nov 2004
    Location
    India
    Posts
    65

    Default

    http://php.flashwebhost.com/stefin/mysql.php

    Code:
    <?php 
    
    
    $db_server = '127.0.0.1'; 
    $db_user = 'fwhphp_user'; 
    $db_password = 'k5BJRaX6SFbs'; 
    $db_name = 'fwhphp_db'; 
    
    
    $mysqli = new mysqli($db_server, $db_user, $db_password, $db_name); 
    
    
    if ($mysqli->connect_errno) { 
        echo 'Connect failed: ' . $mysqli->connect_error; 
        exit(); 
    } 
    
    
    $sql = 'SELECT * FROM `chat`'; 
    $result = $mysqli->query($sql); 
    
    
    echo '<p>MySQL have returned ' . $result->num_rows . ' records.</p>'; 
    
    
    echo '<br>'; 
    echo '<hr>'; 
    
    
    foreach ($result as $row) { 
        echo '<b>' .  $row['name'] . '</b> says : ' . $row['message']; 
        echo '<br>'; 
        echo '<hr>'; 
    }

  3. #3
    Join Date
    Sep 2003
    Posts
    3,040

    Default

    Quote Originally Posted by stefin View Post
    http://php.flashwebhost.com/stefin/mysql.php

    Code:
    <?php 
    
    
    $db_server = '127.0.0.1'; 
    $db_user = 'fwhphp_user'; 
    $db_password = 'k5BJRaX6SFbs'; 
    $db_name = 'fwhphp_db'; 
    
    
    $mysqli = new mysqli($db_server, $db_user, $db_password, $db_name); 
    
    
    if ($mysqli->connect_errno) { 
        echo 'Connect failed: ' . $mysqli->connect_error; 
        exit(); 
    } 
    
    
    $sql = 'SELECT * FROM `chat`'; 
    $result = $mysqli->query($sql); 
    
    
    echo '<p>MySQL have returned ' . $result->num_rows . ' records.</p>'; 
    
    
    echo '<br>'; 
    echo '<hr>'; 
    
    
    foreach ($result as $row) { 
        echo '<b>' .  $row['name'] . '</b> says : ' . $row['message']; 
        echo '<br>'; 
        echo '<hr>'; 
    }
    Congratulation on first to try the MySQL script and post :)

    Due to PHP version change, original code did not work, to fix.

    Find

    PHP Code:
    foreach ($result as $row) { 
        echo 
    '<b>' .  $row['name'] . '</b> says : ' $row['message']; 
        echo 
    '<br>'
        echo 
    '<hr>'

    Replace With

    PHP Code:
    while ($chat $result->fetch_object()) {
        echo 
    '<b>' .  $chat->name '</b> says : ' $chat->message;
        echo 
    '<br>';
        echo 
    '<hr>';


    while() loop, we will learn more about it tomorrow. For now, just use it. It is some what like foreach.
    Become PHP Expert in 30 days
    FreeMarriage.com - Free Online Matrimonial
    FlashWebHost.com - Professional Web Hosting, Designing.

  4. #4
    Join Date
    Sep 2003
    Posts
    3,040

    Default

    Look like there is still some problem with the code, i will check and update my code..

    Problem was caused by today's PHP downgrade on server48 due to some clients still have older scripts and those sites where down after we upgrade to latest PHP version.

    I will update script example, we have to use a new PHP function called while... and Object for this example to work on PHP version 5.3.x
    Become PHP Expert in 30 days
    FreeMarriage.com - Free Online Matrimonial
    FlashWebHost.com - Professional Web Hosting, Designing.

  5. #5
    Join Date
    Nov 2009
    Posts
    76,596

    Default

    http://php.flashwebhost.com/sherly/mysql.php

    Code:
    <?php
    
    $db_server = '127.0.0.1';
    $db_user = 'fwhphp_user';
    $db_password = 'k5BJRaX6SFbs';
    $db_name = 'fwhphp_db';
    
    $mysqli = new mysqli($db_server, $db_user, $db_password, $db_name);
    
    if ($mysqli->connect_errno) {
        echo 'Connect failed: ' . $mysqli->connect_error;
        exit();
    }
    
    $sql = 'SELECT * FROM `chat`';
    $result = $mysqli->query($sql);
    
    echo '<p>MySQL have returned ' . $result->num_rows . ' records.</p>';
    
    echo '<br>';
    echo '<hr>';
    
    while ($chat = $result->fetch_object()) {
        echo '<b>' .  $chat->name . '</b> says : ' . $chat->message;
        echo '<br>';
        echo '<hr>';
    }
    Last edited by sherlyk; 08-02-2014 at 04:15 PM.

  6. #6
    Join Date
    Sep 2003
    Location
    india
    Posts
    11,527

    Default

    http://php.flashwebhost.com/annie/mysql.php

    Code:
    <?php
    
    $db_server = '127.0.0.1';
    $db_user = 'fwhphp_user';
    $db_password = 'k5BJRaX6SFbs';
    $db_name = 'fwhphp_db';
    
    $mysqli = new mysqli($db_server, $db_user, $db_password, $db_name);
    
    if ($mysqli->connect_errno) {
        echo 'Connect failed: ' . $mysqli->connect_error;
        exit();
    }
    $sql = 'SELECT * FROM `chat`';
    $result = $mysqli->query($sql);
    
    echo '<p>MySQL have returned ' . $result->num_rows . ' records.</p>';
    
    echo '<br>';
    echo '<hr>';
    
    while ($chat = $result->fetch_object()) {
      echo '<b>' .  $chat->name . '</b> says : ' . $chat->message;
      echo '<br>';
      echo '<hr>';
    }

  7. #7
    Join Date
    Sep 2003
    Posts
    3,040

    Default

    Quote Originally Posted by annie View Post
    http://php.flashwebhost.com/annie/mysql.php

    Code:
    <?php
    
    $db_server = '127.0.0.1';
    $db_user = 'fwhphp_user';
    $db_password = 'k5BJRaX6SFbs';
    $db_name = 'fwhphp_db';
    
    $mysqli = new mysqli($db_server, $db_user, $db_password, $db_name);
    
    if ($mysqli->connect_errno) {
        echo 'Connect failed: ' . $mysqli->connect_error;
        exit();
    }
    $sql = 'SELECT * FROM `chat`';
    $result = $mysqli->query($sql);
    
    echo '<p>MySQL have returned ' . $result->num_rows . ' records.</p>';
    
    echo '<br>';
    echo '<hr>';
    
    while ($chat = $result->fetch_object()) {
      echo '<b>' .  $chat->name . '</b> says : ' . $chat->message;
      echo '<br>';
      echo '<hr>';
    }

    Good, we will need this code on Day 14 to see data saved to database.
    Become PHP Expert in 30 days
    FreeMarriage.com - Free Online Matrimonial
    FlashWebHost.com - Professional Web Hosting, Designing.

  8. #8
    Join Date
    Nov 2004
    Location
    India
    Posts
    65

    Default

    I corrected the code. Thank you for the help.

    http://php.flashwebhost.com/stefin/mysql_1.php


    Code:
    <?php 
    
    
    $db_server = '127.0.0.1'; 
    $db_user = 'fwhphp_user'; 
    $db_password = 'k5BJRaX6SFbs'; 
    $db_name = 'fwhphp_db'; 
    
    
    $mysqli = new mysqli($db_server, $db_user, $db_password, $db_name); 
    
    
    if ($mysqli->connect_errno) { 
        echo 'Connect failed: ' . $mysqli->connect_error; 
        exit(); 
    } 
    
    
    $sql = 'SELECT * FROM `chat`'; 
    $result = $mysqli->query($sql); 
    
    
    echo '<p>MySQL have returned ' . $result->num_rows . ' records.</p>'; 
    
    
    echo '<br>'; 
    echo '<hr>'; 
    
    
    while ($chat = $result->fetch_object()) {
        echo '<b>' .  $chat->name . '</b> says : ' . $chat->message;
        echo '<br>';
        echo '<hr>';
    }

  9. #9
    Join Date
    Sep 2003
    Posts
    3,040

    Default

    Quote Originally Posted by stefin View Post
    I corrected the code. Thank you for the help.

    http://php.flashwebhost.com/stefin/mysql_1.php


    Code:
    <?php 
    
    
    $db_server = '127.0.0.1'; 
    $db_user = 'fwhphp_user'; 
    $db_password = 'k5BJRaX6SFbs'; 
    $db_name = 'fwhphp_db'; 
    
    
    $mysqli = new mysqli($db_server, $db_user, $db_password, $db_name); 
    
    
    if ($mysqli->connect_errno) { 
        echo 'Connect failed: ' . $mysqli->connect_error; 
        exit(); 
    } 
    
    
    $sql = 'SELECT * FROM `chat`'; 
    $result = $mysqli->query($sql); 
    
    
    echo '<p>MySQL have returned ' . $result->num_rows . ' records.</p>'; 
    
    
    echo '<br>'; 
    echo '<hr>'; 
    
    
    while ($chat = $result->fetch_object()) {
        echo '<b>' .  $chat->name . '</b> says : ' . $chat->message;
        echo '<br>';
        echo '<hr>';
    }

    Very nice, you don't need to remember the code, just use this code with little modification when you need to read data from MySQL.
    Become PHP Expert in 30 days
    FreeMarriage.com - Free Online Matrimonial
    FlashWebHost.com - Professional Web Hosting, Designing.

  10. #10
    Join Date
    Apr 2005
    Posts
    46,704

    Default

    http://php.flashwebhost.com/mini/mysql.php

    Code:
    <?php
    
    $db_server = '127.0.0.1';
    $db_user = 'fwhphp_user';
    $db_password = 'k5BJRaX6SFbs';
    $db_name = 'fwhphp_db';
    
    $mysqli = new mysqli($db_server, $db_user, $db_password, $db_name);
    
    if ($mysqli->connect_errno) {
        echo 'Connect failed: ' . $mysqli->connect_error;
        exit();
    }
    
    $sql = 'SELECT * FROM `chat`';
    $result = $mysqli->query($sql);
    
    echo '<p>MySQL have returned ' . $result->num_rows . ' records.</p>';
    
    echo '<br>';
    echo '<hr>';
    
    while ($chat = $result->fetch_object()) {
        echo '<b>' .  $chat->name . '</b> says : ' . $chat->message;
        echo '<br>';
        echo '<hr>';
    }

Page 1 of 2 12 LastLast

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •