Selected item to use for multiple variables

[kaplanyo]

I apologize if this is the wrong place to ask... I'm brand new here. And I'm not sure if my problem is in my PHP or mySQL.

 

Here's my problem. Any guidance would be much appreciated. I'll try and describe it the best way I can.

 

I've got a list of 'items' and those 'items' have unique 'categories'.

I have 1 table for 'categories' that contains a list of category_ids and their 'category_names'. The other table is the 'item' list. And here's where I'm running into difficulty, I'd like to have the 'item' table contain the category_id from my 'categories' table as a Foreign Key and that category_id's 'category_name' in a 'category' field. I'm getting the Foreign Key, but I can't seem to grab the 'category_name' and assign it to my $category variable in the 'item' table.

 

I've got a page to insert items and a page to update these items. I was working with the update first. And I'm able to grab the list of categories and select the correct one, but I'm not seeing where to assign the category_name to the $category variable. Here's dropdown list:

<p>
      <label for="category_id">Menu category:</label>
      <select name="category_id" id="category_id">
        <option value="">Select category dude</option>
	<?php
	//get details of categories
	$getCategories = 'SELECT * FROM dinner_categories ORDER BY category_id';
	$categoryList = mysql_query($getCategories) or die (mysql_error());
	while ($categories = mysql_fetch_assoc($categoryList)) {?>
        <option value="<?php echo $categories['category_id'];?>"
	<?php if ($categories['category_id'] == $row['category_id']) {
	  echo ' selected="selected"';
	  }?>><?php echo $categories['category_name']; ?></option> 
	  <?php } ?>
      </select>
    </p>

It seems like I want to say something like this somewhere:

$category = $categories['category_name'];

 

Is this more of a mySQL/db question than a PHP because I'm trying to insert?

 

[Barand]

It sounds like you want to put the category name in the item table ??? Your tables should look something like this

[pre]

    category        item

    ---------        ----------

    cat_id  ---+    item_id

    cat_name    |    item_name

                +--- cat_id

[/pre]

[atlanta]

show the rest of the code .

so that we can see the other queries which select the data that needs to be updated that will help.

[kaplanyo]

I'll get the rest of the code in here.

Yes and I'd like to use the cat_name in the item table too. would it look like this then?:

category         item

---------        ----------

cat_id   ---+    item_id

           |    item_name

           +--- cat_id

cat_name ---+--- cat_name

 

I'm planning to sort these out by the cat_id but use the cat_name for display. Does that make sense? I have a feeling I might need to rethink what i'm try to accomplish.

Thanks!

[kaplanyo]

Thanks very much for taking the time to look at this.

Here's the code above the DOCTYPE

<?php
// some session and connection info here.
// include for magicQuotes and make db connection
  include('../includes/corefuncs.php');  
  // remove backslashes
  nukeMagicQuotes();
  // prepare an array of expected items
  $expected = array('category', 'description', 'additional', 'item_id', 'category_id');
  // set required field
  $required = array('description', 'item_id', 'category_id');
  // create empty array for missing fields
  $missing = array();
  // create database connection
  $conn = dbConnect('admin');
  
// get details of selected record
if ($_GET && !$_POST) {
  if (isset($_GET['item_id']) && is_numeric($_GET['item_id'])) {
    $item_id = $_GET['item_id'];
}
  else {
    $item_id = NULL;
}
  if ($item_id) {
    $sql = "SELECT * FROM dinner WHERE item_id = $item_id";
  $result = mysql_query($sql) or die (mysql_error());
  $row = mysql_fetch_assoc($result);
}
  }
// if form has been submitted, update record
if (array_key_exists('update', $_POST)) {
  // prepare expected items for insertion in to database
  foreach ($_POST as $key => $value) {
    // assign to temporary variable and strip whitespace if not an array
$temp = is_array($value) ? $value : trim($value);
// if empty and required, add to $missing array
if (empty($temp) && in_array($key, $required)) {
  array_push($missing, $key);
  }
elseif (in_array($key, $expected)) {
      ${$key} = mysql_real_escape_string($value);
      }
    }
  // abandon the process if primary key invalid
  if (!is_numeric($item_id)) {
    die('Invalid request is now happening');
} 
// check the value of image_id
if (empty($category_id) || !is_numeric($category_id)) {
$category_id = NULL;
}
  // prepare the SQL query if missing is empty
  if (empty($missing)) {
  $sql = "UPDATE dinner SET category_id = $category_id, category = '$category', description = '$description', additional = '$additional'
          WHERE item_id = $item_id";
  // submit the query and redirect if successful
  $done = mysql_query($sql) or die(mysql_error());
  }
  // if successful, redirect to list of existing records
  if ($done || !isset($item_id)) {
  	header('');
    exit;
    }
   }
?>

 

and then here's what my update form looks like

<?php
if ($_POST && isset($missing)) { ?>
<p class="warning">Please complete the missing item(s) indicated.</p>
    <?php } ?>
<?php if (empty($row) && (!$missing)) { ?>
<p class="warning">Invalid request: record does not exist.</p>
    <?php } else {?>
<form id="form1" name="form1" method="post" action="">
    
      <p>
      <label for="category_id">Menu category:</label>
      <select name="category_id" id="category_id">
        <option value="">Select category dude</option>
	<?php
	//get details of categories
	$getCategories = 'SELECT * FROM dinner_categories ORDER BY category_id';
	$categoryList = mysql_query($getCategories) or die (mysql_error());
	while ($categories = mysql_fetch_assoc($categoryList)) {?>
        <option value="<?php echo $categories['category_id'];?>"
	<?php if ($categories['category_id'] == $row['category_id']) {
	  echo ' selected="selected"';
	  }?>><?php echo $categories['category_name']; ?></option> 
	  <?php } ?>
      </select>
    </p>
    <p>
        <label for="description">Description:  <?php 
	if (isset($missing) && in_array('description', $missing)) { ?>
        <span class="warning">Please add a description.</span><?php } ?>
        </label>
        <textarea name="description" class="widebox" id="description" cols="60" rows="8"><?php 
	if (!$missing) {
	  echo ($row['description']);
	  }
	else { echo htmlentities($_POST['description']); } ?></textarea>
    </p>
    <p>
        <label for="additional">Additional:</label>
        <input name="additional" type="text" class="widebox" id="additional"
  		<?php
	if (!$missing) {
      echo 'value="'.htmlentities($row['additional']).'"';
	  } else { 
	  echo 'value="'.htmlentities($_POST['additional']).'"'; 
	  } ?>
        />
    </p>
	<input name="item_id" type="hidden"
  		<?php
	if (!$missing) {
    echo 'value="'.htmlentities($row['item_id']).'"';
	  } else { 
	  echo 'value="'.htmlentities($_POST['item_id']).'"'; 
	  } ?>
          />
    <p>
        <input type="submit" name="update" value="Update menu item" />
        <input name="cancel" type="submit" value="Cancel" />
    </p>
</form>
<?php } ?>

[Barand]

If your going to store it that way, forget about a relational database and just use a flat file, because that's all you end  up with.

 

You should be using JOINs to get the cat names

 

SELECT category.cat_name, item.item_name

FROM category JOIN item on category.cat_id = item_cat_id

ORDER BY category.cat_name, item.item_name

[kaplanyo]

Thanks for your help.

I'm not sure how to do what your saying about the JOIN, but I'll look it up.

I'm starting to see what you mean about just using the flat file, the relational stuff is confusing me. In my little sketch diagram it seemed like it was better to keep all the items in one table and organize them by category, i reckon i should do that without having to use the relational db.