two tables 1 id?

Hello!

I am trying to a build a CMS for my first big php project, I have had a lot of difficulty with adding images but finaally got something along with dynamic text built using dream weaver.

I have built it so that the image is a separate table from the text, but i would like the images to be set to the primary key of the text table, so they display together.

heres the code

add_info.php

Code:

<?php require_once('Connections/conn_addnews.php'); ?>
<?php
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 . "'" : "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"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO addnews (news_head, news_body) VALUES (%s, %s)",
                       GetSQLValueString($_POST['textfield'], "text"),
                       GetSQLValueString($_POST['textarea'], "text"));

  mysql_select_db($database_conn_addnews, $conn_addnews);
  $Result1 = mysql_query($insertSQL, $conn_addnews) or die(mysql_error());

  $insertGoTo = "view.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}
?><form action="<?php echo $editFormAction; ?>" method="POST" enctype="multipart/form-data" name="form1">
  <label>headine:
  <input type="text" name="textfield">
  </label>
  <p>
    <label>body:
    <textarea name="textarea" rows="10"></textarea>
    </label>
    <br>
  </p>
  <p>
    <label>image:
    <input name="userfile" type="file" id="userfile">
    </label>
  </p>
  <p>
    <label>
    <input type="submit" name="Submit" value="Submit">
    </label>
  </p>
  <input type="hidden" name="MM_insert" value="form1">
</form>
<p>
  <?php #--------------------------------------------------#
#DB CONNECT
#--------------------------------------------------#
$host = $hostname_conn_addnews;
$user = $username_conn_addnews;
$pass = $password_conn_addnews;
$db = $database_conn_addnews;
$table= "image";

//$conn = mysql_connect($host, $user, $pass)
$conn = $conn_addnews
OR DIE (mysql_error());
@mysql_select_db ($db, $conn) OR DIE (mysql_error());

#--------------------------------------------------#
#ON SUBMIT WITH A FILE CHOSEN
#--------------------------------------------------#
if ($_FILES)
{
#ALLOWED FILE TYPES
$image_types = Array (
"image/bmp",
"image/jpeg",
"image/pjpeg",
"image/gif",
"image/x-png",
"application/octet-stream");

#IF THE FILE UPLOADS SET USERFILE AND BUILD VARIABLES
if (is_uploaded_file ($_FILES['userfile']['tmp_name']))
{
$userfile = addslashes (fread
(fopen($_FILES["userfile"]["tmp_name"], "r"),
filesize($_FILES["userfile"]["tmp_name"])));
$file_name = $_FILES["userfile"]["name"];
$file_size = $_FILES["userfile"]["size"];
$file_type = $_FILES["userfile"]["type"];

#IF THE FILE UPLOADED IS IN THE ALLOWED FILE TYPES ARRAY UPLOAD IT
if (in_array (strtolower ($file_type), $image_types))
{
$sql = "INSERT INTO ".$table." "
. "(image_type, image, image_size, image_name, image_date) ";
$sql.= "VALUES (";
$sql.= "'{$file_type}', '{$userfile}', '{$file_size}', '{$file_name}', NOW())";
@mysql_query ($sql, $conn);
Header("Location:".$_SERVER["PHP_SELF"]);
exit();
}
}
}
#--------------------------------------------------#
#FUNCTIONS
#--------------------------------------------------#

#RETURNS FILESIZE WITH APPROPRIATE NOTATION
function get_filesize ($dsize)
{
if (strlen($dsize) <= 9 && strlen($dsize) >= 7)
{
$dsize = number_format($dsize / 1048576,1);
return "$dsize MB";
}
elseif (strlen($dsize) >= 10)
{
$dsize = number_format($dsize / 1073741824,1);
return "$dsize GB";
}
else
{
$dsize = number_format($dsize / 1024,1);
return "$dsize KB";
}
}
?>
</p>

and view.php, the page in question

Code:

<table height="128" border="1">
  <tr>
    <td>image_id</td>
    <td>news_head</td>
    <td>news_body</td>
  </tr>
<?php require_once('Connections/conn_addnews.php'); ?>

<?php
$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
  $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;
?>

<?php
mysql_select_db($database_conn_addnews, $conn_addnews);
$query_Recordset1 = "SELECT * FROM addnews";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $conn_addnews) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
?>

<?php do { ?>
    <tr>
      <td><?php echo $row_Recordset1['image_id']; ?></td>
      <td><?php echo $row_Recordset1['news_head']; ?></td>
      <td><?php echo $row_Recordset1['news_body']; ?></td>
    </tr>
    <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>

<?php
if (isset($_GET['totalRows_Recordset1'])) {
  $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
  $all_Recordset1 = mysql_query($query_Recordset1);
  $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;

mysql_free_result($Recordset1);
?>
<?php
#--------------------------------------------------#
#DECISION STRUCTURE FOR VIEWING/REMOVING A FILE
#--------------------------------------------------#
//if ($_GET)
//{
//#SET VARIABLES FOR IMAGE_ID AND ACTION
//$iid = $_GET['iid'];
//$act = $_GET['act'];
//
//#SWITCH CASE FOR DECIDING WHAT ACTION IS HAPPENING
//switch ($act)
//{
//case 'view':
//
//$sql = "select * from ".$table." where image_id=" .$iid;
//$result = mysql_query($sql,$conn);
//if (mysql_num_rows ($result)>0)
//{
//$row = @mysql_fetch_array($result);
//$image_type = $row["image_type"];
//$image = $row["image"];
//Header("Content-type: $image_type");
//print $image;
//exit(); #ADD SO THE GETIMAGESIZE() FUNCTION WORKS PROPERLY
//}
//break;
//
//case 'rem':
//$sql = "DELETE FROM ".$table." WHERE image_id=" .$iid;
//@mysql_query ($sql, $conn);
//Header("Location:".$_SERVER["PHP_SELF"]);
//exit();
//break;
//
//default:
//print "<img src=\"image.php?iid=$iid\">";
//break;
//}
//}

#--------------------------------------------------#
#FUNCTIONS
#--------------------------------------------------#

#RETURNS FILESIZE WITH APPROPRIATE NOTATION
function get_filesize ($dsize)
{
if (strlen($dsize) <= 9 && strlen($dsize) >= 7)
{
$dsize = number_format($dsize / 1048576,1);
return "$dsize MB";
}
elseif (strlen($dsize) >= 10)
{
$dsize = number_format($dsize / 1073741824,1);
return "$dsize GB";
}
else
{
$dsize = number_format($dsize / 1024,1);
return "$dsize KB";
}
}
?>


<?php
#--------------------------------------------------#
#DISPLAY IMAGES AND INFORMATION
#--------------------------------------------------#
$table = 'image';
$sql = "SELECT * FROM ".$table." ORDER BY 'image_id'";
$result = mysql_query ($sql, $conn_addnews);
$i=0;
$str='';

if (mysql_num_rows($result)>0)
{
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
#THIS VARIABLE IS AN ARRAY THAT HOLDS THE SIZE PROPERTIES OF THE IMAGE... USED TO GET THE DIMENSIONS
$size = getimagesize("http://".$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF']."?act=view&iid=".$row["image_id"]);

$i++;
$str .= $i.". ";
$str .= "<a href=\"index_master.php?act=view&iid=".$row["image_id"]."\">";
$str .= "<img border=\"0\" height=\"90\" width=\"100\" src=\"index_master.php?act=view&iid=".$row["image_id"]."\"></a> ";
$str .= "[Name: ".$row["image_name"]."] ";
$str .= "[WxH: ".$size[0]."x".$size[1]." ] ";
$str .= "[Date: ".$row["image_date"]."] ";
$str .= "[Size: ". get_filesize($row["image_size"])."] ";
$str .= "[<a href=\"index_master.php?act=rem&iid=".$row["image_id"]."\">Remove</a>]<br>";
}
print $str;
}

im thinking some thing like this but i dunno..
$master_id = "SELECT 'image_id' FROM 'add_news'"
$sql = "SELECT * FROM ".$table." ORDER BY '$master_id'";

[Free WordPress Themes]

why do you store the image name in a second table?

[Join us @ facebook]

Having a table for images may be better, because this way you will be able to use more than one images per page...

[Free WordPress Themes]

Having a table for images may be better, because this way you will be able to use more than one images per page...

I think he is using on image for each record (because of the dreamweaver code )

i have it so the text table and image table are using an identical primary key which is image_id in an attempt to link them...i admit i dont really know what im doing here..i need a shove in the right direction.

Yeah, i would like to add a feature of uploading multible files later, and also mp3's..

[Join us @ facebook]

The right logic on this is the opposite. I mean you should have a primary key in the content table, which will use to link to the table of images. This way you can have more than one images to each listing.

But still I am not sure where you are stucked with this....

right ok, is it cool to ask if u could write a bit of pseudo code on this,

something like this?
$master_id = "SELECT 'image_id' FROM 'textcontent_table"
$result = "SELECT * FROM ".imagetable" ORDER BY '$master_id'";....?


my brain is fryed over this, time for a guiness

[Join us @ facebook]

Ok, here is an example :

$master_id = "SELECT 'id' FROM 'textcontent_table";// <- This is the unique id of your content page
$result = "SELECT * FROM "imagetable" WHERE contentID = '$master_id'";....?


So you will need two tables with a structure like this :

textcontent_table
id, etc.
id in this table is primary key

imagetable
id, contentID, etc.

id here is primary key and contentID is index. In the contentID you will store the id of the content page that this photo is displayed.

Is it more clear now?

So
$master_id = "SELECT 'id' FROM 'textcontent_table";

Then,  "SELECT 'contentID' FROM 'imagetable' INPUT '$master_id;

then
$result = "SELECT * FROM "imagetable" WHERE contentID = '$master_id'";?

[Free WordPress Themes]

"INPUT" is not a MYsql command

Bollocks!


Dreamweaver spat this out

$recordID = $_GET['recordID'];
$query_DetailRS1 = "SELECT * FROM addnews WHERE image_id = $recordID"

so i figured
$sql = "SELECT * FROM image WHERE image_id = $recordID";
$result = mysql_query ($sql, $conn_addnews);

but Alas!
it doest display, no errors givin

[Free WordPress Themes]

do you have more than one image for each news article?

no, it should only be one, in the database i have an image at ID 1 and 2.

heres the new code i have for view.php

Code:

<?php require_once('../Connections/conn_addnews.php'); ?><?php
$maxRows_DetailRS1 = 10;
$pageNum_DetailRS1 = 0;
if (isset($_GET['pageNum_DetailRS1'])) {
  $pageNum_DetailRS1 = $_GET['pageNum_DetailRS1'];
}
$startRow_DetailRS1 = $pageNum_DetailRS1 * $maxRows_DetailRS1;

mysql_select_db($database_conn_addnews, $conn_addnews);
$recordID = $_GET['recordID'];
$query_DetailRS1 = "SELECT * FROM addnews WHERE image_id = $recordID";
$query_limit_DetailRS1 = sprintf("%s LIMIT %d, %d", $query_DetailRS1, $startRow_DetailRS1, $maxRows_DetailRS1);
$DetailRS1 = mysql_query($query_limit_DetailRS1, $conn_addnews) or die(mysql_error());
$row_DetailRS1 = mysql_fetch_assoc($DetailRS1);

if (isset($_GET['totalRows_DetailRS1'])) {
  $totalRows_DetailRS1 = $_GET['totalRows_DetailRS1'];
} else {
  $all_DetailRS1 = mysql_query($query_DetailRS1);
  $totalRows_DetailRS1 = mysql_num_rows($all_DetailRS1);
}
$totalPages_DetailRS1 = ceil($totalRows_DetailRS1/$maxRows_DetailRS1)-1;
?>

<?php
  #--------------------------------------------------#
#FUNCTIONS
#--------------------------------------------------#

#RETURNS FILESIZE WITH APPROPRIATE NOTATION
function get_filesize ($dsize)
{
if (strlen($dsize) <= 9 && strlen($dsize) >= 7)
{
$dsize = number_format($dsize / 1048576,1);
return "$dsize MB";
}
elseif (strlen($dsize) >= 10)
{
$dsize = number_format($dsize / 1073741824,1);
return "$dsize GB";
}
else
{
$dsize = number_format($dsize / 1024,1);
return "$dsize KB";
}
}
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Untitled Document</title>
</head>

<body>

<p>&nbsp;</p>
<table border="1" align="center">
 
  <tr>
    <td>image_id</td>
    <td><?php echo $row_DetailRS1['image_id']; ?> </td>
  </tr>
  <tr>
    <td>news_head</td>
    <td><?php echo $row_DetailRS1['news_head']; ?> </td>
  </tr>
  <tr>
    <td>news_body</td>
    <td><?php echo $row_DetailRS1['news_body']; ?>

  </tr>
  <tr>
  <td>image</td>
  <td><?php
#--------------------------------------------------#
#DISPLAY IMAGES AND INFORMATION
#--------------------------------------------------


$sql = "SELECT * FROM image WHERE image_id = $recordID";
$result = mysql_query ($sql, $conn_addnews);
$i=0;
$str='';

if (mysql_num_rows($result)>0)
{
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
#THIS VARIABLE IS AN ARRAY THAT HOLDS THE SIZE PROPERTIES OF THE IMAGE... USED TO GET THE DIMENSIONS
$size = getimagesize("http://".$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF']."?act=view&iid=".$row["image_id"]);

$i++;
$str .= $i.". ";
$str .= "<a href=\"view.php?act=view&iid=".$row["image_id"]."\">";
$str .= "<img border=\"0\" height=\"90\" width=\"100\" src=\"view.php?act=view&iid=".$row["image_id"]."\"></a> ";
//$str .= "[Name: ".$row["image_name"]."] ";
//$str .= "[WxH: ".$size[0]."x".$size[1]." ] ";
//str .= "[Date: ".$row["image_date"]."] ";
//$str .= "[Size: ". get_filesize($row["image_size"])."] ";
//$str .= "[<a href=\"index_master.php?act=rem&iid=".$row["image_id"]."\">Remove</a>]<br>";
}
print $str;
}

?><?php  
  <?php mysql_free_result($master_detail);
?> <?php */?>
</td></td>
</table>

</body>
</html><?php
mysql_free_result($DetailRS1);
?>

I'm guesing mix dw generated along with one's one attempts may be bad practise, but hey

[Free WordPress Themes]

If you have one photo, than store the name in the news table. this way its easy to use the DW behaviors