Where to begin? 1 db - 3 tables - updating only.

Frapster

I have a database created in MySQL. It is a database that contains information about lamps for tanning beds. it's structured like so:

dbname: lamps

table1name: family2

fields:
id
familyname
url
urlag

table2name: lamp_type2

fields:
id
family
name

table3name: replacement2

fields:
id
groupid
lampid
preferred

This is a URL of how it works:

http://www.australiangold.com/lamps/lamps.asp

Table one holds the lamp name and urls and works with table 2 to populate the 2 drop-downs.

Table 3 is called to create the list that appears once you hit the submit button.

My goal is to build something that does the following:

I want to be able to submit a new lamp
I want to be able to assign lamp compatibilities

Table one and two contain all lamp names and 'product numbers' for both our company lamps and competitor lamps. However our matrix only shows compatibility information for our lamps.

I have literally just finished my first effort at doing data driven sites and I'm very proud of it. But this project is just a tad over my head (if not a lot).

Can someone help me along with this effort? Yes - i'm asking someone to helpe me write this. lol I suspect we're not talking about a lot here - but I could clearly be wrong. hehe

thanks,

Rob

Frapster

I think I know how I'm going to do this.

I can add a lamp family easily enough. I can add a lamp type easily enough. Adding a lamp type would require that the lamp it's associated exist first so I have 1 form to add the family (if it doesn't exist) and the other to add the lamp type.

to achieve that I need to be able to create a page that has two fields. The first is a list of all the lamp family's that exist in the first database. The second field is blank. I must enter the lamp type code manually, select the appropriate lamp family and hit submit. Upon submission it would enter a new record in the 2nd table that takes the lamp name and it's associated 'id' then inserting it into the 2nd table in the appropriate fields.

where you can help
What I do not know how to do - how do I get PHP to populate a list of family names from the first database? I think I can google that one.

Next would be to pass the appropriate value (not name) into the 2nd database to create the association - (the id# not the actual family name).

I actually think I can make progress on this except for those two things.

I'll focus on modifying table 3 after this.

Frapster

http://www.weberdev.com/ViewArticle-68.html

hope this works!

Frapster

I can confidently add a new record but now I want to update an existing record. I'm using the following code but it's not working. As you can see in the code I'm using a fancy-schmancy dynamically generated form list feature and I don't think there's any conflicts from that code - but I could be wrong. If you see what's wrong - any help is greatly appreciated. Thanks in advance.

In the meantime I'll work on deleting records. That should be easy.

Oh yeah - I have included the information about opening the database at the top of my code. This code appears further down in the HTML of my page. The database is opened and closed just find top and bottom.

<?php
if(isset($_POST['update']))
{ 
$family_name = $_POST['family_name'];
$id = $_POST['id'];
$nameadd = $_POST['nameadd'];

$query = "UPDATE family2 SET family_name = $nameadd" . "WHERE id = $id";
mysql_query($query) or die('Error, update failed');
} 
else
{
?>
<form name="update" method="post" action="fam_add_del2.php">
                    <table width="100%"  border="0" cellpadding="3" cellspacing="1" bgcolor="#666666">
                      <tr align="center" valign="top" bgcolor="#CCCCCC">
                        <td><span class="style34">Pick a Lamp Family Name </span></td>
                        <td><span class="style34">Enter Modified Name Here </span></td>
                        <td><span class="style34">Click this button to submit change. </span></td>
                      </tr>
                      <tr align="center" valign="top" bgcolor="#FFFFFF">
                        <td width="33%"><select name="fieldname">
                            <?php
// php to select drop down menu options from tablename table
$result = @mysql_query("SELECT family_name, id FROM family2 ORDER BY family_name asc");
if (!$result)
{
echo("<p>Error performing query: " . mysql_error() . "</p>");
exit();
}
while ( $row = mysql_fetch_array($result) )
{
$searchfieldname=$row["family_name"];
$searchfieldid=$row["id"];
echo '<option value="' . $searchfieldid . '">' . $searchfieldname . '</option>';
}
?>
                          </select></td>
                        <td width="33%"><input name="nameadd" type="text" id="nameadd"></td>
                        <td width="33%"><input name="update" type="submit" id="update" value="Update"></td>
                      </tr>
                    </table>
                  </form><?php } ?>

tsinka

Hi,

I see two problems.

1) change the query to

$query = "UPDATE family2 SET family_name = '$nameadd' WHERE id = $id";

2) Where does $POST['id'] come from ?
Suggestion (just a shot in the dark): $POST['fieldname']

Besides that you should add some kind of form validation (e.g. validate that family name is a valid string)

Thomas

Frapster

thanks - now that you point it out I'm not sure I need $_POST['id'].

I've actually made some progress although I think I just trashed every record in my database with one edit - fortunately I made a backup last night.

As for validation - I agree. I'll have to validate for duplicate records.

Thanks for your help!

Frapster

$nameadd = $_POST['nameadd'];
$fieldname = $_POST['fieldname'];

$query = "UPDATE family2 SET family_name = '$nameadd' WHERE '$fieldname' = id";
mysql_query($query) or die(mysql_error());

I finally realized I was calling the wrong variables/field names in light of how the select list is labeled and values are defined. It's now working just fine!

Thanks!