Unknown column '1' in 'field list'

jeff5656 · December 1, 2008

I get this error in commit.php: Unknown column '1' in 'field list'

Here is commit.php:

<?php 
include "../connectdb.php";
$this_month=$_POST['which_month'];

$query = "UPDATE staffsched SET ";
$set = array();
foreach($_POST as $field => $value){
    $field = mysql_real_escape_string($field);
    $value = mysql_real_escape_string($value);
    $set[] = "`{$field}` = '{$value}'";

}


$query .= implode(", ",$set) . " WHERE `which_month` = '".$this_month."'";
mysql_query($query) or die(mysql_error());

header("Location: displayboth.php");
?>

Here is the form that points to commit:

<?php

// Report all PHP errors
error_reporting(E_ALL);
// Same as error_reporting(E_ALL);
ini_set('error_reporting', E_ALL);

include ("../connectdb.php");

$curr_month = $_POST['which_month'];

$consultsq1 = "SELECT * FROM `staffsched` WHERE `which_month`='".$curr_month."'";
$result = mysql_query ($consultsq1) or die ("Invalid query: " . mysql_error ());
$row = mysql_fetch_array ($result);

?>
<table border="1"  border="1" cellpadding="0" width="100%">
<th width='2%'>Date</th><th>ICU Fellow</th><th>ICU Staff</th><th>F2 Staff</th><th>Intervention</th><th>West Bloomfield</th><th>Transplant</th><th>Pulm HTN</th><th>Warren Days</th><th>Warren Nights (5p-8a)</th>

<form action='commit.php' method='post'>
<tr><td ><table >

<?php
$varArray = array("which_date");
$varArraySize = count($varArray);

for ($i = 0; $i < $varArraySize;$i++) {

   for ($f = 1; $f < 32; $f++) {
     
 echo "<td style='border-bottom-style: solid;'> ";
 echo "<input name='".$row[$varArray[$i] . $f]."' type='text' size='13' value= '".$row[$varArray[$i] . $f]."'; />";
 echo "</td>";
 echo "<tr>";
   }
   

}
echo "</table></td>";

echo "<td valign='top' width='20' align='center'><table>";
$varArray = array("icufellow");
$varArraySize = count($varArray);

for ($i = 0; $i < $varArraySize;$i++) {
   
   for ($f = 1; $f < 32; $f++) {
     echo "<td style='border-bottom-style: solid;'> ";
 echo "<input name='".$row[$varArray[$i] . $f]."' type='text' size='13' value= '".$row[$varArray[$i] . $f]."'; />";
 echo "</td>";
 echo "<tr>";
   }

}
echo "</table></td>";

echo "<td valign='top' align='center' width ='20'><table>";
$varArray = array("icustaff");
$varArraySize = count($varArray);

for ($i = 0; $i < $varArraySize;$i++) {

  for ($f = 1; $f < 32; $f++) {
    echo "<td style='border-bottom-style: solid;'> ";
 echo "<input name='".$row[$varArray[$i] . $f]."' type='text' size='13' value= '".$row[$varArray[$i] . $f]."'; />";
 echo "</td>";
 echo "<tr>";
   }
   
}
echo "</table></td>";

echo "<td valign='top' align='center' width ='20'><table>";
$varArray = array("f2staff");
$varArraySize = count($varArray);

for ($i = 0; $i < $varArraySize;$i++) {
   
   for ($f = 1; $f < 32; $f++) {
     echo "<td style='border-bottom-style: solid;'> ";
 echo "<input name='".$row[$varArray[$i] . $f]."' type='text' size='13' value= '".$row[$varArray[$i] . $f]."'; />";
 echo "</td>";
 echo "<tr>";
   }
   
}
echo "</table></td>";

echo "<td valign='top' align='center' width ='20'><table>";
$varArray = array("intervent");
$varArraySize = count($varArray);

for ($i = 0; $i < $varArraySize;$i++) {

   
   for ($f = 1; $f < 32; $f++) {
    echo "<td style='border-bottom-style: solid;'> ";
 echo "<input name='".$row[$varArray[$i] . $f]."' type='text' size='13' value= '".$row[$varArray[$i] . $f]."'; />";
 echo "</td>";
 echo "<tr>";
   }

}
echo "</table></td>";

echo "<td valign='top' align='center' width='20'><table>";
$varArray = array("wb");
$varArraySize = count($varArray);

for ($i = 0; $i < $varArraySize;$i++) {

   
   for ($f = 1; $f < 32; $f++) {
     echo "<td style='border-bottom-style: solid;'> ";
 echo "<input name='".$row[$varArray[$i] . $f]."' type='text' size='13' value= '".$row[$varArray[$i] . $f]."'; />";
 echo "</td>";
 echo "<tr>";
   }
   

}
echo "</table></td>";


echo "<td valign='top' align='center' width='20'><table>";
$varArray = array("tx");
$varArraySize = count($varArray);

for ($i = 0; $i < $varArraySize;$i++) {

   
   for ($f = 1; $f < 32; $f++) {
     echo "<td style='border-bottom-style: solid;'> ";
 echo "<input name='".$row[$varArray[$i] . $f]."' type='text' size='13' value= '".$row[$varArray[$i] . $f]."'; />";
 echo "</td>";
 echo "<tr>";
   }
   

}
echo "</table></td>";

echo "<td valign='top' align='center' width='15'><table>";
$varArray = array("phtn");
$varArraySize = count($varArray);

for ($i = 0; $i < $varArraySize;$i++) {

   
   for ($f = 1; $f < 32; $f++) {
     echo "<td style='border-bottom-style: solid;'> ";
 echo "<input name='".$row[$varArray[$i] . $f]."' type='text' size='13' value= '".$row[$varArray[$i] . $f]."'; />";
 echo "</td>";
 echo "<tr>";
   }
   

}
echo "</table></td>";

echo "<td valign='top' align='center' width='20'><table>";
$varArray = array("warren");
$varArraySize = count($varArray);

for ($i = 0; $i < $varArraySize;$i++) {

   
   for ($f = 1; $f < 32; $f++) {
     echo "<td style='border-bottom-style: solid;'> ";
 echo "<input name='".$row[$varArray[$i] . $f]."' type='text' size='13' value= '".$row[$varArray[$i] . $f]."'; />";
 echo "</td>";
 echo "<tr>";
   }
   

}
echo "</table></td>";

echo "<td valign='top' align='center' width='20'><table>";
$varArray = array("wn");
$varArraySize = count($varArray);

for ($i = 0; $i < $varArraySize;$i++) {

   
   for ($f = 1; $f < 32; $f++) {
    echo "<td style='border-bottom-style: solid;'> ";
 echo "<input name='".$row[$varArray[$i] . $f]."' type='text' size='13' value= '".$row[$varArray[$i] . $f]."'; />";
 echo "</td>";
 echo "<tr>";
   }
   

}
echo "</table></td>";


echo "</table>";
$edit_date = date("Y-m-d H:i:s"); ?>
    <input type="hidden" name ="edit_date" value="<?php echo $edit_date; ?>"/>
        <input type="hidden" name ="which_month" value="<?php echo $curr_month; ?>"/>
    <input type="submit" value="Update"/></td>
</form>

My field names are things like icufellow1, icufellow2 etc up to 31. This is why I set up the form with an array, because there are so many variables that only differ by the suffix number. The form populates the values CORRECTLY, but I guess those values don't get passed correctly to commit.php?

cooldude832 · December 1, 2008

echo out the query statement before running it and see what its showing

jeff5656 · December 1, 2008

A lot of the fields in the form are blank. Here is the query echoed:

UPDATE staffsched SET `1` = '1', `2` = '2', `3` = '3', `4` = '4', `5` = '5', `6` = '6', `7` = '7', `8` = '8', `9` = '9', `10` = '10', `11` = '11', `12` = '12', `13` = '13', `14` = '14', `15` = '15', `16` = '16', `17` = '17', `18` = '18', `19` = '19', `20` = '20', `21` = '21', `22` = '22', `23` = '23', `24` = '24', `25` = '25', `26` = '26', `27` = '27', `28` = '28', `29` = '29', `30` = '30', `31` = '31', `bobby` = 'bobby', `Stagner` = 'Stagner', `edit_date` = '2008-11-30 22:28:27', `which_month` = 'December' WHERE `which_month` = 'December'Unknown column '1' in 'field lis

See screen shot for what the form looks like before I submit it

[attachment deleted by admin]

dropfaith · December 1, 2008

show the table structure of your database the error in the echoed query states that column 1 doesnt exist

Unknown column '1' in 'field lis

also i dont think you need back ticks or ' around field names

Name = '$Name' works fine for me on all my edit forms i use

jeff5656 · December 1, 2008

-- Table structure for table `staffsched`
-- 

CREATE TABLE `staffsched` (
  `which_month` varchar(20) NOT NULL default '',
  `which_date1` int(254) NOT NULL default '0',
  `which_date2` int(254) NOT NULL default '0',
  `which_date3` int(254) NOT NULL default '0',
  `which_date4` int(254) NOT NULL default '0',
  `which_date5` int(254) NOT NULL default '0',
  `which_date6` int(254) NOT NULL default '0',
  `which_date7` int(254) NOT NULL default '0',
  `which_date8` int(254) NOT NULL default '0',
  `which_date9` int(254) NOT NULL default '0',
  `which_date10` int(254) NOT NULL default '0',
  `which_date11` int(254) NOT NULL default '0',
  `which_date12` int(254) NOT NULL default '0',
  `which_date13` int(254) NOT NULL default '0',
  `which_date14` int(254) NOT NULL default '0',
  `which_date15` int(254) NOT NULL default '0',
  `which_date16` int(254) NOT NULL default '0',
  `which_date17` int(254) NOT NULL default '0',
  `which_date18` int(254) NOT NULL default '0',
  `which_date19` int(254) NOT NULL default '0',
  `which_date20` int(254) NOT NULL default '0',
  `which_date21` int(254) NOT NULL default '0',
  `which_date22` int(254) NOT NULL default '0',
  `which_date23` int(254) NOT NULL default '0',
  `which_date24` int(254) NOT NULL default '0',
  `which_date25` int(254) NOT NULL default '0',
  `which_date26` int(254) NOT NULL default '0',
  `which_date27` int(254) NOT NULL default '0',
  `which_date28` int(254) NOT NULL default '0',
  `which_date29` int(254) NOT NULL default '0',
  `which_date30` int(254) NOT NULL default '0',
  `which_date31` int(254) NOT NULL default '0',
  `icufellow1` varchar(20) NOT NULL default '',
  `icufellow2` varchar(20) NOT NULL default '',
  `icufellow3` varchar(20) NOT NULL default '',
  `icufellow4` varchar(20) NOT NULL default '',
  `icufellow5` varchar(20) NOT NULL default '',
  `icufellow6` varchar(20) NOT NULL default '',
  `icufellow7` varchar(20) NOT NULL default '',
  `icufellow8` varchar(20) NOT NULL default '',
  `icufellow9` varchar(20) NOT NULL default '',
  `icufellow10` varchar(20) NOT NULL default '',
  `icufellow11` varchar(20) NOT NULL default '',
  `icufellow12` varchar(20) NOT NULL default '',
  `icufellow13` varchar(20) NOT NULL default '',
  `icufellow14` varchar(20) NOT NULL default '',
  `icufellow15` varchar(20) NOT NULL default '',
  `icufellow16` varchar(20) NOT NULL default '',
  `icufellow17` varchar(20) NOT NULL default '',
  `icufellow18` varchar(20) NOT NULL default '',
  `icufellow19` varchar(20) NOT NULL default '',
  `icufellow20` varchar(20) NOT NULL default '',
  `icufellow21` varchar(20) NOT NULL default '',
  `icufellow22` varchar(20) NOT NULL default '',
  `icufellow23` varchar(20) NOT NULL default '',
  `icufellow24` varchar(20) NOT NULL default '',
  `icufellow25` varchar(20) NOT NULL default '',
  `icufellow26` varchar(20) NOT NULL default '',
  `icufellow27` varchar(20) NOT NULL default '',
  `icufellow28` varchar(20) NOT NULL default '',
  `icufellow29` varchar(20) NOT NULL default '',
  `icufellow30` varchar(20) NOT NULL default '',
  `icufellow31` varchar(20) NOT NULL default '',
  `icustaff1` varchar(20) NOT NULL default '',
  `icustaff2` varchar(20) NOT NULL default '',
  `icustaff3` varchar(20) NOT NULL default '',
  `icustaff4` varchar(20) NOT NULL default '',
  `icustaff5` varchar(20) NOT NULL default '',
  `icustaff6` varchar(20) NOT NULL default '',
  `icustaff7` varchar(20) NOT NULL default '',
  `icustaff8` varchar(20) NOT NULL default '',
  `icustaff9` varchar(20) NOT NULL default '',
  `icustaff10` varchar(20) NOT NULL default '',
  `icustaff11` varchar(20) NOT NULL default '',
  `icustaff12` varchar(20) NOT NULL default '',
  `icustaff13` varchar(20) NOT NULL default '',
  `icustaff14` varchar(20) NOT NULL default '',
  `icustaff15` varchar(20) NOT NULL default '',
  `icustaff16` varchar(20) NOT NULL default '',
  `icustaff17` varchar(20) NOT NULL default '',
  `icustaff18` varchar(20) NOT NULL default '',
  `icustaff19` varchar(20) NOT NULL default '',
  `icustaff20` varchar(20) NOT NULL default '',
  `icustaff21` varchar(20) NOT NULL default '',
  `icustaff22` varchar(20) NOT NULL default '',
  `icustaff23` varchar(20) NOT NULL default '',
  `icustaff24` varchar(20) NOT NULL default '',
  `icustaff25` varchar(20) NOT NULL default '',
  `icustaff26` varchar(20) NOT NULL default '',
  `icustaff27` varchar(20) NOT NULL default '',
  `icustaff28` varchar(20) NOT NULL default '',
  `icustaff29` varchar(20) NOT NULL default '',
  `icustaff30` varchar(20) NOT NULL default '',
  `icustaff31` varchar(20) NOT NULL default '',
  `f2staff1` varchar(20) NOT NULL default '',
  `f2staff2` varchar(20) NOT NULL default '',
  `f2staff3` varchar(20) NOT NULL default '',
  `f2staff4` varchar(20) NOT NULL default '',
  `f2staff5` varchar(20) NOT NULL default '',
  `f2staff6` varchar(20) NOT NULL default '',
  `f2staff7` varchar(20) NOT NULL default '',
  `f2staff8` varchar(20) NOT NULL default '',
  `f2staff9` varchar(20) NOT NULL default '',
  `f2staff10` varchar(20) NOT NULL default '',
  `f2staff11` varchar(20) NOT NULL default '',
  `f2staff12` varchar(20) NOT NULL default '',
  `f2staff13` varchar(20) NOT NULL default '',
  `f2staff14` varchar(20) NOT NULL default '',
  `f2staff15` varchar(20) NOT NULL default '',
  `f2staff16` varchar(20) NOT NULL default '',
  `f2staff17` varchar(20) NOT NULL default '',
  `f2staff18` varchar(20) NOT NULL default '',
  `f2staff19` varchar(20) NOT NULL default '',
  `f2staff20` varchar(20) NOT NULL default '',
  `f2staff21` varchar(20) NOT NULL default '',
  `f2staff22` varchar(20) NOT NULL default '',
  `f2staff23` varchar(20) NOT NULL default '',
  `f2staff24` varchar(20) NOT NULL default '',
  `f2staff25` varchar(20) NOT NULL default '',
  `f2staff26` varchar(20) NOT NULL default '',
  `f2staff27` varchar(20) NOT NULL default '',
  `f2staff28` varchar(20) NOT NULL default '',
  `f2staff29` varchar(20) NOT NULL default '',
  `f2staff30` varchar(20) NOT NULL default '',
  `f2staff31` varchar(20) NOT NULL default '',
  `intervent1` varchar(20) NOT NULL default '',
  `intervent2` varchar(20) NOT NULL default '',
  `intervent3` varchar(20) NOT NULL default '',
  `intervent4` varchar(20) NOT NULL default '',
  `intervent5` varchar(20) NOT NULL default '',
  `intervent6` varchar(20) NOT NULL default '',
  `intervent7` varchar(20) NOT NULL default '',
  `intervent8` varchar(20) NOT NULL default '',
  `intervent9` varchar(20) NOT NULL default '',
  `intervent10` varchar(20) NOT NULL default '',
  `intervent11` varchar(20) NOT NULL default '',
  `intervent12` varchar(20) NOT NULL default '',
  `intervent13` varchar(20) NOT NULL default '',
  `intervent14` varchar(20) NOT NULL default '',
  `intervent15` varchar(20) NOT NULL default '',
  `intervent16` varchar(20) NOT NULL default '',
  `intervent17` varchar(20) NOT NULL default '',
  `intervent18` varchar(20) NOT NULL default '',
  `intervent19` varchar(20) NOT NULL default '',
  `intervent20` varchar(20) NOT NULL default '',
  `intervent21` varchar(20) NOT NULL default '',
  `intervent22` varchar(20) NOT NULL default '',
  `intervent23` varchar(20) NOT NULL default '',
  `intervent24` varchar(20) NOT NULL default '',
  `intervent25` varchar(20) NOT NULL default '',
  `intervent26` varchar(20) NOT NULL default '',
  `intervent27` varchar(20) NOT NULL default '',
  `intervent28` varchar(20) NOT NULL default '',
  `intervent29` varchar(20) NOT NULL default '',
  `intervent30` varchar(20) NOT NULL default '',
  `intervent31` varchar(20) NOT NULL default '',
  `wb1` varchar(20) NOT NULL default '',
  `wb2` varchar(20) NOT NULL default '',
  `wb3` varchar(20) NOT NULL default '',
  `wb4` varchar(20) NOT NULL default '',
  `wb5` varchar(20) NOT NULL default '',
  `wb6` varchar(20) NOT NULL default '',
  `wb7` varchar(20) NOT NULL default '',
  `wb8` varchar(20) NOT NULL default '',
  `wb9` varchar(20) NOT NULL default '',
  `wb10` varchar(20) NOT NULL default '',
  `wb11` varchar(20) NOT NULL default '',
  `wb12` varchar(20) NOT NULL default '',
  `wb13` varchar(20) NOT NULL default '',
  `wb14` varchar(20) NOT NULL default '',
  `wb15` varchar(20) NOT NULL default '',
  `wb16` varchar(20) NOT NULL default '',
  `wb17` varchar(20) NOT NULL default '',
  `wb18` varchar(20) NOT NULL default '',
  `wb19` varchar(20) NOT NULL default '',
  `wb20` varchar(20) NOT NULL default '',
  `wb21` varchar(20) NOT NULL default '',
  `wb22` varchar(20) NOT NULL default '',
  `wb23` varchar(20) NOT NULL default '',
  `wb24` varchar(20) NOT NULL default '',
  `wb25` varchar(20) NOT NULL default '',
  `wb26` varchar(20) NOT NULL default '',
  `wb27` varchar(20) NOT NULL default '',
  `wb28` varchar(20) NOT NULL default '',
  `wb29` varchar(20) NOT NULL default '',
  `wb30` varchar(20) NOT NULL default '',
  `wb31` varchar(20) NOT NULL default '',
  `tx1` varchar(20) NOT NULL default '',
  `tx2` varchar(20) NOT NULL default '',
  `tx3` varchar(20) NOT NULL default '',
  `tx4` varchar(20) NOT NULL default '',
  `tx5` varchar(20) NOT NULL default '',
  `tx6` varchar(20) NOT NULL default '',
  `tx7` varchar(20) NOT NULL default '',
  `tx8` varchar(20) NOT NULL default '',
  `tx9` varchar(20) NOT NULL default '',
  `tx10` varchar(20) NOT NULL default '',
  `tx11` varchar(20) NOT NULL default '',
  `tx12` varchar(20) NOT NULL default '',
  `tx13` varchar(20) NOT NULL default '',
  `tx14` varchar(20) NOT NULL default '',
  `tx15` varchar(20) NOT NULL default '',
  `tx16` varchar(20) NOT NULL default '',
  `tx17` varchar(20) NOT NULL default '',
  `tx18` varchar(20) NOT NULL default '',
  `tx19` varchar(20) NOT NULL default '',
  `tx20` varchar(20) NOT NULL default '',
  `tx21` varchar(20) NOT NULL default '',
  `tx22` varchar(20) NOT NULL default '',
  `tx23` varchar(20) NOT NULL default '',
  `tx24` varchar(20) NOT NULL default '',
  `tx25` varchar(20) NOT NULL default '',
  `tx26` varchar(20) NOT NULL default '',
  `tx27` varchar(20) NOT NULL default '',
  `tx28` varchar(20) NOT NULL default '',
  `tx29` varchar(20) NOT NULL default '',
  `tx30` varchar(20) NOT NULL default '',
  `tx31` varchar(20) NOT NULL default '',
  `phtn1` varchar(20) NOT NULL default '',
  `phtn2` varchar(20) NOT NULL default '',
  `phtn3` varchar(20) NOT NULL default '',
  `phtn4` varchar(20) NOT NULL default '',
  `phtn5` varchar(20) NOT NULL default '',
  `phtn6` varchar(20) NOT NULL default '',
  `phtn7` varchar(20) NOT NULL default '',
  `phtn8` varchar(20) NOT NULL default '',
  `phtn9` varchar(20) NOT NULL default '',
  `phtn10` varchar(20) NOT NULL default '',
  `phtn11` varchar(20) NOT NULL default '',
  `phtn12` varchar(20) NOT NULL default '',
  `phtn13` varchar(20) NOT NULL default '',
  `phtn14` varchar(20) NOT NULL default '',
  `phtn15` varchar(20) NOT NULL default '',
  `phtn16` varchar(20) NOT NULL default '',
  `phtn17` varchar(20) NOT NULL default '',
  `phtn18` varchar(20) NOT NULL default '',
  `phtn19` varchar(20) NOT NULL default '',
  `phtn20` varchar(20) NOT NULL default '',
  `phtn21` varchar(20) NOT NULL default '',
  `phtn22` varchar(20) NOT NULL default '',
  `phtn23` varchar(20) NOT NULL default '',
  `phtn24` varchar(20) NOT NULL default '',
  `phtn25` varchar(20) NOT NULL default '',
  `phtn26` varchar(20) NOT NULL default '',
  `phtn27` varchar(20) NOT NULL default '',
  `phtn28` varchar(20) NOT NULL default '',
  `phtn29` varchar(20) NOT NULL default '',
  `phtn30` varchar(20) NOT NULL default '',
  `phtn31` varchar(20) NOT NULL default '',
  `warren1` varchar(20) NOT NULL default '',
  `warren2` varchar(20) NOT NULL default '',
  `warren3` varchar(20) NOT NULL default '',
  `warren4` varchar(20) NOT NULL default '',
  `warren5` varchar(20) NOT NULL default '',
  `warren6` varchar(20) NOT NULL default '',
  `warren7` varchar(20) NOT NULL default '',
  `warren8` varchar(20) NOT NULL default '',
  `warren9` varchar(20) NOT NULL default '',
  `warren10` varchar(20) NOT NULL default '',
  `warren11` varchar(20) NOT NULL default '',
  `warren12` varchar(20) NOT NULL default '',
  `warren13` varchar(20) NOT NULL default '',
  `warren14` varchar(20) NOT NULL default '',
  `warren15` varchar(20) NOT NULL default '',
  `warren16` varchar(20) NOT NULL default '',
  `warren17` varchar(20) NOT NULL default '',
  `warren18` varchar(20) NOT NULL default '',
  `warren19` varchar(20) NOT NULL default '',
  `warren20` varchar(20) NOT NULL default '',
  `warren21` varchar(20) NOT NULL default '',
  `warren22` varchar(20) NOT NULL default '',
  `warren23` varchar(20) NOT NULL default '',
  `warren24` varchar(20) NOT NULL default '',
  `warren25` varchar(20) NOT NULL default '',
  `warren26` varchar(20) NOT NULL default '',
  `warren27` varchar(20) NOT NULL default '',
  `warren28` varchar(20) NOT NULL default '',
  `warren29` varchar(20) NOT NULL default '',
  `warren30` varchar(20) NOT NULL default '',
  `warren31` varchar(20) NOT NULL default '',
  `edit_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `current_status` enum('c','n') NOT NULL default 'c',
  `wn1` varchar(20) NOT NULL default '',
  `wn2` varchar(20) NOT NULL default '',
  `wn3` varchar(20) NOT NULL default '',
  `wn4` varchar(20) NOT NULL default '',
  `wn5` varchar(20) NOT NULL default '',
  `wn6` varchar(20) NOT NULL default '',
  `wn7` varchar(20) NOT NULL default '',
  `wn8` varchar(20) NOT NULL default '',
  `wn9` varchar(20) NOT NULL default '',
  `wn10` varchar(20) NOT NULL default '',
  `wn11` varchar(20) NOT NULL default '',
  `wn12` varchar(20) NOT NULL default '',
  `wn13` varchar(20) NOT NULL default '',
  `wn14` varchar(20) NOT NULL default '',
  `wn15` varchar(20) NOT NULL default '',
  `wn16` varchar(20) NOT NULL default '',
  `wn17` varchar(20) NOT NULL default '',
  `wn18` varchar(20) NOT NULL default '',
  `wn19` varchar(20) NOT NULL default '',
  `wn20` varchar(20) NOT NULL default '',
  `wn21` varchar(20) NOT NULL default '',
  `wn22` varchar(20) NOT NULL default '',
  `wn23` varchar(20) NOT NULL default '',
  `wn24` varchar(20) NOT NULL default '',
  `wn25` varchar(20) NOT NULL default '',
  `wn26` varchar(20) NOT NULL default '',
  `wn27` varchar(20) NOT NULL default '',
  `wn28` varchar(20) NOT NULL default '',
  `wn29` varchar(20) NOT NULL default '',
  `wn30` varchar(20) NOT NULL default '',
  `wn31` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`which_month`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

trq · December 1, 2008

Im not even going to comment on the code but your database structure. Sorry, but its ridiculous. There's a book in my signature (Hudzilla) which has a chapter on database normalization, you ought read it.

dropfaith · December 1, 2008

agreed fully on that..

it should be your next step on this project to work on that database

jeff5656 · December 1, 2008

Well regardless of how the database gets rewritten, the user still needs a form that has 279 fields of free text that varies each month. Each of those 279 fields is a separate variable. So I don't know how normalization would solve that. In any case I am locked into this now, so if anyone could help me figure this out I would appreciate it. I will check out your book link but can you help me now?

Mchl · December 1, 2008

Does he? Or maybe he needs 28 to 31 forms of 10 fields each?

Avoid large structures. They look impressive, but are hard to code and maintain.

jeff5656 · December 1, 2008

So I gather from the comments that because my database is "ridiculous" (and I know it is), no ones going to help me solve it? I don't even know where the "1" is coming from in the unknown colum 1 error. We can all laugh about how incompetent I am and how my code sucks but THEN can you help me???

I only learned php a few months ago and I do it on the side bewteen my full time job. I KNOW the next step is to really learn about relational databses and normalization, but from this ptoject you can see that I did not do it very well and I am into a very complex thing now but I still need to find the solution to this. If I can solve this error, then the code will work and one day when i have time I can learn about proper relat dbase and norms.

Mchl · December 1, 2008

We are not laughing at you. We re indicating you better solutions to your design.

In this kind of applications, proper database design helps in about every other aspect of programming.

I've been reviewing your form code, but I must tell I've no idea where the column names come from as well. If you split your form into several smaller ones, they'd be easier to debug.

Mchl · December 1, 2008

Oh, I know!

<input name='".$row[$varArray[$i] . $f]."' type='text' size='13' value= '".$row[$varArray[$i] . $f]."'; />

should be

<input name='".$varArray[$i] . $f."' type='text' size='13' value= '".$row[$varArray[$i] . $f]."'; />

(I think)

jeff5656 · December 1, 2008

MChl's solution worked! Now that you have all seen how my form needs to be and how the database is set up, HOW would you have set it up "correctly". I never asked before because what I need is so complicated I didn't know how to ask, but now that you see all 3 things - user form, database structure and php code, how would you do it differently so I can do it correctly next time?

Mchl · December 1, 2008

First of all, perhaps you could tell us a little more about it.

It's some kind of schedule for intensive care unit staff, but if you told us what do you want it to be able to do, we will be more likely to give you some clues.

jeff5656 · December 1, 2008

It's the monthly call schedule for these groups: icu staff, icu fellows, wb staff, transplant (tx), pulmonary htn, warren, warren-ICU.

All these groups have people on call all 31 days. So the schedule itself is complex!

Here is the link that has blank fields because not all is in yet:

http://www.hfhpulm.com/schedules/staffcallcurrent.php

And look above for how the edit form looks (I attached it in one of my posts)

Mchl · December 1, 2008

So you have on fact

1. A list of people

2. A list of positions

And need to

3. Assign people to positions for each day of the year

Is that right

cooldude832 · December 1, 2008

look around for shift scheduling tutorials, you can do this a lot more efficiently

1 table called users stores all users

1 table called shifts stores all shifts to be filled and the user type that needs to fill them

1 table that links the shifts to a user

Done.

dropfaith · December 1, 2008

this would cut down the amount of difficulty fiquring your db a bit

Date ICU Fellow F2 Staff Intervention West Bloomfield Transplant Pulm HTN Warren Days ICU Main & Warren

make a database for each section and just relate each of them to the date on the form....

im sure others have some better ideas but its all i got off hand working 60 hour weeks is draining my brainl i swear

jeff5656 · December 1, 2008

Sorry it took so long - I didn't see the second page of this thread :-)

So you have on fact

1. A list of people

2. A list of positions

And need to

3. Assign people to positions for each day of the year

Is that right

Yes, that is correct

Mchl · December 1, 2008

Then you could design your database using these points

1. Have a table for people

2. Have a table for positions

3. Have a table, that assigns people, to position and date

table staff
ID, name, surname, ...
1, Leonard, McKoy
2, Beverly, Crusher

table positions
ID, position
1, ICU Fellow
2, F2 Staff

table schedule
staffID, positionID, date
1,1,2008-12-01
2,2,2008-12-01
1,2,2008-12-02
2,1,2008-12-02

Data like this would mean, that dr McKoy (staffID = 1) is staffing ICU Fellow (positionID = 1) position on December 1st, and then goes to F2 Staff (positionID = 2) on December 2nd, whereas dr Crusher (staffID = 2) is working F2 Staff on Decemeber 1st, and ICU Fellow on December 2nd

Makes sense?

jeff5656 · December 1, 2008

I guess I should have thought about it befoe doing it my way. Your way is indeed simpler. Would you still be able to have a form where you choose the month (which table does month go in?) and then have a grid of 279 boxes so you could edit the whole thing at once?

Here's the finished product with the LONG way:

http://www.hfhpulm.com/schedules/staffcallcurrent.php

Thanks for everyone's help you guys are great!!

cooldude832 · December 1, 2008

My Method you could pull up a day and it list all the shifts for that day and then you fill them in via drop downs

You have to build each shift

and FYI I live 2 miles from the HF center in West Bloomfield

Mchl · December 1, 2008

Would you still be able to have a form where you choose the month (which table does month go in?) and then have a grid of 279 boxes so you could edit the whole thing at once?

Yes, you would. And in fact, you could make this form dynamic, so that whenever new position is added to positions table, a new set of dropdowns appears

(I dare you to try to add new position to your current design )

As for where the month goes... nowhere. There are dates in 'schedule' table, so if you want to display schedule for December 2008, you select all records, where YEAR(date) = 2008 AND MONTH(date) = 12

Note: I just realised that 'date' should not be used as a column name, although it will not trigger an error IIRC. It's better to use some other name.

jeff5656 · December 1, 2008

FYI I live 2 miles from the HF center in West Bloomfield

Cool! Small world indeed.

I dare you to try to add new position to your current design

Ugh... Well at least I learned a little about arrays because with my current there's no way i was going to type 279 different variables out. You could use my code in a powerpoint to programmers on what not to do, before launching in to how to properly construct a database ON PAPER before going ahead with the code.

My current code is done and works so NEXT project I will use yours and cooldude832 and other;s suggestions...

Mchl · December 1, 2008

Speaking of PowerPoint

Take a look at stickies in this forum

There's one called SQL Anti-Patterns. Really informative, and not too heavy.

And read about normalizing like you promised

Sign In

Unknown column '1' in 'field list'

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

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

Link to comment

Share on other sites

Join the conversation

Important Information