Home arrow MySQL arrow Page 6 - Dynamically Insert and Update Values In a MySQL Database Using OOP

UpdateDB in Action - MySQL

Stop writing insert and update SQL statements and cut the time you spend writing simple SQL in half while focusing on the more complicated things. Leave it up to OOP to help you out. We will make a class that goes out and looks for the values for us and builds a SQL statement on the fly. All we have to do is make sure the column names in the database correspond with the field names in the HTML form. Believe me when I say it saves TONS of time. I never write applications that don't use it.

TABLE OF CONTENTS:
  1. Dynamically Insert and Update Values In a MySQL Database Using OOP
  2. Essential Connection
  3. Adam Up
  4. If You POST It, It Will Go
  5. Updateagenessly
  6. UpdateDB in Action
  7. You Wouldn't Have to Update It Had You Gotten It Right the First Time
By: Sam 'SammyK' Powers
Rating: starstarstarstarstar / 96
February 04, 2004

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

This will be similar to how we used AddToDB earlier, only this time, we want to query out all the people in the database and update the one we choose. First, let's output those names to the screen.


<?php
// Instantiate the class
$SQL = new MyDatabase;
// Connect to the database
$SQL->Connect();
 
? >
<html>
<head>
<title>Using UpdateDB</title>
<meta http-equiv="Content-Type" 
content
="text/html; charset=iso-8859-1">
</head>
 
<
body>
<table width="500" border="0" 
cellspacing
="0" cellpadding="3">
 
<tr bgcolor="#CCCCCC">
  
<td width="121">Name</td>
  
<td width="30">Age</td>
  
<td width="79">Gender</td>
  
<td width="246">Phone</td>
 
</tr>
<?php
 
$results 
mysql_query("SELECT 
id,name,age,gender,phone FROM my_tbl 
WHERE (1) ORDER BY name"
) or 
die
("MYSQL ERROR: ".mysql_error());
 
if(
$row=mysql_fetch_assoc($results))
{
 do
 
{
  
? >
   
<tr>
   
<td><a href="<?php echo 
   
$_SERVER['PHP_SELF']; 

?id=<?php 
   echo $row["id"]

"
><?php echo 
   $row
["name"]; 

</a></td>
   <td><?php echo $row["age"]; 

</td>
   <td><?php echo $row["gender"]; 

</td>
   <td><?php echo $row["phone"]; 

</td>
   </tr>
  <?php
 
}
 
while ($row=mysql_fetch_assoc($results));
}
else
{
 
? >
  
<tr>
  
<td colspan="4">No data</td>
  
</tr>
 
<?php
}
? >

</table><br>

I trust that the above code needs no in-depth explanation. It's simply connecting to the database using the MyDatabase class and pulling an associative array while looping through the results. Now let's alter our HTML form from above to be used for updating and not inserting.


<?php
if($_GET['id'])
{
 $results 
mysql_query("SELECT 
 name,age,gender,phone FROM my_tbl 
 WHERE id="
.$_GET['id']) or die("MYSQL
 ERROR: "
.mysql_error());
 $row
=mysql_fetch_assoc($results);
 


 <form action="<?php echo 
 $_SERVER
['PHP_SELF']; 


 method="post" name="myForm">
  <p>Name: <input name="name" 
  type="text" value="<?php echo
  $row
["name"]; 

"></p>
  <p>Age: <input name="age" type="text"
  value="<?php echo $row["age"]; 

"></p>
  <p>Gender:<br>
     <input name="gender"
  type="radio" value="MALE"<?php echo 
  
($row["gender"] == 'MALE') ? 
  
' checked' ''

> Male<br>
     <input type="radio" 
  name="gender" value="FEMALE"<?php echo 
  
($row["gender"] == 'FEMALE') ? 
  
' checked' ''

> Female</p>
  <p>Phone Number: <input name="phone" 
  type="text" value="<?php echo 
  $row
["phone"]; 

"></p>
  <p><input name="action" type="submit"
  value="UpdateDB">
  <input name="id" type="hidden" 
  value="<?php echo $_GET["id"]; 

"></p>
</form>
<?php
}
$SQL->Disconnect();
? >

</body>
</html>

The only real difference here is the addition of:

<input name="id" type="hidden"
value="<?php echo $_GET["id"]; ?>">

This is just a hidden form field to pass the id. You will also notice we are echoing out the data in the form fields like so:

value="<?php echo $row["age"]; ?>"

Alright, now we have got to make this beast actually update the data in the database table.



 
 
>>> More MySQL Articles          >>> More By Sam 'SammyK' Powers
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: