Inserting record into database.

btr4 · June 17, 2010

Hello again, and sorry for so many questions. I'm creating a staff management page, where you can insert (so far) the staff member's name, their biography, and a picture. So far, inserting the bio and name works fine, but I can't get the picture to upload, or have the filename show up in the database.

Initially, I had a separate form for uploading a picture, and it worked, though I didn't have it set up to add the file to the database. I'm trying to condense the forms into one for simplicity for the user.

Here's the code I have for inserting the information into the database:

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "addstaff")) {
  $insertSQL = sprintf("INSERT INTO cms_staff (staffname, staffbio, staffpic) VALUES (%s, %s, %s)",
                       GetSQLValueString($_POST['staffname'], "text"),
                       GetSQLValueString($_POST['staffbio'], "text"),
                       GetSQLValueString($_POST['staffpic'], "text"));

  mysql_select_db($database_hcc, $hcc);
  $Result1 = mysql_query($insertSQL, $hcc) or die(mysql_error());

  $insertGoTo = "upload_staff.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];

The $insertGoTo will then try to process the following script (upload_staff.php):

<?php
if ((($_FILES["file"]["type"] == "image/gif")
|| ($_FILES["file"]["type"] == "image/jpeg")
|| ($_FILES["file"]["type"] == "image/pjpeg"))
&& ($_FILES["file"]["size"] < 70000))
  {
  if ($_FILES["file"]["error"] > 0)
    {
    echo "Return Code: " . $_FILES["file"]["error"] . "<br />";
    }
  else
    {
    echo "Upload: " . $_FILES["file"]["name"] . "<br />";
    echo "Type: " . $_FILES["file"]["type"] . "<br />";
    echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb<br />";
    echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br />";

    if (file_exists("../images/" . $_FILES["file"]["name"]))
      {
      echo $_FILES["file"]["name"] . " already exists. ";
      }
    else
      {
      move_uploaded_file($_FILES["file"]["tmp_name"],
      "../images/" . $_FILES["file"]["name"]);
      echo "Stored in: " . "../images/" . $_FILES["file"]["name"];
      }
  }
}
else
  {
  echo "Invalid file";
  }
$insertGoTo = "success.html";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
?>

My guess is that after upload_staff.php loads, it no longer has the values from the form, so has nothing to upload/move/show.

Any ideas on how to combine the upload script with the database inserting script? And how do I resolve the issue of the picture name not being put into the database?

mrMarcus · June 17, 2010

Your logic is wrong. You are inserting into the db before you are uploading the image. You don't have to be a PHP pro to realize that's not going to work.

Do all of your validation, file uploading, etc., first. Then, you can execute your queries based on valid form data, and handle invalid form data appropriately (by not executing a query).

btr4 · June 17, 2010

Yeah, that's what I thought the issue may be. Now, is it possible to have a require_once pointing to the upload script, then carry on with the validation/inserting?

mrMarcus · June 17, 2010

Of course. Just remember to keep track of the exact image location (path to image), which appears it would be:

../images/FILENAME_HERE.extension

and insert that into your query.

btr4 · June 17, 2010

Ok, so just to be clear:

On the form, I want the post action to run the upload script first. Then, I want the database insertion to occur.

So, something like this (assuming the post action is to run this):

/* Session validation goes here */
require_once ('../admin/upload_staff.php')
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "addstaff")) {
  $insertSQL = sprintf("INSERT INTO cms_staff (staffname, staffbio, staffpic) VALUES (%s, %s, %s)",
                       GetSQLValueString($_POST['staffname'], "text"),
                       GetSQLValueString($_POST['staffbio'], "text"),
                       GetSQLValueString($_POST['staffpic'], "text"));

  mysql_select_db($database_hcc, $hcc);
  $Result1 = mysql_query($insertSQL, $hcc) or die(mysql_error());

  $insertGoTo = "success.html";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}


?>

Now, I'm pretty sure that will upload the picture and post the staff name and bio into the database, but the picture location won't be put in.

I'm a little confused as to how to get the location to insert.

mrMarcus · June 17, 2010

The script you're using is very sloppy and confusing for newbies.

Completely untested:

<?php
if (isset($_POST['submit']))
{
if (isset($_POST['staffname']) && !empty($_POST['staffname'])) {
	$staffname = mysql_real_escape_string($_POST['staffname']);
}
else {
	$errors['staffname'] = 'Please enter a staff name.';
}

if (isset($_POST['staffbio']) && !empty($_POST['staffbio'])) {
	$staffbio = mysql_real_escape_string($_POST['staffbio']);
}
else {
	$errors['staffbio'] = 'Please enter a staff bio.';
}

//upload images;
if (is_array($_FILES))
{
	//path to image; no trailing slash;
	$img_dir = 'path_to_img_folder';

	//being upload;
	if (count($_FILES) > 0)
	{
		//check if file is uploaded;
		if (is_uploaded_file($_FILES['staffpic']['tmp_name']))
		{
			//check file size;
			if ($_FILES['staffpic']['size'] > 0)
			{
				//get mime;
				$image_info = getimagesize($_FILES['staffpic']['tmp_name']);

				//file type ok;
				if (preg_match('/image\/(jpg|jpeg|gif|png)/si', $image_info['mime']))
				{
					//get file extension;
					$ext = pathinfo($_FILES['staffpic']['name'], PATHINFO_EXTENSION);

					//create mainPic var;
					$staffpic =  strtolower(preg_replace('/([^a-zA-Z0-9\_\-\.]+)/', '-', $_FILES['staffpic']['name']) .'.'. strtolower($ext));

					//move file;
					if (!@move_uploaded_file($_FILES['staffpic']['tmp_name'], $img_dir .'/'. $staffpic)) {
						$errors['staffpic'] = 'File could not be copied.';
					}
				}
				else {
					$errors['staffpic'] = 'Incorrect file type.';
				}
			}
			else {
				$errors['staffpic'] = 'File is empty.';
			}
		}
		else {
			$errors['staffpic'] = 'There was a problem uploading the file.';
		}
	}
	else {
		$errors['staffpic'] = 'No file was found.';
	}
}
else {
	//remove this else{} if file upload is not mandatory;
	$errors['staffpic'] = 'Please upload a file.';
}

if (!is_array($errors))
{
	$sql = sprintf("
		insert into `cms_staff` (`staffname`, `staffbio`, `staffpic`)
		values ('%s', '%s', '%s')
	", $staffname, $staffbio, $staffpic);

	if ($result = @mysql_query($sql)) {
		$message = 'Successful insert.';
	}
	else {
		$message = 'Insert failed. Quick, do something.';
	}
}
}
echo (isset($message) ? $message .'<br />' : '');
?>
<form action="" method="post" enctype="multipart/form-data">
<?php echo (isset($errors['staffname']) ? $errors['staffname'] .'<br />' : ''); ?>
Name: <input type="text" name="staffname" /><br />
<?php echo (isset($errors['staffbio']) ? $errors['staffbio'] .'<br />' : ''); ?>
Bio: <input type="textarea" name="staffbio" /><br />
<?php echo (isset($errors['staffpic']) ? $errors['staffpic'] .'<br />' : ''); ?>
Image: <input type="file" name="staffpic" /><br />
<input type="submit" name="submit" />
</form>

btr4 · June 20, 2010

I tried the code above to no avail, but I did some research, and came up with something like this:

<?php require_once('../Connections/hcc.php'); ?>

<?php

if ((($_FILES["file"]["type"] == "image/gif")
|| ($_FILES["file"]["type"] == "image/jpeg")
|| ($_FILES["file"]["type"] == "image/pjpeg"))
&& ($_FILES["file"]["size"] < 70000))
  {
  if ($_FILES["file"]["error"] > 0)
    {
    echo "Return Code: " . $_FILES["file"]["error"] . "<br />";
    }
  else
    {
    echo "Upload: " . $_FILES["file"]["name"] . "<br />";
    echo "Type: " . $_FILES["file"]["type"] . "<br />";
    echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb<br />";
    echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br />";

    if (file_exists("../admin/images/" . $_FILES["file"]["name"]))
      {
      echo $_FILES["file"]["name"] . " already exists. ";
      }
    else
      {
      move_uploaded_file($_FILES["file"]["tmp_name"],
      "../admin/images/" . $_FILES["file"]["name"]);
      }

?>



<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  $updateSQL = sprintf("UPDATE cms_staff SET staffpic=%s WHERE staffid=%s",
                       GetSQLValueString($_POST['staffpic'], "text");
				   
  mysql_select_db($database_hcc, $hcc);
  $Result1 = mysql_query($updateSQL, $hcc) or die(mysql_error());

  $updateGoTo = "staff_cp.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
    $updateGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: staff_cp.php", $updateGoTo));
}

$colname_rsStaff = "-1";
if (isset($_GET['id'])) {
  $colname_rsStaff = $_GET['id'];
}
mysql_select_db($database_hcc, $hcc);
$query_rsStaff = sprintf("SELECT * FROM cms_staff WHERE staffid = %s", GetSQLValueString($colname_rsStaff, "int"));
$rsStaff = mysql_query($query_rsStaff, $hcc) or die(mysql_error());
$row_rsStaff = mysql_fetch_assoc($rsStaff);
$totalRows_rsStaff = mysql_num_rows($rsStaff);
?>

<?php
mysql_free_result($rsStaff);
?>

When I click 'upload' on the form, I'm returned with an error: Parse error: syntax error, unexpected ';' in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\HCC\admin\test.php on line 74, which most likely concerns the following block:

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  $updateSQL = sprintf("UPDATE cms_staff SET staffpic=%s WHERE staffid=%s",
                       GetSQLValueString($_POST['staffpic'], "text");

Am I at least on the right track?

Sign In

Inserting record into database.

Recommended Posts

btr4

Link to comment

Share on other sites

mrMarcus

Link to comment

Share on other sites

btr4

Link to comment

Share on other sites

mrMarcus

Link to comment

Share on other sites

btr4

Link to comment

Share on other sites

mrMarcus

Link to comment

Share on other sites

btr4

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information