[SOLVED] Unknown Column in Field List

aeafisme23 · April 22, 2008

Can't run query because Unknown column 'contact_date' in 'field_list'

I know that the main cause of this is because of quotations single or double quotes but apparently process of elimination has not worked out so well. Does it matter that the single quotes are within doubles? An extra eye would be very helpful, i will post all the sql statements first and then the majority of the code below it with the same SQL statements. Thanks so much everyone!

All SQL statements on php page:

    $sql = "select * from contact_log where id = $id order by station_id"; 
    $sql = "select * from contact_log order by station_id";  
    $sql = "insert into station_list (station_id, contact_date, contact_person, contact_kv, contact_notes) values('$station_id','$contact_date','$contact_person','$contact_kv','$contact_notes')"; 
    $msg = "Record successfully added"; 
  }elseif($action=="edit"){ 
    $sql = "update station_list set station_id = '$station_id', contact_date = '$contact_date', contact_person = '$contact_person', contact_kv = '$contact_kv', contact_notes = '$contact_notes' where id = $id"; 
    $msg = "Record successfully updated";

The rest of the code in the order it is shown ( i left out db connection and another part

/************************************************************************* 
               show the input / edit form 
*************************************************************************/ 
function show_form($handle='',$data='') 
{ 
  //$handle is the link to the resultset, the ='' means that the handle can be empty / null so if nothing is picked it won't blow up 
  
  //set default values 
  $station_id  = ''; 
  $contact_date  = ''; 
  $contact_person  = ''; 
  $contact_kv  = ''; 
  $contact_notes  = ''; 
  $id      = ''; 
  $value      = 'Add';  //submit button value 
  $action     = 'add';  //default form action is to add a new dma/station to db 

  //set the action based on what the user wants to do 
  if ($handle) 
  { 
    //set form values for button and action 
    $action = "edit"; 
    $value  = "Update"; 
    
    //get the values from the db resultset 
    $row = mysql_fetch_array($handle); 
    $station_id  = $row['station_id']; 
    $contact_date  = $row['contact_date']; 
    $contact_person  = $row['contact_person']; 
    $contact_kv  = $row['contact_kv']; 
    $contact_notes  = $row['contact_notes']; 
    $id          = $row['id']; 
  
  }//end if 
  
  //error handling from the processing form function 
  if($data != '') 
  { 
    $elements = explode("|",$data); 
        $station_id     = $elements[0]; 
        $contact_date     = $elements[1]; 
        $contact_person     = $elements[2]; 
        $contact_kv     = $elements[3]; 
        $contact_notes     = $elements[4]; 
        $id             = $elements[5]; 
  } 
?> 

<body> 

<form name="form1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>?action=<?php  echo $action?>"> 
<table width="700" bgcolor="ffffff" cellpadding="0" cellpsacing="0" border="1" bordercolor="#000000">
<tr>
	<td><input type="hidden" value="<?php echo $id?>" name="id">
		<table width="700" cellpadding="0" cellpsacing="0" border="0" bordercolor="#000000">
			<tr>
				<td align="left" width="180">Station ID</td>
				<td width="320" align="left"><input type="text" name="station_id" size="15" value="<?php echo $station_id?>"></td>
				<td align="left" width="200">All Upper Case ex) KCNC</td>
			</tr>
			<tr>
				<td align="left" width="180">Date of Contact</td>
				<td width="320" align="left"><input type="text" name="contact_date" size="15" value="<?php echo $contact_date?>"></td>
				<td align="left" width="200">ex) 05-21-2008 </td>
			</tr>
			<tr>
				<td align="left" width="180">Person Contacted</td>
				<td width="320" align="left"><input type="text" name="contact_person" size="40" value="<?php echo $contact_person?>"></td>
				<td align="left" width="200">ex) Joe Smith </td>
			</tr>
			<tr>
				<td align="left" width="180">KinderVision Contact</td>
				<td width="320" align="left"><input type="text" name="contact_kv" size="40" value="<?php echo $contact_kv?>"></td>
				<td align="left" width="200">ex) Doug Sebastian </td>
			</tr>
			<tr>
				<td align="left" width="180" valign="top">Contact Notes</td>
				<td width="320" align="left"><textarea name="contact_notes" cols="40" rows="6"><?php echo $contact_notes?></textarea></td>
				<td align="left" width="200"></td>
			</tr>
			<tr>
				<td colspan="3">
				<p align="center"><input name="submit" type="submit" value="<?php echo $value?>"> <input name="reset" type="reset" value="Clear Form"></p>
				</td>
			</tr>
		</table>
	</td>
</tr>
</table>	

<? 
}//end function 


/************************************************************************* 
               list all the DMA/Stations in the db 
*************************************************************************/ 
function list_users() 
{ 
    $y = 0; //counter 
    
    $sql = "select * from contact_log order by station_id";  //may want to add the option where clause to only take kids with an active status 
    $result = conn($sql); 
  
  echo "<table width='700' align='center' cellpadding='0' cellspacing='0'> 
        <tr><td colspan='5' align='center' style='font-size:18px; font-weight:bold;'>Manage Contact Log</td></tr> 
        <tr><td colspan='5'> </td></tr> 
        <tr><td colspan='5'><a href='".$_SERVER['PHP_SELF']."?action=add'>Add a new Contact Log</a></td></tr> 
        <tr><td colspan='5'> </td></tr>"; 
        
     if (mysql_num_rows($result)){ 
      //show a list of kids with name as a link to the prepopulated form with their data in it 
      while($rows = mysql_fetch_array($result)){ 
        
        //change row background color 
        (($y % 2) == 0) ? $bgcolor = "#F8F7F2" : $bgcolor=" #FFFFFF"; 
        
        //build strings to make life easier 
        $station_id = $rows['station_id']; 
        $contact_date = $rows['contact_date']; 
        $contact_person = $rows['contact_person']; 
        $contact_kv = $rows['contact_kv']; 
        $contact_notes = $rows['contact_notes']; 
        $id = $rows['id']; 
        
        //convert status to readable string from 1 or 0 
        ($status == 0) ? $status = "Available to contact" : $status = "Do not contact at present."; 
        
        //echo out the row 
        echo "<tr style='background-color:$bgcolor;'><td><a href='".$_SERVER['PHP_SELF']."?id=$id'>$station_id</a></td>
			<td>$contact_date</td>
			<td>$contact_person</td>
			<td>$contact_kv</td>
			<td>$contact_notes</td>
		<tr>"; 
        $y++;  //increment the counter 
      }//end while 
      echo "</table>"; 
  }else{ 
    //handle no results 
    echo "<tr><td colspan='5' align='center'><b>No data found.</b></td></tr>"; 
  }//endif 
} 


/************************************************************************* 
               add / update the contact's data 
*************************************************************************/ 
function process_form() 
{ 
  $station_id  = ''; 
  $contact_date  = ''; 
  $contact_person  = ''; 
  $contact_kv  = ''; 
  $contact_notes  = ''; 
  $id     = ''; 
  $action = ''; 
  $status = 0;    //default value 

  $station_id  = @$_POST['station_id']; 
  $contact_date  = @$_POST['contact_date'];  
  $contact_person  = @$_POST['contact_person']; 
  $contact_kv  = @$_POST['contact_kv'];  
  $contact_notes  = @$_POST['contact_notes']; 
  $id     = @$_POST['id'];          
  $action = @$_GET['action']; 
  $status = @$_POST['status']; 
  
  //if no status is set, defaults to 0 (allow contact) 
  if ($status == ''){$status = 0; } 
    
  if (($station_id=='')||($contact_date=='')||($contact_person=='')||($contact_kv=='')||($contact_notes=='')) 
  { 
    $msg = "Some data from the form was forgotten. Please fill in the entire form."; 
    confirm($msg); 
    $data = "$station_id|$contact_date|$contact_person|$contact_kv|$contact_notes|$id"; 
    show_form('',$data); 
    die(); 
  }//end if 
  
  //You could add some validation of the data ( I recommend it and its a great way to get your feet wet with php ) 


  if ($action == "add") 
  { 
    $sql = "insert into station_list (station_id, contact_date, contact_person, contact_kv, contact_notes) values('$station_id','$contact_date','$contact_person','$contact_kv','$contact_notes')"; 
    $msg = "Record successfully added"; 
  }elseif($action=="edit"){ 
    $sql = "update station_list set station_id = '$station_id', contact_date = '$contact_date', contact_person = '$contact_person', contact_kv = '$contact_kv', contact_notes = '$contact_notes' where id = $id"; 
    $msg = "Record successfully updated"; 
  } 
  $result = conn($sql); 
  if (mysql_errno()==0) 
  { 
    confirm($msg); 
    list_users(); 
  }else{ 
    $msg = "There was a problem adding the user to the database. Error is:".mysql_error(); 
    confirm($mag); 
  }//end if 
      
}

jonsjava · April 22, 2008

just did some minor cleanup on the query. it should have worked as-is. are you sure contact_date is a proper field?

$sql = "select * from contact_log where id = {$id} order by station_id;"; 
    $sql = "select * from contact_log order by station_id;";  
    $sql = "insert into station_list (station_id, contact_date, contact_person, contact_kv, contact_notes) values('{$station_id}','{$contact_date}','{$contact_person}','{$contact_kv}','{$contact_notes}');"; 
    $msg = "Record successfully added"; 
  }elseif($action=="edit"){ 
    $sql = "update station_list set station_id = '{$station_id}', contact_date = '{$contact_date}', contact_person = '{$contact_person}', contact_kv = '{$contact_kv}', contact_notes = '{$contact_notes}' where id = {$id};"; 
    $msg = "Record successfully updated";

aeafisme23 · April 22, 2008

Thank you for cleaning it up jonsjava, i am now going to use the "proper" format of SQL you suggested and after 2 hours of looking through my code you are right as well for the most part. I was using the wrong SQL table on my inserts station_list when it should have been contact_log as stated everywhere above in the code!!!! I hate simple errors, it's why i needed an extra eye. Thanks again JON!

Sign In

[SOLVED] Unknown Column in Field List

Recommended Posts

aeafisme23

Link to comment

Share on other sites

jonsjava

Link to comment

Share on other sites

aeafisme23

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information