SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am using the "newjoke.php" . Basically the the form is working. I have pull down menus for 5 tables and the contents in those tables are appearing just fine...However, nothing is being written to my Lookup table and there is no relationship forming between my main table: Business and the other tables: City, Category, Subcategory, Zip, Neighborhood.....

    This is my PHP code..I hack it a bit to fit my needs...

    Any help would be excellent..

    PHP Code:

    <HTML>
    <HEAD>
    <TITLE> Add New Joke </TITLE>
    </HEAD>
    <BODY>
    <?php

    if ($submit): // A new joke has been entered
    // using the form.

    if ($aid == "") {
    echo("<P>You must choose a author " .
    "for this joke. Click 'Back' " .
    "and try again.</P>");
    exit();
    }

    $dbcnx = @mysql_connect("localhost", "XXXXX", "XXXXXX");
    mysql_select_db("XXXXX");

    $sql = "INSERT INTO Business SET " .
    "Business_Name='$businessname', " .
    "Business_Description='$businessname', " .
    "Address_1='$address1', " .
    "Address_2='$address2', " .
    "Phone_Number='$phonenumber', " .
    "E_Mail='$email', " .
    "Web_Site='$website', " .
    "Contact_Name='$contactname', " .
    "Date=CURDATE(),".
    "businessindex='$businessid'";

    if (mysql_query($sql)) {
    echo("<P>New joke added</P>");
    } else {
    echo("<P>Error adding new joke: " .
    mysql_error() . "</P>");
    }

    $businessid = mysql_insert_id();

    $cats = mysql_query("SELECT ID, Category FROM Category");

    while ($cat = mysql_fetch_array($cats)) {
    $categoryid = $cat["ID"];
    $cname = $cat["Category"];
    $var = "cat$categoryid"; // The name of the variable
    if ($$var) { // The checkbox is checked
    $sql = "INSERT IGNORE INTO Businessrecord" .
    "SET Business_ID=$businessid, Category_ID=$categoryid, Subcategory_ID=$subcategoryid, City_ID=$cityid, Neighborhood_ID=$neighborhoodid, Zip_ID=$zipid";
    $ok = mysql_query($sql);
    if ($ok) {
    echo("<P>Joke added to category: $cname</P>");
    } else {
    echo("<P>Error inserting joke into category $cname:" .
    mysql_error() . "</P>");
    }
    } // end of if ($$var)
    } // end of while loop
    ?>

    <P><A HREF="<?php echo($PHP_SELF); ?>">Add another Joke</A></P>
    <P><A HREF="jokes.php">Return to Joke Search</A></P>

    <?php
    else: // Allow the user to enter a new category

    $dbcnx = @mysql_connect("localhost", "XXXX", "XXXXX");
    mysql_select_db("XXXXX");

    $authors = mysql_query("SELECT ID, Sub_Category FROM Subcategory");
    $cats = mysql_query("SELECT ID, Category FROM Category");
    $cities = mysql_query("SELECT ID, City FROM City");
    $neighborhood = mysql_query("SELECT ID, Neighborhood FROM Neighborhood");
    $zipcode = mysql_query("SELECT ID, Zip FROM Zip");
    ?>

    <FORM ACTION="<?php echo($PHP_SELF); ?>" METHOD=POST>
    <P>Enter new business listing:<BR>

    Business Name:
    <INPUT TYPE=TEXT NAME="businessname" SIZE=50 MAXLENGTH=100><BR>
    <TEXTAREA NAME="businessdescription" ROWS=15 COLS=45 WRAP>
    </TEXTAREA><br>
    Address 1: <INPUT TYPE=TEXT NAME="address1" SIZE=50
    MAXLENGTH=100><BR>
    Address 2: <INPUT TYPE=TEXT NAME="address2" SIZE=50
    MAXLENGTH=100><BR>
    City: <INPUT TYPE=TEXT NAME="city" SIZE=50
    MAXLENGTH=100><BR>
    State: <INPUT TYPE=TEXT NAME="state" SIZE=2
    MAXLENGTH=2><BR>
    Phone Number: <INPUT TYPE=TEXT NAME="phonenumber" SIZE=25
    MAXLENGTH=25><BR>
    E-Mail: <INPUT TYPE=TEXT NAME="email" SIZE=50
    MAXLENGTH=100><BR>
    Web Site: <INPUT TYPE=TEXT NAME="website" SIZE=50
    MAXLENGTH=100><BR>
    Contact Name: <INPUT TYPE=TEXT NAME="contactname" SIZE=50
    MAXLENGTH=100><BR>


    <P>Place in categories:<BR>
    <?php
    while ($cat = mysql_fetch_array($cats)) {
    $categoryid = $cat["ID"];
    $cname = $cat["Category"];
    echo("<INPUT TYPE=CHECKBOX NAME='$categoryid'>$cname<BR>\n");
    }
    ?>
    </p>
    <P>Sub Category:
    <SELECT NAME="aid" SIZE=1>
    <OPTION SELECTED VALUE="">Select One
    <OPTION VALUE="">---------
    <?php
    while ($author = mysql_fetch_array($authors)) {
    $aid = $author["ID"];
    $aname = $author["Sub_Category"];
    echo("<OPTION VALUE='$aid'>$aname\n");
    }
    ?>
    </SELECT></P>
    <P>City:
    <SELECT NAME="cityid" SIZE=1>
    <OPTION SELECTED VALUE="">Select One
    <OPTION VALUE="">---------
    <?php
    while ($cityentry = mysql_fetch_array($cities)) {
    $cityid = $cityentry["ID"];
    $cityname = $cityentry["City"];
    echo("<OPTION VALUE='$cityid'>$cityname\n");
    }
    ?>
    </SELECT></P>

    <P>Neighborhood:
    <SELECT NAME="neighborhoodid" SIZE=1>
    <OPTION SELECTED VALUE="">Select One
    <OPTION VALUE="">---------
    <?php
    while ($neighborhoodentry = mysql_fetch_array($neighborhood)) {
    $neighborhoodid = $neiborhoodentry["ID"];
    $neighborhoodname = $neiborhoodentry["Neighborhood"];
    echo("<OPTION VALUE='$neighborhoodid'>$neighborhoodname\n");
    }
    ?>
    </SELECT></P>
    <P>Zip Code:
    <SELECT NAME="zipid" SIZE=1>
    <OPTION SELECTED VALUE="">Select One
    <OPTION VALUE="">---------
    <?php
    while ($zipentry = mysql_fetch_array($zipcode)) {
    $zipid = $zipentry["ID"];
    $zipname = $zipentry["Zip"];
    echo("<OPTION VALUE='$zipid'>$zipname\n");
    }
    ?>
    </SELECT></P>
    </P>
    <P><INPUT TYPE=SUBMIT NAME="submit" VALUE="SUBMIT"></P>
    </FORM>
    <?php endif; ?>
    </BODY>
    </HTML>

  2. #2
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The problem is in the following:

    $sql = "INSERT IGNORE INTO Businessrecord" .
    "SET Business_ID=$businessid, Category_ID=$categoryid, Subcategory_ID=$subcategoryid, City_ID=$cityid, Neighborhood_ID=$neighborhoodid, Zip_ID=$zipid";


    The dot (.) operator sticks the two strings together to form the following MySQL query:

    INSERT IGNORE INTO BusinessrecordSET Business_ID=$businessid, Category_ID=$categoryid, Subcategory_ID=$subcategoryid, City_ID=$cityid, Neighborhood_ID=$neighborhoodid, Zip_ID=$zipid

    See the problem? You're missing a space.

    ------------------
    -Kevin Yank.
    http://www.SitePoint.com/
    Helping Small Business Grow Online!

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Kevin,

    Okay I will try that...I am also a bit confused...How do we form a relationship between the tables?

    My main question is this, my table Business which holds the main business records, how will this be related to my supporting tables? Do we need to create other columns to insert the id's from my other tables
    Or does the lookup table form the whole relationship and how is this linked to my main table?


    Thanks,

  4. #4
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">quote/font><HR>How do we form a relationship between the tables?<HR></BLOCKQUOTE>

    If you need a many-to-many relationship, you'll need a lookup table for every pair of "regular" tables that you need to relate. One-to-many, or many-to-one relationships don't need a lookup table, and can be represented by just having a column in Table A for the ID of the entry in Table B you wish to relate to. One-to-one relationships don't require multiple tables at all.

    <BLOCKQUOTE><font size="1" face="Verdana, Arial">quote/font><HR>My main question is this, my table Business which holds the main business records, how will this be related to my supporting tables?<HR></BLOCKQUOTE>

    Depends what information you want to store in your supporting tables, and what type of relationship (see above) there will be between this information and the entries in your Business table.

    <BLOCKQUOTE><font size="1" face="Verdana, Arial">quote/font><HR>Do we need to create other columns to insert the id's from my other tables<HR></BLOCKQUOTE>

    You do this for one-to-many and many-to-one relationships, where a lookup table is unneccessary.

    <BLOCKQUOTE><font size="1" face="Verdana, Arial">quote/font><HR>Or does the lookup table form the whole relationship<HR></BLOCKQUOTE>

    Yes, but a lookup table is overkill for anything simpler than a many-to-many relationship.

    <BLOCKQUOTE><font size="1" face="Verdana, Arial">quote/font><HR>and how is this linked to my main table?<HR></BLOCKQUOTE>

    I think you need to read Part Five of my article series again. All of these issues are covered in detail, with supporting examples.



    ------------------
    -Kevin Yank.
    http://www.SitePoint.com/
    Helping Small Business Grow Online!


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
  •