Working with 2 MySQL tables in 1 DB w/form

software development

#1

Hi There-

I built a simple yet functional content management system that I’ve put in place for my Web site that is working splendidly. The main table (content) contains the following columns:

  • contentID
  • createDate
  • updateDate
  • categoryName
  • contentTitle
  • categoryRef
  • contentText

I would like to eliminate the categoryName column, move it into a separate table, and reference it with the categoryRef column. The categories table contains the following columns:

  • categoryID
  • categoryName

Both tables are populated with data. I have an edit form that I use to edit the content. The data are pulled into the form just fine and I have no problem editing data in the content table. Here’s where I’d appreciate some feedback:

  1. When I edit a record from the content table that references a certain row from the categories table, I would like for the name of that category to be selected by default (I’ve succeeded in doing this) BUT NOT be displayed a 2nd time in the drop-down list.

  2. When I select a new categoryName, I would like for the categoryRef to be updated in the content table. This does not seem to be working.

Here’s a screenshot of what I’m describing:

http://www.gabeanderson.com/temp/cms_screenshot.jpg

Below are code snippets first from the edit template and also from the template that’s doing the inserts but is not working. When I select another categoryName, submit the form, and return to edit the same record, no change is reflected in the selected categoryName.

Any input would be appreciated!

Thanks,
Gabe

// SNIPPETS FROM EDIT TEMPLATE
// run query to pull record data (contentID, createDate, updateDate, contentTitle, contentText)
$sql = “SELECT * FROM content
WHERE contentID=$contentID”;
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);

// run 2nd query against categories table to get category names
$sql2 = “SELECT * FROM categories order by categoryName”;
$result2 = mysql_query($sql2);

contentTitle:

categoryName:

<?php $sqlSelect = "SELECT * FROM categories where categoryID=$categoryRef"; $resultSelect = mysql_query($sqlSelect); $myrowSelect = mysql_fetch_array($resultSelect); $categoryNameSelect = $myrowSelect["categoryName"]; $categoryID = $myrowSelect["categoryID"]; printf("$categoryNameSelect");

if ($myrow2 = mysql_fetch_array($result2)) {

do {

?>

<?= printf("%s (%s) \n",$myrow2["categoryName"],$myrow2["categoryID"]); ?> <?php } while ($myrow2 = mysql_fetch_array($result2)); } ?>

// SNIPPETS FROM INSERT TEMPLATE

if ($submit) {

if ($contentID) {

$sql = “UPDATE content SET
updateDate=’$datetime’,
categoryRef=’$categoryRef’,
contentTitle=’$contentTitle’,
contentText=’$contentText’
WHERE contentID=$contentID”;

} else {

$sql = "INSERT INTO content

(createDate,updateDate,categoryRef,contentTitle,contentText)

VALUES
(’$datetime’,’$datetime’,’$categoryRef’,’$contentTitle’,’$contentText’)";

}


#2

I don’t do PHP but I think I understand the code you have now.

The problem you have now is you are first making the current category as an option then making ALL categories as options. In your do … while loop, just skip to the next iteration if the $categoryRef matches the $categoryID:

if ($categoryRef == $categoryID) { next; } Second, your categoryRef column is not being updated because in your do … while loop you need to change

<option value="<?= $categoryID ?>"> to this

<option value="<?= $myrow2["categoryID"] ?>">


#3

Thanks very much for the reply and suggestions!

Based on your input, the insert/update is now working great. I’m still having problems, however, with getting the selected category only to display once. Below is what I have.

I’ve also tried variations of the if ($categoryRef == $categoryID) clause, such as moving the option selected bit inside the if statement. Still no luck.

Any thoughts?

Thanks,
Gabe

categoryName:

<?php $sqlSelect = "SELECT * FROM categories where categoryID=$categoryRef"; $resultSelect = mysql_query($sqlSelect); $myrowSelect = mysql_fetch_array($resultSelect); $categoryNameSelect = $myrowSelect["categoryName"]; $categoryID = $myrowSelect["categoryID"];

?>

<?php printf("$categoryNameSelect ($categoryID)"); if ($myrow2 = mysql_fetch_array($result2)) { do { if ($categoryRef == $categoryID) { next; } ?> "> <?= printf("%s (%s) \n",$myrow2["categoryName"],$myrow2["categoryID"]); ?> <?php } while ($myrow2 = mysql_fetch_array($result2)); } ?>


#4

[quote]I’ve also tried variations of the if ($categoryRef == $categoryID) clause, such as moving the option selected bit inside the if statement. Still no luck.

Any thoughts?
[/quote]
You seem to be confused about your code as much as I am. The if clause is not working because it has the wrong variables in it and it is not checking the current category id with the category id of the row just fetched. Easy enough to fix - I made the same mistake you did and forgot to subscript to $myrow2.


#5

Woohoo! I just resolved this! Thanks, Atropos7, for your continued prodding and suggestions. :slight_smile:

Here’s the code in all its functional glory:

if ($myrow2 = mysql_fetch_array($result2)) {

do {

?>

<?php // if do encounters active category, make it selected value if ($myrow2["categoryID"] == $categoryID) { ?> <?php

printf("$categoryNameSelect ($categoryID)");
} else {
?>

"> <?= printf("%s (%s) \n",$myrow2["categoryName"],$myrow2["categoryID"]); ?> <?php } } while ($myrow2 = mysql_fetch_array($result2)); } ?>