php/mysql update problem and switch execution question

[osward]

Hello there,

I am using phpnuke 8.0 to build my website, knowing little on php programing.

I am assembling a module for my member which is basically cut and paste existing code section of various module that I found it useful.

Here is the 1st problem I encounter:

I had a function to edit a event row form the database which is fine with me, than I pass on the code to a function that save(update) the data to the database.

function EventsEdit($eid) {
        global $bgcolor2, $bgcolor4, $prefix, $db, $multilingual, $admin_file, $AllowableHTML;
        include("header.php");
        GraphicAdmin();
        OpenTable();
        echo "<center><font class=\"title\"><b>"._EDITEVENT."</b></font></center>";
        CloseTable();
        echo "<br>";
        $eid = intval($eid);
        $result = $db->sql_query("select * from ".$prefix."_event_cat where eid=$eid");
        while ($row = $db->sql_fetchrow($result)) {
        $code = filter($row['code'], "nohtml");
        $code1 = $code;
        $name = filter($row['name'], "nohtml");
        $desc = $row['desc'];
        $date = filter($row['date'], "nohtml");
        $time = filter($row['time'], "nohtml");
        $place = filter($row['place'], "nohtml");
        $cost = filter($row['cost'], "nohtml");
        $host = filter($row['host'], "nohtml");
        $vacancy = intval($row['vacancy']);
        $active = intval($row['active']);
        OpenTable();
        echo "<br>";
        echo "<table border=\"0\" width='100%'>"
        ."<form action=\"".$admin_file.".php\" method=\"post\">"
        ."<tr><td>"._CODE.":</td><td>"
        ."<input type=\"text\" name=\"code\" value=\"$code\" size=\"30\" maxlength=\"60\"></td></tr>"
        ."<tr><td>"._EVENTNAME."</td><td>"
        ."<input type=\"text\" name=\"name\" value=\"$name\" size=\"30\" maxlength=\"60\"></td></tr>";
        echo "<tr><td>"._DESC.":</td>"
        ."<td><textarea wrap=\"virtual\" cols=\"70\" rows=\"15\" name=\"desc\">$desc</textarea><br>"._255CHARMAX."</td></tr>";
        echo "<tr><td>"._EVENTDATE.":</td>"
        ."<td><input type=\"text\" name=\"date\" value=\"$date\" size=\"30\" maxlength=\"60\"></td></tr>"
        ."<tr><td>"._EVENTTIME.":</td>"
        ."<td><input type=\"text\" name=\"time\" value=\"$time\" size=\"30\" maxlength=\"60\"></td></tr>"
        ."<tr><td>"._PLACE.":</td>"
        ."<td><input type=\"text\" name=\"place\" value=\"$place\" size=\"30\" maxlength=\"60\"></td></tr>";
        echo "<tr><td>"._COST.":</td>"
        ."<td><input type=\"text\" name=\"cost\" value=\"$cost\" size=\"30\" maxlength=\"60\"></td></tr>"
        ."<tr><td>"._HOST.":</td>"
        ."<td><input type=\"text\" name=\"host\" value=\"$host\" size=\"30\" maxlength=\"60\"></td></tr>"
        ."<tr><td>"._VACANCY.":</td>"
        ."<td><input type=\"text\" name=\"vacancy\" value=\"$vacancy\" size=\"30\" maxlength=\"100\"></td></tr>";
        if ($active == 1) {
            $sel1 = "checked";
            $sel2 = "";
        } elseif ($active == 0) {
            $sel1 = "";
            $sel2 = "checked";
        }
        echo "<tr><td>"._EVENTACTIVE."</td><td><input type=\"radio\" name=\"active\" value=\"1\" $sel1>"._YES." &nbsp;&nbsp;"
        ."<input type=\"radio\" name=\"active\" value=\"0\" $sel2>"._NO."</td></tr>";
        echo "<tr><td&nbsp;</td><td>&nbsp;</td></tr>";    
        echo "<tr><td>&nbsp;</td>"
        ."<td><input type=\"hidden\" name=\"eid\" value=\"$eid\">"
        ."<input type=\"hidden\" name=\"op\" value=\"EventsEditSave\">"
        ."<input type=\"submit\" value=\""._SAVECHANGES."\"></td></tr></table></form>";
        CloseTable();
        include("footer.php");
        }
    }        
 
    function EventsEditSave($eid, $code, $name, $desc, $date, $time, $place, $cost, $host, $vacancy, $active) {
        global $prefix, $db, $admin_file, $stop;
 
 
        $db->sql_query("UPDATE ".$prefix."_event_cat SET code='$code', name='$name', desc='$desc', date='$date', time='$time', place='$place', cost='$cost', host='$host', vacancy='$vacancy', active='$active' WHERE eid=$eid") or die(mysql_error());            
        Header("Location: ".$admin_file.".php?op=EventsAdmin");
    }
 
 
 

I had cut out the error check section of the function EventsEditSave for easy reading purpose.

The "or die(mysql_error());" is what I place after the update statement to identify the problems

and here is the mysql_error() returns
quote of error
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc='Test', date='2007-11-30', time='18:00-20:00', place='Beijing', cost='50', ' at line 1
unquote of error

I had spent hours to alter the Update statement but with no luck. Anyone may spot the error would be nice.

My 2nd problem is to use how to use the define switch

 
    switch($sortby) {
 
       case "A_date":
     $result = $db->sql_query("select eid, code, name, date, time, place, cost, host, vacancy, active from ".$prefix."_event_cat WHERE date > $now ORDER by date ASC LIMIT 0, 20");
       break;
 
       case "A_code":
      $result = $db->sql_query("select eid, code, name, date, time, place, cost, host, vacancy, active from ".$prefix."_event_cat WHERE date > $now ORDER by code ASC LIMIT 0, 20");
       break;
 
       case "D_date":
      $result = $db->sql_query("select eid, code, name, date, time, place, cost, host, vacancy, active from ".$prefix."_event_cat WHERE date > $now ORDER by date DESC LIMIT 0, 20");
       break;
 
       case "D_code":
      $result = $db->sql_query("select eid, code, name, date, time, place, cost, host, vacancy, active from ".$prefix."_event_cat WHERE date > $now ORDER by code DESC LIMIT 0, 20");
       break;
 
       default:
      $result = $db->sql_query("select eid, code, name, date, time, place, cost, host, vacancy, active from ".$prefix."_event_cat WHERE date > $now ORDER by date ASC LIMIT 0, 20");
 
}
 

orginally it was the default case in the switch to get the information from the database table and display it as a table, I want to add function by putting a link in the table heading to let user sort 2 coloums of the display table. I create the switch but I don't know how to call it, particular in the area that needs the default statement to reterieve the data. I am not sure I made myself clear or not

I enclosed the orginal section of code below

function EventsMain($user) {
        global $bgcolor2, $bgcolor1,$stop, $prefix, $db, $user;
 
        include("header.php");
            OpenTable();
            echo "<center><font class=\"title\"><b>"._EVENTCAT."</b></font></center>";
        echo "<br>";
        // Display Event List
 
        echo "<table width=\"100%\" border=\"1\">"
        ."<tr><td colspan=\"9\" bgcolor=\"$bgcolor2\"><center>"._EVENTLIST."</font></center></td></tr>"
        ."<tr><td align=\"center\" bgcolor=\"$bgcolor2\">"._EVENTDATE."</td>"
            ."<td align=\"center\" bgcolor=\"$bgcolor2\">"._CODE."</td>"
            ."<td align=\"center\" bgcolor=\"$bgcolor2\">"._EVENTNAME."</td>"
        ."<td align=\"center\" bgcolor=\"$bgcolor2\">"._HOST."</td>"
        ."<td align=\"center\" bgcolor=\"$bgcolor2\">"._EVENTTIME."</td>"
            ."<td align=\"center\" bgcolor=\"$bgcolor2\">"._PLACE."</td>"
        ."<td align=\"center\" bgcolor=\"$bgcolor2\">"._COST."</td>"
            ."<td align=\"center\" bgcolor=\"$bgcolor2\">"._AVAIL."</td>"
            ."<td align=\"center\" bgcolor=\"$bgcolor2\">"._STATUS."</td>"
              ."</tr>";
 
 
            $now = Date(Y-m-d);
/*** This is where I need to call the switch or replace with the switch***/
 
            $result = $db->sql_query("select eid, code, name, date, time, place, cost, host, vacancy, active from ".$prefix."_event_cat WHERE date > $now ORDER by date DESC LIMIT 0, 20");
 
/*********************************************/
 while ($row = $db->sql_fetchrow($result)) {
            $eid = intval($row['eid']);
            $code = filter($row['code'], "nohtml");
            $name = filter($row['name'], "nohtml");
            $date = filter($row['date'], "nohtml");
            $time = filter($row['time'], "nohtml");
            $place = filter($row['place'], "nohtml");
            $cost = filter($row['cost'], "nohtml");
            $host = filter($row['host'], "nohtml");
            $vacancy = intval($row['vacancy']);
            $active = intval($row['active']);
            $result1 = $db->sql_query("select eid, code, name from ".$prefix."_event_user where eid=$eid");
            $confirm = $db->sql_numrows($result1);
            $result1 = $db->sql_query("select eid, code, name from ".$prefix."_event_user_temp where eid=$eid");
            $temp = $db->sql_numrows($result1);
            $available = $vacancy-($confirm+$temp);
            echo "<tr>"
            ."<td align=\"center\">$date</td>"
            ."<td align=\"center\">$code</td>"
            ."<td align=\"center\">$name</td>"
            ."<td align=\"center\">$host</td>"
            ."<td align=\"center\">$time</td>"
            ."<td align=\"center\">$place</td>"
            ."<td align=\"center\">$cost</td>"
            ."<td align=\"center\">$available</td>";
            echo "<td align=\"center\"><font class=\"content\">&nbsp;<a href=\"".$admin_file.".php?op=EventsEdit&amp;eid=$eid\"><img src=\"images/edit.gif\" alt=\""._EDITEVENT."\" title=\""._EDITEVENT."\" border=\"0\" width=\"17\" height=\"17\"></a>";
            //echo "<a href=\"".$admin_file.".php?op=EventsDelete&amp;eid=$eid\"><img src=\"images/delete.gif\" alt=\""._DELETEEVENT."\" title=\""._DELETEEVENT."\" border=\"0\" width=\"17\" height=\"17\"></a>  ";
 
            $event_act = $active;
            if ($active == 1) {
                $active = "<img src=\"images/active.gif\" alt=\""._EVENTACTIVE."\" title=\""._EVENTACTIVE."\" border=\"0\" width=\"16\" height=\"16\">";
                $change = "<img src=\"images/inactive.gif\" alt=\""._EVENTNOTACTIVE."\" title=\""._EVENTNOTACTIVE."\" border=\"0\" width=\"16\" height=\"16\">";
            } elseif ($active == 0) {
                $active = "<img src=\"images/inactive.gif\" alt=\""._EVENTNOTACTIVE."\" title=\""._EVENTNOTACTIVE."\" border=\"0\" width=\"16\" height=\"16\">";
                $change = "<img src=\"images/active.gif\" alt=\""._EVENTACTIVE."\" title=\""._EVENTACTIVE."\" border=\"0\" width=\"16\" height=\"16\">";
                }
            echo "<td align=\"center\"><font class=\"content\">&nbsp;$active</a> </td></tr>";
                    }
            echo "</table>"
            ."<br><br>";
            CloseTable();
            echo "<br><br>";
            include("footer.php");
            }
 

I hope I made myself clear on why I want to do and please help

Thanks in advance

[pbmods]

Heya, osward. Welcome to TSDN!

You're getting the first error because 'desc' is a MySQL keyword.

Try enclosing your column and field names in backticks (`).

E.g., `desc` instead of desc.

Using backticks is a good habit to get into, as it makes your code easier to read, and consequently easier to decipher three months later when you have to come back to something that you don't remember writing.

[osward]

Thanks for the tips, as I mentioned I am not a "programer" but try to assemby blocks of code to make things happens.

I will remember to avoid using description or desc for any table field name but content instead. I might change the field name in the database table as well to avoid any problem.

I search over the web and found some handy javascript for sorting coloum table that might solved my 2nd problem. However, I am still keen to learn how to call the switch. I know I call the switch in sending form like

<input type=\"hidden\" name=\"op\" value=\"EventsAdd\">

where this is common amoung the code I read within phpnuke's module but could not figure out how am I going to use the switch in my 2nd problem. Anyone could tip me or point me to a reference

Thanks