Page 1 of 1

Updating date fields problem

Posted: Tue Sep 12, 2006 9:47 am
by genista
Hi all,
I have a script that allows a user to update their details. The date of birth is split into three seperate fields and I need to pull the information from the database and then let a user update it, the problem is when I do try and do an update I get an error - cannot update database.

The code below shows how I have set out the date of birth:

Code: Select all

<p>Date of Birth: </td><td><?php
$id = $_SESSION['username'];
$query = "select * from users where username='$id'";

    //now we pass the query to the database 
	$result=mysql_query($query) or die("Could not get data.".mysql_error()); 

    //get the first (and only) row from the result 
    $row = mysql_fetch_array($result, MYSQL_ASSOC); 
echo $query;
/*$dob ="select * from users where username='$id'";

$result=mysql_query($query) or die("Could not get data.".mysql_error()); 

    //get the first (and only) row from the result 
    $row = mysql_fetch_array($result, MYSQL_ASSOC); */
    
   $dbday=$row['dbday'];
    $dbmonth=$row['dbmonth'];
    $dbyear=$row['dbyear'];

$currentValue = $row['dbday']; 

//generate drop down for day 
echo '<select name="dbday">'; 
for ($i=1; $i < 32; $i++) 
{ 
    //adds a leading zero if needed 
    //$lz = strlen($i) == 1 ? '0'.$i : $i; 
    $lz = str_pad($i, 2, '0', STR_PAD_LEFT); 
    // check if the value displayed is the one currenlty selected 
    $checkedStatus = ''; 
    if ($i == $currentValue) 
    { 
        $checkedStatus = 'SELECTED'; 
    } 
    echo '<option value="'.$lz.'" '.$checkedStatus.'>'.$lz.'</option>'; 
} 
echo '</select>'; 

$currentvalue2 = $row['dbmonth'];

//generate drop down for month 
$months = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'); 
echo '<select name="dbmonth"><option value="01" />Jan'; 
for ($i=1; $i <= sizeof($months); $i++) 
{ 
//adds a leading zero if needed 
$lz = strlen($i) == 1 ? '0'.$i : $i; 
$checkedStatus = ''; 
    if ($i == $currentvalue2) 
    { 
        $checkedStatus = 'SELECTED'; 
    } 
    echo '<option value="'.$lz.'" '.$checkedStatus.'>'.$months[$i-1]; 
} 
echo '</select>'; 
    
//year
$currentvalue3 = $row['dbyear'];

echo '<select name="dbyear">'; 
for ($i=1900; $i < 2006; $i++)
{ 
$lz = strlen($i) == 1 ? '0'.$i : $i; 
$checkedStatus = '';
if ($i == $currentvalue3) 
     { 
        $checkedStatus = 'SELECTED'; 
    } 
    
	echo '<option value="'.$lz.'" '.$checkedStatus.'>'.$lz.'</option>'; 
} 
echo '</select>'; 
?>
The update query look slike so:

Code: Select all

$query = "UPDATE `users` 
              SET `first_name` = '$first_name', 
              `maiden_name` = '$maiden_name', 
              `last_name` = '$last_name', 
 'dbday' = '$dbday',
'dbmonth' = '$dbmonth',
'$dbyear' = '$dbyear'
              WHERE `username` = '". mysql_real_escape_string($_SESSION['username']). "' 
              LIMIT 1";  

 $result = mysql_query($query, $link) or die('Update failed: ' . mysql_error()); 

 mysql_info($link) ; 
    if(mysql_affected_rows($link) == 0) 
    { 
      //$link next to affected rows and mysql query
        echo 'The record was not updated.'; 
    } 
     
    else 
    { 
        echo 'Record updated successfully...'; 
    }
Further up the form when setting out the variables I notice that if I remove..

Code: Select all

$address_line1=$_POST["address_line1"]; 
    $address_line2=$_POST["address_line2"]; 
    $town=$_POST["town"]; 
    $county=$_POST["county"];

[b]...these I dont get the error:[/b]

$dbday=$_POST["dbday"];
    $dbmonth=$_POST["dbmonth"];
    $dbyear=$_POST["dbyear"];

I hope this helps.

thanks,

G

Posted: Tue Sep 12, 2006 9:58 am
by GM
To be honest, I haven't read closely, but the first thing that springs to mind is that you've got some data that contains an apostrophe, or some other value that has caused a problem in your HTML source.

Try echoing the query to the screen, and run it manually in the MySQL console?

Posted: Tue Sep 12, 2006 10:41 am
by RobertGonzalez
You are probably getting an unknown field name error because of this line in your query...

Code: Select all

'$dbyear' = '$dbyear'
Try removing the dollar sign on the field name.

Posted: Tue Sep 12, 2006 3:39 pm
by feyd
..single quotes tells the database it's a string, too.