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 Link to comment https://forums.phpfreaks.com/topic/238074-error-with-mysql-syntax-but-cant-see-where/ 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] Link to comment https://forums.phpfreaks.com/topic/238074-error-with-mysql-syntax-but-cant-see-where/#findComment-1223387 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 Link to comment https://forums.phpfreaks.com/topic/238074-error-with-mysql-syntax-but-cant-see-where/#findComment-1223388 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. Link to comment https://forums.phpfreaks.com/topic/238074-error-with-mysql-syntax-but-cant-see-where/#findComment-1223390 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 Link to comment https://forums.phpfreaks.com/topic/238074-error-with-mysql-syntax-but-cant-see-where/#findComment-1223423 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 Link to comment https://forums.phpfreaks.com/topic/238074-error-with-mysql-syntax-but-cant-see-where/#findComment-1223433 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.