Results 1 to 1 of 1

Thread: MySQL & PHP

  1. #1

    Thread Starter
    WiggleWiggle dclamp's Avatar
    Join Date
    Aug 2006
    Posts
    3,527

    MySQL & PHP

    Here's a basic run down of the MySQL commands INSERT, DELETE, UPDATE, and SELECT. I might miss a few things, but it's just to give you an understanding of the commands if you don't know them already..

    INSERT:
    Used to insert new information into a table. The following example will insert the values "input1" through "input3" into the table tablename one time. The (field1, ..., field3) brackets are optional, but useful if you want to insert into only certain fields in that table, or if you want to insert information in a different order than the table's fields are in.
    Syntax: INSERT INTO `tablename` (field1, field2, field3) VALUES ('input1', 'input2', 'input3');

    DELETE:
    Used to delete records from a table. Useful only if you want to delete records a few, or one, at a time. If you want to delete an entire table's contents, use TRUNCATE (more on it below). The following example will delete ALL records in tablename where field1 is equal to input1, which means if every record in your table has input1 as the value of field1, they will all be deleted. If you only wanted to delete one record, this can be achieved if you use LIMIT in conjunction with the query, more on that below as well!
    Syntax: DELETE FROM `tablename` WHERE field1='input1';
    --> TRUNCATE: Used to remove the contents of an entire table
    --> Syntax: TRUNCATE `tablename`;

    UPDATE:
    Used to update records in a table. Syntax is very similar to DELETE, and works basically the same way. The following will change every record that has field2 being input2 to be input2a, which means if every record had field2 as input2, then all of the records in the table would be changed. This can also be controlled with the addition of LIMIT, more on that still below.
    Syntax: UPDATE `tablename` SET field2='input2a' WHERE field2='input2';

    SELECT:
    Used to display records of a table. It can select all fields, and all records, or only a select few using the WHERE clause, and will then only display them if the conditions are met. The following will select and return all records and fields from tablename.
    Syntax: SELECT * FROM `tablename`;
    The following will only select field1 from tablename, and only if field3 is equal to input3.
    Syntax: SELECT field1 FROM `tablename` WHERE field3='input3';

    LIMIT:
    This will limit the amount of records returned, and can be quite confusing at times. You can simply add "LIMIT 1" to an UPDATE, SELECT, or DELETE query and it will update, select, or delete only one record. However, if you're making something that will be displaying multiple pages, with a maximum number of records for each page, you can use "LIMIT start, numrecords". The following will select all fields and all records from tablename, and will start at the first record (0), and display 15 records at a time.
    Syntax with SELECT: SELECT * FROM `tablename` LIMIT 0, 15;

    Remember, this was just a basic run down of commands you wanted and their syntax, if you didn't already know. Queries can get a lot more complex and be manipulated in tons of ways, using different operators and other things like that.



    ok... now that that's out of the way!

    Basically, using PHP with MySQL is very easy. The key functions you'll be using are mysql_query() and mysql_fetch_array(), after you've connected to your server and selected a database.

    CONNECT TO SERVER
    So, first off, to connect to a server and select a database you can do this:
    PHP Code:
    <?php
      
    //connecting to
      
    mysql_connect("host""user""pass") or die("could not connect to MySQL server");
      
    //selecting database
      
    mysql_select_db("databasename") or die("could not select MySQL database");
    ?>
    EXECUTE SQL QUERY
    Now, to execute a query in a database, you will use mysql_query(), with the same syntax detailed above. For example, if you wanted to insert data into a database:
    PHP Code:
    <?php
      
    //adding to
      
    mysql_query("INSERT INTO `tablename` VALUES('input1', 'input2', 'input3')");
    ?>
    And that's that. The values input1 through input3 will now be inserted into tablename.

    DELETE
    To delete from a table, or edit, it's the same.
    PHP Code:
    <?php
      
    //deleting from
      
    mysql_query("DELETE FROM `tablename` WHERE field1='input1'");
      
    //truncating table
      
    mysql_query("TRUNCATE `tablename`");
      
    //editing records
      
    mysql_query("UPDATE `tablename` SET field2='input2a' LIMIT 1");
    ?>
    SELECTS
    Now, selecting is a little bit different, unless you're only looking for one record. You will want to create a loop when selecting multiple records so that you can display all of them at a time, otherwise it will only display the first record found (which is useful as well, especially when looking for username/password combinations when logging someone into a system). Here's an example using a while() loop that will display all records in a table:
    PHP Code:
    <?php
      
    //build our query
      
    $query mysql_query("SELECT * `tablename`");
      
    //loop through each record
      
    while($array mysql_fetch_array($query)){
        
    //print out the field values and values using print_r
        
    echo '<pre>';
        
    print_r($array);
        echo 
    '</pre>';
        
    //using print_r is just to show every value, if you want to select
        // --> a specific value, you can use this syntax:
        // --> $array['field1']
      
    }
    ?>
    You can also use LIMITs in the while() loop query to display only a certain number of records, etc.

    Hope that helps a little.

    Code:
    Thanks KOWS for this
    Last edited by dclamp; Oct 1st, 2007 at 07:41 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
  •  



Click Here to Expand Forum to Full Width