SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    823
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question PHP coding for Lookup Table

    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:
    Code:
    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:
    Code:
    $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

  2. #2
    Avid Logophile silver trophy
    ParkinT's Avatar
    Join Date
    May 2006
    Location
    Central Florida
    Posts
    2,342
    Mentioned
    192 Post(s)
    Tagged
    4 Thread(s)

    More details

    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
    Don't be yourself. Be someone a little nicer. -Mignon McLaughlin, journalist and author (1913-1983)


    Git is for EVERYONE
    Literally, the best app for readers.
    Make Your P@ssw0rd Secure
    Leveraging SubDomains

  3. #3
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    823
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ParkinT View Post
    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:

    Code:
    <?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:
    Code:
    <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
    Attached Images Attached Images
    Last edited by toad78; Oct 8, 2007 at 15:37.

  4. #4
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    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...

    Code MySQL:
    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)
    PHP Code:
    $sql "SELECT * FROM...";
    $result $db->query($sql);
    while(
    $image $db->FetchArray($result)){
       
    $images[] = $image['filename'];

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •