Support Library > General Information > Working with MySQL and phpMyAdmin

MySQL and PHP MyAdmin

Before you get started please be advised. If you have no experience working with MySQL or PHP you should consult with a developer or visit your local bookstore. We will provide technical support for the operation of these products, however, we can't provide training or development support.  This sample is provided only to demonstrate one approach to using the phpMyAdmin interface. To learn more about MySQL please visit  http://www.mysql.com/documentation/

If you have requested a MySQL database, we create an empty database using your account login information. The database is empty because we have no idea what type of information you want to store.  In this example, we have a small database called "myusers" where we want to store the name, email address and job function of each user.  We login to the interface and we see our empty database.  We need to create a table to hold our data.

As shown above, we create a table called "users" with 3 fields in it.  The 3 fields will hold the Name, Email address, and Job of each user. We click the Go button. 

We assign our 3 fields in the newly created table, as shown above.  Each is a character field.  We assign a length that we feel will be more than long enough to hold any record. Note that we selected VARCHAR for the field type and "not null". This means that the field can't be blank and the field will contain Variable Characters.  There are other options for the fields that might be more useful for what you are tying to accomplish.  

Scrolling to the right in the field assignment screen, we set the option to make the first field, "Name", the key field we want to search on.  A key field is what allows you to look up records in a table. When done we click the Save key (shown above in previous screen).

The screen above shows that our table has been created. You can do many things from this interface including using direct SQL statements on your database. Many screens have links to help.

Now we have to create some PHP code that will reside inside our webpage to talk to our database. In the example below "//" means that it is a comment about what the code is doing.

We need to tell our web page to connect to our mySQL database. Below we tell it to connect to the local server and enter our user ID and password to the database. We also tell it what database to use. In this case it's "myusers".

             //connect to server and choose database
             $conn=mysql_connect("localhost", "your_user-name", "your-password");
             $db=mysql_select_db("myusers", $conn);
             

Sample code to insert or delete a record:

             //the user has hit either the update or delete button.
             if(!empty($submit)){
             

             //this is a new row an must be inserted
             if($submit=="update" && empty($user_id)){
             $query = "INSERT INTO myusers (Name, Email, Job)
                       VALUES ('$Name', '$Email', ', '$Job')";
             mysql_query($query) or die(mysql_error());
             if(mysql_affected_rows()==0){
                 echo "INSERT FAILED, Please contact site admin or try again";
                 exit;
             }else{
                 //get the primary key so I can display the 
                 //record in the form
                 $user_id=mysql_insert_id();
             } 
             }
             //now I know this row is form upating an exisiting record
             elseif($submit=="update" && !empty($user_id)){
             $query="UPDATE users SET
             Name='$Name',
             Email='$Email',
             Job='$Job'
                     WHERE user_id=$user_id";
                     mysql_query($query);
             if(mysql_affected_rows()==0){
                 echo "Warning, nothing changed in the submitted row.";
                 exit;
             }
             } 
             elseif($submit=="delete" && !empty($user_id)){
             $query="DELETE FROM myusers WHERE user_id=$user_id";
             mysql_query($query) or die(mysql_error());
             if(mysql_affected_rows()==0){
                 echo "Warning, No rows deleted.";
                 exit;
             }
             }
             }
             if(!empty($user_id)){
             $result2=mysql_query("SELECT * FROM myusers WHERE user_id=$user_id");
             $row2=mysql_fetch_assoc($result2);
             }
             
             ?>
             
             <table>
             <tr>
             <td>
             
             <p>Click <a href="<? echo $PHP_SELF; ?>">here</a> for blank form.</p>
             <?
             //get listing of everyone in users table for printout
             //user_ids will be included in querystring for so that when user
             //clicks on one user_id, the results will be displayed in the form
             $result1=mysql_query("SELECT user_id, concat(Name) as username 
                             FROM myusers
                             ORDER BY Name") or die(mysql_error());
             while($row1=mysql_fetch_assoc($result1)){
             echo "<a href=$PHP_SELF?user_id=" . 
             $row1[user_id] . ">" . $row1[username] . "</a><br>";
             }
             
             
             ?>
             </td>
             <td>
             <form>
             
             <input type="hidden" name="user_id" value="<?php echo $row2[user_id]?>"><br>
             <input type="text" name="Name" value="<?php echo $row2[Name]?>">Name<br>
             <input type="submit" name="submit" value="update">
             <input type="submit" name="submit" value="delete"><br>
             </form>
             </td>
             
             </tr>
             </table>