Jump to content

Error with mysql syntax but cant see where


Slowie

Recommended Posts

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 :)

 

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

 

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

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.