Slowie Posted June 1, 2011 Share Posted June 1, 2011 Hi guys i have the following code which is misbehaving can anyone see where its wrong? <?php include 'dbc.php'; page_protect(); company(); $Referrer = mysql_query("SELECT * FROM Referrer WHERE SentOut='0' "); if (isset($_POST['submit'])) { //Assign each array to a variable $StaffMember = $_POST['StaffMember']; $referrer = $_POST['referrer']; $referred = $_POST['referred']; $SentOut = $_POST['SentOut']; $today = date("y.m.d H:i:s"); $user_id = $_SESSION['user_id']; $IssueNum = $_POST['Referrerid']; $limit = count($StaffMember); for($k=0;$k<$limit;$k++){ $msg[] = "$limit New KPI's Added"; $values[$k] = array( $StaffMember[$k],$referrer[$k],$referred[$k],$SentOut[$k],$today,$user_id ); // build the array of values for the query string } foreach( $values as $key => $value ) { $query = "UPDATE `Referrer` (StaffMember, referer, referred, SentOut, SentOutDate, SentOutBy) VALUES ('" . implode( '\', \'', $value ) . "') WHERE IssueNum= '{$IssueNum[$key]}'"; mysql_query($query) or die(mysql_error()); } } if (checkAdmin()) { ?> <html> <head> <title>Book Off Holiday</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <script src="php_calendar/scripts.js" type="text/javascript"></script> <link href="styles.css" rel="stylesheet" type="text/css"> </head> <body> <form name="form" action="SendReferrers.php" method="post"> <table width="100%" border="0" cellspacing="0" cellpadding="5" class="main"> <tr> <td colspan="3"> </td> </tr> <td width="160" valign="top"> <?php if (isset($_SESSION['user_id'])) { } ?> <a href="admin.php">Admin CP </a> </td> <td width="732" valign="top"> <p> <h3 class="titlehdr">New KPI</h3> <table width="300px" border="0" align="Centre" cellpadding="2" cellspacing="0"> <tr bgcolor="#000050"> <td width="20px"><h3 class="Text2">Referrer ID</h3></td> <td width="20px"><h3 class="Text2">Staff Member</h3></td> <td width="20px"><h3 class="Text2">referrer</h3></td> <td width="20px"><h3 class="Text2">referred</h3></td> <td width="40px"><h3 class="Text2">Sent Out</h3></td> </tr> <?php while ($rrows = mysql_fetch_array($Referrer)) {?> <tr> <td><h3 class="Text3"><input type="" name="Referrerid[]" id="Referrerid[]" size="4" value="<?php echo $rrows['IssueNum'];?>" /></h3></td> <td><h3 class="Text3"><input type="" name="StaffMember[]" id="StaffMember[]" size="4" value="<?php echo $rrows['StaffMember'];?>" /></h3></td> <td><h3 class="Text3"><input type="" name="referrer[]" id="referrer[]" value="<?php echo $rrows['referer'];?>" /></h3></td> <td><h3 class="Text3"><input type="" name="referred[]" id="referred[]" value="<?php echo $rrows['referred'];?>" /></h3></td> <td><h3 class="Text3"><input name="SentOut[]" type="checkbox" value="1" id="SentOut[]"></h3></td> </tr> <?php } ?> </table> <input name="submit" type="submit" id="submit" value="Create"> </table> </form> </body> </html> <?php } ?> the error i get when the submit button is clicked is 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 '(StaffMember, referer, referred, SentOut, SentOutDate, SentOutBy) VALUES' at line 1 any help would be appriciative Quote Link to comment Share on other sites More sharing options...
revraz Posted June 1, 2011 Share Posted June 1, 2011 Try using UPDATE like: UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] Quote Link to comment Share on other sites More sharing options...
DarkKnight2011 Posted June 1, 2011 Share Posted June 1, 2011 Hi, $query = "UPDATE `Referrer` (StaffMember, referer, referred, SentOut, SentOutDate, SentOutBy) VALUES ('" . implode( '\', \'', $value ) . "') WHERE IssueNum= '{$IssueNum[$key]}'"; This format is used for INSERT statements only as far as i know, the correct syntax for an UPDATE statement should be UPDATE [LOW_PRIORITY] [iGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] So yours should be something like this... // shortened just for example $query = "UPDATE Referrer set StaffMember = $staffMember, referer = $referer WHERE IssueNum = $issueNum" Also is this column spelt correctly? referer the table name is Referrer Hope this helps, DK Quote Link to comment Share on other sites More sharing options...
revraz Posted June 1, 2011 Share Posted June 1, 2011 INSERT statements don't use WHERE clauses. If it was a column error, it would return as such. Quote Link to comment Share on other sites More sharing options...
Slowie Posted June 1, 2011 Author Share Posted June 1, 2011 how would i go about doing this with an array then as my values are put through an array to a query Quote Link to comment Share on other sites More sharing options...
DarkKnight2011 Posted June 1, 2011 Share Posted June 1, 2011 Depends on your array to be honest, You could do something like..... $params = array( 'StaffMember' => 'staffmembervalue', 'referrer' => 'referrervalue' ); $query = "UPDATE Refferer SET "; foreach($params as $field => $value) { $query .= "$field = '$value', "; } // remove the last comma and space $query = substr($query, 0, strlen($query) - 2); There are probably easier or better ways of doing this but it will work and should get you on the right path Regards DK Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.