PHP coding for Lookup Table

[toad78]

Greetings, experts!

I'm having trouble coding PHP to UPDATE an image filename to a lookup table within my sandv_update.php page.

I have three tables within my database:

Table: sandf
sandf_id (primary key)
other fields that pertain to sandv.php

Table: categories
cat_id (primary key)
category

Table: image_cat_lookup
image_id (primary key)
cat_id (primary key)


#1: sandv.php needs to have four images displayed on this page all at once. I know how to get ONE image from my database, but not four--I was highly recommended to create a lookup table. Unfortunately, I am lost when it comes to trying to program PHP to UPDATE to my lookup table. I want to allow the user to choose multiple filenames (the list box is allready set up). I understand the point of a lookup table, but don't know how to implement the code.

Currently, this is what my code looks like:

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  $updateSQL = sprintf("UPDATE sandf SET colt_start_desc=%s, mounted_desc=%s, follow_up_desc=%s, refinement_desc=%s, restarting_desc=%s, priv_sess_ranch=%s, priv_sess_facility=%s, group_lessons=%s, discount_coupons=%s, sess_desc=%s, sandf_facility=%s, images_id=%s WHERE sandf_id=%s",
                       GetSQLValueString($_POST['colt_start_desc'], "text"),
                       GetSQLValueString($_POST['mounted_desc'], "text"),
                       GetSQLValueString($_POST['follow_up_desc'], "text"),
                       GetSQLValueString($_POST['refinement_desc'], "text"),
                       GetSQLValueString($_POST['restarting_desc'], "text"),
                       GetSQLValueString($_POST['priv_sess_ranch'], "text"),
                       GetSQLValueString($_POST['priv_sess_facility'], "text"),
                       GetSQLValueString($_POST['group_lessons'], "text"),
                       GetSQLValueString($_POST['discount_coupons'], "text"),
                       GetSQLValueString($_POST['sess_desc'], "text"),
                       GetSQLValueString($_POST['sandf_facility'], "text"),
                       GetSQLValueString($_POST['sandf_id'], "int"));

$query_getSandF = sprintf("SELECT * FROM sandf WHERE sandf_id = %s", GetSQLValueString($colname_getSandF, "int"));
$getSandF = mysql_query($query_getSandF, $blogAdmin) or die(mysql_error());
$row_getSandF = mysql_fetch_assoc($getSandF);
$totalRows_getSandF = mysql_num_rows($getSandF);

#2: on my sandv.php page, how do I access these image files? Which table do I use to SELECT the information to retrieve on my sandf.php page?

Currently my code is:

$query_getSandF = "SELECT * FROM sandf ORDER BY sandf.sandf_id DESC LIMIT 1";
$getSandF = mysql_query($query_getSandF, $sandfquery) or die(mysql_error());
$row_getSandF = mysql_fetch_assoc($getSandF);
$totalRows_getSandF = mysql_num_rows($getSandF);

I hope I'm not overwhelming anyone, but I've been puzzling over this for weeks and would appreciate any expertise!

I thank you for your time,
Heather

[ParkinT]

Perhaps you can better qualify what you need to accomplish.
First things I noticed are:
You build a string, $updateSQL, and never use it.
Your SELECT statement (in the second block of code) includes an ORDER BY clause but uses LIMIT 1. This means you will only get ONE record.

Your post does not make it clear:
Where the images are stored (in the database?)
What criteria is used to determine which images to retrieve

[toad78]

Perhaps you can better qualify what you need to accomplish.
First things I noticed are:
You build a string, $updateSQL, and never use it.
Your SELECT statement (in the second block of code) includes an ORDER BY clause but uses LIMIT 1. This means you will only get ONE record.

Your post does not make it clear:
Where the images are stored (in the database?)
What criteria is used to determine which images to retrieve

I have a sample of my page in 'image 1.jpg'. I would like to have more than one image display on this page all at the same time; a total of four to be exact.

I have a database table called 'sandf'. The attachment 'sandftable.jpg' is what my table looks like. This table has my content that displays on the page that is displayed in 'image 1.jpg'.

My php code for 'sandv.php' before my <DOCTYPE> tab is as follows:

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $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;
}
}

mysql_select_db($database_sandfquery, $sandfquery);
$query_getSandF = "SELECT * FROM sandf ORDER BY sandf.sandf_id DESC LIMIT 1";
$getSandF = mysql_query($query_getSandF, $sandfquery) or die(mysql_error());
$row_getSandF = mysql_fetch_assoc($getSandF);
$totalRows_getSandF = mysql_num_rows($getSandF);
?>

My html is as follows:

<div id="images"><img class="sc" src="<?php echo $row_getSandF['image_id']; ?>" alt="" name=""  />
<img src="<?php echo $row_getSandF['image_id']; ?>" alt="" class="sc" />
<img src="<?php echo $row_getSandF['image_id']; ?>" alt="" class="sc" /></div>

I would like to have all four images displayed at the same time on my page. What am I doing wrong? I have been trying to understand how lookup table(s) work, thanks to another asisstant on this site, but I guess I'm making it harder than it should.

Heather

[Beowolf]

I'm not 100% sure what you intend, but looking at the information you've given so far it looks like each category can have several images, but each sandf entry can only have one.

I'll assume that you want each entry in the sandf table to have 4 (or n) images.

Table: sandf
sandf_id (primary key)
cat_id (foreign key)
other fields that pertain to sandv.php

Table: categories
cat_id (primary key)
category

Table: images
image_id (primary key)
filename
size
etc...

Table: image_cat_lookup
image_id (foreign key)
cat_id (foreign key)

If you know the sandf id for which you want the (4) images...

SELECT * 
FROM images i, image_cat_lookup icl, sandf s
WHERE s.sandf_id = $id
AND icl.cat_id = s.cat_id
LIMIT 4

Then to fetch the results... (I forget the mysql functions, but you'll get the idea)

$sql = "SELECT * FROM...";
$result = $db->query($sql);
while($image = $db->FetchArray($result)){
   $images[] = $image['filename'];
} 

And to display them...

<div id="images">
<?php foreach($images as $image){
    echo '<img class="sc" src="'.$image['filename'].'" alt="" name=""  />';
?>
</div>