|
PHP and MSQL Database Maintenance Example |
This PHP script will allow you to open any MiniSQL database, and then list the database contents, make additions, deletions, and updates. The script communicates with the MSQL server to determine the names and types of fields in the database table. The first field in the database table needs to be a unique string field which will be used as the key for changes. Other fields can be any type. If you use this script in your own project, please drop us a note and let us know if we may include a link on this page.
camelcity.com can help you with every aspect of your database project, from obtaining and installing the server software to customizing the scripts.
/* Beginning of user-customizable section */ $database="addresses"; /* The msql database to open */ $table="addresses"; /* The msql table to use */ $order="order by Email"; /* How to order results from msql select */ $home_url=$PHP_SELF; /* Pointer to this script for HREFs etc */ /* End of user-customizable section */ setType($sql,"string"); /* The sql query to send to msql */ /* Fetch the data from msql database and dump it into $result */ ; $result = msql($database,"select * from $table $order"); $num_rows = msql_numrows($result); $num_fields = msql_numfields($result)>
Database: Table: Fields: Rows:
if($add); /* We have a new row to add from form input */ $sql = "insert into $table ("; $field=0; /* Counter for while loop through fields */ /* Get all the field names and start building insert statement*/ while ($field < $num_fields); $sql = $sql + msql_fieldname($result,$field) + ","; $field++; endwhile; $sql = Reg_Replace(",$","",$sql); /* Trim the trailing comma */ $sql = $sql + ") values ("; $field=0; /* Counter for while loop through fields */ /* Add the field values from the form to the insert statement */ while ($field < $num_fields); /* Determine field type to quote char fields */ ; $field_type = msql_fieldtype($result,$field); switch ($field_type); case "char"; $quote="'"; /* chars need quoting */ break; default; $quote=""; /* non-chars need no quotes */ $$field=0+$$field; /* force empty fields to 0 */ break; endswitch ; $sql = "$sql" + "$quote" + $$field + "$quote" + ","; $field++; endwhile; $sql = Reg_Replace(",$","",$sql); /* Trim the trailing comma */ $sql = $sql + ")"; /* Finished building insert statement, now insert the row */> Adding row: echo $sql>
msql($database,"$sql")> else;if($update); /* We have a row to update from form input */ $sql="update $table set "; $field=0; /* Counter for while loop through fields */ /* Build the update statement */ while ($field < $num_fields); /* Determine field type to quote char fields */ $field_type = msql_fieldtype($result,$field); $field_name = msql_fieldname($result,$field); switch ($field_type); case "char"; $quote="'"; /* chars need quoting */ break; default; $quote=""; /* non-chars need no quotes */ $$field=0+$$field; /* force empty fields to 0 */ break; endswitch ; $sql = $sql + $field_name + "=" + "$quote" + $$field + "$quote" + ","; $field++; endwhile; $sql = Reg_Replace(",$","",$sql); /* Trim the trailing comma */ $field_name = msql_fieldname($result,0); $sql= $sql + " where $field_name='$to_update'" /* Finished building update statement, now perform update */ > Updating row: echo $sql>
msql($database,"$sql")> else; if($delete); /* We have a row to delete from form input */ $field_name = msql_fieldname($result,0); $sql="delete from $table where $field_name='$to_delete'"> Deleting row: echo $sql>
msql($database,"$sql")> else; if($addform) /* Process a request to display form to add a new row */>
else; if($updateform); /* Process request to display an update form */ /* Generate input form for update */ $field_name = msql_fieldname($result,0); $result = msql($database,"select * from $table where $field_name='$to_update'"); $num_rows = msql_numrows($result); $num_fields = msql_numfields($result)> else; /* Else fall through to listing of all rows in table */ /* Print out the table headers */ >| Action | $field=0; while ($field < $num_fields)>echo msql_fieldname($result,$field)> | $field++; endwhile>
|---|---|
| /* Output delete and update form, using field 0 as the key */ > $field_name = msql_fieldname($result,0)> | $field=0; /* Output all fields */ ; while ($field < $num_fields); /* Determine field type, and set the alignment */ ; $field_type = msql_fieldtype($result,$field); switch ($field_type); case "char"; $align="left"; /* Characters left-aligned */ break; default; $align="right"; /* Numbers right-aligned */ break; endswitch ; /* Output the data */ >> $field_name = msql_fieldname($result,$field); echo msql_result($result,$row,$field_name)>  | $field++; endwhile; /* End of row */ >