Update MySQL Table from PHP

[Anon]

Help. I've got a script I know works. It update a record in a table fine. However it just seems to decide to stop working. It doesn't produce an error (mysql errno : 0) so I'm at a loss.

If I delete the data fields that I'm trying to update. It'll then update the fields, and all following attempts to update work.

However I left it overnight I just went back to it and it now doesn't work. I delete the contents of the fields again and voila, it works.

Anybody know why?

update statement below.

Nick Gushlow.
----------oOo-----------
$insert_result = mysql_query("UPDATE calls
SET short_log = '$new_short_log',full_log = '$new_full_log'
WHERE dws_call_id = '$call_id'
");

[Anon]

A good idea to do when you have a SQL statement that doesn't appear to be working is to output the query statement (ie "update ....") to the screen and try putting that directly into the mysql monitor window and see what happens there. It may be able to tell you more. PHP should pick up an error but often times I have found that sometimes it can miss little things.

[Anon]

OK, so I got the output of the statement and stuck it into the console and it worked. After I stuck it into the console, the update script worked again!!!

I and it now continues to work now. However if I leave it alone for a while it will stop working again; but when I edit the table via another method (console or phpMyAdmin) and then try it works.

I don't really understand why.

I've added my code below just in case it helps. Sorry about the size but I thought I should add it all.

Nick.

------oOo------
<?
// connect to session and load in core compnents
session_start();
include ($DOCUMENT_ROOT.'/core/database.php');
include ($DOCUMENT_ROOT.'/core/error.php');

db_connect();
db_select();

$access = mysql_query("SELECT access FROM user
WHERE username = '$user'");
$row_access = mysql_fetch_array($access);

if ($row_access[access] > 3) {
include ($DOCUMENT_ROOT.'/core/header_staff_admin.inc');
} else {
include ($DOCUMENT_ROOT.'/core/header_staff_general.inc');
}

if ((($error > 28) && ($error < 32)) || ($error > 499) || ($error == 24) || ($error == 23)){
$error_code = $error;
} else {
$error_code = 0;
}

$search_value = $search;
// query calls table dws call id
$result = mysql_query("SELECT FROM calls
WHERE dws_call_id = '$call_id'
");
$row = mysql_fetch_array($result);
if (!$row) {
$error = 32;
echo "<script language=\"JavaScript\"> ";
echo "location='main.php';";
echo "</script>";
exit;
}
// get info from user table
$user_result = mysql_query("SELECT FROM user
WHERE uid_user = '$row[uid_user]'
");
$user_row = mysql_fetch_array($user_result);
// get info from customer table
$cust_result = mysql_query("SELECT FROM customer
WHERE cust_id = '$row[cust_id]'
");
$cust_row = mysql_fetch_array($cust_result);
// get info from type table
$type_result = mysql_query("SELECT FROM type
");
$type_row = mysql_fetch_array($type_result);
// get info from team table
$team_result = mysql_query("SELECT FROM teams
");
$team_row = mysql_fetch_array($team_result);
// get info from status table
$stat_result = mysql_query("SELECT FROM status
");
$stat_row = mysql_fetch_array($stat_result);
// get info from priority table
$pri_result = mysql_query("SELECT * FROM priority
");
$pri_row = mysql_fetch_array($pri_result);

// ------ MAIN PROCEDURE ------//
if ($Submit) {
// check status of record
if ($row[status] > 1) {
// append log with new entry short log, log and date
// get date
$current_date = date("Y-m-d");
// get current short_log
$current_short_log = $row[short_log];
// get current log
$current_log = $row[full_log];
// update short desc
$new_short_log = $current_short_log."\n\n----------oOo----------\n".
$current_date." - ".$desc;
// update full log
$new_full_log = $current_log."\n\n----------oOo----------\n".
$current_date." - ".$user." - ".$log;

/ $insert_result = mysql_query("INSERT INTO calls(dws_call_id, uid_user, cust_id, type,
cust_priority, dws_priority, date_logged,
team_id, status, date_assigned, assigned_to,
req_finish_date, short_desc,
long_desc, short_log, full_log)
VALUES('$call_id','$row[uid_user]','$row[cust_id]','$row[type]',
'$row[cust_priority]','$row[dws_priority]','$row[date_logged]',
'$row[team_id]','$row[status]','$row[sate_assigned]','$row[assigned_to]',
'$row[req_finish_date]','$row[short_desc]','$row[long_desc]',
'$new_short_log','$new_full_log')
WHERE call_id = '$row[call_id]'
");
/

    // insert changes into the database
    $insert_result = mysql_query("UPDATE calls
                                  SET short_log = '$new_short_log',full_log = '$new_full_log'
                                  WHERE dws_call_id = '$call_id'
                                ");

//echo $call_id."<br>";
//echo "<textarea name=\"log\" wrap=\"VIRTUAL\" cols=\"72\" rows=\"20\" readonly>".$new_short_log."</textarea>";
//echo "<textarea name=\"log\" wrap=\"VIRTUAL\" cols=\"72\" rows=\"20\" readonly>".$new_full_log."</textarea>";
//echo "<br>".$insert_result;
//echo "<br>UPDATE calls SET short_log = '".$new_short_log."',full_log = '".$new_full_log."' WHERE dws_call_id = '".$call_id."';";
//exit;

    if ($insert_result) {
        $error = 30;
        echo "<script language=\"JavaScript\"> ";
        echo "location='main.php';";
        echo "</script>";
        exit;
    } else {
        // Data insertion error!!!!
        $error = 999;
        echo "<script language=\"JavaScript\"> ";
        echo "location='main.php';";
        echo "</script>";
        exit;
    }
} else {
    $error = 31;
    echo "<script language=\"JavaScript\"> ";
    echo "location='main.php';";
    echo "</script>";
    exit;
}

}
?>
<TABLE width="100%" border="0" cellspacing="0" cellpadding="0" background="../im/pixel_blue.gif">
<TR>
<TD width="10">&nbsp;</TD>
<TD height="20">&nbsp;</TD>
<TD width="10">&nbsp;</TD>
</TR>
<TR>
<TD>&nbsp;</TD>
<TD>
<TABLE width="100%" border="0" cellspacing="0" cellpadding="0" background="../im/pixel_blank.gif">
<TR>
<TD height="20">&nbsp;</TD>
</TR>
<TR>
<TD>
<FORM method="post" action="">
<TABLE border="0" cellspacing="0" cellpadding="0" bgcolor="#CCCCCC" background="../im/pixel_blank.gif">
<TR bgcolor="#666666">
<TD colspan="4"><FONT face="Arial, Helvetica, sans-serif" color="#FFFFFF" size="2">&nbsp;<B>Add
To Log :
<?php echo $call_id; ?>
</B></FONT></TD>
</TR>
<TR>
<TD height="10" colspan="4">&nbsp;</TD>
</TR>
<TR>
<TD width="10">&nbsp;</TD>
<TD><FONT face="Arial, Helvetica, sans-serif" color="#000000" size="2">Date:</FONT></TD>
<TD><FONT face="Arial, Helvetica, sans-serif" color="#000000" size="2"><?php echo date("Y-m-d");?></TD>
<TD width="10">&nbsp;</TD>
</TR>
<TR>
<TD height="10" colspan="4">&nbsp;</TD>
</TR>
<TR>
<TD>&nbsp;</TD>
<TD><FONT face="Arial, Helvetica, sans-serif" color="#000000" size="2">Short
Description:</FONT></TD>
<TD><FONT size="-1" point-size="7">
<INPUT type="text" name="desc" size="50">
</FONT></TD>
<TD>&nbsp;</TD>
</TR>
<TR>
<TD height="10" colspan="4">&nbsp;</TD>
</TR>
<TR>
<TD>&nbsp;</TD>
<TD colspan="2"><FONT face="Arial, Helvetica, sans-serif" color="#000000" size="2">Log:</FONT></TD>
<TD>&nbsp;</TD>
</TR>
<TR>
<TD>&nbsp;</TD>
<TD colspan="2"><FONT size="-1" point-size="7">
<TEXTAREA name="log" cols="80" rows="10" wrap="VIRTUAL"></TEXTAREA>
</FONT></TD>
<TD>&nbsp;</TD>
</TR>
<TR>
<TD height="10" colspan="4">&nbsp;</TD>
</TR>
<TR>
<TD>&nbsp;</TD>
<TD colspan="2"><FONT size="-1" point-size="7">
<INPUT type="submit" name="Submit" value="Submit">
<INPUT type="reset" name="reset" value="Reset">
</FONT></TD>
<TD>&nbsp;</TD>
</TR>
<TR>
<TD height="10" colspan="4">&nbsp;</TD>
</TR>
</TABLE>
</FORM>
</TD>
</TR>
</TABLE>
</TD>
<TD>&nbsp;</TD>
</TR>
<TR>
<TD width="10">&nbsp;</TD>
<TD height="20">&nbsp;</TD>
<TD width="10">&nbsp;</TD>
</TR>
<TR>
<TD width="10">&nbsp;</TD>
<TD height="20">
<table border="0" cellspacing="0" cellpadding="0" bgcolor="#CCCCCC" background="../im/pixel_blank.gif">
<tr bgcolor="#666666">
<td colspan="8"><font face="Arial, Helvetica, sans-serif" color="#FFFFFF" size="2">&nbsp;<b>Log
for Call ID :
<?php echo $call_id; ?>
</b></font></td>
</tr>
<tr>
<td colspan="4"><font face="Arial, Helvetica, sans-serif" size="2" color="#cc0000">&nbsp;
<?php error_handling($error_code); ?>
</font></td>
</tr>
<tr>
<td width="10">&nbsp;</td>
<td width="200" colspan="2"><font face="Arial, Helvetica, sans-serif" color="#000000" size="2">Call
ID:
<?php echo $call_id; ?>
</font></td>
<td width="200" colspan="2"><font face="Arial, Helvetica, sans-serif" color="#000000" size="2">Customer:
<?php echo $cust_row[cust_name];?>
</font></td>
<td width="200" colspan="2"><font face="Arial, Helvetica, sans-serif" color="#000000" size="2">Customer
ID:
<?php echo $cust_row[cust_id];?>
</font></td>
<td width="10">&nbsp;</td>
</tr>
<tr>
<td height="10" colspan="8">&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td width="100"><font face="Arial, Helvetica, sans-serif" color="#000000" size="2">Contact:</font></td>
<td width="180">
<?php echo $user_row[realname]; ?>
</td>
<td width="120"><font face="Arial, Helvetica, sans-serif" color="#000000" size="2">Email:</font></td>
<td width="180">
<?php echo $user_row[email]; ?>
</td>
<td width="100"><font face="Arial, Helvetica, sans-serif" color="#000000" size="2">Phone:</font></td>
<td width="180"><?php echo $cust_row[cust_phone]; ?></td>
<td>&nbsp;</td>
</tr>
<tr>
<td height="10" colspan="8">&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td><font face="Arial, Helvetica, sans-serif" color="#000000" size="2">Type:</font></td>
<td><font size="-1" point-size="7"><?php echo $type_row[type_desc]; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif" color="#000000" size="2">Customer
Priority:</font></td>
<td><font face="Arial, Helvetica, sans-serif" color="#000000" size="2"><?php echo $row[cust_priority]; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif" color="#000000" size="2">DWS
Priority:</font></td>
<td><?php echo $row[dws_priority]; ?></td>
<td>&nbsp;</td>
</tr>
<tr>
<td height="10" colspan="8">&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td><font face="Arial, Helvetica, sans-serif" color="#000000" size="2">Date
Logged:</font></td>
<td><font face="Arial, Helvetica, sans-serif" color="#000000" size="2"><?php echo $row[date_logged]; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif" color="#000000" size="2">Team:</font></td>
<td><?php echo $team_row[team_name]; ?></td>
<td><font face="Arial, Helvetica, sans-serif" color="#000000" size="2"><b>Status:</b></font></td>
<td><?php echo $stat_row[status_desc]; ?></td>
<td>&nbsp;</td>
</tr>
<tr>
<td height="10" colspan="8">&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td><font face="Arial, Helvetica, sans-serif" color="#000000" size="2">Date
Assigned:</font></td>
<td><font face="Arial, Helvetica, sans-serif" color="#000000" size="2"><?php echo $row[date_assigned]; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif" color="#000000" size="2">Assigned
to:</font></td>
<td><?php
$assigned = mysql_query("SELECT * FROM user
WHERE uid_user = '$row[assigned_to]'
");
$assigned_row = mysql_fetch_array($assigned);
echo $assigned_row[realname]; ?></td>
<td><font face="Arial, Helvetica, sans-serif" color="#000000" size="2">Date
Complete:</font></td>
<td><font face="Arial, Helvetica, sans-serif" color="#000000" size="2"><?php echo $row[date_complete]; ?></font></td>
<td>&nbsp;</td>
</tr>
<tr>
<td height="10" colspan="8">&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td><font face="Arial, Helvetica, sans-serif" color="#000000" size="2">Description:</font></td>
<td colspan="5"><font face="Arial, Helvetica, sans-serif" color="#000000" size="2"><?php echo $row[short_desc]; ?></font></td>
<td>&nbsp;</td>
</tr>
<tr>
<td height="10" colspan="8">&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td><font face="Arial, Helvetica, sans-serif" color="#000000" size="2">Full
Description:</font></td>
<td colspan="5"><font face="Arial, Helvetica, sans-serif" color="#000000" size="2"><?php echo $row[long_desc]; ?></font></td>
<td>&nbsp;</td>
</tr>
<tr>
<td height="10" colspan="8">&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td colspan="6"><font face="Arial, Helvetica, sans-serif" color="#000000" size="2">Log:</font></td>
<td>&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td colspan="6"><font face="Arial, Helvetica, sans-serif" color="#000000" size="2">
<textarea name="log" wrap="VIRTUAL" cols="72" rows="20" readonly><?php echo $row[full_log];?></textarea>
</font></td>
<td>&nbsp;</td>
</tr>
<tr>
<td height="10" colspan="8">&nbsp;</td>
</tr>
</table>
</TD>
<TD width="10">&nbsp;</TD>
</TR>
</TABLE>
</BODY>

[Anon]

Just been goign though another update script I have, which updates to the same table; and that works fine. The mysql_quey is done the same, the only difference is the script that fails mysteriously updates longtext fields.

Would the fact that I'm using longtext have anything to do with it?