By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 1,570 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

php/mysql update problem and switch execution question

osward
P: 38
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.

Expand|Select|Wrap|Line Numbers
  1. function EventsEdit($eid) {
  2.         global $bgcolor2, $bgcolor4, $prefix, $db, $multilingual, $admin_file, $AllowableHTML;
  3.         include("header.php");
  4.         GraphicAdmin();
  5.         OpenTable();
  6.         echo "<center><font class=\"title\"><b>"._EDITEVENT."</b></font></center>";
  7.         CloseTable();
  8.         echo "<br>";
  9.         $eid = intval($eid);
  10.         $result = $db->sql_query("select * from ".$prefix."_event_cat where eid=$eid");
  11.         while ($row = $db->sql_fetchrow($result)) {
  12.         $code = filter($row['code'], "nohtml");
  13.         $code1 = $code;
  14.         $name = filter($row['name'], "nohtml");
  15.         $desc = $row['desc'];
  16.         $date = filter($row['date'], "nohtml");
  17.         $time = filter($row['time'], "nohtml");
  18.         $place = filter($row['place'], "nohtml");
  19.         $cost = filter($row['cost'], "nohtml");
  20.         $host = filter($row['host'], "nohtml");
  21.         $vacancy = intval($row['vacancy']);
  22.         $active = intval($row['active']);
  23.         OpenTable();
  24.         echo "<br>";
  25.         echo "<table border=\"0\" width='100%'>"
  26.         ."<form action=\"".$admin_file.".php\" method=\"post\">"
  27.         ."<tr><td>"._CODE.":</td><td>"
  28.         ."<input type=\"text\" name=\"code\" value=\"$code\" size=\"30\" maxlength=\"60\"></td></tr>"
  29.         ."<tr><td>"._EVENTNAME."</td><td>"
  30.         ."<input type=\"text\" name=\"name\" value=\"$name\" size=\"30\" maxlength=\"60\"></td></tr>";
  31.         echo "<tr><td>"._DESC.":</td>"
  32.         ."<td><textarea wrap=\"virtual\" cols=\"70\" rows=\"15\" name=\"desc\">$desc</textarea><br>"._255CHARMAX."</td></tr>";
  33.         echo "<tr><td>"._EVENTDATE.":</td>"
  34.         ."<td><input type=\"text\" name=\"date\" value=\"$date\" size=\"30\" maxlength=\"60\"></td></tr>"
  35.         ."<tr><td>"._EVENTTIME.":</td>"
  36.         ."<td><input type=\"text\" name=\"time\" value=\"$time\" size=\"30\" maxlength=\"60\"></td></tr>"
  37.         ."<tr><td>"._PLACE.":</td>"
  38.         ."<td><input type=\"text\" name=\"place\" value=\"$place\" size=\"30\" maxlength=\"60\"></td></tr>";
  39.         echo "<tr><td>"._COST.":</td>"
  40.         ."<td><input type=\"text\" name=\"cost\" value=\"$cost\" size=\"30\" maxlength=\"60\"></td></tr>"
  41.         ."<tr><td>"._HOST.":</td>"
  42.         ."<td><input type=\"text\" name=\"host\" value=\"$host\" size=\"30\" maxlength=\"60\"></td></tr>"
  43.         ."<tr><td>"._VACANCY.":</td>"
  44.         ."<td><input type=\"text\" name=\"vacancy\" value=\"$vacancy\" size=\"30\" maxlength=\"100\"></td></tr>";
  45.         if ($active == 1) {
  46.             $sel1 = "checked";
  47.             $sel2 = "";
  48.         } elseif ($active == 0) {
  49.             $sel1 = "";
  50.             $sel2 = "checked";
  51.         }
  52.         echo "<tr><td>"._EVENTACTIVE."</td><td><input type=\"radio\" name=\"active\" value=\"1\" $sel1>"._YES." &nbsp;&nbsp;"
  53.         ."<input type=\"radio\" name=\"active\" value=\"0\" $sel2>"._NO."</td></tr>";
  54.         echo "<tr><td&nbsp;</td><td>&nbsp;</td></tr>";    
  55.         echo "<tr><td>&nbsp;</td>"
  56.         ."<td><input type=\"hidden\" name=\"eid\" value=\"$eid\">"
  57.         ."<input type=\"hidden\" name=\"op\" value=\"EventsEditSave\">"
  58.         ."<input type=\"submit\" value=\""._SAVECHANGES."\"></td></tr></table></form>";
  59.         CloseTable();
  60.         include("footer.php");
  61.         }
  62.     }        
  63.  
  64.     function EventsEditSave($eid, $code, $name, $desc, $date, $time, $place, $cost, $host, $vacancy, $active) {
  65.         global $prefix, $db, $admin_file, $stop;
  66.  
  67.  
  68.         $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());            
  69.         Header("Location: ".$admin_file.".php?op=EventsAdmin");
  70.     }
  71.  
  72.  
  73.  
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

Expand|Select|Wrap|Line Numbers
  1.  
  2.     switch($sortby) {
  3.  
  4.        case "A_date":
  5.      $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");
  6.        break;
  7.  
  8.        case "A_code":
  9.       $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");
  10.        break;
  11.  
  12.        case "D_date":
  13.       $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");
  14.        break;
  15.  
  16.        case "D_code":
  17.       $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");
  18.        break;
  19.  
  20.        default:
  21.       $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");
  22.  
  23. }
  24.  
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
Expand|Select|Wrap|Line Numbers
  1. function EventsMain($user) {
  2.         global $bgcolor2, $bgcolor1,$stop, $prefix, $db, $user;
  3.  
  4.         include("header.php");
  5.             OpenTable();
  6.             echo "<center><font class=\"title\"><b>"._EVENTCAT."</b></font></center>";
  7.         echo "<br>";
  8.         // Display Event List
  9.  
  10.         echo "<table width=\"100%\" border=\"1\">"
  11.         ."<tr><td colspan=\"9\" bgcolor=\"$bgcolor2\"><center>"._EVENTLIST."</font></center></td></tr>"
  12.         ."<tr><td align=\"center\" bgcolor=\"$bgcolor2\">"._EVENTDATE."</td>"
  13.             ."<td align=\"center\" bgcolor=\"$bgcolor2\">"._CODE."</td>"
  14.             ."<td align=\"center\" bgcolor=\"$bgcolor2\">"._EVENTNAME."</td>"
  15.         ."<td align=\"center\" bgcolor=\"$bgcolor2\">"._HOST."</td>"
  16.         ."<td align=\"center\" bgcolor=\"$bgcolor2\">"._EVENTTIME."</td>"
  17.             ."<td align=\"center\" bgcolor=\"$bgcolor2\">"._PLACE."</td>"
  18.         ."<td align=\"center\" bgcolor=\"$bgcolor2\">"._COST."</td>"
  19.             ."<td align=\"center\" bgcolor=\"$bgcolor2\">"._AVAIL."</td>"
  20.             ."<td align=\"center\" bgcolor=\"$bgcolor2\">"._STATUS."</td>"
  21.               ."</tr>";
  22.  
  23.  
  24.             $now = Date(Y-m-d);
  25. /*** This is where I need to call the switch or replace with the switch***/
  26.  
  27.             $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");
  28.  
  29. /*********************************************/
  30.  while ($row = $db->sql_fetchrow($result)) {
  31.             $eid = intval($row['eid']);
  32.             $code = filter($row['code'], "nohtml");
  33.             $name = filter($row['name'], "nohtml");
  34.             $date = filter($row['date'], "nohtml");
  35.             $time = filter($row['time'], "nohtml");
  36.             $place = filter($row['place'], "nohtml");
  37.             $cost = filter($row['cost'], "nohtml");
  38.             $host = filter($row['host'], "nohtml");
  39.             $vacancy = intval($row['vacancy']);
  40.             $active = intval($row['active']);
  41.             $result1 = $db->sql_query("select eid, code, name from ".$prefix."_event_user where eid=$eid");
  42.             $confirm = $db->sql_numrows($result1);
  43.             $result1 = $db->sql_query("select eid, code, name from ".$prefix."_event_user_temp where eid=$eid");
  44.             $temp = $db->sql_numrows($result1);
  45.             $available = $vacancy-($confirm+$temp);
  46.             echo "<tr>"
  47.             ."<td align=\"center\">$date</td>"
  48.             ."<td align=\"center\">$code</td>"
  49.             ."<td align=\"center\">$name</td>"
  50.             ."<td align=\"center\">$host</td>"
  51.             ."<td align=\"center\">$time</td>"
  52.             ."<td align=\"center\">$place</td>"
  53.             ."<td align=\"center\">$cost</td>"
  54.             ."<td align=\"center\">$available</td>";
  55.             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>";
  56.             //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>  ";
  57.  
  58.             $event_act = $active;
  59.             if ($active == 1) {
  60.                 $active = "<img src=\"images/active.gif\" alt=\""._EVENTACTIVE."\" title=\""._EVENTACTIVE."\" border=\"0\" width=\"16\" height=\"16\">";
  61.                 $change = "<img src=\"images/inactive.gif\" alt=\""._EVENTNOTACTIVE."\" title=\""._EVENTNOTACTIVE."\" border=\"0\" width=\"16\" height=\"16\">";
  62.             } elseif ($active == 0) {
  63.                 $active = "<img src=\"images/inactive.gif\" alt=\""._EVENTNOTACTIVE."\" title=\""._EVENTNOTACTIVE."\" border=\"0\" width=\"16\" height=\"16\">";
  64.                 $change = "<img src=\"images/active.gif\" alt=\""._EVENTACTIVE."\" title=\""._EVENTACTIVE."\" border=\"0\" width=\"16\" height=\"16\">";
  65.                 }
  66.             echo "<td align=\"center\"><font class=\"content\">&nbsp;$active</a> </td></tr>";
  67.                     }
  68.             echo "</table>"
  69.             ."<br><br>";
  70.             CloseTable();
  71.             echo "<br><br>";
  72.             include("footer.php");
  73.             }
  74.  
I hope I made myself clear on why I want to do and please help

Thanks in advance
Oct 20 '07 #1
Share this Question
Share on Google+
2 Replies


pbmods
Expert 5K+
P: 5,821
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.
Oct 20 '07 #2

osward
P: 38
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
Oct 20 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.