Mysql syntax error: inserting image location

PHP
1

Hi there,

Thanks for taking a moment to look at my issue. I am working on registration / login system with image upload for user avatar. I feel I am pretty close but am getting an error in my mysql syntaxthat inserts the image location into the database. The error reads “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 ‘’ at line 2.” Everything else seems to be working okay.

FYI: The directory to store images is “user_pic,” the table name is “myMembers,” and the column name is “pic_location.”

Here is the php:


<?php
include_once("scripts/checkuserlog.php");
?>
<?php
// This code runs only if the username is posted
if (isset ($_POST['username'])){
	 
	 $username = preg_replace('#[^A-Za-z0-9]#i', '', $_POST['username']); // filter everything but letters and numbers
	 $gender = preg_replace('#[^a-z]#i', '', $_POST['gender']); // filter everything but lowercase letters
	 $b_m = preg_replace('#[^0-9]#i', '', $_POST['birth_month']); // filter everything but numbers
	 $b_d = preg_replace('#[^0-9]#i', '', $_POST['birth_day']); // filter everything but numbers
	 $b_y = preg_replace('#[^0-9]#i', '', $_POST['birth_year']); // filter everything but numbers
	 $email1 = $_POST['email1'];
	 $email2 = $_POST['email2'];
	 $pass1 = $_POST['pass1'];
	 $pass2 = $_POST['pass2'];
	 $user_pic = $_FILES['user_pic'];
	 
	 $humancheck = $_POST['humancheck'];
	
	 $email1 = stripslashes($email1); 
	 $pass1 = stripslashes($pass1); 
	 $email2 = stripslashes($email2);
	 $pass2 = stripslashes($pass2); 
	 
	 $email1 = strip_tags($email1);
	 $pass1 = strip_tags($pass1);
	 $email2 = strip_tags($email2);
	 $pass2 = strip_tags($pass2);
	
	 // Connect to database
	 include_once "scripts/connect_to_mysql.php";
	 $emailCHecker = mysql_real_escape_string($email1);
	 $emailCHecker = str_replace("`", "", $emailCHecker);
	 // Database duplicate username check setup for use below in the error handling if else conditionals
	 $sql_uname_check = mysql_query("SELECT username FROM myMembers WHERE username='$username'"); 
	 $uname_check = mysql_num_rows($sql_uname_check);
	 // Database duplicate e-mail check setup for use below in the error handling if else conditionals
	 $sql_email_check = mysql_query("SELECT email FROM myMembers WHERE email='$emailCHecker'");
	 $email_check = mysql_num_rows($sql_email_check);
	
	 // Error handling for missing data
	 if ((!$username) || (!$gender) || (!$b_m) || (!$b_d) || (!$b_y) || (!$email1) || (!$email2) || (!$pass1) || (!$pass2) || (!$user_pic)) { 
	
	 $errorMsg = 'ERROR: You did not submit the following required information:<br /><br />';
	
	 if(!$username){ 
	   $errorMsg .= ' * User Name<br />';
	 } 
	 if(!$gender){ 
	   $errorMsg .= ' * Gender: Confirm your sex.<br />';
	 } 	
	 if(!$b_m){ 
	   $errorMsg .= ' * Birth Month<br />';      
	 }
	 if(!$b_d){ 
	   $errorMsg .= ' * Birth Day<br />';        
	 } 
	 if(!$b_y){ 
	   $errorMsg .= ' * Birth year<br />';        
	 } 		
	 if(!$email1){ 
	   $errorMsg .= ' * Email Address<br />';      
	 }
	 if(!$email2){ 
	   $errorMsg .= ' * Confirm Email Address<br />';        
	 } 	
	 if(!$pass1){ 
	   $errorMsg .= ' * Login Password<br />';      
	 }
	 if(!$pass2){ 
	   $errorMsg .= ' * Confirm Login Password<br />';        
	 } 	
	 if(!$user_pic){ 
	   $errorMsg .= ' * Add a Profile Photo<br />';        
	 } 	
	
	 } else if ($email1 != $email2) {
			  $errorMsg = 'ERROR: Your Email fields below do not match<br />';
	 } else if ($pass1 != $pass2) {
			  $errorMsg = 'ERROR: Your Password fields below do not match<br />';	 
	 } else if (strlen($username) < 4) {
			   $errorMsg = "<u>ERROR:</u><br />Your User Name is too short. 4 - 20 characters please.<br />"; 
	 } else if (strlen($username) > 20) {
			   $errorMsg = "<u>ERROR:</u><br />Your User Name is too long. 4 - 20 characters please.<br />"; 
	 } else if ($uname_check > 0){ 
			  $errorMsg = "<u>ERROR:</u><br />Your User Name is already in use inside of our system. Please try another.<br />"; 
	 } else if ($email_check > 0){ 
			  $errorMsg = "<u>ERROR:</u><br />Your Email address is already in use inside of our system. Please use another.<br />"; 
	 } else if ($_FILES['user_pic']['size'] > 2000000 ){ 
              $errorMsg = "<u>ERROR:</u><br />Your image is too large.<br />"; 
              unlink($_FILES['user_pic']['tmp_name']); 
	 } else if (!preg_match("/\\.(gif|jpg|png|jpeg)$/i", $_FILES['user_pic']['name'])) { 
              $errorMsg = "<u>ERROR:</u><br />Your image is in an unacceptable format.<br />"; 
              unlink($_FILES['user_pic']['tmp_name']); 
     } else {
	 
	 
     // Error handling is ended, process the data and add member to database
	
     $email1 = mysql_real_escape_string($email1);
     $pass1 = mysql_real_escape_string($pass1);
	 
     // Add MD5 Hash to the password variable
     $db_password = md5($pass1); 
	 
     // Convert Birthday to a DATE field type format(YYYY-MM-DD) out of the month, day, and year supplied 
     $full_birthday = "$b_y-$b_m-$b_d";

     // GET USER IP ADDRESS
     $ipaddress = getenv('REMOTE_ADDR');
     
     //add the avatar
     $name = $_FILES['user_pic']['name'];
     $tmp_name = $_FILES['user_pic']['tmp_name'];
     $location = "user_pic/$name";
     move_uploaded_file($tmp_name, "user_pic/.$name");
	 
     // Add user info into the database table for the main site table
     $sql = mysql_query("INSERT INTO myMembers (username, gender, birthday, email, password, pic_location, ipaddress, sign_up_date) 
     VALUES('$username','$gender','$full_birthday','$email1','$db_password', '$location', '$ipaddress', now()")  
     or die (mysql_error());
 
     $id = mysql_insert_id();
	 
     // Create directory to hold each user's files(pics, MP3s, etc.)
     $newname = "image01.jpg";
     $place_file = move_uploaded_file( $_FILES['user_pic']['tmp_name'], "members/$id/".$newname);
     
     mysql_query("INSERT INTO myMembers (pic_location) VALUES ('$location')");
   
     include_once 'msgToUser.php'; 
     exit();

     }// Close else after duplication checks

} else { // if the form is not posted with variables, place default empty variables so no warnings or errors show
	  
	  $errorMsg = "";
      $username = "";
	  $gender = "";
	  $b_m = "";
	  $b_d = "";
	  $b_y = "";
	  $email1 = "";
	  $email2 = "";
	  $pass1 = "";
	  $pass2 = "";
	  $user_pic = "";
}

?>

I believe the offending query is “mysql_query(“INSERT INTO myMembers (pic_location) VALUES (‘$location’)”);” because the problem arose after adding this section. I also tried including the pic_location in prior query so that it read “$sql = mysql_query(“INSERT INTO myMembers (username, gender, birthday, email, password, pic_location, ipaddress, sign_up_date)
VALUES(‘$username’,‘$gender’,‘$full_birthday’,‘$email1’,‘$db_password’, ‘$location’, ‘$ipaddress’, now()”)
or die (mysql_error());” but that did not seem to work either…

This is the html portion if you’re interested:


<html>
<body>
<h4>Create your Account: </h4><h9>all fields required</h9>

<table class="table_f" width="100%" cellpadding="3">
<form action="register.php" method="post" enctype="multipart/form-data">
          <tr>
          <td colspan="2"><font color="#94A0D1"><?php print "$errorMsg"; ?></font></td>
          </tr>       
          <tr>
          <td><h11>User Name:</h11></td>
            <td><input name="username" type="text" class="formFields" id="username" value="<?php print "$username"; ?>" size="32" maxlength="20" />
          </tr>
          <tr>
            <td><h11>Gender:</h11></td>
            <td><label>        
            <input name="gender" style="color: #a2a2a2; font-family: 'light', Verdana; font-size: 11px; letter-spacing: 1px" type="radio" id="gender" value="m" checked="checked" />Male  
            <input type="radio" name="gender" id="gender" value="f" />Female
</label></td>
          </tr>
          <tr>
            <td><h11>Date of Birth: </h11></td>
            <td>
<select name="birth_month" class="formFields" id="birth_month">
<option value="<?php print "$b_m"; ?>"><?php print "$b_m"; ?></option>
<option value="01">January</option>
<option value="02">February</option>
<option value="03">March</option>
<option value="04">April</option>
<option value="05">May</option>
<option value="06">June</option>
<option value="07">July</option>
<option value="08">August</option>
<option value="09">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>
</select> 
<select name="birth_day" class="formFields" id="birth_day">
<option value="<?php print "$b_d"; ?>"><?php print "$b_d"; ?></option>
<option value="01">1</option>
<option value="02">2</option>
<option value="03">3</option>
<option value="04">4</option>
<option value="05">5</option>
<option value="06">6</option>
<option value="07">7</option>
<option value="08">8</option>
<option value="09">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
<option value="13">13</option>
<option value="14">14</option>
<option value="15">15</option>
<option value="16">16</option>
<option value="17">17</option>
<option value="18">18</option>
<option value="19">19</option>
<option value="20">20</option>
<option value="21">21</option>
<option value="22">22</option>
<option value="23">23</option>
<option value="24">24</option>
<option value="25">25</option>
<option value="26">26</option>
<option value="27">27</option>
<option value="28">28</option>
<option value="29">29</option>
<option value="30">30</option>
<option value="31">31</option>
</select> 
<select name="birth_year" class="formFields" id="birth_year">
<option value="<?php print "$b_y"; ?>"><?php print "$b_y"; ?></option>
<option value="2010">2010</option>
<option value="2009">2009</option>
<option value="2008">2008</option>
<option value="2007">2007</option>
<option value="2006">2006</option>
<option value="2005">2005</option>
<option value="2004">2004</option>
<option value="2003">2003</option>
<option value="2002">2002</option>
<option value="2001">2001</option>
<option value="2000">2000</option>
<option value="1999">1999</option>
<option value="1998">1998</option>
<option value="1997">1997</option>
<option value="1996">1996</option>
<option value="1995">1995</option>
<option value="1994">1994</option>
<option value="1993">1993</option>
<option value="1992">1992</option>
<option value="1991">1991</option>
<option value="1990">1990</option>
<option value="1989">1989</option>
<option value="1988">1988</option>
<option value="1987">1987</option>
<option value="1986">1986</option>
<option value="1985">1985</option>
<option value="1984">1984</option>
<option value="1983">1983</option>
<option value="1982">1982</option>
<option value="1981">1981</option>
<option value="1980">1980</option>
<option value="1979">1979</option>
<option value="1978">1978</option>
<option value="1977">1977</option>
<option value="1976">1976</option>
<option value="1975">1975</option>
<option value="1974">1974</option>
<option value="1973">1973</option>
<option value="1972">1972</option>
<option value="1971">1971</option>
<option value="1970">1970</option>
<option value="1969">1969</option>
<option value="1968">1968</option>
<option value="1967">1967</option>
<option value="1966">1966</option>
<option value="1965">1965</option>
<option value="1964">1964</option>
<option value="1963">1963</option>
<option value="1962">1962</option>
<option value="1961">1961</option>
<option value="1960">1960</option>
<option value="1959">1959</option>
<option value="1958">1958</option>
<option value="1957">1957</option>
<option value="1956">1956</option>
<option value="1955">1955</option>
<option value="1954">1954</option>
<option value="1953">1953</option>
<option value="1952">1952</option>
<option value="1951">1951</option>
<option value="1950">1950</option>
<option value="1949">1949</option>
<option value="1948">1948</option>
<option value="1947">1947</option>
<option value="1946">1946</option>
<option value="1945">1945</option>
<option value="1944">1944</option>
<option value="1943">1943</option>
<option value="1942">1942</option>
<option value="1941">1941</option>
<option value="1940">1940</option>
<option value="1939">1939</option>
<option value="1938">1938</option>
<option value="1937">1937</option>
<option value="1936">1936</option>
<option value="1935">1935</option>
<option value="1934">1934</option>
<option value="1933">1933</option>
<option value="1932">1932</option>
<option value="1931">1931</option>

</select> 
</div>
</td>
          </tr>                  
          <tr>
            <td><h11>Email Address: </h11></td>
            <td><input name="email1" type="text" class="formFields" id="email1" value="<?php print "$email1"; ?>" size="32" maxlength="48" /></td>
          </tr>
          <tr>
            <td><h11>Confirm Email: </h11></td>
            <td><input name="email2" type="text" class="formFields" id="email2" value="<?php print "$email2"; ?>" size="32" maxlength="48" /></td>
          </tr>
          <tr>
            <td><h11>Create Password: </h11></td>
            <td><input name="pass1" type="password" class="formFields" id="pass1" size="32" maxlength="16" />
          </tr>
          <tr>
            <td><h11>Confirm Password: </h11></td>
            <td><input name="pass2" type="password" class="formFields" id="pass2" size="32" maxlength="16" />
          </tr>
          <tr>
            <td><h11>Add Profile Photo: </h11></td>         
              <input type='hidden' name='MAX_FILE_SIZE' value='2000000'>
              <td width="521"><input name="user_pic" type="file" class="formFields" size="42" />
              50 kb max </td>
          </tr> 
          <tr>
            <td> </td>
            <td>
              <input type="submit" style="color: #a2a2a2; font-family: helvetica; font-size: 11px; letter-spacing: 1px" name="Submit" value="Register" />
            </td>
          </tr>
        </form>
      </table>
      <br /></td>
  </tr>
</table>
</body>
</html>

Thanks for any advice that will help me address this issue. I’m sure that the script could be better so any general comments on improving it are welcome, also.

Thanks a lot. I really appreciate any help.

nbewley

2

Do the following to see what the actual query being run is.

<?php
echo "INSERT INTO myMembers (pic_location) VALUES ('$location')";
die();

mysql_query("INSERT INTO myMembers (pic_location) VALUES ('$location')");
?>

Also, move_uploaded_file() returns true or false so it’ll probably be better to run the above INSERT only if the file is actually moved.

3

You seem to do some things twice:


move_uploaded_file($tmp_name, "user_pic/.$name");
...
$place_file = move_uploaded_file( $_FILES['user_pic']['tmp_name'], "members/$id/".$newname);

and


$sql = mysql_query("INSERT INTO myMembers (username, gender, birthday, email, password, pic_location, ipaddress, sign_up_date) 
     VALUES('$username','$gender','$full_birthday','$email1','$db_password', '$location', '$ipaddress', now()")  
     or die (mysql_error());
...
mysql_query("INSERT INTO myMembers (pic_location) VALUES ('$location')");

Why do you want to move the uploaded file twice? And why do you want to insert the location again, after having it inserted already in the first query?
And if you need the second query, then I think it should be an UPDATE, not an INSERT.

4

Wow,

This is great. Thanks so much guys for your informative and timely responses. I realized that I had missed a closing ‘)’ bracket in my query after: now())")

But, I’m really interested in the other comments:

In reference to webdev’s point about move_uploaded_file. Would it be, best, then, to structure it in an if…else statement so that it reads something like:


……
     // Create directory to hold each user's files(pics, MP3s, etc.)		
     mkdir("members/$id", 0755);	
     $newname = "image01.jpg";
     
     //add the avatar
     $name = $_FILES['user_pic']['name']; 
     $tmp_name = $_FILES['user_pic']['tmp_name'];
     $location = "user_pic/$name";
     $place = move_uploaded_file($tmp_name, "user_pic/.$name");
	 
     if ($place = !0) {
	 
		 // Add user info into the database table for the main site table
		 $sql = mysql_query("INSERT INTO myMembers (username, gender, birthday, email, password, pic_location, ipaddress, sign_up_date) 
		 VALUES('$username','$gender','$full_birthday','$email1','$db_password', '$location', '$ipaddress', now())")  
		 or die (mysql_error());

		 } else {
		 	echo "There was an error uploading your file";
		 	die();
……

Or is there another method that has proved successful for you?

Thanks a lot Guido, also, for pointing out those sloppy elements in my code. I guess it would be best to include everything in the single query:
$sql = mysql_query(“INSERT INTO myMembers (username, gender, birthday, email, password, pic_location, ipaddress, sign_up_date) VALUES(‘$username’,‘$gender’,‘$full_birthday’,‘$email1’,‘$db_password’, ‘$location’, ‘$ipaddress’, now())”) or die (mysql_error());.

Unless there is a better solution…I’m very interested in other ideas.

Thanks very much for the support. I really appreciate all of the comments!
nbewley

5

That’s basically it. You could simplify it a little with

<?php

if(move_uploaded_file($tmp_name, "user_pic/.$name")){

    // insert record in db

} else {

   //do something else
}


?>

And if for some reason the insertion fails, you should then delete the uploaded file from its final location so the data in the database matches what’s stored on your website.

6

Or if you prefer to automate that process then store the image in the database as well instead of just a link to it.

7

yes storing the image in the db is an option but then you could run into performance issues due to a bloated database.

I think it would be more efficient to store just the path to the image in the db and delete the uploaded file from disk if the record insertion failed -


<?php

$query = 'insert into............';

if(!mysql_query($query)) {
     unlink($path2image);
}

?>
8

Thanks a lot for the insights. I really appreciate all of your ideas.

After re-arranging the structure of my query to include the “if…else” for move_uploaded_file, I have encountered another small error: “Warning: mkdir() [function.mkdir]: File exists in ………/register.php on line 137” which corresponds with the mkdir function:

mkdir("members/$id", 0755);

Not sure why I am getting this now. I put the mkdir function before the move_uploaded_file if…else conditionality so that the script reads:


...
	 // Create directory(folder) to hold each user's files(pics, MP3s, etc.)	
     $id = mysql_insert_id();
     mkdir("members/$id", 0755);
     mkdir("members/$id/pictures", 0755, true);	

     $newname = "avatar.jpg";
     
     //add the avatar
     $name = $_FILES['user_pic']['name'];
	 $tmp_name = $_FILES['user_pic']['tmp_name'];
     $location = "members/$id/$newname";
	 move_uploaded_file($tmp_name, "members/$id/pictures/".$newname);
	 
	 if(move_uploaded_file($tmp_name, "user_pic/.$name")){

		 // Add user info into the database table for the main site table
		 $sql = mysql_query("INSERT INTO myMembers (username, gender, birthday, email, password, pic_location, ipaddress, sign_up_date) 
		 VALUES('$username','$gender','$full_birthday','$email1','$db_password', '$location', '$ipaddress', now())")  
		 or die (mysql_error());
		 
		 } else {
			 echo "There was an error uploading your file";
			 unlink($tmp_name); 
			 die();
			 
	 	 }
...

The only directory that exists in “members” is “0” which contains the default settings for the user information. Before, the script was taking the $id=mysql_insert_id(); and creating a new user value based on the order in which they had registered.

Perhaps putting “mysql_insert_id();” before I have actually used a query to insert information has prevented me from generating an id? Is there any way that I can fix this to maintain a similar structure?

Thanks again for taking the time to share your knowledge.
nbewley

9

File exists

Isn’t the error message telling you the directory already exists?

10

Thanks for your response. Yes, correct. The directory “0” exists in “members”.

However, I’m trying to create a new directory based on the mysql_insert_id(); but the id is only being entered as 0, not as an incremental value. Before, I had placed the call for mysql_insert_id(); after the query had been run and everything worked fine. But now that the insert_id(); function is before the query it doesn’t work and seems to only be inserting a value of “0.”

I would like to put the insert_id(); before the query because it seems like the proper order of the tasks is to: create the directory, then place the file in said directory, then query the mysql database and insert the image location. I’m not sure what I am missing…

Thanks,
nbewley

11

You can’t get the inserted id from mysql if nothing has been inserted yet. If you want to use the id in the creating of the dir, then the proper order is:
query the mysql database and insert the image location, then create the directory, then place the file in said directory

12

Then as guido is alluding to, you’ll have to change the order you are doing things.

  1. Insert the record in the database. If insert fails, go to 6.

  2. If insert is successful, get the latest id.

  3. create directory using id in the path

  4. move file to directory in 3.

  5. if move is unsuccessful, delete record from 1.

  6. end