Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Dec 2010
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    PHP MySQL database creation and sorting

    I've been teaching a PHP class at a local community center for a few weeks now. The students wanted to start doing some "useful" stuff with PHP so we started working in MySQL but I've been having a lot of trouble with MySQL. It's been a loge time since I've worked with MySQL in PHP and back when I did I was so spoiled by all the frameworks and premade libraries I had.

    We've trudged our way though just making the databases in PHP but now it's time for the user to input some stuff and I'm just dead in the water with this. The idea we had was to make a database where the user would input a student's first and last name as well as graduation year and then sort the database by each of the user inputs in ascending or descending order.

    So far we have been connecting to MySQL like this

    Code:
    <?php
    
    /*** mysql hostname ***/
    $hostname = 'localhost';
    
    /*** mysql username ***/
    $username = 'username';
    
    /*** mysql password ***/
    $password = 'password';
    
    /*** connect to the database ***/
    $link = @mysql_connect($hostname, $username, $password);
    
    /*** check if the link is a valid resource ***/
    if(is_resource($link))
        {
        /*** if we are successful ***/
        echo 'Connected successfully';
    
        /*** close the connection ***/
        mysql_close($link);
        }
    else
        {
        /*** if we fail to connect ***/
        echo 'Unable to connect';
        }
    ?>
    I've not been able to get this example started successfully at all. Any help at all would be greatly appreciated. I just want to give them the best and most up to date information that I can. While I wait for a response I will be franticly reading my old PHP and MySQL ebooks.

  • #2
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,469
    Thanks
    8
    Thanked 1,085 Times in 1,076 Posts
    I assume you already have the database created?

    And then, you have to create a table (or tables) and some columns.

    Not sure how much you have created.

    This is the basic "connect" script:
    PHP Code:
    <?php
    // Connect to MySQL Database
    $dbhost 'localhost';
    $dbuser 'username';
    $dbpass 'password';
    $dbname 'databasename';

    $conn mysql_connect($dbhost$dbuser$dbpass) or die('Error connecting to mysql');
    mysql_select_db($dbname) or die('Cannot select database');

    // Now, you can query the database.

    ?>

    Note, that all webhosts don't use "localhost" as the host name.
    You have to consult with your webhost on how they reference that.

    If you created the database, and made a table with columns (variable names),
    let us know. Tell us what your table is called, and some variable names.

    You access your MySQL using your webhost's control panel (MySQL admin).



    .

  • #3
    Regular Coder
    Join Date
    May 2008
    Posts
    446
    Thanks
    23
    Thanked 5 Times in 5 Posts
    Some side thoughts on the development of the database... use phpMyAdmin, it comes with xamp, and any cPanel with a web hoster. creating databses, tables, and fields with it is a breeze. A 10 minute youtube video tutorial will tell you most everything you will need to know.

    As for your learning material I would suggest avoiding old programming books, after a couple of years the syntax gets deprecated. I would suggest PHP.net and mysql.com. They both have very thorough and up to date documentation on the coding and pitfalls to be aware of.

  • #4
    New to the CF scene
    Join Date
    Dec 2010
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I thank you for the responses but I guess I did a bad job of explaining what I needed. As far as the web hosting stuff goes we are using a nice little server that I installed a lamp stack on. We have not created the database yet but that's not an issue.

    As far as using phpmyadmin to do this I'd rather not. We do have it installed and I showed them how to use it but I want to make a webpage that they can go to and add students. Since the next step is (as far as I was planning) is making users (with a username and password) for a website so using phpmyadmin would not really be effect for what I was hoping to do. I want all of this to be done in php.

    The thing I have having trouble with is writing the script that will allow the user to come to a webpage with a html form, enter their data, then use php to add the data to mysql. Creating for form is no problem it's using php to add the data to mysql that I'm not sure about. Short of reading the whole php manual I was hoping someone could give me an example or tell me where to look in the manual. I'm also not entirely sure of how to write the sorting routines in php.

    I just gave the connection script since I know there are about a thousand ways to connect to mysql with php. As far as I know using different connection methods changes the way you do things a little bit.

  • #5
    Regular Coder
    Join Date
    May 2008
    Posts
    446
    Thanks
    23
    Thanked 5 Times in 5 Posts
    To give you an idea of what to look for:

    in the action portion of your form define a page to take the user to that will hold the php function for creating a new student and submitting it to the mysql database.

    your form input fields will need the name="post[first_name]" attribute so PHP knows what is what during processing. Make sure your form has the attribute method="post" so post[first_name] will make sense.

    After firing the fucntion holding all the processing and query strings, you will want your function to look similar to this:

    PHP Code:
    function create_profile($params)
      {
        
    $connection db_connect();
        
    $query sprintf("INSERT INTO profile SET 
                              first_name = '%s',
                              last_name = '%s',
                              born = '%s',
                              interests = '%s',
                              personality = '%s',
                  briefdescribe = '%s',
                              time = NOW(),
                              profile_id = '%s'"
    ,
                              
    mysql_real_escape_string($params['first_name']),
                              
    mysql_real_escape_string($params['last_name']),
                              
    mysql_real_escape_string($params['born']),
                              
    mysql_real_escape_string($params['interests']),
                              
    mysql_real_escape_string($params['personality']),
                              
    mysql_real_escape_string($params['briefdescribe']),
                              
    mysql_real_escape_string($params['profile_id']));
        
    $result mysql_query($query);
        if (!
    $result) return false;
        else return 
    true;
      } 
    Something right along those lines, just need to adjust it to fit your field names and what data you want to save.

    As far as PHPMyAdmin goes, I dont use it to add data into the fields. I just use it to create empty databases, tables, and fields and define the primary along with other properties for the fields... but I leave them empty, I use PHP and mysql to fill them.

  • #6
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,469
    Thanks
    8
    Thanked 1,085 Times in 1,076 Posts
    I think we're saying, use phpMyAdmin to create the database, table, and columns.
    Once that is done, you can then use PHP to access it ... no more phpMyAdmin.

    The database, tables and columns must exist ... or you can't use PHP to query it.


  •  

    Posting Permissions

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