syntax error problem

natman3

I have been trying to resolve this error for over three days. I have a form that is populated with data so that I can view the data that I want to modify. I can get the data to display whats in the dba but when i go to submit i get the syntax error of:

You have an error in your SQL syntax near '' at line 1

this code is on the editpage.php


<?php
include($_SERVER['DOCUMENT_ROOT']."/user/ext_user_profile.php"); 
error_reporting (E_ALL); // I use this only for testing
$update_profile = new Users_profile;

$update_profile->access_page($_SERVER['PHP_SELF'], $_SERVER['QUERY_STRING']); // protect this page too.

// gets data to populate the form
$update_profile->get_runner_data();

if (isset($_POST['profile_data'])) {

            // function that either updates or inserts 
$update_profile->save_profile_date($_POST['cid'], $_POST['first'], $_POST['last'], $_POST['year'], $_POST['age'], $_POST['gender']); 

}
$error = $update_profile->the_msg; // error message


?>

<form name="form1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
		<!--<input type="hidden" name="cid" value="<=$cid;?>"> -->
	   <table width="500" border="0" cellpadding="2" cellspacing="1" class="box" align="center">
		<tr> 
		  <td width="100">first</td>
		  <td><input name="first" type="text" class="box" id="first" value="<?=$update_profile->first;?>"> </td>
		</tr>
		<tr> 
		  <td width="100">last</td>
		  <td><input name="last" type="text" class="box" id="last" value="<?=$update_profile->last;?>"></td>
		</tr>
		<tr> 
		  <td width="100">year</td>
		  <td><input name="year" type="text" class="box" id="year" value="<?=$update_profile->year;?>"></td>
		</tr>
		<tr> 
		  <td width="100">age</td>
		  <td><input name="age" type="text" class="box" id="age" value="<?=$update_profile->age;?>"></td>
		</tr>
		<tr> 
		  <td width="100">gender</td>
		  <td><input name="gender" type="text" class="box" id="gender" value="<?=$update_profile->gender;?>"></td>
		</tr>
		<tr> 
		  <td width="100">&nbsp;</td>
		  <td>&nbsp;</td>
		</tr>
		<tr> 
		  <td colspan="2" align="center"><input type="hidden" name="id" value="<?php echo  $update_profile->profile_id; ?>">
		  <input name="profile_data" type="submit" class="box" id="profile_data" value="Update Runner"></td>
		</tr>
	  </table>
	 <p align="center"><a href="add_manage_coll.php">Back to admin page</a></p>
	</form>

bellow here are the functions in the class:

function save_profile_date($ident = "", $first = "", $last = "", $year = "", $age = "", $gender = "") {
		if ($_SESSION['is_rec']) {		
			$sql = sprintf("UPDATE %s SET first=%s, last=%s, year=%s, age=%s, gender=%s, last_change=NOW() WHERE cid = %s AND user_id = %s", 
				PROFILE_TABLE, $this->ins_string($first), $this->ins_string($last), $this->ins_string($year),
				$this->ins_string($age), $this->ins_string($gender), $this->ins_string($ident, "int"), $_SESSION['id']);
		} else {
			$sql = sprintf("INSERT INTO %s (cid, user_id, first, last, year, age, gender, last_change) VALUES (NULL, %s, %s, %s, %s, %s, %s, NOW())",
				PROFILE_TABLE, $_SESSION['id'], $this->ins_string($first), $this->ins_string($last), 
				$this->ins_string($year), $this->ins_string($age), $this->ins_string($gender));
		}
		if (mysql_query($sql) or die (mysql_error())) {
			$this->profile_id = (!$_SESSION['is_rec']) ? mysql_insert_id() : $ident;
			$this->the_msg = $this->extra_text(2);
		} else {			
			$this->the_msg = $this->extra_text(3);
		}
	}	

// Function gets the runner of the user
function get_runner_data() {
	$this->get_user_CID();
	$uid = $_GET['cid'];
	$_SESSION['cid'] = $uid;
	$sql = sprintf("SELECT cid, user_id, first, last, year, age, gender FROM %s WHERE cid = $uid", PROFILE_TABLE, $this->uid);		
	$result = mysql_query($sql) or die (mysql_error());		
	if (mysql_num_rows($result) == 0) {
		$_SESSION['is_rec'] = false;					
		$this->the_msg = $this->extra_text(1); // There is no profile data at the moment				
	} else {
		$_SESSION['is_rec'] = true; // this session acts like an extra controle			
	while ($obj = mysql_fetch_object($result)) {
		$this->profile_id = $obj->cid;
		$this->first = $obj->first;
		$this->last = $obj->last;
		$this->year = $obj->year;
		$this->age = $obj->age;
		$this->gender = $obj->gender;


		}
	}
}

function get_user_CID() {
		$sql_info = sprintf("SELECT cid FROM %s WHERE first = '%s' AND last = '%s'", $this->collegiate_table, $this->first, $this->last);
		$res_info = mysql_query($sql_info);
		$this->cid = mysql_result($res_info, 0, "cid");
		$_SESSION['cid'] = $this->cid;		
	}

bpat1434

Have you printed the query to make sure it looks proper?

~Brett

natman3

Yeah i tried using this: the printf for the UPDATE

			if ($_SESSION['is_rec']) {
			$sql = printf("UPDATE %s SET first=%s, last=%s, year=%s, age=%s, gender=%s, last_change=NOW() WHERE cid = %s AND user_id = %s", 
				PROFILE_TABLE, $this->ins_string($first), $this->ins_string($last), $this->ins_string($year),
				$this->ins_string($age), $this->ins_string($gender), $_SESSION['user_id']);
		} else {
			$sql = sprintf("INSERT INTO %s (id, user_id, first, last, year, age, gender, last_change) VALUES (NULL, %s, %s, %s, %s, %s, %s, NOW())",
				PROFILE_TABLE, $_SESSION['user_id'], $this->ins_string($first), $this->ins_string($last), 
				$this->ins_string($year), $this->ins_string($age), $this->ins_string($gender));
		}	
		if (mysql_query($sql) or die (mysql_error())) {
			$this->profile_id = (!$_SESSION['is_rec']) ? mysql_insert_id() : $ident;
			$this->the_msg = $this->extra_text(2);
		} else {
			$this->the_msg = $this->extra_text(3);
		}
	}

but i got this error:

You have an error in your SQL syntax near '' at line 1

Which leads me to believe there is a confilct somewhere else where I am not sure

bpat1434

Okay. So try this:

$sqlupdate = "UPDATE %s 
          SET first=%s, last=%s, year=%s, age=%s, gender=%s, last_change=NOW() 
          WHERE cid = %s 
          AND user_id = %s";
$sql = sprintf($sqlupdate, PROFILE_TABLE, $this->ins_string($first), $this->ins_string($last), $this->ins_string($year),
                $this->ins_string($age), $this->ins_string($gender), $_SESSION['user_id']);

print($sql);

That will print out your sql statement for you to check to see EXACTLY what is being sent to mySQL.

Then, each sql command is on its own individual line so that when you get errors, the line number will point not to line 1, but to the line that the error occurs on.

~Brett

natman3

I think the problem lies elsewhere. because I changed this:

function save_profile_date($ident = "", $first = "", $last = "", $year = "", $age = "", $gender = "") {
			if ($_SESSION['is_rec']) {
			$sql = sprintf("UPDATE %s SET first=%s, last=%s, year=%s, age=%s, gender=%s, last_change=NOW() WHERE cid = %s AND user_id = %s", 
				PROFILE_TABLE, $this->ins_string($first), $this->ins_string($last), $this->ins_string($year),
				$this->ins_string($age), $this->ins_string($gender), $_SESSION['user_id']);
		} else {
			$sql = sprintf("INSERT INTO %s (id, user_id, first, last, year, age, gender, last_change) VALUES (NULL, %s, %s, %s, %s, %s, %s, NOW())",
				PROFILE_TABLE, $_SESSION['user_id'], $this->ins_string($first), $this->ins_string($last), 
				$this->ins_string($year), $this->ins_string($age), $this->ins_string($gender));
		}	
		if (mysql_query($sql) or die (mysql_error())) {
			$this->profile_id = (!$_SESSION['is_rec']) ? mysql_insert_id() : $ident;
			$this->the_msg = $this->extra_text(2);
		} else {
			$this->the_msg = $this->extra_text(3);
		}
	}

to this:

function save_profile_date($ident = "", $first = "", $last = "", $year = "", $age = "", $gender = "") {

		$sqlupdate = "UPDATE %s SET first=%s, last=%s, year=%s, age=%s, gender=%s, last_change=NOW() WHERE cid = %s AND user_id = %s"; 
		$sql = sprintf($sqlupdate, PROFILE_TABLE, $this->ins_string($first), $this->ins_string($last), $this->ins_string($year), 
				$this->ins_string($age), $this->ins_string($gender), $_SESSION['user_id']); 

print($sql);

}

and still go the error: You have an error in your SQL syntax near '' at line 1

which leads me to think that the problem is in this function:

<?php
include($_SERVER['DOCUMENT_ROOT']."/user/ext_user_profile.php"); 
error_reporting (E_ALL); // I use this only for testing
$update_profile = new Users_profile;

$update_profile->access_page($_SERVER['PHP_SELF'], $_SERVER['QUERY_STRING']); // protect this page too.

// with this function that populates the form fileds
$update_profile->get_runner_data();

if (isset($_POST['profile_data'])) {

            // i dont think its with this funciton but i could be wrong
$update_profile->save_profile_date($_POST['cid'], $_POST['first'], $_POST['last'], $_POST['year'], $_POST['age'], $_POST['gender']); 

}
$error = $update_profile->the_msg; // error message


?>

where is the other function:

// Function gets the runner of the user
	function get_runner_data() {
		$this->get_user_CID();
		$uid = $_GET['cid'];
		$_SESSION['cid'] = $uid;
		$sql = sprintf("SELECT cid, user_id, first, last, year, age, gender FROM %s WHERE cid = $uid", PROFILE_TABLE, $this->uid);		
		$result = mysql_query($sql) or die (mysql_error());		
		if (mysql_num_rows($result) == 0) {
			$_SESSION['is_rec'] = false;					
			$this->the_msg = $this->extra_text(1); // There is no profile data at the moment				
		} else {
			$_SESSION['is_rec'] = true; // this session acts like an extra controle			
		while ($obj = mysql_fetch_object($result)) {
			$this->profile_id = $obj->cid;
			$this->first = $obj->first;
			$this->last = $obj->last;
			$this->year = $obj->year;
			$this->age = $obj->age;
			$this->gender = $obj->gender;


		}
	}
}

bpat1434

If you're not going to read the help I give you, then don't post.

You get an SQL error. So you need to debug your SQL first. One step is to write each command on its own line. That way when mySQL throws an error, it will tell you the line (of the SQL) it is throwing it on.

~Brett