Page 1 of 1

Form Updating two MySQL tables

Posted: Fri Mar 02, 2007 3:38 pm
by justinfm
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi,

Well, I've been pulling my hair out trying to figure out a how to even get started on this project. I'm looking for advise on what tutorials to read, what I need to learn, and if my concept for how to do this is valid.

I'm making a directory based using Joomla and the component MOS directory. 

MOS directory lets you setup unlimited custom fields.

I've setup all of the US counties by state.

Someone signing up for the directory needs to go through 5 steps (only 2 of them seem tricky).

[b]Step 1. [/b] Create a username and password (already taken care of by Joomla)

[b]Step 2. [/b]Check 5 boxes (they all most be checked to proceed so I was just going to do that with javascript).

[b]Step 3.[/b] Select a state, and then select counties from that state. I have this coded, however I'm not sure that it's working right. You can see it at http://www.AppraiserAgent.com/sandbox/opt.php I made the display.php so that might be why it's not working. I need the output to be |CID|CID|CID| (category ID) Here's the code I used for display the display form, so you can tell me if that should show the whole array...

Code: Select all

<?php

    $language = $_POST['counties']; 
    $n         = count($language); 
    $i         = 0; 
     
    echo "The languages you selected are \r\n" . 
         "<ol>"; 
    while ($i < $n) 
    { 
        echo "<li>{$language[$i]}</li> \r\n"; 
        $i++; 
    } 
    echo "</ol>";

?>
Step 4. Take the input from the previous page, as well as about 70 other pieces of information and feed it into two different tables. The two tables have the following stuctures.

Code: Select all

Listing Table

 lid int(11)   UNSIGNED No  auto_increment               
  cid varchar(255)                 
  title varchar(100)                  
  address varchar(255)                  
  address2 varchar(255)                  
  address3 varchar(255)                  
  city varchar(255)                  
  region varchar(255)                  
  zip varchar(255)                  
  country varchar(255)                  
  phone varchar(100)                  
  fax varchar(100)                  
  email varchar(100)                  
  url varchar(250)                  
  logourl varchar(60)                  
  submitter int(11)                  
  status tinyint(2)                   
  date int(10)                  
  hits int(11)                  
  rating double(6,4)               
  votes int(11)                  
  introtext mediumtext                  
  fulltext mediumtext                  
  metakey text                  
  metadesc text                  
  created datetime                
  created_by int(11)                 
  created_by_alias varchar(100)                  
  modified datetime                  
  modified_by int(11)                  
  checked_out int(11)                  
  checked_out_time datetime                   
  publish_up datetime                  
  publish_down datetime                   
  comments int(11)                   
  premium tinyint(2)                 
  access int(11)                 
  published int(11)                  
  recepLink 

Custom Record Table

  lid int(11)   No 0                
  companyphone2 varchar(255)                 
  contactfname varchar(255)                 
  contactlname varchar(255)                 
  contactphone varchar(255)                 
  contactfax varchar(255)                 
  contactemail varchar(255)                 
  contactitle varchar(255)                 
  insuranceco varchar(255)                 
  insurancenumber varchar(255)                 
  insuranceexpiration datetime                
  categories1 varchar(255)                 
  categories2 varchar(255)                 
  categories3 varchar(255)                 
  1004fullurar varchar(255)                 
  1004URARFHA varchar(255)  
  2to4family varchar(255)                 
  704driveby varchar(255)                 
  comparablerentschedule varchar(255)                 
  condominium varchar(255)                 
  cooperative1075 varchar(255)                 
  deskreview varchar(255)                 
  fannie2055driveby varchar(255)                 
  fannie2055full varchar(255)                 
  fannie2065driveby varchar(255)                 
  fannie2065full varchar(255)                 
  fannie2075 varchar(255)                 
  fieldreview varchar(255)                 
  freddie2055driveby varchar(255)                 
  freddie2055full varchar(255)                 
  freddie2070driveby varchar(255)                 
  freddie2070full varchar(255)                 
  land varchar(255)                 
  mobilehome varchar(255)                 
  operatingincomestatement varchar(255)                 
  appraisalupdate varchar(255)                 
  payment1 varchar(255)                 
  payment2 varchar(255)                 
  payment3 varchar(255)                 
  payment4 varchar(255)                 
  payment5 varchar(255)                 
  payment6 varchar(255)                 
  turnaroundtime varchar(255)                 
  additionalinfo varchar(255)                 
  1092brokerpriceopinion varchar(255)
Below is a link to this part of the form:
http://www.appraiseragent.com/sandbox/form-step-4.pdf

Step 5 is the payment side of things, and I have a pretty good idea how I'll do that.

My Concept making the step 4 form, was to have two forms one for each table (I'd rather not do this if I don't have to), The questions are mixed up between the two tables, so I figured I'd have the forms use hidden fields and then have the main form submit the values into those forms through javascript.


Finally, I need to make a new version of the form (one page) that allows the end user to edit the listing once it's up.

Things I know I need to learn:
>How to use a form to add content to a table or how to use a form to add content to two tables
>Can I just add content to one field in a table (in other words for step 3, could I just submit the information from that box directly to the listing table?
>How do I prevent MySql Injection (whatever that is)

Any suggestions/advise would be super helpful.


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Fri Mar 02, 2007 4:19 pm
by RobertGonzalez
>How to use a form to add content to a table or how to use a form to add content to two tables
You don't. You use the form to gather the data, you use the code to filter, validate and handle the data. What you are looking for is how to use the $_POST superglobal array in conjunction with data validation and filtration in conjunction with INSERT queries.
>Can I just add content to one field in a table (in other words for step 3, could I just submit the information from that box directly to the listing table?
Yes.

Code: Select all

INSERT INTO `table` SET `fieldname` = `fieldvalue`;
>How do I prevent MySql Injection (whatever that is)
This is way beyond the scope of a single thread. It has also been discussed in length around here and all over the web. Try a google search or search these boards for a lot of good information on SQL injection.