php mysql help

[c1lonewolf]

Ok, so I'm helping a friend redevelope his website...problem is the previous programmers he had left everything in a shamble. It's currently using html, css, javascript, JQuery, AJAX, php & mysql. It has a lot of repeated coding which I've managed to break most of it down to individual files. However, they mutilated the template and I can barely understand their code. The database isn't that much better. A while ago, KH was teaching me easy ways to work with database input& output by using extract($row) method. I still have the scripts I was using. I'm looking to simplify things so is it possible to place a relatedID into another table when form is submited?

I working the new db:
Item Categories:
catid * catTitle * catDesc

Items:
itmId * itmTitle * itmDesc

Item Images:
ImgId * relItmId * ImgFileName * ImgPrmy(primary image 1/0)

ItemToCategories:
ItmCatID * relItmID * relCatId

theres more but I just need to know how I post itemID to item Images table when form is submitted. Hope I described this well enough. Also, it has to handle searches like dating sites do...country, state, city, radius of city, zip code, radius of zip code(ex: within 20 miles of).

I just need simple and this is an example of what I have to work with:

view item page

<?php
require_once('dh_index_header.php');
if (!isset($_GET["id"])) {
	die("no date!");
} else {
	$id = $_GET["id"];
	if (!is_numeric($id)) {
		die("no data!");
	}
}
$result = mysql_query("select a.Price,a.City,a.Country,a.Title,a.Description,a.Category,a.recdate,a.images,a.image2,a.image3,a.image4,a.imgWidth,a.imgHeight,a.ftype,b.stateName,c.id,c.cateName,a.email from ads_list as a LEFT OUTER JOIN sys_states as b ON b.id=a.State LEFT OUTER JOIN ads_cate as c ON c.id=a.Category where a.id=" . $id . ";") or die(mysql_error());
if (mysql_num_rows($result) <= 0) {
	die("data not found!");
} else {
	$rs = mysql_fetch_array($result);
	$Title = $rs["Title"];
	$Description = $rs["Description"];
	$State = $rs["stateName"];
	$City = $rs["City"];
	$Country = $rs["Country"];
	$Types = $rs["ftype"];
	$AdCategory = $rs["Category"];
	$CatID = $rs["id"];
	$Category = $rs["cateName"];
	$images = $rs["images"];
	$image2 = $rs["image2"];
	$image3 = $rs["image3"];
	$image4 = $rs["image4"];
	$imgWidth = $rs["imgWidth"];
	$imgHeight = $rs["imgHeight"];
	$Email = $rs["email"];
	$Price = $rs["Price"];
	// 20110330 - Added by Jason to display an appropriate message for price when it's a freebie! Added the "$" prefix because its added to the price when initially submitted to the db table.
	if ($Price == '$0.00') {
		$Price = "This item is FREE!";
	}
	//date("M-d,Y",$rs["recdate"]);
	$recDate = gmdate("M d, Y", strtotime($rs["recdate"]));
}
//echo print_r($images);
?>

<div id="adView"">
  <h2> <?php
echo stripslashes($Title);
?> >> [ <?php
echo $Category;
?> ]</h2>

<div class="adSpacer"></div>

<div class="ad_menu">
<div class="fbookrec">
<? phprequire('../dh_besystm_accts/facebookconnect.php'); ?>
</div>

<script type="text/javascript">
function printpage()
  {
  window.print()
  }
</script>

<ul id="ad_links"><li><a href="#" onclick="printpage()">Print</a></li><li><a href="dh_send_to_friend_form.php?sT=2&id=<?=$id?>&type=<?=$Types?>&cate=<?=$Category?>&country=<?=$Country?>&state=<?=$State?>&city=<?=$City?>">Send to Friend</a></li><li><a href="dh_contact_form.php?sT=2&id=<?=$id?>">Contact
        This Seller</a></li></ul>

</div>

<br style="clear: left"; />
  <div id="sideA" style="float: left;">
  
<div class="adDetls">
    <p><strong>Post Date:</strong> <?php
echo $recDate;
?></p>
    <p><strong>Country:</strong>
      <?php
if ($rs["Country"] == 'US')
	echo "US";
else {
	$result = mysql_query("select country from countries where id=" . $rs["Country"] . ";") or die(mysql_error());
	if (mysql_num_rows($result) <= 0) {
		echo("None");
	} else {
		$rs = mysql_fetch_array($result);
		echo $rs["country"];
	}
}
?>
    </p>
    <p><strong>State:</strong> <?php
echo $State;
?></p>
    <p><strong>City:</strong>
      <?php
if ($City == 0)
	echo "None";
else {
	$result = mysql_query("select cityName from sys_city where id=" . $City . ";") or die(mysql_error());
	if (mysql_num_rows($result) <= 0) {
		echo("None");
	} else {
		$rs = mysql_fetch_array($result);
		echo $rs["cityName"];
	}
}
?>
    </p>
    <p><strong>Category:</strong> <?php
echo $Category;
?></p>

</div>

    <p class="descBox" style=""><strong>Description:</strong><br />
      <?php
echo nl2br($Description);
?> 

<br /><br />
<strong>Price:</strong> <?php echo $Price; ?>


</p>

<br />


  </div>
  <!-- Similar Items START -->
  

  
  <div id="indexImgListings">
  <h3 style="text-align: center;">Similar Items</h3>

  <?php
$sql = "
(SELECT
a.id AS a_id,
a.Price,
a.City,
a.Country,
a.Title,
a.Category,
a.images AS a_image,
a.recdate,
a.imgWidth,
a.imgHeight,
a.ftype,
c.id,
c.cateName,
a.email,
'item' AS type FROM ads_list
AS a LEFT OUTER JOIN ads_cate
AS c ON c.id=a.Category WHERE to_days(now())<=(to_days(recdate)+14) AND a.Category = $CatID ORDER BY RAND() LIMIT 4)";
$result2 = mysql_query($sql) or die(mysql_error());
$count = 0;
while ($row = mysql_fetch_assoc($result2)) {
	if ($row["a_image"] != "") {
		$images2 = $row["a_image"];
		if (strlen($images2) > 0) {
			$maxWidth2 = 100;
			$maxHeight2 = 100;
			$img2 = getimagesize($upfileFolder . $images2);
			if ($img2[0] < $maxWidth2) {
				$maxWidth2 = $img2['width'];
			}
		}
		if ($row['type'] == "item") {
			echo "
		<div class='figure'>
		<div class='photo'>
  <a href='dh_ads_view.php?id=" . $row['a_id'] . "'>
  <img
  src=
  '" . $upfileFolder . $images2 . "'
  border=0  width='" . $maxWidth2 . "' height='" . $maxHeight2 . "'/></a></div>
  <p><a href='dh_ads_view.php?id=" . $row['a_id'] . "'>" . stripslashes(substr($row['Title'], 0, 30)) . "</a></p>
  </div> <br style='clear: left' />";
		}
		$count++;
	}
	
	//echo print_r($row);
}
?>  

  </div>

  <br style='clear: left' />
  <div id="picsBox">
    <p>Click an image to enlarge.</p>
    <?php
if (strlen($images) > 0) {
	$maxWidth = 100;
	$img = getimagesize($upfileFolder . $images);
	if ($img[0] < $maxWidth) {
		$maxWidth = $img['width'];
	}
?>
    <ul>
      <li> <a rel="shadowbox" href="<?php
      echo $upfileFolder . $images;
?>" target="_blank"> <img src="<?php
      echo $upfileFolder . $images;
?>" border="0" width="<?php
      echo $maxWidth;
?>"/> </a> </li>
      <?php
}
if (strlen($image2) > 0) {
	$maxWidth = 100;
	$img = getimagesize($upfileFolder . $image2);
	if ($img[0] < $maxWidth) {
		$maxWidth = $img['width'];
	}
?>
      <li> <a rel="shadowbox" href="<?php
          echo $upfileFolder . $image2;
?>" target="_blank"> <img src="<?php
          echo $upfileFolder . $image2;
?>" border="0" width="<?php
          echo $maxWidth;
?>" /> </a></li>
      <?php
}
if (strlen($image3) > 0) {
	$maxWidth = 100;
	$img = getimagesize($upfileFolder . $image3);
	if ($img[0] < $maxWidth) {
		$maxWidth = $img['width'];
	}
?>
      <li><a rel="shadowbox" href="<?php
              echo $upfileFolder . $image3;
?>" target="_blank"> <img src="<?php
              echo $upfileFolder . $image3;
?>" border="0" width="<?php
              echo $maxWidth;
?>" /> </a></li>
      <?php
}
if (strlen($image4) > 0) {
	$maxWidth = 100;
	$img = getimagesize($upfileFolder . $image4);
	if ($img[0] < $maxWidth) {
		$maxWidth = $img['width'];
	}
?>
      <li><a rel="shadowbox" href="<?php
                  echo $upfileFolder . $image4;
?>" target="_blank"> <img src="<?php
                  echo $upfileFolder . $image4;
?>" border="0" width="<?php
                  echo $maxWidth;
?>"  /> </a></li>
      <?php
}
?>
    </ul>
  </div>
  </div>
  <?php
require_once('dh_footer.php');
?>

[fivesidecube]

Hi c1lonewolf,

I'm having some problems understanding what you're after. I think that what you're after is to learn the ImgId for the new row inserted into the Item Images table in the database? IE. When your user hits submit, the PHP script executes an 'insert into <Images Tables>' SQL instruction that automatically assigns a value for the ImgId field.

Is this correct? If so, then you need to be looking at the mysql_insert_id function. Straight after the insert instruction, the mysql_insert_id function will give you the allocated id for the insert instruction. This can be used in following insert statements allowing you to connect the different rows in the different tables.

If this is incorrect, please can you post the actual table structure for the Item Images table. You can get this by running the SQL statement 'desc <Item Images>;' replacing the <Item Images> with the name of the table. Then describe how the table(s) change when the user hits the submit button and where you're have problems.