event_update php page

begeiste · October 21, 2011, 3:46am

Hi,

I knew how to make insert into in sql statement as well as delete. But never learned how to update the record on mysql database and php page. Could anyone please give some tips to make the update.php page? Can I tweak this event_add.php page to be event_update.php page? Any tips would be much appreciated!
This is my event_add.php page

&lt;?
require_once("includes/config.php");

session_start();

$db_connection = mysql_connect ($DBHost, $DBUser, $DBPass) OR die (mysql_error());  
$db_select = mysql_select_db ($DBName) or die (mysql_error());

if($use_auth)
{
	if(!isset($_SESSION['admin_id']))
	{
		if ((!isset($_POST['USER'])) AND (!isset($_POST['PASS']))) {
		?&gt;
		&lt;!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"&gt;
		&lt;html&gt;
		&lt;head&gt;
		&lt;title&gt;&lt;?php include("titles.php"); ?&gt; - Add Event&lt;/title&gt;
		&lt;meta http-equiv="Content-Type" content="text/html; charset=UTF-8"&gt;
		&lt;link href="images/cal.css" rel="stylesheet" type="text/css"&gt;
		&lt;/head&gt;
		
		&lt;body&gt;
		&lt;br&gt;&lt;br&gt;
		&lt;form name="form1" method="post" action="event_add.php"&gt;
		  
			&lt;div class=""&gt;&lt;span class=""&gt;Username:&lt;/span&gt;&lt;input name="USER" type="text" id="USER"&gt;&lt;/div&gt;
            &lt;div class=""&gt;&lt;span class=""&gt;Password:&lt;/span&gt;&lt;input name="PASS" type="password" id="PASS"&gt;&lt;/div&gt;
            &lt;div class=""&gt;&lt;span class=""&gt;&lt;/span&gt;&lt;input type="submit" name="Submit" value=" login"&gt;&lt;/div&gt;
			
		&lt;input type="hidden" name="day" id="day" value="&lt;? echo $_GET['day']; ?&gt;"&gt;
		&lt;input type="hidden" name="month" id="month" value="&lt;? echo $_GET['month']; ?&gt;"&gt;
		&lt;input type="hidden" name="year" id="year" value="&lt;? echo $_GET['year']; ?&gt;"&gt;
		&lt;input type="hidden" name="id" id="id" value="&lt;? echo $_GET['id']; ?&gt;"&gt;
		&lt;/form&gt;
		&lt;/body&gt;
		&lt;/html&gt;
		&lt;?
			exit;
		} 
		ELSE
		{
			$query = "SELECT admin_id FROM ".$TBL_PR."admins WHERE admin_username='".addslashes($_POST['USER'])."' AND admin_password='".addslashes(md5($_POST['PASS']))."' LIMIT 1";
			$query_result = mysql_query ($query);
			while ($info = mysql_fetch_array($query_result))
			{
				$admin_id = $info['admin_id'];
			}
		
			IF(isset($admin_id))
			{
				$_SESSION['admin_id'] = $admin_id;
			}
			ELSE
			{
				header("Location: event_add.php?day=".$_POST['day']."&month=".$_POST['month']."&year=".$_POST['year']."&id=" . $_POST['id']);
				exit;
			}
		}
	}
}

IF(isset($_POST['submit']))
{
	$db_table = $TBL_PR . "events";
	
	$_POST['description'] = substr($_POST['description'],0,1500);
	$_POST['title'] = substr($_POST['title'],0,100);
	$_POST['pic'] = substr($_POST['pic'],0,255);
	$_POST['purl'] = substr($_POST['purl'],0,255);
	$_POST['vurl'] = substr($_POST['vurl'],0,255);

	mysql_query("INSERT INTO $db_table ( `event_id` , `event_day` , `event_month` , `event_year` , `event_time` , `event_title` , `event_desc`, `event_pic`,`event_purl`,`event_vurl` ) VALUES ('', '".addslashes($_POST['day'])."', '".addslashes($_POST['month'])."', '".addslashes($_POST['year'])."', '".addslashes($_POST['hour'].":".$_POST['minute'])."', '".addslashes($_POST['title'])."', '".addslashes($_POST['description'])."','".addslashes($_POST['pic'])."','".addslashes($_POST['purl'])."','".addslashes($_POST['vurl'])."')");
	$_POST['month'] = $_POST['month'] + 1;
?&gt;
&lt;!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"&gt;
&lt;html&gt;
&lt;head&gt;
&lt;title&gt;&lt;?php include("titles.php");?&gt;&lt;/title&gt;
&lt;meta http-equiv="Content-Type" content="text/html; charset=UTF-8"&gt;
&lt;script language='javascript' type="text/javascript"&gt;
&lt;!--
 function redirect_to(where, closewin)
 {
 	opener.location= 'index.php?' + where;
 	
 	if (closewin == 1)
 	{
 		self.close();
 	}
 }
  //--&gt;
 &lt;/script&gt;
&lt;/head&gt;
&lt;body onLoad="javascript:redirect_to('month=&lt;? echo $_POST['month'].'&year='.$_POST['year']; ?&gt;',1);"&gt;
&lt;/body&gt;
&lt;/html&gt;
&lt;?
}
ELSE 
{
?&gt;
&lt;!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"&gt;
&lt;html&gt;
&lt;head&gt;
&lt;title&gt;&lt;?php include("titles.php");?&gt;&lt;/title&gt;
&lt;meta http-equiv="Content-Type" content="text/html; charset=UTF-8"&gt;
&lt;link href="images/cal.css" rel="stylesheet" type="text/css"&gt;
&lt;/head&gt;
&lt;body&gt;
&lt;div class="eventFormControl"&gt;
&lt;form name="form1" method="post" action=""&gt;
      &lt;div class="eventDateControl"&gt;&lt;span class="addevent"&gt;Event Date&lt;/span&gt;&lt;br&gt; 
        &lt;span class="addeventextrainfo"&gt;(MM/DD/YY)&lt;/span&gt;&lt;/div&gt;
      &lt;div class="formMenuControl"&gt; 
        &lt;select name="month" id="month"&gt;
           &lt;option value="12" &lt;? IF($_GET['month'] == "12"){ echo "selected"; } ?&gt;&gt;12&lt;/option&gt;
        &lt;/select&gt; &lt;select name="day" id="day"&gt;
          &lt;option value="1" &lt;? IF($_GET['day'] == "1"){ echo "selected"; } ?&gt;&gt;01&lt;/option&gt;
          &lt;option value="2" &lt;? IF($_GET['day'] == "2"){ echo "selected"; } ?&gt;&gt;02&lt;/option&gt;
          &lt;option value="3" &lt;? IF($_GET['day'] == "3"){ echo "selected"; } ?&gt;&gt;03&lt;/option&gt;
          &lt;option value="4" &lt;? IF($_GET['day'] == "4"){ echo "selected"; } ?&gt;&gt;04&lt;/option&gt;
          &lt;option value="5" &lt;? IF($_GET['day'] == "5"){ echo "selected"; } ?&gt;&gt;05&lt;/option&gt;
          &lt;option value="6" &lt;? IF($_GET['day'] == "6"){ echo "selected"; } ?&gt;&gt;06&lt;/option&gt;
          &lt;option value="7" &lt;? IF($_GET['day'] == "7"){ echo "selected"; } ?&gt;&gt;07&lt;/option&gt;
          &lt;option value="8" &lt;? IF($_GET['day'] == "8"){ echo "selected"; } ?&gt;&gt;08&lt;/option&gt;
          &lt;option value="9" &lt;? IF($_GET['day'] == "9"){ echo "selected"; } ?&gt;&gt;09&lt;/option&gt;
          &lt;option value="10" &lt;? IF($_GET['day'] == "10"){ echo "selected"; } ?&gt;&gt;10&lt;/option&gt;
          &lt;option value="11" &lt;? IF($_GET['day'] == "11"){ echo "selected"; } ?&gt;&gt;11&lt;/option&gt;
          &lt;option value="12" &lt;? IF($_GET['day'] == "12"){ echo "selected"; } ?&gt;&gt;12&lt;/option&gt;
          &lt;option value="13" &lt;? IF($_GET['day'] == "13"){ echo "selected"; } ?&gt;&gt;13&lt;/option&gt;
          &lt;option value="14" &lt;? IF($_GET['day'] == "14"){ echo "selected"; } ?&gt;&gt;14&lt;/option&gt;
          &lt;option value="15" &lt;? IF($_GET['day'] == "15"){ echo "selected"; } ?&gt;&gt;15&lt;/option&gt;
          &lt;option value="16" &lt;? IF($_GET['day'] == "16"){ echo "selected"; } ?&gt;&gt;16&lt;/option&gt;
          &lt;option value="17" &lt;? IF($_GET['day'] == "17"){ echo "selected"; } ?&gt;&gt;17&lt;/option&gt;
          &lt;option value="18" &lt;? IF($_GET['day'] == "18"){ echo "selected"; } ?&gt;&gt;18&lt;/option&gt;
          &lt;option value="19" &lt;? IF($_GET['day'] == "19"){ echo "selected"; } ?&gt;&gt;19&lt;/option&gt;
          &lt;option value="20" &lt;? IF($_GET['day'] == "20"){ echo "selected"; } ?&gt;&gt;20&lt;/option&gt;
          &lt;option value="21" &lt;? IF($_GET['day'] == "21"){ echo "selected"; } ?&gt;&gt;21&lt;/option&gt;
          &lt;option value="22" &lt;? IF($_GET['day'] == "22"){ echo "selected"; } ?&gt;&gt;22&lt;/option&gt;
          &lt;option value="23" &lt;? IF($_GET['day'] == "23"){ echo "selected"; } ?&gt;&gt;23&lt;/option&gt;
          &lt;option value="24" &lt;? IF($_GET['day'] == "24"){ echo "selected"; } ?&gt;&gt;24&lt;/option&gt;
          &lt;option value="25" &lt;? IF($_GET['day'] == "25"){ echo "selected"; } ?&gt;&gt;25&lt;/option&gt;
         &lt;/select&gt;
         &lt;select name="year" id="year"&gt;
          &lt;option value="2011" &lt;? IF($_GET['year'] == "2011"){ echo "selected"; } ?&gt;&gt;2011&lt;/option&gt;
        &lt;/select&gt;
    &lt;/div&gt;
    &lt;div class="dataEntryControl"&gt; 
      &lt;div class="tCtrl"&gt;&lt;div class="addevent"&gt;Event Title:&lt;/div&gt;&lt;input name="title" type="text" id="title" size="60"&gt;&lt;/div&gt;
      &lt;div class="dCtrl"&gt;&lt;div class="addevent"&gt;Event Description:&lt;/div&gt;&lt;textarea name="description" cols="52" rows="18" id="description"&gt;&lt;/textarea&gt;&lt;/div&gt;
      &lt;div class="pCtrl"&gt;&lt;div class="addevent"&gt;Event Photo:&lt;/div&gt;&lt;input name="pic" type="text" id="pic" size="60"&gt;&lt;/div&gt;
      &lt;div class="plCtrl"&gt;&lt;div class="addevent"&gt;Photo:&lt;/div&gt;&lt;input name="purl" type="text" id="purl" size="60"&gt;&lt;/div&gt;
      &lt;div class="vlCtrl"&gt;&lt;div class="addevent"&gt;Video:&lt;/div&gt;&lt;input name="vurl" type="text" id="vurl" size="60"&gt;&lt;/div&gt;&lt;br&gt;
      &lt;div class=""&gt;&lt;input name="submit" type="submit" id="submit" value="Add Event"&gt;&lt;/div&gt;
    &lt;/div&gt;
&lt;/form&gt;
&lt;/div&gt;
&lt;/body&gt;
&lt;/html&gt;
&lt;? 
} 
?&gt;

programthis · October 22, 2011, 9:12pm

Use the MySQL UPDATE function. Your SQL statement would look something like this:

UPDATE $db_table SET <column_name_1> = <new_data_1> [, <column_name_2> = <new_data_2>, … <column_name_n> = <new_data_n>] WHERE event_id= xxx

begeiste · October 24, 2011, 4:37pm

Thanks for this simple concept. Would you please give me a specific sample like: should I get the $id=$_GET[‘id’] first? or where should I put the $id=$_GET[‘id’]? and What should I deal with <column_name_1> = <new_data_1>…?

Much appreciated!!!

Cups · October 24, 2011, 5:52pm

If the form that allows the user to UPDATE records uses the POST method, you access $_POST variables, if it uses GET its $_GET variables.

essentially:


$event_year = mysql_real_escape_string($_POST['event_year']);
// and so on

$sql = "UPDATE yourtable set 
event_year = '$event_year'
// and so on
WHERE event_id = " . (int) $_POST['event_id'];

You should be able to work it out from that.

First though, create a concrete working UPDATE example that puts test data to make sure you understand what is going on and the kind of error messages you can expect … paste it directly into PhpMyAdmin or whatever you use to manage your database.

eg

UPDATE yourtable set event_year = 1900 where event_id = 1;

[fphp]mysql_real_escape_string[/fphp]

begeiste · October 24, 2011, 7:42pm

Hi Cups,

I must thank you for your suggestion. I am a newbie here, I changed the update sql statement …, it is still not working.I appreciated your time and your effort.

<?
require_once(“includes/config.php”);

session_start();

$db_connection = mysql_connect ($DBHost, $DBUser, $DBPass) OR die (mysql_error());
$db_select = mysql_select_db ($DBName) or die (mysql_error());

if($use_auth)
{
if(!isset($_SESSION[‘admin_id’]))
{
if ((!isset($_POST[‘USER’])) AND (!isset($_POST[‘PASS’]))) {
?>
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”>
<html>
<head>
<title><?php include(“titles.php”); ?> - Add Event</title>
<meta http-equiv=“Content-Type” content=“text/html; charset=UTF-8”>
<link href=“images/cal.css” rel=“stylesheet” type=“text/css”>
</head>

	&lt;body&gt;
	&lt;br&gt;&lt;br&gt;
	&lt;form name="form1" method="post" action="event_add.php"&gt;
	  
		&lt;div class=""&gt;&lt;span class=""&gt;Username:&lt;/span&gt;&lt;input name="USER" type="text" id="USER"&gt;&lt;/div&gt;
        &lt;div class=""&gt;&lt;span class=""&gt;Password:&lt;/span&gt;&lt;input name="PASS" type="password" id="PASS"&gt;&lt;/div&gt;
        &lt;div class=""&gt;&lt;span class=""&gt;&lt;/span&gt;&lt;input type="submit" name="Submit" value=" login"&gt;&lt;/div&gt;
		
	&lt;input type="hidden" name="day" id="day" value="&lt;? echo $_GET['day']; ?&gt;"&gt;
	&lt;input type="hidden" name="month" id="month" value="&lt;? echo $_GET['month']; ?&gt;"&gt;
	&lt;input type="hidden" name="year" id="year" value="&lt;? echo $_GET['year']; ?&gt;"&gt;
	&lt;input type="hidden" name="id" id="id" value="&lt;? echo $_GET['id']; ?&gt;"&gt;
	&lt;/form&gt;
	&lt;/body&gt;
	&lt;/html&gt;
	&lt;?
		exit;
	} 
	ELSE
	{
		$query = "SELECT admin_id FROM ".$TBL_PR."admins WHERE admin_username='".addslashes($_POST['USER'])."' AND admin_password='".addslashes(md5($_POST['PASS']))."' LIMIT 1";
		$query_result = mysql_query ($query);
		while ($info = mysql_fetch_array($query_result))
		{
			$admin_id = $info['admin_id'];
		}
	
		IF(isset($admin_id))
		{
			$_SESSION['admin_id'] = $admin_id;
		}
		ELSE
		{
			header("Location: event_update.php?day=".$_POST['day']."&month=".$_POST['month']."&year=".$_POST['year']."&id=" . $_POST['id']);
			exit;
		}
	}
}

}

IF(isset($_POST[‘submit’]))
{
$db_table = $TBL_PR . “events”;
$event_id = $_GET[‘event_id’];
$_POST[‘description’] = substr($_POST[‘description’],0,1500);
$_POST[‘title’] = substr($_POST[‘title’],0,100);
$_POST[‘pic’] = substr($_POST[‘pic’],0,255);
$_POST[‘purl’] = substr($_POST[‘purl’],0,255);
$_POST[‘vurl’] = substr($_POST[‘vurl’],0,255);

mysql_query("UPDATE $db_table SET `event_id`,`event_day` = '".addslashes($_POST['day'])."',`event_month` = '".addslashes($_POST['month'])."',`event_year` = '".addslashes($_POST['year'])."',`event_time` = '".addslashes($_POST['hour'].":".$_POST['minute'])."',`event_title` ='".addslashes($_POST['title'])."',`event_desc` = '".addslashes($_POST['description'])."', `event_pic` = '".addslashes($_POST['pic'])."', `event_purl` = '".addslashes($_POST['purl'])."', `event_vurl` = '".addslashes($_POST['vurl'])."' WHERE `event_id` = '".addslashes($_POST['event_id'])."'");

$_POST['month'] = $_POST['month'] + 1;

?>
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”>
<html>
<head>
<title><?php include(“titles.php”);?></title>
<meta http-equiv=“Content-Type” content=“text/html; charset=UTF-8”>
<script language=‘javascript’ type=“text/javascript”>
<!–
function redirect_to(where, closewin)
{
opener.location= ‘index.php?’ + where;

if (closewin == 1)
{
	self.close();
}

}
//–>
</script>
</head>
<body onLoad=“javascript:redirect_to(‘month=<? echo $_POST[‘month’].’&year=‘.$_POST[‘year’]; ?>’,1);”>
</body>
</html>
<?
}
ELSE
{
?>
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”>
<html>
<head>
<title><?php include(“titles.php”);?></title>
<meta http-equiv=“Content-Type” content=“text/html; charset=UTF-8”>
<link href=“images/cal.css” rel=“stylesheet” type=“text/css”>
</head>
<body>
<div class=“eventFormControl”>
<form name=“form1” method=“post” action=“”>
<div class=“eventDateControl”><span class=“addevent”>Event Date</span><br>
<span class=“addeventextrainfo”>(MM/DD/YY)</span></div>
<div class=“formMenuControl”>
<select name=“month” id=“month”>
<option value=“12” <? IF($_GET[‘month’] == “12”){ echo “selected”; } ?>>12</option>
</select> <select name=“day” id=“day”>
<option value=“1” <? IF($_GET[‘day’] == “1”){ echo “selected”; } ?>>01</option>
<option value=“2” <? IF($_GET[‘day’] == “2”){ echo “selected”; } ?>>02</option>
<option value=“3” <? IF($_GET[‘day’] == “3”){ echo “selected”; } ?>>03</option>
<option value=“4” <? IF($_GET[‘day’] == “4”){ echo “selected”; } ?>>04</option>
<option value=“5” <? IF($_GET[‘day’] == “5”){ echo “selected”; } ?>>05</option>
<option value=“6” <? IF($_GET[‘day’] == “6”){ echo “selected”; } ?>>06</option>
<option value=“7” <? IF($_GET[‘day’] == “7”){ echo “selected”; } ?>>07</option>
<option value=“8” <? IF($_GET[‘day’] == “8”){ echo “selected”; } ?>>08</option>
<option value=“9” <? IF($_GET[‘day’] == “9”){ echo “selected”; } ?>>09</option>
<option value=“10” <? IF($_GET[‘day’] == “10”){ echo “selected”; } ?>>10</option>
<option value=“11” <? IF($_GET[‘day’] == “11”){ echo “selected”; } ?>>11</option>
<option value=“12” <? IF($_GET[‘day’] == “12”){ echo “selected”; } ?>>12</option>
<option value=“13” <? IF($_GET[‘day’] == “13”){ echo “selected”; } ?>>13</option>
<option value=“14” <? IF($_GET[‘day’] == “14”){ echo “selected”; } ?>>14</option>
<option value=“15” <? IF($_GET[‘day’] == “15”){ echo “selected”; } ?>>15</option>
<option value=“16” <? IF($_GET[‘day’] == “16”){ echo “selected”; } ?>>16</option>
<option value=“17” <? IF($_GET[‘day’] == “17”){ echo “selected”; } ?>>17</option>
<option value=“18” <? IF($_GET[‘day’] == “18”){ echo “selected”; } ?>>18</option>
<option value=“19” <? IF($_GET[‘day’] == “19”){ echo “selected”; } ?>>19</option>
<option value=“20” <? IF($_GET[‘day’] == “20”){ echo “selected”; } ?>>20</option>
<option value=“21” <? IF($_GET[‘day’] == “21”){ echo “selected”; } ?>>21</option>
<option value=“22” <? IF($_GET[‘day’] == “22”){ echo “selected”; } ?>>22</option>
<option value=“23” <? IF($_GET[‘day’] == “23”){ echo “selected”; } ?>>23</option>
<option value=“24” <? IF($_GET[‘day’] == “24”){ echo “selected”; } ?>>24</option>
<option value=“25” <? IF($_GET[‘day’] == “25”){ echo “selected”; } ?>>25</option>
</select>
<select name=“year” id=“year”>
<option value=“2011” <? IF($_GET[‘year’] == “2011”){ echo “selected”; } ?>>2011</option>
</select>
</div>
<div class=“dataEntryControl”>
<div class=“tCtrl”><div class=“addevent”>Event Title:</div><input name=“title” type=“text” id=“title” size=“60”></div>
<div class=“dCtrl”><div class=“addevent”>Event Description:</div><textarea name=“description” cols=“52” rows=“18” id=“description”></textarea></div>
<div class=“pCtrl”><div class=“addevent”>Event Photo:</div><input name=“pic” type=“text” id=“pic” size=“60”></div>
<div class=“plCtrl”><div class=“addevent”>Photo:</div><input name=“purl” type=“text” id=“purl” size=“60”></div>
<div class=“vlCtrl”><div class=“addevent”>Video:</div><input name=“vurl” type=“text” id=“vurl” size=“60”></div><br>
<div class=“”><input name=“submit” type=“submit” id=“submit” value=“Add Event”></div>
</div>
</form>
</div>
</body>
</html>
<?
}
?>

Cups · October 24, 2011, 8:07pm

Forget that lot, have a go at making a simple php script which just runs a mysql UPDATE statement.

Post your attempt here.

begeiste · October 24, 2011, 11:24pm

Thanks a lot.