help pulling record id to other table in single post

Nomaad

I am posting a form that has property data in one table and image paths in another table. I have a field in the photo table to pass the property's record id to. So that way I can then get them back together later in the display page. Is there anyway to pass that record id to that other table if both are being posted at the same time?

Is it a MySQL thing?

Thanks in advance. Here some of the code:

//begin image upload

if(isset($_GET['photo_id']))
{

$id      = $_GET['photo_id'];
$query   = "SELECT name, type, size, path FROM prop_photos WHERE photo_id = '$id'";
$result  = mysql_query($query) or die('Error, query failed');
list($name, $type, $size, $filePath) = mysql_fetch_array($result);

header("Content-Disposition: attachment; filename=$name");
header("Content-type: $type");
header("Content-length: $size");

readfile($filePath);

exit;

}

$uploadDir = 'prop_photos/';

if(isset($POST['upload']))
{
$fileName = $FILES['userfile']['name'];
$tmpName = $FILES['userfile']['tmp_name'];
$fileType = $FILES['userfile']['type'];
$fileSize = $_FILES['userfile']['size'];

$filePath = $uploadDir . $fileName;

$result    = move_uploaded_file($tmpName, $filePath);
if (!$result) {
    echo "You did not select an image so a default \"No Picture\" box will be shown. You can add a picture later by editing the agents profile.";

}


if(!get_magic_quotes_gpc())
{
    $fileName  = addslashes($fileName);
    $filePath  = addslashes($filePath);
}

$query = "INSERT INTO prop_photos (photo_id, prop_id, name, type, size, path) ".
"VALUES ('$id', '$prop_id', '$fileName', '$fileType', '$fileSize', '$filePath')";

		 mysql_query($query) or die('Error, query failed : ' . mysql_error());

}
//end image upload

// begin property record insert
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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 . "'" : "CURRENT_DATE()";
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"] == "form1")) {
$insertSQL = sprintf("INSERT INTO properties (prop_id, agent_id, prop_status, prop_type, prop_name, price, address, city, county, zipcode, date_updated, occ_rate, g_sqft, usable_area, parking, price_sqft, yr_built, utilities, freestanding, description) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
GetSQLValueString($POST['prop_id'], "int"),
GetSQLValueString($POST['agent_id'], "int"),
GetSQLValueString($POST['prop_status'], "text"),
GetSQLValueString($POST['prop_type'], "text"),
GetSQLValueString($POST['prop_name'], "text"),
GetSQLValueString($POST['price'], "text"),
GetSQLValueString($POST['address'], "text"),
GetSQLValueString($POST['city'], "text"),
GetSQLValueString($POST['county'], "text"),
GetSQLValueString($POST['zipcode'], "text"),
GetSQLValueString($POST['date_updated'], "date"),
GetSQLValueString($POST['occ_rate'], "text"),
GetSQLValueString($POST['g_sqft'], "text"),
GetSQLValueString($POST['usable_area'], "text"),
GetSQLValueString($POST['parking'], "text"),
GetSQLValueString($POST['price_sqft'], "text"),
GetSQLValueString($POST['yr_built'], "text"),
GetSQLValueString($POST['utilities'], "text"),
GetSQLValueString($POST['freestanding'], "text"),
GetSQLValueString($POST['description'], "text"));

$Result1 = mysql_query($insertSQL) or die(mysql_error());

$insertGoTo = "index.php?prop_type=rent";
if (isset($SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}
//end property data
?>

visualAd

Yes, it is an SQL thing. I am assuming that the properties table uses the ID in a one to one relationship with the pictures tables i.e: each record in the pictures table, contains one corresponding record in the properties table.

If so you can join the tables using SQL:

SELECT 
    name, type, size, path 
FROM 
    prop_photos, path_photos 
WHERE 
    prop_photos.photo_id=path_photos.photo_id AND photo_id = '$id';

Nomaad

But I am trying to insert.

I actually think I have it figured out. It is working now. I'll keep an eye on the DB to make sure it doesn't start spewing funky numbers . . . 🙂

//look around this section to see what I added
GetSQLValueString($POST['parking'], "text"),
GetSQLValueString($POST['price_sqft'], "text"),
GetSQLValueString($POST['yr_built'], "text"),
GetSQLValueString($POST['utilities'], "text"),
GetSQLValueString($POST['freestanding'], "text"),
GetSQLValueString($POST['description'], "text"));

$Result1 = mysql_query($insertSQL) or die(mysql_error());
//added this right after my first insert statement before MySQL could let go of it.
$mysql_id = mysql_insert_id();

//end text insertion
//Tried this with the last insert id call but couldn't get it to work
//$mysql_id = mysql_query("SELECT LAST_INSERT_ID()");
//$pid = mysql_fetch_row($mysql_id);

//begin image upload
$uploadDir = 'prop_photos/';

if(isset($POST['upload']))
{
$fileName = $FILES['userfile']['name'];
$tmpName = $FILES['userfile']['tmp_name'];
$fileType = $FILES['userfile']['type'];
$fileSize = $_FILES['userfile']['size'];

$filePath = $uploadDir . $fileName;

$result    = move_uploaded_file($tmpName, $filePath);
if (!$result) {
    echo "You did not select an image so a default \"No Picture\" box will be shown. You can add a picture later by editing the agents profile.";

}


if(!get_magic_quotes_gpc())
{
    $fileName  = addslashes($fileName);
    $filePath  = addslashes($filePath);
}

$query = "INSERT INTO prop_photos (photo_id, prop_id, name, type, size, path) ".
"VALUES ('$id', '$mysql_id', '$fileName', '$fileType', '$fileSize', '$filePath')";
mysql_query($query) or die('Error, query failed : ' . mysql_error());

Nomaad

Oops meant to say thanks and that I added in the VALUES string of the second insert '$mysql_id'

visualAd

Sorry, having read the post again, I see that is what you are asking. However, it is easily miss interpreted (as just demonstrated). When you post, try to avoid posting all your code, becuase it can put people off helping you and be as specific as possible. 🙂

Glad you got it sorted.

Also, use

 and [code] tags, as they preserve whitespace in your code and make it easier to read.