can't update sql table with new values from input field

neurotopia · June 3, 2010

Hi, I've checked in the FAQ code snippets section, w3chools, and googled quite a bit but still could not find what I needed. I apologize in advance if it's there and I missed it -- I am still quite new to PHP and mySQL and may not have used the proper terminology in my searches.

I am using a form to edit mySQL data. I am able to populate text box fields with information from the database, but I cannot get the altered text box data to update the mySQL table. This is a code sample of what I've done:

form.php

$info = mysql_query("SELECT name, color FROM people WHERE uid='3'");

Name: <input type="text" name="namefield" size="40" value="<? echo($info['name']); ?>" />
Favorite Color:  <input type="text" name="colorfield" size="40" value="<? echo($info['color']); ?>" />

action.php

mysql_query("DROP FROM people WHERE uid='3'");
mysql_query("INSERT INTO  people (name, color) VALUES ('$_POST[namefield]','$_POST[colorfield]');

The information in form.php shows up as it should, but my action.php is not able to change the values in the mySQL db.

I don't think deleting the old data and then recreating the record with the new data is probably the best way to go, but it seemed the simplest. I feel like I'm missing something REALLY obvious but haven't been able to figure out what it is. Any help would be greatly appreciated.

Thanks!

mrMarcus · June 3, 2010

I've never actually seen it done this way.

Use UPDATE:

$sql = "
update `people`
set
	`name` = mysql_real_escape_string($_POST['namefield']),
	`color` = mysql_real_escape_string($_POST['colorfield'])
where `uid` = 3
";

if ($result = @mysql_query($sql)) {
echo 'Success!';
}
else {
trigger_error(mysql_error());
}

neurotopia · June 3, 2010

My earlier attempts included using UPDATE but were also unsuccessful. That's why I finally resorted to deleting the table row entirely, and recreating it anew. But because both approaches have failed, I'm beginning to suspect that the problem has something to do with the value of namefield and colorfield not being properly updated to reflect the changes. So that when the data is written into the table, the old values are simply being re-entered.

Does that make any sense? Again, I apologize if my terminology is not as precise as it should be.

PFMaBiSmAd · June 3, 2010

DROP FROM does not exist as a command and produces an sql error.

You do need to use an UPDATE query. You need to debug why the update query you tried did not work.

neurotopia · June 3, 2010

yikes. you're right. i mistakenly typed "drop" in my post. the actual code is "delete"

form.php

$info = mysql_query("SELECT name, color FROM people WHERE uid='3'");

Name: <input type="text" name="namefield" size="40" value="<? echo($info['name']); ?>" />
Favorite Color:  <input type="text" name="colorfield" size="40" value="<? echo($info['color']); ?>" />

action.php

mysql_query("DELETE FROM people WHERE uid='3'");
mysql_query("INSERT INTO  people (name, color) VALUES ('$_POST[namefield]','$_POST[colorfield]');

I will attempt to use UPDATE again and post my results. I was trying to avoid posting my whole code, but I guess it might come down to that. I can also post the errors I'm getting (which suggests that the namefield and colorfield values aren't being updated because if they were blank prior my update, they remain that way even after I attempt to create a new record with new values.

Thank you for your help and patience.

PFMaBiSmAd · June 3, 2010

The INSERT query you have been posting (both times in this thread) contains a php syntax error and your code is not even being executed.

Are you developing and debugging php code on a development system with error_reporting set to E_ALL and display_errors set to ON in your master php.ini so that php will help you by reporting and displaying all the errors it detects. You will save a TON of time.

neurotopia · June 3, 2010

I am, indeed, working on a server with php error logs and have been using them to debug. That's where I encountered the errors telling me I was trying to create new records but the values being inserted were null despite the fact I had entered text into the input text fields. My actual code is a bit different than what I posted, I was trying to simplify it while keeping it logically equivalent. Should I attach the .php files or should I insert the text in a post? They total around 400 lines of code and I feel kinda bad asking someone to trudge through all that.

PFMaBiSmAd · June 3, 2010

My actual code is a bit different than what I posted

There's no way anyone can help you with what your code is doing or not doing without seeing your actual relevant code and any errors you are getting.

neurotopia · June 3, 2010

You're right, I was just scared to post so much code. But yeah, if you don't see the actual code it makes it hard to get help. Again, I apologize for my noobishness and REALLY appreciate your help.

Ok... here goes:

database.php

<?php
    require_once dirname(__FILE__).'/db/MySQLDataSource.php';
    if(isset($database)) die('database already set');
    $databaseLink = mysql_connect('www.xxxxxxxxxxxxxxxxxxxxxxxx.com:xxxx', 'xxxxxxx','xxxxxxxxx');
    if($databaseLink===FALSE) die('Could not connect: ' . mysql_error());
    mysql_select_db('testfunk', $databaseLink) or die('Could not select database: '. mysql_error($databaseLink));
    $database = new MySQLDataSource($databaseLink);
?>

MySQLDataSource.php

<?php

require_once dirname(__FILE__).'/DataSource.php';
require_once dirname(__FILE__).'/NoRowException.php';
require_once dirname(__FILE__).'/SQLException.php';

/**
* This is a database abstraction layer to avoid any MySQL-specific calls.
* It also provides an easier way to make queries, escape data, and support
* transactions.
*
* The functions are intentionally compatible with Java PreparedStatement
* because PHP has no existing database abstraction standard and part of the
* application is already in Java.
*/
class MySQLDataSource implements DataSource {

    /**
     * The database link this instance is using.
     */
    private $databaseLink;

    /**
     * To optimize the database calls, only sends BEGIN, COMMIT, and ROLLBACK
     * as necessary based on this flag.
     */
    private $isInTransaction = false;

    public function __construct($databaseLink) {
        $this->databaseLink = $databaseLink;
        $this->executeUpdate("SET SESSION max_allowed_packet=67108864");
    }

    /**
     * Used internally during error handling to rollback a transactions without
     * losing the original error message.
     */
    private function rollbackIgnoreErrors() {
        if($this->isInTransaction) {
            $this->isInTransaction = false;
            $result = mysql_query("ROLLBACK", $this->databaseLink);
            if($result !== FALSE && $result !== TRUE) mysql_free_result($result);
        }
    }

    /**
     * Checks if this data source is already in a transaction.
     */
    public function isInTransaction() {
        return $this->isInTransaction;
    }

    /**
     * Begins a transaction.
     */
    public function begin() {
        if(!$this->isInTransaction) {
            $result = mysql_query("BEGIN", $this->databaseLink);
            if($result === FALSE) throw new SQLException('Database.begin: mysql_query failed: '.mysql_error($this->databaseLink));
            if($result !== TRUE && !mysql_free_result($result)) throw new SQLException('Database.begin: mysql_free_result failed: '.mysql_error($this->databaseLink));
            $this->isInTransaction = true;
        }
    }

    /**
     * Commits a transaction.
     */
    public function commit() {
        if($this->isInTransaction) {
            $result = mysql_query("COMMIT", $this->databaseLink);
            if($result === FALSE) {
                $message = 'Database.commit: mysql_query failed: '.mysql_error($this->databaseLink);
                $this->rollbackIgnoreErrors();
                throw new SQLException($message);
            }
            if($result !== TRUE && !mysql_free_result($result)) {
                $message = 'Database.commit: mysql_free_result failed: '.mysql_error($this->databaseLink);
                $this->rollbackIgnoreErrors();
                throw new SQLException($message);
            }
            $this->isInTransaction = false;
        }
    }

    /**
     * Rollback a transaction.
     */
    public function rollback() {
        if($this->isInTransaction) {
            $result = mysql_query("ROLLBACK", $this->databaseLink);
            if($result === FALSE) throw new SQLException('Database.rollback: mysql_query failed: '.mysql_error($this->databaseLink));
            if($result !== TRUE && !mysql_free_result($result)) throw new SQLException('Database.rollback: mysql_free_result failed: '.mysql_error($this->databaseLink));
            $this->isInTransaction = false;
        }
    }

    /**
     * Escapes and substitutes the provided parameters.
     * Each ? will be replaced by the values provided in the function parameters,
     * after being properly escaped.
     * The parameters are indexed starting one.
     */
    private function substituteParameters($sql, array $params) {
        $numParams = count($params);
        $escaped = '';
        $curParam = 1;
        $curPos = 0;
        while($pos=strpos($sql, '?', $curPos)) {
            if($curParam>=$numParams) {
                $this->rollbackIgnoreErrors();
                throw new SQLException('Database.substituteParameters: Not enough parameters');
            }
            $escaped .= substr($sql, $curPos, $pos-$curPos);
            $value = $params[$curParam++];
            if($value===NULL) {
                $escaped .= "NULL";
            } else {
                $real_escaped = mysql_real_escape_string($value, $this->databaseLink);
                if($real_escaped === FALSE) {
                    $message = 'Database.substituteParameters: mysql_real_escape_string: '.mysql_error($this->databaseLink);
                    $this->rollbackIgnoreErrors();
                    throw new SQLException($message);
                }
                $escaped .= "'";
                $escaped .= $real_escaped;
                $escaped .= "'";
            }
            $curPos = $pos+1;
        }
        $escaped .= substr($sql, $curPos);
        if($curParam!=$numParams) {
            $this->rollbackIgnoreErrors();
            throw new SQLException('Database.substituteParameters: Too many parameters');
        }
        return $escaped;
    }

    /**
     * Executes an update not checking the results.
     *
     * Each ? will be replaced by the values provided in the function parameters,
     * after being properly escaped.
     */
    public function executeUpdate($sql) {
        $args = func_get_args();
        $sql = $this->substituteParameters($sql, $args);
        $result = mysql_query($sql, $this->databaseLink);
        if($result === FALSE) {
            $message = 'Database.executeUpdate: mysql_query failed: '.mysql_error($this->databaseLink);
            $this->rollbackIgnoreErrors();
            error_log("SQL: ".$sql);
            throw new SQLException($message);
        }
        if($result !== TRUE && !mysql_free_result($result)) {
            $message = 'Database.executeUpdate: mysql_free_result failed: '.mysql_error($this->databaseLink);
            $this->rollbackIgnoreErrors();
            throw new SQLException($message);
        }
    }

    /**
     * Executes a query expecting exactly one row of results.  It will throw
     * a NoRowException if no rows are returned.  Will throw SQLException if 
     * more than one row is returned.
     *
     * The query should produce a single column.  To get an array of results see
     * executeQueryRow()
     *
     * Each ? will be replaced by the values provided in the function parameters,
     * after being properly escaped.
     */
    public function executeQuery($sql) {
        $args = func_get_args();
        $sql = $this->substituteParameters($sql, $args);
        $result = mysql_query($sql, $this->databaseLink);
        if($result === FALSE) {
            $message = 'Database.executeQuery: mysql_query failed: '.mysql_error($this->databaseLink);
            $this->rollbackIgnoreErrors();
            error_log("SQL: ".$sql);
            throw new SQLException($message);
        }
        $row = mysql_fetch_array($result, MYSQL_NUM);
        if($row === FALSE) throw new NoRowException('Database.executeQuery: No row returned');
        $cnt = count($row);
        if($cnt!=1) {
            $this->rollbackIgnoreErrors();
            error_log("SQL: ".$sql);
            throw new SQLException('Database.executeQuery: Incorrect number of columns from query: '.$cnt);
        }
        $value = $row[0];
        if(mysql_fetch_array($result, MYSQL_NUM) !== FALSE) {
            $this->rollbackIgnoreErrors();
            error_log("SQL: ".$sql);
            throw new SQLException('Database.executeQuery: More than one row returned');
        }
        if($result !== TRUE && !mysql_free_result($result)) {
            $message = 'Database.executeQuery: mysql_free_result failed: '.mysql_error($this->databaseLink);
            $this->rollbackIgnoreErrors();
            throw new SQLException($message);
        }
        return $value;
    }

    /**
     * Executes a query expecting exactly one row of results.  It will throw
     * a NoRowException if no rows are returned.  Will throw SQLException if 
     * more than one row is returned.
     *
     * Each ? will be replaced by the values provided in the function parameters,
     * after being properly escaped.
     *
     * Returns an associative array of the results.
     */
    public function executeQueryRow($sql) {
        $args = func_get_args();
        $sql = $this->substituteParameters($sql, $args);
        $result = mysql_query($sql, $this->databaseLink);
        if($result === FALSE) {
            $message = 'Database.executeQueryRow: mysql_query failed: '.mysql_error($this->databaseLink);
            $this->rollbackIgnoreErrors();
            error_log("SQL: ".$sql);
            throw new SQLException($message);
        }
        $row = mysql_fetch_array($result, MYSQL_ASSOC);
        if($row === FALSE) throw new NoRowException('Database.executeQueryRow: No row returned');
        if(mysql_fetch_array($result, MYSQL_NUM) !== FALSE) {
            $this->rollbackIgnoreErrors();
            error_log("SQL: ".$sql);
            throw new SQLException('Database.executeQueryRow: More than one row returned');
        }
        if($result !== TRUE && !mysql_free_result($result)) {
            $message = 'Database.executeQueryRow: mysql_free_result failed: '.mysql_error($this->databaseLink);
            $this->rollbackIgnoreErrors();
            throw new SQLException($message);
        }
        return $row;
    }

    /**
     * Executes a query expecting any number of rows of results.
     *
     * Each ? will be replaced by the values provided in the function parameters,
     * after being properly escaped.
     *
     * Returns the results as an indexed array of associative arrays.
     */
    public function executeQueryRows($sql) {
        $args = func_get_args();
        $sql = $this->substituteParameters($sql, $args);
        $result = mysql_query($sql, $this->databaseLink);
        if($result === FALSE) {
            $message = 'Database.executeQueryRows: mysql_query failed: '.mysql_error($this->databaseLink);
            $this->rollbackIgnoreErrors();
            error_log("SQL: ".$sql);
            throw new SQLException($message);
        }
        $values = array();
        while(($row = mysql_fetch_array($result, MYSQL_ASSOC)) !== FALSE) {
            $values[] = $row;
        }
        if($result !== TRUE && !mysql_free_result($result)) {
            $message = 'Database.executeQueryRows: mysql_free_result failed: '.mysql_error($this->databaseLink);
            $this->rollbackIgnoreErrors();
            throw new SQLException($message);
        }
        return $values;
    }

    /**
     * Executes a query expecting any number of rows of results.
     * Each result should have a single column.
     *
     * Each ? will be replaced by the values provided in the function parameters,
     * after being properly escaped.
     *
     * Returns an indexed array of the results.
     */
    public function executeQueryArray($sql) {
        $args = func_get_args();
        $sql = $this->substituteParameters($sql, $args);
        $result = mysql_query($sql, $this->databaseLink);
        if($result === FALSE) {
            $message = 'Database.executeQueryArray: mysql_query failed: '.mysql_error($this->databaseLink);
            $this->rollbackIgnoreErrors();
            error_log("SQL: ".$sql);
            throw new SQLException($message);
        }
        $values = array();
        while(($row = mysql_fetch_array($result, MYSQL_NUM)) !== FALSE) {
            $cnt = count($row);
            if($cnt!=1) {
                $this->rollbackIgnoreErrors();
                error_log("SQL: ".$sql);
                throw new SQLException('Database.executeQueryArray: Incorrect number of columns from query: '.$cnt);
            }
            $values[] = $row[0];
        }
        if($result !== TRUE && !mysql_free_result($result)) {
            $message = 'Database.executeQueryArray: mysql_free_result failed: '.mysql_error($this->databaseLink);
            $this->rollbackIgnoreErrors();
            throw new SQLException($message);
        }
        return $values;
    }

    /**
     * Executes a query expecting any number of rows of results.
     * Each result should have two columns.  The first is the associative array
     * key, and the second column is the value.
     *
     * An error will occur if a key is found more than once.
     *
     * Each ? will be replaced by the values provided in the function parameters,
     * after being properly escaped.
     *
     * Returns an associative array of the results.
     */
    public function executeQueryMap($sql) {
        $args = func_get_args();
        $sql = $this->substituteParameters($sql, $args);
        $result = mysql_query($sql, $this->databaseLink);
        if($result === FALSE) {
            $message = 'Database.executeQueryMap: mysql_query failed: '.mysql_error($this->databaseLink);
            $this->rollbackIgnoreErrors();
            error_log("SQL: ".$sql);
            throw new SQLException($message);
        }
        $values = array();
        while(($row = mysql_fetch_array($result, MYSQL_NUM)) !== FALSE) {
            $cnt = count($row);
            if($cnt!=2) {
                $this->rollbackIgnoreErrors();
                error_log("SQL: ".$sql);
                throw new SQLException('Database.executeQueryMap: Incorrect number of columns from query: '.$cnt);
            }
            $key = $row[0];
            if(isset($values[$key])) {
                $this->rollbackIgnoreErrors();
                error_log("SQL: ".$sql);
                throw new SQLException('Database.executeQueryMap: Key found twice: '.$key);
            }
            $values[$key] = $row[1];
        }
        if($result !== TRUE && !mysql_free_result($result)) {
            $message = 'Database.executeQueryMap: mysql_free_result failed: '.mysql_error($this->databaseLink);
            $this->rollbackIgnoreErrors();
            throw new SQLException($message);
        }
        return $values;
    }
    
    	/* This function will take a string in the format of a single item or
* 	multiple items in the format 1,2,3,4,5 or an array of items.
* 	The output will be a readable set of items with the last two items
* 	separated by " and ".
*
* 	@param  string|array $numbers The list of items as a string or array.
* 	@return string The formatted items.
*/

function formatItems($numbers) {
	if (is_array($numbers)) {
	    // If numbers is an array then implode it into a comma separated string.
	    $numbers = implode(',', $numbers);
	}
  
	if (is_string($numbers)) {
	    /*
	    Make sure all commas have a single space character after them and that 
	    there are no double commas in the string.
	    */ 
	    $numbers = trim($numbers);
	    $patterns[0] = '/\s*,\s*/';
	    $patterns[1] = '/,{2,}/';
	    $patterns[2] = '/,+$/';
	    $patterns[3] = '/^,+/';
	    $patterns[4] = '/,/';
	    $replacements[0] = ',';
	    $replacements[1] = ',';
	    $replacements[2] = '';
	    $replacements[3] = '';
	    $replacements[4] = ', ';
	    $numbers= preg_replace($patterns, $replacements, $numbers);

	    // The string contains commas, find the last comma in the string.
	    $lastCommaPos = strrpos($numbers, ',') - strlen($numbers);

	    // Replace the last ocurrance of a comma with " and "
	    $numbers = substr($numbers, 0, $lastCommaPos) . str_replace(',', ' and ', substr($numbers, $lastCommaPos));
	}
	return $numbers;
}

    
    
}
?>

global.php

<?php
    require_once dirname(__FILE__).'/database.php';

    /*
     * Encodes HTML.
     */
    function eh($text) {
        return htmlspecialchars($text);
    }

    /*
     * Encodes a URL query parameter.
     */
    function eu($text) {
        return urlencode($text);
    }
?>

base-top.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-US">
<head>
    <meta http-equiv="Content-Style-Type" content="text/css" />
<meta http-equiv="X-UA-Compatible" content="IE=8" />
    <meta http-equiv="Content-Script-Type" content="text/javascript" />
    

    <style type="text/css">
	body {font-family:Arial,Verdana,Helvetica,sans-serif;font-size:100%}
    	reg {font-family:Arial,Verdana,Helvetica,sans-serif;font-size:.75em}
    	little {font-family:Arial,Helvetica,Verdana,sans-serif;font-size:small}
    	biggish {font-family:Arial,Helvetica,Verdana,sans-serif}
    	banner {font-family:Times,"Times New Roman",serif;text-align:center;font-size:1.50em}
	h {font-family:Arial,Verdana,Helvetica,sans-serif} 
	p {font-family:Arial,Verdana,Helvetica,sans-serif;font-size:small}
	table {border-collapse:collapse; border:0px; padding:0px;margin:0px}
	img {padding:0px; border-width:0px; margin:0px;display:block}
	td {padding:0px; border-width:0px; margin:0px}
	a:link {color:#000000; text-decoration:none}
	a:active {color:#ff0000; text-decoration:none}
	a:visited {color:#000000; text-decoration:none}
	a:hover {color:#dd0000; text-decoration:none}
</style>

    <title>NCNG Family Programs</title>
    <? require_once dirname(__FILE__).'/global.php'; ?>
</head>
<body>

<? echo ("<form method=\"post\" action=\"".$actionfile."\">") ?>
<center>
	<table align="center">
		<tr><td><center><img src="./images/stickpeople.gif" width="768" height="172"></center></td></tr>
	</table>
	<table width="1024">
		<tr style="height:30px;width:1024px;"><td colspan="5">&nbsp</td></tr>
		<tr style="height:25px;width:1024px;" align="right"><td>&nbsp</td></tr>	
		<tr style="width:1024px;">
			<td style="height:100%;width:200px;" align="right" valign="top">
				<table>
					<? $navbar=array('events', 'announcements', 'armories', 'directory', 'newsletter', 'forms', 'logout', 'admin'); ?>
					<? $filtersub=array('all posts', 'general', 'm.f.l.c.', 'p.f.c.', 'f.a.c.', 'child & youth', 'public affairs'); ?>
					<? $adminsub=array('events', 'announcements', 'armories', 'directory', 'newsletter', 'forms'); ?>
					<? if ($_SESSION['canadmin']='1') {
						$len=count($navbar);
					} else {
						$len=(count($navbar)-1);
					} ?>
					<? for ($i = 0; $i < $len; $i++) { ?>
						<? $linkref=$navbar[$i].'.php'; ?>
						<tr height="24">
							<? if ($currentpage == $navbar[$i]) { ?>
								<td width="12"><img src="./images/left.gif"></td>
								<td style="background:#f3f3ff;" width="168" align="right">
									<? echo ("<a href=".$navbar[$i].".php".">") ?>	<h><? echo($navbar[$i]) ?></h></a>
								</td>
								<td style="background:#f3f3ff;" width="20">&nbsp</td>
								<? if ($currentpage == "announcements") { ?>
									<? include 'filtersub.php'; ?>									
								<? } ?>  									
								<? if ($currentpage == "admin") { ?>
									<? include 'adminsub.php'; ?>									
								<? } ?>  
							<? } else {?>
								<td width="180" align="right" colspan="2">
									<? echo ("<a href=".$navbar[$i].".php".">") ?>	<h><? echo($navbar[$i]) ?></h></a>
								</td>
								<td width="20">&nbsp</td>																		
							<? } ?>
						</tr>
					<? } ?>
				</table>
			</td>
			<td style="background:#f3f3ff">&nbsp</td>
			<td style="height:100%; width:774px; background:#f3f3ff;" colspan="2" align="left" valign="top">
				<table>
					<tr style="height:50px"><td>&nbsp</td></tr>
					<tr>
						<td align="center">

admin-events.php

<? $currentpage='admin'; ?>
<? $currentsubpage='events'; ?>
<? $actionfile='admin-events-edit.php' ?>
<? include 'base-top.php';?>

<?
function r_implode($glue, $pieces) { 
	foreach($pieces as $r_pieces) { 
		if(is_array($r_pieces)) { 
			$retVal[] = r_implode($glue, $r_pieces); 
		} else { 
			$retVal[] = $r_pieces; 
		} 
	} 
	return implode( $glue, $retVal ); 
} 
?>

<table style="width:700px;">
<tr align="center">
	<td style="text-align:center; background:#f3f3ff; border-top:1px solid black; border-bottom:1px solid black; width:600px;" colspan="2">
		<banner><? echo ($currentsubpage." manager"); ?></banner>
	</td>
</tr>
<tr style="height:30px;">
	<td>
		&nbsp
	</td>
</tr>
<tr>
	<td colspan="3" align="left">
		<button type="submit" name="neweventbutton" style="width:200px;" value="pushed">
			Create New Event
		</button>
	</td>
</tr>
<tr>
	<td style="height:10px;">
		&nbsp
	</td>
</tr>
<? $event = $database->executeQueryRows("SELECT eid, eventname, start, end, city, pocname FROM events ORDER BY eid"); ?>

<? $len = count($event); ?>
<? for($i=0; $i<$len; $i++) { ?>
	<? $eventuids = $database->executeQueryRows("SELECT uid FROM bookings WHERE eid=?",$event[$i]['eid']); ?>
	<? $uidlist = r_implode(", ", $eventuids); ?>
	<? $uidarray = explode(", ", $uidlist); ?>
	<? foreach ($uidarray as $uid) {?>
		<? $mflc[] = $database->executeQueryRows("SELECT fullname FROM users WHERE (grp='mflc' AND uid=?) ORDER BY fullname",$uid); ?>	
		<? $pfc[] = $database->executeQueryRows("SELECT fullname FROM users WHERE (grp='pfc' AND uid=?) ORDER BY fullname",$uid); ?>
		<? $fac[] = $database->executeQueryRows("SELECT fullname FROM users WHERE (grp='fac' AND uid=?) ORDER BY fullname",$uid); ?>				
	<? } ?>
	<? $mflcnames = r_implode(", ", $mflc); ?>
	<? $pfcnames = r_implode(", ", $pfc); ?>
	<? $facnames = r_implode(", ", $fac); ?>
	<tr>
		<td colspan="6">
			<table style="width:700px; border:8px solid #f9f9ff; background:#f9f9ff; text-align:left;">
				<tr>
					<td style="width:200px; text-align:left; vertical-align:bottom;" colspan="2">
						<? if($i<count($event)) { ?>
								<? echo($event[$i]['eventname']) ?><br>
						<? } ?>
					</td>
					<td style="text-align:left; width:70px; vertical-align:top; border:8px solid $f9f9ff;">
						<button type="submit" name="editbutton" style="width:70px;" value="<? echo($event[$i]['eid']); ?>">
							Edit
						</button>
					</td>
					<td style="width:50px; display:inline;">
						&nbsp
					</td>
					<td style="text-align:left; width:70px; vertical-align:top; border:8px solid $f9f9ff;">
						<button type="submit" name="deletebutton" style="width:70px;" value="<? echo($event[$i]['eid']); ?>">
							Delete
						</button>
					</td>
				</tr>
				<tr>
					<td style="font-size:0.6875em;" colspan="6">

							&nbsp&nbsp&nbsp&nbsp&nbsp
							Begin: <? echo($event[$i]['start']); ?>
							&nbsp&nbsp&nbsp&nbsp&nbsp
							End: <? echo($event[$i]['end']); ?><br>
							&nbsp&nbsp&nbsp&nbsp&nbsp
							City: <? echo($event[$i]['city']); ?>
							&nbsp&nbsp&nbsp&nbsp&nbsp
							PoC: <? echo($event[$i]['pocname']); ?><br>

							<? $mflcnames = str_replace(", , ,", ",", $mflcnames); ?>
							<? $mflcnames = str_replace(", ,", ",", $mflcnames); ?>
							<? $mflcnames = str_replace(",,", ", ", $mflcnames); ?>


							<? $pfcnames = str_replace(", , ,", ",", $pfcnames); ?>
							<? $pfcnames = str_replace(",,", ",", $pfcnames); ?>
							<? $pfcnames = str_replace(",,", ", ", $pfcnames); ?>

							<? $facnames = str_replace(", , ,", ",", $facnames); ?>
							<? $facnames = str_replace(", ,", ",", $facnames); ?>
							<? $facnames = str_replace(",,", ", ", $facnames); ?>

							&nbsp&nbsp&nbsp&nbsp&nbsp
							<? if(substr($mflcnames, -2)==", ") {
								$mflcnames=substr($mflcnames, 0, -2);
							} ?>
							<? if(strlen($mflcnames)<9) {
								$mflcnames="";
							} ?>
							MFLCs: <? echo($mflcnames); ?><br>
							&nbsp&nbsp&nbsp&nbsp&nbsp																											
							<? if(substr($mflcnames, -2)==", ") {
								$pfcnames=substr($pfcnames, 0, -2);
							} ?>
							<? if(strlen($pfcnames)<9) {
								$pfcnames="";
							} ?>
							PFCs: <? echo($pfcnames); ?><br>								
							&nbsp&nbsp&nbsp&nbsp&nbsp																											
							<? if(substr($mflcnames, -2)==", ") {
								$facnames=substr($facnames, 0, -2);
							} ?>
							<? if(strlen($facnames)<9) {
								$facnames="";
							} ?>
							FACs: <? echo($facnames); ?><br>
					</td>
				</tr>
			</table>
		</td>	
	</tr>
	<tr>
		<td style="height:16px">
			&nbsp
		</td>
	</tr>
	<? $mflc=""; ?>
	<? $pfc=""; ?>		
	<? $fac=""; ?>
<? } ?>
</table>
<? include 'base-bot.php' ?>

admin-events-edit.php

<? $currentpage='admin'; ?>
<? $currentsubpage='events'; ?>
<? $actionfile='admin-events-action.php'; ?>

<? include 'base-top.php'; ?>

<? if ($_POST['neweventbutton']!=='pushed') { ?>
<? if (isset($_POST['editbutton'])) { ?>
	<? $selectedeid = $_POST['editbutton'] ?>
	<? $nextaction="save"; ?>
<? } else { ?>
	<? $selectedeid = $_POST['deletebutton'] ?>
	<? $nextaction="delete";	?>	
<? } ?>
<? $event = $database->executeQueryRow("SELECT eventname,placename,address,city,	state,pocname,pocphone,pocemail FROM events WHERE eid = ?", $selectedeid); ?>
<? } else { ?>
<input type="hidden" name="newevent" value="yes" />
<? } ?>	

<? $_POST['areacode']=substr($event[pocphone],0,-6); ?>
<? $_POST['prefix']=substr($event[pocphone],3,-3); ?>
<? $_POST['suffix']=substr($event[pocphone],6,0); ?>

<input type="hidden" name="selectedeid" value="<? echo($selectedeid); ?>" /> 

<table colspan="2" style="width:700px;">
<tr align="center">
	<td style="text-align:center; background:#f3f3ff; border-top:1px solid black; border-bottom:1px solid black; width:600px;" colspan="3">
		<banner><? echo ("events manager"); ?></banner>
	</td>
</tr>
<tr>
	<td colspan="3" style="height:50px; text-align:right; vertical-align:top; font-size:.625em; color:red;">
		Red box indicates required field.
	</td>
</tr>
<tr>
	<td  colspan="2" style="height:26px">
			Event Name: 
			<input type="text" name="neweventname" size="50" value="<? echo($event['eventname']); ?>" style="border:2px inset red;" />
	</td>
</tr>
<tr>
	<td colspan="3" style="height:26px">
		&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
		<FORM name="startform">
			Start: 
			<input class="normal" 	name="startfield" value="<? echo($event['start']); ?>" size="18" style="border: 2px inset red; display:inline" />
			<a href="javascript:void(0)" onclick="if(self.gfPop)gfPop.fPopCalendar(document.startform.startdate);return false;" >
				<img 	style="display:inline; vertical-align:center;" 
						class="PopcalTrigger" name="popcal" 
						src="./popcal/datetime/calbtn.gif" width="28" 
						height="16" border="0" alt=""
				/>
			</a>
		</FORM>
		&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
		<FORM name="endform" style="display:inline;">
			End: 
			<input class="normal" 	name="endfield" value="<? echo($event['end']); ?>" size="18" style="border: 2px inset red;" />
			<a href="javascript:void(0)" onclick="if(self.gfPop)gfPop.fPopCalendar(document.endform.enddate);return false;" >
				<img 	style="display:inline; vertical-align:center;" 
						class="PopcalTrigger" name="popcal" 
						src="./popcal/datetime/calbtn.gif" width="28" 
						height="16" border="0" alt=""
				/>
			</a>
		</FORM>
	</td>
</tr>
<tr style="height:60px;" valign="bottom">
	<td colspan="2" style="height:26px">
		&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
		Placename: 
		<input type="text" name="placename" size="40" value="<? echo($event['placename']); ?>" />
	</td>
</tr>
<tr>
	<td colspan="2" style="height:26px">
		&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
		Street Address: 
		<input type="text" name="streetaddress" size="50" value="<? echo($event['address']); ?>"	style="border: 2px inset red;" />			
	</td>
</tr>
<tr>
	<td colspan="2" style="height:26px">
		&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
		City: 
		<input type="text" name="city" size="30" value="<? echo($event['city']); ?>" style="border: 2px inset red;" />
		&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
		State: 
		<input type="text" name="state" value="NC" size="1" value="<? echo($event['state']); ?>"	style="border: 2px inset red;" />							
	</td>
</tr>
<tr style="height:60px;" valign="bottom">
	<td colspan="2" style="height:26px">
		&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
		PoC Name: 
		<input type="text" name="pocname" size="40" value="<? echo($event['pocname']); ?>" 	style="border: 2px inset red;" />
	</td>
</tr>
<tr>
	<td colspan="3" style="height:26px">	
		&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
		PoC Phone: 
		<input type="text" name="areacode" size="1" value="<? echo($_POST['areacode']); ?>" style="border: 2px inset red;" />
		-
		<input type="text" name="pre" size="1" 	value="<? echo($_POST['prefix']); ?>" style="border: 2px inset red;" />
		-
		<input type="text" name="suffix-new" size="2" value="<? echo($_POST['suffix']); ?>" style="border: 2px inset red;" />				
		&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
		PoC eMail: 
		<input type="text" name="email" size="30" value="<? echo($event['pocemail']); ?>" />			
	</td>
</tr>
<tr>
	<td style="heigh:30px;">
		&nbsp
	</td>
</tr>				
<tr style="height:30px;">
	<td>
		&nbsp
	</td>
</tr>		
<tr>
	<td align="center" colspan="5">
		<table style="width:610px; border:5px solid #f9f9ff; background:#f9f9ff;">
			<tr style="text-align:center; border-bottom:1px solid black;">
				<td style="width:200px;">
					<h>MFLC</h>
				</td>
				<td style="width:200px; border-left:1px solid black; border-right:1px solid black;">
					<h>PFC</h>
				</td>
				<td style="width:200px;">
					<h>FAC</h>
				</td>
			</tr>
			<? $mflc = $database->executeQueryRows("SELECT fullname, uid FROM users WHERE grp='mflc' ORDER BY fullname"); ?>
			<? $pfc = $database->executeQueryRows("SELECT fullname, uid FROM users WHERE grp='pfc' ORDER BY fullname"); ?>
			<? $fac = $database->executeQueryRows("SELECT fullname, uid FROM users WHERE grp='fac' ORDER BY fullname"); ?>
			<? $len = count($mflc)>count($pfc) ? count($mflc) : count($pfc); ?>
			<? for($i=0; $i<$len; $i++) { ?>
			<tr>
				<td style="font-size:.8em; padding:2px;">
					<? if($i<count($mflc)) { ?>
						<input 
							type="checkbox"
							name="uids[]" 
							value="<? echo($mflc[$i]['uid']); ?>"
							<? if ( $database->executeQuery("SELECT (SELECT bid FROM bookings WHERE eid=? AND uid=?) IS NOT NULL", $selectedeid, $mflc[$i]['uid'])) { ?>
								checked="checked"
							<? } ?>
						/>
						<? echo($mflc[$i]['fullname']); ?>
						<input type="hidden" name="assigned[]" value="<?echo($mflc[$i]['fullname']); ?>" />
					<? } ?>
				</td>
				<td style="font-size:.8em; border-left:1px solid black; border-right:1px solid black; padding:2px;">									
					<? if($i<count($pfc)) { ?>
						<input 
							type="checkbox"
							name="uids[]" 
							value="<? echo($pfc[$i]['uid']); ?>"
							<? if ( $database->executeQuery("SELECT (SELECT bid FROM bookings WHERE eid=? AND uid=?) IS NOT NULL", $selectedeid, $pfc[$i]['uid'])) { ?>
								checked="checked"
							<? } ?>
						/>
						<? echo($pfc[$i]['fullname']); ?>
						<input type="hidden" name="assigned[]" value="<?echo($pfc[$i]['fullname']); ?>" 	/>
					<? } ?>
				</td>
				<td style="font-size:.8em; padding:2px;">									
					<? if($i<count($fac)) { ?>
						<input 
							type="checkbox"
							name="uids[]" 
							value="<? echo($fac[$i]['uid']); ?>"
							<? if ( $database->executeQuery("SELECT (SELECT bid FROM bookings WHERE eid=? AND uid=?) IS NOT NULL", $selectedeid, $fac[$i]['uid'])) { ?>
								checked="checked"
							<? } ?>
						/>
						<? echo($fac[$i]['fullname']); ?>
						<input type="hidden" name="assigned[]" value="<?echo($fac[$i]['fullname']); ?>" />
					<? } ?>
				</td>						
			</tr>
			<? } ?>	
		</table>
	</td>
</tr> 
<tr>
	<td colspan="2" style="height:80px; align:center;">
		<? if (isset($_POST["deletebutton"])) { ?>
			<button type="submit" name="confirmdeletebutton" style="width:200px; color:red;" value="pushed">
				Confirm Delete		
			</button>
		<? } else {?>
			<button type="submit" name="savebutton" style="width:200px;" value="pushed">
				Save Event		
			</button>
		<? } ?>
	</td>
	<td colspan="2" style="height:80px;">
		<button type="submit" name="backbutton" style="width:200px;" value="pushed">
			Go Back
		</button>		
	</td>
</tr>
</table>  	
<? include 'base-bot.php'; ?>

admin-events-action.php

<?
    require_once dirname(__FILE__).'/global.php';
    $database->begin();
    try {

    	if($_POST['confirmdeletebutton']=="pushed") {
    		$database->executeUpdate("DELETE FROM events WHERE eid=?", $_POST['selectedeid']);
    		$database->executeUpdate("DELETE FROM bookings WHERE eid=?", $_POST['selectedeid']);
    	}

	if($_POST['savebutton']=="pushed") {
		if($_POST['newevent']!=="yes") {
			$database->executeUpdate("DELETE FROM events WHERE eid=?", $_POST['selectedeid']);
			$database->executeUpdate("DELETE FROM bookings WHERE eid=?", $_POST['selectedeid']);			
		}	
 		$eventname = $_POST['neweventname'];
 		$pocphone = $_POST['areacode'].$_POST['prefix'];
	 	$database->executeUpdate("INSERT INTO events (eventname,start,end,placename,address,city,state,pocname,pocphone,pocemail) 
	 		VALUES (?,?,?,?,?,?,?,?,?,?)",
	 		$eventname,$POST['start'],$POST['end'],$POST['placename'],$POST['address'],$POST['city'],$POST['state'],$POST['pocname'],$POST['pocphone'],$POST['pocemail']);

	 	$_POST['selectedeid'] = $database->executeQuery("SELECT eid FROM events WHERE eventname=? AND start=? AND end=? AND address=? AND city=? AND pocname=?",
			$eventname, $POST['start'], $POST['end'], $POST['address'], $POST['city'], $POST['pocname']);

  		if(isset($_POST['uids'])) {
			foreach($_POST['uids'] as $userid) {
		  		$database->executeUpdate("INSERT INTO bookings (eid, uid) VALUES (?, ?)", $_POST['selectedeid'], $userid);
		  		$emailaddress[] = $database->executeQuery("SELECT email FROM users WHERE uid =?", $userid);
 			}
	 		$database->commit();
   		}
   		
		$emaillist = implode(", ",$emailaddress); 
		echo $emaillist; 

		/* ---------------------------- disabled for now
		$to = $emaillist;
		$subject = "Event Notice";
		$body = "The following have been requested to attend the ".$eventname.": ".$peoples;
		$from = "[email protected]";
		$envelopefrom = "-f [email protected]";
		$headers = "From: $from";
		mail($to,$subject,$body,$headers,$envelopefrom);
		echo "Mail Sent.";      
		*/    	   		
	}
    } catch(Exception $e) {
        $database->rollback();
        throw $e;
    }

    require dirname(__FILE__).'/admin-events.php';
?>

base-bot.php

							</td>
					</tr>				
				</table>
			</td>
			<td width="50" style="background:#f3f3ff;" valign="top"><img src="./images/uright.gif"></td>
		</tr>
		<tr height="50">
			<td width="200">&nbsp</td>
			<td width="50"><img src="./images/bleft.gif"></td>
			<td colspan="3" width="774" style="background:#f3f3ff;">&nbsp</td>
		</tr>	
	</table>
</center>
</form>
<iframe 
width="188" 
height="166" 
name="gToday:datetime:agenda.js:gfPop:plugins_time.js" 
id="gToday:datetime:agenda.js:gfPop:plugins_time.js" 
src="./popcal/datetime/ipopeng.htm" 
scrolling="no" 
frameborder="0" 
style="visibility:visible; z-index:999; position:relative; top:-500px; left:-500px;">
</iframe>
</body>
</html>

Yeah... it's a lot of code. And I apologize if I should have somehow been able to abbreviate it or present it in some other way.

neurotopia · June 3, 2010

The files that are most relevant to the specific task are admin-events-edit.php and admin-events-action.php. I simply included the other files because they have functions that are called by the others.

neurotopia · June 3, 2010

And here is the tail from the php error log:

[Thu Jun 03 10:21:53 2010] [error] [client 24.172.255.218] SQL: INSERT INTO events (eventname,start,end,placename,address,city,state,pocname,pocphone,pocemail) \n\t\t \t\tVALUES ('RSP',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), referer: http://www.eth0s.com/testfunk/admin-events-edit.php
[Thu Jun 03 10:21:53 2010] [error] [client 24.172.255.218] PHP Fatal error:  Uncaught exception 'SQLException' with message 'Database.executeUpdate: mysql_query failed: Column 'start' cannot be null' in /www/eth0s.com/webapps/ROOT/testfunk/db/MySQLDataSource.php:152\nStack trace:\n#0 /www/eth0s.com/webapps/ROOT/testfunk/admin-events-action.php(20): MySQLDataSource->executeUpdate('INSERT INTO eve...', 'RSP', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)\n#1 {main}\n  thrown in /www/eth0s.com/webapps/ROOT/testfunk/db/MySQLDataSource.php on line 152, referer: http://www.eth0s.com/testfunk/admin-events-edit.php

The values being entered remain null, even though I populated those variables with data. This is why I think the problem is with the input text fields not updating the variables, rather than with my sql query.

neurotopia · June 3, 2010

WOOT!!! I figured it out! I forgot several underscores in $_POST in the line where I was writing the SQL data back to my table. :rtfm: I guess after 14 hours of staring at PHP my eyes were shot.

Thanks again for your tolerance and patience. I still very much appreciate all your assistance with this. And, again, sorry about all the code.

Sign In

can't update sql table with new values from input field

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information