Change database from mysql 4 to mysql 5 affects my calendar event page

I changed my database from mysql 4.1.10 to mysql 5.0 and I encountered error in my calendar event page. The output of my calendar date was change instead of date it becomes “>” and also when I press the button to move to another month I can’t move to another month. I have no idea why it happened.
Here is the old calendar code:


<?php
	$dbCnx = @mysql_connect("localhost","", "") or die('Could not Connect to the database')
	mysql_select_db("mydb");	
?>
<html>
<script>
function goLastMonth(month, year){
if(month == 1){
--year;
month = 13;
}
document.location.href = '<?=$_SERVER['PHP_SELF'];?>?month='+(month-1)+'&year='+year;
}
function goNextMonth(month, year){
if(month == 12){
++year;
month = 0;
}
document.location.href = '<?=$_SERVER['PHP_SELF'];?>?month='+(month+1)+'&year='+year;
} 
function remChars(txtControl, txtCount, intMaxLength)
{
if(txtControl.value.length > intMaxLength)
txtControl.value = txtControl.value.substring(0, (intMaxLength-1));
else
txtCount.value = intMaxLength - txtControl.value.length;
}
function checkFilled() {
var filled = 0
var x = document.form1.calName.value;
if (x.length > 0) {filled ++}
var y = document.form1.calDesc.value;
if (y.length > 0) {filled ++}
if (filled == 2) {
document.getElementById("Submit").disabled = false;
}
else {document.getElementById("Submit").disabled = true} // in case a field is filled then erased
}
</script>
<body>
<?php
$day = (isset($_GET["day"])) ? $_GET['day'] : "";
$month = (isset($_GET["month"])) ? $_GET['month'] : "";
$year = (isset($_GET["year"])) ? $_GET['year'] : "";
if(empty($day)){ $day = date("j"); }
if(empty($month)){ $month = date("n"); }
if(empty($year)){ $year = date("Y"); } 
$currentTimeStamp = strtotime("$year-$month-$day");
$monthName = date("F", $currentTimeStamp);
$numDays = date("t", $currentTimeStamp);
$counter = 0;
function hiLightEvt($eMonth,$eDay,$eYear){
$todaysDate = date("n/j/Y");
$dateToCompare = $eMonth . '/' . $eDay . '/' . $eYear;
if($todaysDate == $dateToCompare){
$aClass='class="today"';
}else{
$sql="select count(calDate) as eCount from calTbl where calDate = '" . $eMonth . '/' . $eDay . '/' . $eYear . "'";
$result = mysql_query($sql);
while($row= mysql_fetch_array($result)){
if($row['eCount'] >=1){
$aClass = 'class="event"';
}elseif($row['eCount'] ==0){
$aClass ='class="normal"';
}
}
}
return $aClass;
}
?>
<div id="Calendar_Event">
<table width="350" cellpadding="0" cellspacing="0">
<tr>
<td width="50" colspan="1">
<input type="button" value=" < " onClick="goLastMonth(<?php echo $month . ", " . $year; ?>);">
</td>
<td width="250" colspan="5">
<span class="title" style="color:#FFFFFF"><?php echo $monthName . " " . $year; ?></span><br>
</td>
<td width="50" colspan="1" align="right">
<input type="button" value=" > " onClick="goNextMonth(<?php echo $month . ", " . $year; ?>);">
</td>
</tr> 
<tr>
<th>M</td>
<th>T</td>
<th>W</td>
<th>T</td>
<th>F</td>
<th>S</td>
<th>S</td>
</tr>
<tr>
<?php
for($i = 1; $i < $numDays+1; $i++, $counter++){
$dateToCompare = $month . '/' . $i . '/' . $year;
$timeStamp = strtotime("$year-$month-$i");
if($i == 1){
$firstDay = date("N", $timeStamp);
for($j = 1; $j < $firstDay; $j++, $counter++){
echo "<td>&nbsp;</td>";
} 
}
if($counter % 7 == 0 ){
?>
</tr><tr>
<?php
}
?>
<!--right here--><td width="50" <?=hiLightEvt($month,$i,$year);?>><a href="<?=$_SERVER['PHP_SELF'] . '?month='. $month . '&day=' . $i . '&year=' . $year;?>&v=1"><?=$i;?></a></td> 
<?php
}
?>
</table>
</div>
<div id="New_Event">
<?php
if(isset($_GET['v'])){
if(isset($_POST['Submit'])){
$sql="insert into calTbl(calName,calDesc,calDate,calStamp) values('" . $_POST['calName'] ."','" . $_POST['calDesc'] . "','" . $_POST['calDate'] . "',now())";
mysql_query($sql);
}
$sql="select calName,calDesc, DATE_FORMAT(calStamp, '%a %b %e %Y') as calStamp from calTbl where calDate = '" . $month . '/' . $day . '/' . $year . "'";
$result = mysql_query($sql);
$numRows = mysql_num_rows($result);
if ($_SESSION['username']=="user1") 
{ 
$check1=mysql_query("SELECT * FROM tbllogin WHERE Username='user1' AND Department='Staff' AND Permission='True'"); 
$check2 = mysql_fetch_array($check1); 
$username = $check2['Username']; 
$dep = $check2['Department']; 
$permiss = $check2['Permission']; 
if($username=="user1" && $dep=="Staff" && $permiss=="True"){ 
$_SESSION['isallowed'] = $check2['Permission']; 
?> 
<a href="<?=$_SERVER['PHP_SELF'];?>?month=<?=$_GET['month'] . '&day=' . $_GET['day'] . '&year=' . $_GET['year'];?>&v=1&f=true">Add Even</a><a href="<?=$_SERVER['PHP_SELF'];?>?month=<?=$_GET['month'] . '&day=' . $_GET['day'] . '&year=' . $_GET['year'];?>&v=1&f=true">t</a>
<?php  
} 
}
else 
{ echo "You cannot Add New Event"; } 
?>
</div>
<div id="Cal_Event">
<?php
if(isset($_GET['f'])){
include 'calform.php';
}
if($numRows == 0 ){
echo '';
}else{
echo '<h3>Event Listed</h3>';
while($row = mysql_fetch_array($result)){
?>
<h5><?=$row['calName'];?></h5>
<?=$row['calDesc'];?><br/>
Listed On: <?=$row['calStamp'];?>
<?php
}
}
}
?>
</div>
</body>
</html>

On my new codes I only change the codes for the connection of database like the “host”, ”username”, ”password”, ”database name”, “table name”.
Honestly, I have no idea why it happened after I change my database. Actually my tbluser was work so I connected in my database on my other webpage.
Any help is highly appreciated. Thank you in advance.

My bad. btw Anthony, you have a stray ,

  mysql_real_escape_string($_POST['calDesc']),
);

should be

mysql_real_escape_string($_POST[‘calDesc’])
);

Swap these lines:

$sql="insert into tblcal(calName,calDesc,calDate) values('" . $_POST['calName'] ."','" . $_POST['calDesc'] . "','" . $_POST['calDate'] . "',now())"; 
mysql_query($sql); 

with these lines

$sql = "
    INSERT INTO
        tblcal
            (
                  calName
                , calDesc
                , calDate
            )
    VALUES
        (
              {$_POST['calName']}
            , {$_POST['calDesc']}
            , NOW()
        )
";
$escaped_query=mysql_real_escape_string($sql);

$result=mysql_query($escaped_query, $link);

// Display a MySQL error if the query failed

if ( $result === false ) {
    echo mysql_errno($link) . ": " . mysql_error($link) . "\
";
}

echo out your assembled queries and try them in PhpMyAdmin or similar to see if you get the results you expect. If not, it’s probably a MySQL issue. If the queries look wrong you may need to debug PHP.

I can’t really follow it when there is procedural PHP, HTML and Javascript code so mixed together.

Forgive me for I have sinned and not read every line of this thread…

But one major diff between Mysql 4.x and 5.x is that used to be valid code:

“insert into table values ‘’, $name, $age”

whereas in 5.0+ you have to do:

“insert into table values 0, $name, $age”

So, you have to tell auto-increment fields to enter a zero to make it work.

I found where in the codes I got this error:
here is:


<!--right here--><td width="50" <?=hiLightEvt($month,$i,$year);?>><a href="<?=$_SERVER['PHP_SELF'] . '?month='. $month . '&day=' . $i . '&year=' . $year;?>&v=1"><?=$i;?></a></td> 

As you can see the open connection that I used in my php is <?= and I change it to <?php and the result is the > was disappear but the date still did not appear. It is blank already.And the good news is I fix the problem in Choosing the Month but chnaging the <?= to <?php,so now my problem is to view the date…

This part:

<?php echo 'hiLightEvt($month,$i,$year)';?>

should be

<?php echo hiLightEvt($month,$i,$year);?>

did you try <?php echo hiLightEvt($month,$i,$year); ?>

The difference is that <?= echoes automatically, whereas <?php does not.

by the way this is not related to the mysql version, but has to do with the php short tags setting. Perhaps the php version changed or the php.ini configuration was changed.

:stuck_out_tongue: Touche

:eek:

Er, I know it’s early in the UK, but you do know that’s super-wrong don’t you?

Try this, it doesn’t escape the whole query. :stuck_out_tongue:


<?php
error_reporting(-1);
ini_set('display_errors', true);

$sql = sprintf(
  "INSERT INTO tblcal (calName, calDesc, calDate) VALUES ('&#37;s', '%s', NOW());",
  mysql_real_escape_string($_POST['calName']),
  mysql_real_escape_string($_POST['calDesc']),
);

$res = mysql_query($sql);

if(false === $res){
  printf("(%s) : %s\
", mysql_errno(), mysql_error());
  exit;
}
?>

I resolved my problem in viewing the date. Now my problem is, when I’m done adding event and I refresh the calendar page and I press the date which I put an event. Sad to say the event that I input did not take effect or did not save and also it did not appear in my database.
Here is my code for the form for adding event:


<style type="text/css">
<!--
#CAL_FORM {
	position:absolute;
	width:333px;
	height:181px;
	z-index:1;
	top: 16px;
}
-->
</style>
<form id="form1" name="form1" method="post" action="<?php echo $_SERVER['PHP_SELF'].'?month=' .$_GET['month'] . '&day=' . $_GET['day'] . '&year=' . $_GET['year'];?>&v=1">
  <div id="CAL_FORM">
  <table cellpadding="0" cellspacing="0" class="tableClass">

    <tr>

      <td width="142">Event Name</td>

      <td width="146"><div align="left">

        <input type="text" name="calName" id="calName" onKeyup="checkFilled();">

      </div></td>

    </tr>

    <tr>

      <td rowspan="2">Event Desc</td>

      <td><div align="left">

        <textarea name="calDesc" id="calDesc" cols="15" rows="5" onKeyDown="remChars(this, document.form1.txtCount, 200);"

							onKeyUp="remChars(this, document.form1.txtCount, 200);checkFilled();"></textarea>

        <br/>

      </div></td>

    </tr>

    <tr>

      <td>You have

        <input readonly name="txtCount" type="text" id="txtCount" value="200" size="2" maxlength="3">

characters left!</td>

    </tr>

    <tr>

      <td>Event Date</td>

      <td><div align="left">

        <input type="text" name="calDate" id="calDate" value="<?php echo $_GET['month'] . '/' . $_GET['day'] . '/' . $_GET['year'];?>" readonly>

      </div></td>

    </tr>

    <tr>

      <td colspan="2"><input type="submit" name="Submit" id="Submit" value="Submit" disabled></td>

    </tr>

  </table>

  <p>&nbsp;</p>
  <p>&nbsp;</p>
</div>
</form>

And this is my calendar revise code:


<?php
session_start();
$db_name="db_intranetsystem"; 

mysql_connect("localhost", "root", "open") or die("Cannot connect to server"); 
mysql_select_db("$db_name")or die("Cannot select DB"); 	
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>DSPI CALENDAR EVENTS</title>
<script>
function goLastMonth(month, year){
// If the month is January, decrement the year
if(month == 1){
--year;
month = 13;
}
document.location.href = '<?php echo $_SERVER['PHP_SELF'];?>?month='+(month-1)+'&year='+year;
}
//next function
function goNextMonth(month, year){
// If the month is December, increment the year
if(month == 12){
++year;
month = 0;
}
document.location.href = '<?php echo $_SERVER['PHP_SELF'];?>?month='+(month+1)+'&year='+year;
} 

function remChars(txtControl, txtCount, intMaxLength)
{
if(txtControl.value.length > intMaxLength)
txtControl.value = txtControl.value.substring(0, (intMaxLength-1));
else
txtCount.value = intMaxLength - txtControl.value.length;
}

function checkFilled() {
var filled = 0
var x = document.form1.calName.value;
//x = x.replace(/^\\s+/,""); // strip leading spaces
if (x.length > 0) {filled ++}

var y = document.form1.calDesc.value;
//y = y.replace(/^s+/,""); // strip leading spaces
if (y.length > 0) {filled ++}

if (filled == 2) {
document.getElementById("Submit").disabled = false;
}
else {document.getElementById("Submit").disabled = true} // in case a field is filled then erased

}

</script>

</head>

<body>
<?php
//$todaysDate = date("n/j/Y");
//echo $todaysDate;
// Get values from query string
$day = (isset($_GET["day"])) ? $_GET['day'] : "";
$month = (isset($_GET["month"])) ? $_GET['month'] : "";
$year = (isset($_GET["year"])) ? $_GET['year'] : "";
//comparaters for today's date
//$todaysDate = date("n/j/Y");
//$sel = (isset($_GET["sel"])) ? $_GET['sel'] : "";
//$what = (isset($_GET["what"])) ? $_GET['what'] : "";

//$day = (!isset($day)) ? $day = date("j") : $day = "";
if(empty($day)){ $day = date("j"); }

if(empty($month)){ $month = date("n"); }

if(empty($year)){ $year = date("Y"); } 
//set up vars for calendar etc
$currentTimeStamp = strtotime("$year-$month-$day");
$monthName = date("F", $currentTimeStamp);
$numDays = date("t", $currentTimeStamp);
$counter = 0;
//$numEventsThisMonth = 0;
//$hasEvent = false;
//$todaysEvents = ""; 
//run a selec statement to hi-light the days
function hiLightEvt($eMonth,$eDay,$eYear){
//$tDayName = date("l");
$todaysDate = date("n/j/Y");
$dateToCompare = $eMonth . '/' . $eDay . '/' . $eYear;
if($todaysDate == $dateToCompare){
//$aClass = '<span>' . $tDayName . '</span>';
$aClass='class="today"';
}else{
//$dateToCompare = $eMonth . '/' . $eDay . '/' . $eYear;
//echo $todaysDate;
//return;
$sql="select count(calDate) as eCount from tblcal where calDate = '" . $eMonth . '/' . $eDay . '/' . $eYear . "'";
//echo $sql;
//return;
$result = mysql_query($sql);
while($row= mysql_fetch_array($result)){
if($row['eCount'] >=1){
$aClass = 'class="event"';
}elseif($row['eCount'] ==0){
$aClass ='class="normal"';
}
}
}
return $aClass;
}
?>
<div id="Calendar_Event">
<table width="350" cellpadding="0" cellspacing="0">
<tr>
<td width="50" colspan="1">
<input type="button" value=" < " onClick="goLastMonth(<?php echo $month . ", " . $year; ?>);">
</td>
<td width="250" colspan="5">
<span class="title" style="color:#FFFFFF"><?php echo $monthName . " " . $year; ?></span><br>
</td>
<td width="50" colspan="1" align="right">
<input type="button" value=" > " onClick="goNextMonth(<?php echo $month . ", " . $year; ?>);">
</td>
</tr> 
<tr>
<th>M</td>
<th>T</td>
<th>W</td>
<th>T</td>
<th>F</td>
<th>S</td>
<th>S</td>
</tr>
<tr>
<?php
for($i = 1; $i < $numDays+1; $i++, $counter++){
$dateToCompare = $month . '/' . $i . '/' . $year;
$timeStamp = strtotime("$year-$month-$i");
//echo $timeStamp . '<br/>';
if($i == 1){
// Workout when the first day of the month is
$firstDay = date("N", $timeStamp);
for($j = 1; $j < $firstDay; $j++, $counter++){
echo "<td>&nbsp;</td>";
} 
}
if($counter % 7 == 0 ){
?>
</tr><tr>
<?php
}
?>
<!--right here--><td width="50" <?php echo 'hiLightEvt($month,$i,$year)';?>><a href="<?php echo $_SERVER['PHP_SELF'] . '?month='. $month . '&day=' . $i . '&year=' . $year;?>&v=1"><?php echo $i;?></a></td> 
<?php
}
?>
</table>
</div>
<div id="New_Event">
<?php
if(isset($_GET['v'])){
if(isset($_POST['Submit'])){
$sql="insert into tblcal(calName,calDesc,calDate) values('" . $_POST['calName'] ."','" . $_POST['calDesc'] . "','" . $_POST['calDate'] . "',now())";
mysql_query($sql);
}
$sql="select calName,calDesc from tblcal where calDate = '" . $month . '/' . $day . '/' . $year . "'";
//echo $sql;
//return;
$result = mysql_query($sql);
$numRows = mysql_num_rows($result);

if ($_SESSION['username']=="user1") 
{ 
$check1=mysql_query("SELECT * FROM tbluser WHERE Username='user1' AND Department='Staff' AND Permission='True'"); 
$check2 = mysql_fetch_array($check1); 
$username = $check2['Username']; 
$dep = $check2['Department']; 
$permiss = $check2['Permission']; 
//echo "$username<br/>$dep<br/>$permiss<br/><br/>"; 
if($username=="user1" && $dep=="Staff" && $permiss=="True"){ 
$_SESSION['isallowed'] = $check2['Permission']; 

?> 
<a href="<?php echo $_SERVER['PHP_SELF'];?>?month=<?php echo $_GET['month'] . '&day=' . $_GET['day'] . '&year=' . $_GET['year'];?>&v=1&f=true">Add Even</a><a href="<?php echo $_SERVER['PHP_SELF'];?>?month=<?php echo $_GET['month'] . '&day=' . $_GET['day'] . '&year=' . $_GET['year'];?>&v=1&f=true">t</a>
<?php  
} 
}
else 
{ echo "You cannot Add New Event"; } 

?>
</div>
<div id="Cal_Event">
<?php
if(isset($_GET['f'])){
include 'calform.php';
}
if($numRows == 0 ){
echo '';
}else{
//echo '<ul>';
echo '<h3>Event Listed</h3>';
while($row = mysql_fetch_array($result)){
?>

<h5><?php $row['calName'];?></h5>
<?php $row['calDesc'];?><br/>
<?php
}
}
}
?>
</div>
</body>
</html>

Thank you in advance