Difference between revisions of "Relational database example 4-PHP form interface"

From "A B C"
Jump to navigation Jump to search
m
Line 17: Line 17:
 
<li><span class="toctext">[[Relational database principles]]</span></li>
 
<li><span class="toctext">[[Relational database principles]]</span></li>
 
<li><span class="toctext">[[Relational database example 1]]</span></li>
 
<li><span class="toctext">[[Relational database example 1]]</span></li>
−
<li><span class="toctext">[[Relational database example 2-join tables]]</span></li>
+
<li><span class="toctext">[[Relational database example 2-Perl interface]]</span></li>
−
<li><span class="toctext">[[Relational database example 3-Perl interface]]</span></li>
+
<li><span class="toctext">[[Relational database example 3-join tables]]</span></li>
 
</ul>
 
</ul>
 
</td></tr></table>
 
</td></tr></table>

Revision as of 16:50, 16 September 2012

Relational database example 4-PHP form interface


The contents of this page has recently been imported from an older version of this Wiki. This page may contain outdated information, information that is irrelevant for this Wiki, information that needs to be differently structured, outdated syntax, and/or broken links. Use with caution!


Summary ...


Related Pages


 

Introductory reading



  To continue with our database example, we will develop a Web based form to query and update database contents.

The framework

Here is code for a basic framework for the functions we will develop. 

<?php
$GLOBALS['postPage']   = "ecoint.php"; // the page to be POSTed to

?>
<html>
<head><title>E. Coli Interactions Database</title>
</head>
<body>
<?php

//====== PROCESSING LOGIC =========================
if ( strcmp($_POST['Status'], 'query') == 0 ) {
	displayQuery();
}
elseif ( strcmp($_POST['Status'], 'edit') == 0 ) {
	displayEdit();
}
elseif ( strcmp($_POST['Status'], 'commit') == 0 ) {
	displayCommit();
}
else {
	displayDefault();
}

exit();

//====== PAGES =================================

function displayDefault() {
    
    echo"<h2>Default</h2>";
    echo"<p>";
    printQueryForm();
}

function displayQuery() {
    
    echo"<h2>Display query results</h2>";
    echo"<p>";
    printEditButton();
    echo"<p>";
    printQueryForm();
}

function displayEdit() {
    
    echo"<h2>Edit an entry</h2>";
    echo"<p>";
    printEditForm();
    echo"<p>";
    printQueryForm();
}

function displayCommit() {
    
    echo"<h2>Review edit result</h2>";
    echo"<p>";
    printEditButton();
    echo"<p>";
    printQueryForm();
}

//====== FORMS =================================

function printQueryForm() {
	echo "New query ... ";	
	echo "<form action=\"" . $GLOBALS['postPage'] . "\" method=\"post\">";
	echo "<input type=\"hidden\" name=\"Status\" value=\"query\">";
	echo "<input type=\"submit\" value=\"Query\">";
	echo "</form>";	
}

function printEditForm() {
	echo "Preloaded edit form ... ";	
	echo "<form action=\"" . $GLOBALS['postPage'] . "\" method=\"post\">";
	echo "<input type=\"hidden\" name=\"Status\" value=\"commit\">";
	echo "<input type=\"submit\" value=\"Commit\">";
	echo "</form>";	
}

function printEditButton() {
	echo "One row from interaction table ... ";	
	echo "<form action=\"" . $GLOBALS['postPage'] . "\" method=\"post\">";
	echo "<input type=\"hidden\" name=\"Status\" value=\"edit\">";
	echo "<input type=\"submit\" value=\"Edit\">";
	echo "</form>";	
}

?>
</body>
</html>

The functions are more or less only stubs - but they allow us to explore the logic as we navigate through the different states of this page, triggered by the Status flags we pass through hidden values with the forms. Save the code somewhere in your Web hierarchy and invoke it as http://localhost/test/ecoint.php .


Connecting to the database server

Since everything useful we could do with these forms requires access to the MySQL server, we establish a connection at the very start of our script. As in our Perl examples, the user name and passsword have to be supplied, but they are processed by PHP and not actually ever exposed in the sourcecode of the HTML document that is served by apache.

Here is the function that establishes a connection and makes it available to all other functions in a global variable: 

function connectDB() {
    $GLOBALS['connection'] = mysql_connect("localhost:3306" , "ecodev", 'Nixo$poil')
        or die ("Couldn't connect to database.");
    $a = mysql_query("USE ecoint", $GLOBALS['connection']) 
        or die("Query failed:" . mysql_error($GLOBALS['connection']) . "\n");
}

mysql_connect() and mysql_query() are functions built into a standard PHP distribution. PHP comes with inbuilt MySQL support. By the way: in case you haven't noticed - all functions in highlighted PHP code are linked to function descriptions at http://php.net .


Query Form

Now let's build a form to accept input for a query with a gene name. We only need to add one extra lineto the code of our printQueryForm() stub: 

    echo "<input type=\"text\" size=\"10\" name=\"gene\">";

This creates a text field that captures user input, and associates the input with the name gene when the form is being POSTed. We can evaluate this variable as we drop into the page with the status-flag query, search in the database whether this gene is known and if yes, print out all matching interactions.

Query processing

Sanitizing input

One word of warning: this form processes user input. All user input must be considered tainted and you must sanitize it before use. Here's an example of what may happen if you neglect this.

Even though this is only teaching code, sanitizing should never be omitted. Here is a function that we will use to filter all text input before we even touch it. Many filtering functions try a blacklist approach - reject all kinds of possible evil syntax. I prefer a whitelist approach: allow only what we Do want: the characters [0-9], [a-z], [A-Z] and [-]. Everything else is stripped out. 

function sanitizeGene($s) {
    // Gene names are less than 15 characters in length and
    // allowed to contain only [0-9], [a-z],[A-Z] and [-]    
    $s = substr($s,0,15);
    $s = preg_replace("/[^0-9a-zA-Z\-]/", "", $s);
    return $s;
}

Retrieving all matches from the database

Since we now have a credible gene name, we use it to query the database gene table. We don't assume the name is unique, but retrieve all matching Gene_ID values. 

function displayQuery() {
    
    echo"<h2>Display query results</h2>";
    echo"<p>";
    
    $gene = sanitizeGene($_POST['gene']);
    $query = "SELECT Gene_ID FROM gene WHERE Gene_Name ='" . $gene . "'";
	$result = mysql_query($query, $GLOBALS['connection']) or
	    die("Query failed:" . mysql_error($GLOBALS['connection']) . "\n");
	while ($tmp = mysql_fetch_assoc($result)) {
	    $gene_IDs[ ] = $tmp['Gene_ID'];
	}
}

As a result, the array $gene_IDs is loaded with all matching IDs. We now iterate through the list (which really should only be a single entry and put all matching interactions from the inx table into a table. 


    if (! $gene_IDs) {
        echo "Unknown gene \"$gene\".\n";
    }
    else {
        echo "<table cellpadding=\"4\">\n";
        foreach ($gene_IDs as $ID) {
            $query = "SELECT * FROM inx WHERE int_A_ID =" . $ID . " OR int_B_ID =" . $ID . " ";
            $result = mysql_query($query, $GLOBALS['connection']) or
                die("Query failed:" . mysql_error($GLOBALS['connection']) . "\n");
            while ($tmp = mysql_fetch_assoc($result)) {
                echo "<tr>";
                echo "<td>" . $tmp['int_A_ID'] . "</td>";
                echo "<td>" . $tmp['int_B_ID'] . "</td>";
                echo "<td>" . $tmp['weight']   . "</td>";
                echo "</tr>\n";
            }
        }
       echo "</table>\n";
    }

Fetching gene names instead of IDs

You can verify that this works, if you want. Meanwhile I'll change the code to add a function that converts the gene IDs to gene names. 


function id2name($id) {
    $q = "SELECT Gene_Name FROM gene WHERE Gene_ID =" . $id . " ";
    $result = mysql_query($q, $GLOBALS['connection']) or
	die("Query failed:" . mysql_error($GLOBALS['connection']) . "\n");
    $tmp = mysql_fetch_assoc($result);

    return($tmp['Gene_Name']);
}
</sopurce>

We simply wrap the gene IDs into the function call before we place them into the table - like so: 

<source lang="php">
        echo "<td>" . id2name($tmp['int_A_ID']) . "</td>";

At this point there is nothing much we can add to querying. Let's now turn to editing the interaction weights. (We'll assume that we are not allowed to reconnect the interaction network. But it would be easy to do.)


Requesting an edit

The first thing we need to do is to add an edit button to every row of the table. 

        echo "<td>"; printEditButton($tmp['int_ID']); echo "</td>";

The second thing is to slightly change the stub-function to capture and POST the interaction ID we want to modify. We pass the value for int_ID into the function and POST it as a hidden variable. 

    echo "<input type=\"hidden\" name=\"ID\" value=\"$ID\">";

That's all. Now all that's left to do is to evaluate the request and process it.

Editing a field

To edit a value in the database, we

  1. sanitize the requested ID. Even though the ID was passed from a hidden input field, that does not mean it is tamper-proof. It is trivial to write code that POSTS any input I want. After all, all the POSTed variables of a form are visible in the HTML source code. Once again: any user input must be considered tainted.
  2. retrieve the current value of weight from the database
  3. retrieve the gene names
  4. write a form and preload the current value, also pass the ID of the record we are editing as a hidden value and include a submit button to commit the edit.

Here is the sanitize function. 

function sanitizeNum($s) {
    // IDs are numbers, so are weights. 8 digits and a decimal point ...
    $s = substr($s,0,9);
    $s = preg_replace("/[^0-9.]/", "", $s);
    return $s;
}


Here is the function that sets up the page. 

function displayEdit() {
    
    echo"<h2>Edit an entry</h2>";
    echo"<p>";
	
    $ID = sanitizeNum($_POST['ID']);
    $query = "SELECT * FROM inx WHERE int_ID ='" . $ID . "'";
    $result = mysql_query($query, $GLOBALS['connection']) or
	die("Query failed:" . mysql_error($GLOBALS['connection']) . "\n");
    $tmp = mysql_fetch_assoc($result);
    printEditForm( id2name($tmp['int_A_ID']),
		   id2name($tmp['int_B_ID']),
		   $tmp['weight'],
		   $ID);
    echo"<p>";
    printQueryForm();
}


Here is the function that writes the form. The weight is preloaded into the edit field with the string value=\"$W\". The ID is passed as a hidden value. 

function printEditForm($A, $B, $W, $ID) {
    echo "<p>";
    echo "Interactor A: $A ";
    echo "Interactor B: $B ";
    echo "Weight: <form action=\"" . $GLOBALS['postPage'] . "\" method=\"post\">";
    echo "<input type=\"text\" size=\"5\" name=\"weight\" value=\"$W\">";
    echo "<input type=\"hidden\" name=\"ID\" value=\"$ID\">";
    echo "<input type=\"hidden\" name=\"Status\" value=\"commit\">";
    echo "<input type=\"submit\" value=\"Commit\">";
    echo "</form>";	
}


Committing the new value

This is just a bit more of the same: sanitize input, send an 

UPDATE table SET column = value WHERE key = value;

statement to the database and retrieve the new record for review ... 

function displayCommit() {
    
    echo "<h2>Review edit result</h2>";
    echo "<p>";
    $W  = sanitizeNum($_POST['weight']);
    $ID = sanitizeNum($_POST['ID']);
    $query = "UPDATE inx SET weight =" . $W . " WHERE int_ID =" . $ID . " ";
    $result = mysql_query($query, $GLOBALS['connection']) or
	die("Query failed:" . mysql_error($GLOBALS['connection']) . "\n");

    echo "<table cellpadding=\"4\">\n";
    $query = "SELECT * FROM inx WHERE int_ID ='" . $ID . "'";
    $result = mysql_query($query, $GLOBALS['connection']) or
	die("Query failed:" . mysql_error($GLOBALS['connection']) . "\n");
    $tmp = mysql_fetch_assoc($result);
    echo "<tr>";
    echo "<td>" . id2name($tmp['int_A_ID']) . "</td>";
    echo "<td>" . id2name($tmp['int_B_ID']) . "</td>";
    echo "<td>" . $tmp['weight']   . "</td>";
    echo "<td>"; printEditButton($tmp['int_ID']); echo "</td>";
    echo "</tr>\n";
    echo "</table>\n";
    echo "<p>";
    printQueryForm();
}

That's all.

The final code

For reference, here is the final code. 

<?php
$GLOBALS['postPage'] = "ecoint.php"; // the page to be POSTed to

//For debugging in Komodo - define some values in lieu of input
//$_POST['Status'] = 'query';
//$_POST['gene'] = 'ravA';

?>
<html>
<head><title>E. Coli Interactions Database</title>
</head>
<body>
<?php

connectDB();

//====== PROCESSING LOGIC =========================
if ( strcmp($_POST['Status'], 'query') == 0 ) {
	displayQuery();
}
elseif ( strcmp($_POST['Status'], 'edit') == 0 ) {
	displayEdit();
}
elseif ( strcmp($_POST['Status'], 'commit') == 0 ) {
	displayCommit();
}
else {
	displayDefault();
}

exit();

//====== PAGES =================================

function displayDefault() {
    
    echo"<h2>Default</h2>";
    echo"<p>";
    printQueryForm();
}

function displayQuery() {
    
    echo"<h2>Display query results</h2>";
    echo"<p>";
    
    $gene = sanitizeGene($_POST['gene']);
    $query = "SELECT Gene_ID FROM gene WHERE Gene_Name ='" . $gene . "'";
	$result = mysql_query($query, $GLOBALS['connection']) or
		die("Query failed:" . mysql_error($GLOBALS['connection']) . "\n");
	while ($tmp = mysql_fetch_assoc($result)) {
		$gene_IDs[ ] = $tmp['Gene_ID'];
	}
    
    if (! $gene_IDs) {
        echo "Unknown gene \"$gene\".\n";
    }
    else {
        echo "<table cellpadding=\"4\">\n";
        foreach ($gene_IDs as $ID) {
            $query = "SELECT * FROM inx WHERE int_A_ID =" . $ID . " OR int_B_ID =" . $ID . " ";
            $result = mysql_query($query, $GLOBALS['connection']) or
                die("Query failed:" . mysql_error($GLOBALS['connection']) . "\n");
            while ($tmp = mysql_fetch_assoc($result)) {
                echo "<tr>";
                echo "<td>" . id2name($tmp['int_A_ID']) . "</td>";
                echo "<td>" . id2name($tmp['int_B_ID']) . "</td>";
                echo "<td>" . $tmp['weight']   . "</td>";
                echo "<td>"; printEditButton($tmp['int_ID']); echo "</td>";
                echo "</tr>\n";
            }
        }
       echo "</table>\n";
    }
    

//    printEditButton();
//    echo"<p>";
    printQueryForm();
}

function displayEdit() {
    
    echo"<h2>Edit an entry</h2>";
    echo"<p>";
	
	$ID = sanitizeNum($_POST['ID']);
    $query = "SELECT * FROM inx WHERE int_ID ='" . $ID . "'";
	$result = mysql_query($query, $GLOBALS['connection']) or
		die("Query failed:" . mysql_error($GLOBALS['connection']) . "\n");
	$tmp = mysql_fetch_assoc($result);
    printEditForm( id2name($tmp['int_A_ID']),
				   id2name($tmp['int_B_ID']),
				   $tmp['weight'],
				   $ID);
    echo"<p>";
    printQueryForm();
}

function displayCommit() {
    
    echo "<h2>Review edit result</h2>";
    echo "<p>";
	$W  = sanitizeNum($_POST['weight']);
	$ID = sanitizeNum($_POST['ID']);
    $query = "UPDATE inx SET weight =" . $W . " WHERE int_ID =" . $ID . " ";
	$result = mysql_query($query, $GLOBALS['connection']) or
		die("Query failed:" . mysql_error($GLOBALS['connection']) . "\n");

        echo "<table cellpadding=\"4\">\n";
    $query = "SELECT * FROM inx WHERE int_ID ='" . $ID . "'";
    $result = mysql_query($query, $GLOBALS['connection']) or
		die("Query failed:" . mysql_error($GLOBALS['connection']) . "\n");
	$tmp = mysql_fetch_assoc($result);
	echo "<tr>";
	echo "<td>" . id2name($tmp['int_A_ID']) . "</td>";
	echo "<td>" . id2name($tmp['int_B_ID']) . "</td>";
	echo "<td>" . $tmp['weight']   . "</td>";
	echo "<td>"; printEditButton($tmp['int_ID']); echo "</td>";
	echo "</tr>\n";
    echo "</table>\n";
    echo "<p>";
    printQueryForm();
}


//====== FORMS =================================

function printQueryForm() {
	echo "<p>Query the database:";	
	echo "<form action=\"" . $GLOBALS['postPage'] . "\" method=\"post\">";
    echo "<input type=\"text\" size=\"10\" name=\"gene\">";
	echo "<input type=\"hidden\" name=\"Status\" value=\"query\">";
	echo "<input type=\"submit\" value=\"Query\">";
	echo "</form>";	
}

function printEditForm($A, $B, $W, $ID) {
	echo "<p>";
	echo "Interactor A: $A ";
	echo "Interactor B: $B ";
	echo "Weight: <form action=\"" . $GLOBALS['postPage'] . "\" method=\"post\">";
    echo "<input type=\"text\" size=\"5\" name=\"weight\" value=\"$W\">";
	echo "<input type=\"hidden\" name=\"ID\" value=\"$ID\">";
	echo "<input type=\"hidden\" name=\"Status\" value=\"commit\">";
	echo "<input type=\"submit\" value=\"Commit\">";
	echo "</form>";	
}

function printEditButton($ID) {
	echo "<form action=\"" . $GLOBALS['postPage'] . "\" method=\"post\">";
	echo "<input type=\"hidden\" name=\"ID\" value=\"$ID\">";
	echo "<input type=\"hidden\" name=\"Status\" value=\"edit\">";
	echo "<input type=\"submit\" value=\"Edit\">";
	echo "</form>";	
}

//====== TOOLS ==========================================================

function connectDB() {
    $GLOBALS['connection'] = mysql_connect("localhost:3306" , "ecodev", 'Nixo$poil') or die ("Couldn't connect to database.");
    $a = mysql_query("USE ecoint", $GLOBALS['connection']) or die("Query failed:" . mysql_error($GLOBALS['connection']) . "\n");
}

function sanitizeGene($s) {
    // Gene names are less than 15 characters in length and
    // allowed to contain only [0-9], [a-z],[A-Z] and [-]    
    $s = substr($s,0,15);
    $s = preg_replace("/[^0-9a-zA-Z\-]/", "", $s);
    return $s;
}

function sanitizeNum($s) {
    // IDs are numbers, so are weights. 8 digits and a decimal point ...
    $s = substr($s,0,9);
    $s = preg_replace("/[^0-9.]/", "", $s);
    return $s;
}

function id2name($id) {
	$q = "SELECT Gene_Name FROM gene WHERE Gene_ID =" . $id . " ";
	$result = mysql_query($q, $GLOBALS['connection']) or
		die("Query failed:" . mysql_error($GLOBALS['connection']) . "\n");
	$tmp = mysql_fetch_assoc($result);

	return($tmp['Gene_Name']);
}

?>
</body>
</html>


Refactoring

There is a bit of obvious cleanup that can be done. Some statements are repeated many times over throughout the code. This is generally not good, since it lengthens the code unnecessarily and makes it hard to maintain. I abstract such repetitions out into separate functions. Such improvements that do not change the functionality of the code are called refactoring. 

<?php
$GLOBALS['postPage']   = "ecoint.php"; // the page to be POSTed to

?>
<html>
<head><title>E. Coli Interactions Database</title>
</head>
<body>
<?php

connectDB();

//====== PROCESSING LOGIC =========================
if ( strcmp($_POST['Status'], 'query') == 0 ) {
	displayQuery();
}
elseif ( strcmp($_POST['Status'], 'edit') == 0 ) {
	displayEdit();
}
elseif ( strcmp($_POST['Status'], 'commit') == 0 ) {
	displayCommit();
}
else {
	displayDefault();
}

exit();

//====== PAGES =================================

function displayDefault() {
    
    echo"<h2>Default</h2>";
    echo"<p>";
    printQueryForm();
}

function displayQuery() {
    
    echo"<h2>Display query results</h2>";
    echo"<p>";
    
    $gene = sanitizeGene($_POST['gene']);
    $query = "SELECT Gene_ID FROM gene WHERE Gene_Name ='" . $gene . "'";
	$result = executeSQL($query);
	while ($row = mysql_fetch_assoc($result)) {
		$gene_IDs[ ] = $row['Gene_ID'];
	}
    
    if (! $gene_IDs) {
        echo "Unknown gene \"$gene\".\n";
    }
    else {
        echo "<table cellpadding=\"4\">\n";
        foreach ($gene_IDs as $ID) {
            $query = "SELECT * FROM inx WHERE int_A_ID =" . $ID . " OR int_B_ID =" . $ID . " ";
            $result = executeSQL($query);
            while ($row = mysql_fetch_assoc($result)) {
                echo "<tr>";
                echo "<td>" . id2name($row['int_A_ID']) . "</td>";
                echo "<td>" . id2name($row['int_B_ID']) . "</td>";
                echo "<td>" .         $row['weight']    . "</td>";
                echo "<td>"; printEditButton($row['int_ID']); echo "</td>";
                echo "</tr>\n";
            }
        }
       echo "</table>\n";
    }
    printQueryForm();
}

function displayEdit() {
    
    echo"<h2>Edit an entry</h2>";
    echo"<p>";
    $ID = sanitizeNum($_POST['ID']);
    $query = "SELECT * FROM inx WHERE int_ID ='" . $ID . "'";
    $row = fetchRow($query);
    printEditForm( id2name($row['int_A_ID']),
		   id2name($row['int_B_ID']),
		           $row['weight'],
		           $ID);
    echo"<p>";
    printQueryForm();
}

function displayCommit() {
    
    echo "<h2>Review edit result</h2>";
    echo "<p>";
    $W  = sanitizeNum($_POST['weight']);
    $ID = sanitizeNum($_POST['ID']);
    $query = "UPDATE inx SET weight =" . $W . " WHERE int_ID =" . $ID . " ";
    executeSQL($query);

    $query = "SELECT * FROM inx WHERE int_ID ='" . $ID . "'";
    $row = fetchRow($query);
    echo "<table cellpadding=\"4\">\n";
        echo "<tr>";
        echo "<td>" . id2name($row['int_A_ID']) . "</td>";
        echo "<td>" . id2name($row['int_B_ID']) . "</td>";
        echo "<td>" . $row['weight']   . "</td>";
        echo "<td>"; printEditButton($row['int_ID']); echo "</td>";
        echo "</tr>\n";
    echo "</table>\n";
    echo "<p>";
    printQueryForm();
}


//====== FORMS =================================

function printQueryForm() {
    echo "<p>Query the database:";	
    echo "<form action=\"" . $GLOBALS['postPage'] . "\" method=\"post\">";
    echo "<input type=\"text\" size=\"10\" name=\"gene\">";
    echo "<input type=\"hidden\" name=\"Status\" value=\"query\">";
    echo "<input type=\"submit\" value=\"Query\">";
    echo "</form>";	
}

function printEditForm($A, $B, $W, $ID) {
    echo "<table cellpadding=\"4\">\n";
    echo "<tr>";
    echo "<td>Interactor A:</td>";
    echo "<td>Interactor B:</td>";
    echo "<td>Weight:</td>";
    echo "</tr>\n";
    echo "<tr>";
    echo "<td>" . $A . "</td>";
    echo "<td>" . $B . "</td>";
    echo "<td>";
	echo "<form action=\"" . $GLOBALS['postPage'] . "\" method=\"post\">";
	echo "<input type=\"text\" size=\"5\" name=\"weight\" value=\"$W\">";
	echo "<input type=\"hidden\" name=\"ID\" value=\"$ID\">";
	echo "<input type=\"hidden\" name=\"Status\" value=\"commit\">";
	echo "<input type=\"submit\" value=\"Commit\">";
	echo "</form>";	
    echo "</td>";
    echo "</tr>\n";
    echo "</table>\n";
}

function printEditButton($ID) {
    echo "<form action=\"" . $GLOBALS['postPage'] . "\" method=\"post\">";
    echo "<input type=\"hidden\" name=\"ID\" value=\"$ID\">";
    echo "<input type=\"hidden\" name=\"Status\" value=\"edit\">";
    echo "<input type=\"submit\" value=\"Edit\">";
    echo "</form>";	
}

//====== TOOLS ==========================================================

function connectDB() {
    $GLOBALS['connection'] = mysql_connect("localhost:3306" , "ecodev", 'Nixo$poil')
	or die ("Couldn't connect to database.");
    $q = "USE ecoint";
    executeSQL($q);
}

function sanitizeGene($s) {
    // Gene names are less than 15 characters in length and
    // allowed to contain only [0-9], [a-z],[A-Z] and [-]    
    $s = substr($s,0,15);
    $s = preg_replace("/[^0-9a-zA-Z\-]/", "", $s);
    return $s;
}

function sanitizeNum($s) {
    // IDs are numbers, so are weights. 8 digits and a decimal point ...
    $s = substr($s,0,9);
    $s = preg_replace("/[^0-9.]/", "", $s);
    return $s;
}

function executeSQL($q) {
    $r = mysql_query($q, $GLOBALS['connection']) or
	die("Query failed:" . mysql_error($GLOBALS['connection']) . "\n");
    return $r;
}

function fetchRow($q) {
    $r = executeSQL($q);
    return( mysql_fetch_assoc($r) );
}

function id2name($id) {
    $q = "SELECT Gene_Name FROM gene WHERE Gene_ID =" . $id . " ";
    $row = fetchRow($q);
    return($row['Gene_Name']);
}

?>
</body>
</html>

Further reading

All the basic functionality is there, but pretty it is not (yet). Here are some thoughts to improve appearance and functionality.

  • Add a Cascading Style Sheet (CSS) to format borders, text and font
  • Link Gene-names to GO categories or other information pages on the Web
  • Change the data model to support non-integer weights for interactions
  • Pull in manuscript abstracts from PubMed
  • Integrate a network visualizer
  • [...]


   

Further reading and resources

 

Retrieved from "http://steipe.biochemistry.utoronto.ca/abc/index.php?title=Relational_database_example_4-PHP_form_interface&oldid=5528"