www.webdeveloper.com
Results 1 to 5 of 5

Thread: Determining Select Data to be posted on an HTML page by information given in SQL

  1. #1
    Join Date
    Feb 2014
    Posts
    14

    Determining Select Data to be posted on an HTML page by information given in SQL

    I'm posting this in SQL part because everything in this exercise (including select portions of HTML and PHP) are determined by the information given by the SQL file

    Basic Instructions:


    I am able to do steps 1 since downloading WAMP seemed easy enough and step 2 in the exercise as this is a basic buildoff of one of my older examples from a few weeks ago

    Step 3 in terms of SQL coding as far as I know should look like this (named computerdatabaseMYSQL.sql)
    Code:
    USE computerExamples;
    
    CREATE TABLE prod (
    deptno tinyint(2) NOT NULL,
    dname varchar(14),
    loc varchar(131),
    PRIMARY KEY (deptno)
    );
    
    CREATE TABLE prod (
           	PRODNO int(4),
            PNAME varchar(10),
            PDESC varchar(20),
            BASEPRICE decimal(7,2),
            ADDNAMONE varchar(10),
            ADDNAMTWO varchar(10),
    	ADDNAMTHREE varchar(10),
     	ADDPRICEONE decimal(7,2),
    	ADDPRICEONE decimal(7,2),
    	ADDPRICEONE decimal(7,2),
            primary key (prodno),
            FOREIGN KEY (deptno) REFERENCES dept(deptno)
     );
     
    INSERT INTO DEPT VALUES
    	(10, 'CONSTRUCTION', 'NEW YORK');
    INSERT INTO DEPT VALUES
    	(11, 'SHIPPING', 'CALIFORNIA');
    
    INSERT INTO PROD VALUES
            (1001, 'COMPUTER', 'BASE DESIGN TOWER', 999.99, 'MOUSE', 'KEYBOARD', 'MONITOR', 4.50, 3.75, 300.00);
    INSERT INTO PROD VALUES
            (1002, 'LAPTOP', 'BASE LAPTOP', 1999.99, 'MOUSE', 'EXTERNAL MIC', 'POWERSUPPLY', 4.50, 30.00, 35.00);
    the menupage from step 5 was made by me
    Code:
    <!DOCTYPE html>
    <html lang = "en">
    <head>
    <title> MenuPage.html </title>
    <meta charset = "utf-8" />
    <script type = "text/javascript" src = "computerjs.js">
    </script>
    <style type = "text/css">
    	.spanred {font-size: 4em; font-family: Ariel; color: red;}
    	.spanblue {font-size: 3em; font-family: Times New Roman; color: blue;}
    								</style>
    
    </head>
    <body>
    <form action = "">
    <table>
    <tr>
    <th><span class = "spanred"> Calvin's Electronics Shop</span> </th>
    </tr>
    
    <tr>
    <th><span class = "spanblue"> Get Your Computational Devices Here</span> </th>
    </tr>
    
    <tr>
    <th><img class = "Left" src = "Compshop.png" alt = "Picture of computer shop"/></th>
    </tr>
    
    <tr>
    <td>  Please Choose from theese products: </td>
    <td> <a href = "Computerhtml.html"> <input type="button" value="Computer Tower" onClick=""> </a> </td>
    <td> <a href = "Laptophtml.html"> <input type="button" value="Laptop" onClick=""> </a> </td>
    </tr>
    
    </table>
    </form>
    </script>
    </body>
    </html>
    Step 6 seems to be a headscratcher since i think it asks for dynamic text changing based on both button presses or somthing along those lines (also updating the fields in the SQL file in the PHP file also).

    yes this thing is very messy because right now I have it set up where the menu code above goes another html document (Laptophtml.html) [but it's a last ditch effort is this problem can't be solved]

    Code:
    <!DOCTYPE html>
    <html lang = "en">
    <head>
    <title> Computerhtml.html </title>
    <meta charset = "utf-8" />
    <script type = "text/javascript" src = "computerjs.js">
    </script>
    <style type = "text/css">
    	.spanred {font-size: 4em; font-family: Ariel; color: red;}
    	.spanblue {font-size: 3em; font-family: Times New Roman; color: blue;}
    								</style>
    
    </head>
    <body>
    <form action = "">
    <table>
    <tr>
    <th><span class = "spanred"> Calvin's Computer Section </span> </th>
    </tr>
    
    <tr>
    <th><span class = "spanblue"> Basic Computers and Periphreals </span> </th>
    </tr>
    
    <tr>
    <th><img class = "Left" src = "computer.png" alt = "Picture of computer"/></th>
    </tr>
    
    <FORM ACTION="processCompphp.php" method=post>
    <tr>
    <th>   </th>
    <th>   </th>
    <th> Base Price   </th>
    <th> 999.99</th>
    </tr>
    
    <tr>
    <th> Add-on </th>
    <th> Quantity </th>
    <th> Add-on Price </th>
    <th> Add-on Total </th>
    </tr>
    
    <tr>
    <th> Monitor</th>
    <td> <input type="type" id="monitorAmt" size="2" value="0" onblur="addOption(this.id,this.value,300.00,'monitorTot')" /> </td>
    <td> 300.00 </td>
    <td> <input type="text" size=5 name= "qtyMon" id="monitorTot" readonly /> </td>
    </tr>
    
    <tr>
    <th> Keyboard </th>
    <td> <input type = "type" id = "keyboardAmt" size = "2" value = "0" onblur="addOption(this.id,this.value,3.75,'keyboardTot')"/> </td>
    <td> 3.75 </td>
    <td> <input type="text" size=5 name= "qtyKey" id="keyboardTot" readonly /> </td>
    </tr>
    
    <tr>
    <th> Mouse </th>
    <td> <input type="type" id="mouseAmt" size="2" value="0" onblur="addOption(this.id,this.value,4.50,'mouseTot')"/> </td>
    <td> 4.50 </td>
    <td> <input type="text" size=5 name= "qtyMou" id="mouseTot" readonly /> </td>
    </tr>
    
    <tr>
    <td>  </td>
    <td>  </td>
    <td> <input type="button" value="Grand Total" onclick="computeCost();" /> </td>
    <td> <input type="text" size=5 id="cost" readonly /> </td>
    <td> <a href = "MenuPagehtml.html"> <input type="button" value="Back to Main Menu" onClick=""> </a> </td>
    </tr>
    
    <tr>
    <td>  </td>
    <td> <input type="submit" value="Enter Order"></td>
    </tr>
    
    </table>
    </form>
    
    <script type="text/javascript">  //computerjs.js
    
    function computeCost() {
      var totalCost = 999.99;
          totalCost += Number(document.getElementById('monitorTot').value);
          totalCost += Number(document.getElementById('keyboardTot').value);
          totalCost += Number(document.getElementById('mouseTot').value);
      document.getElementById("cost").value = totalCost.toFixed(2);
    }
    
    function addOption(IDSv,amount,cost,IDSt) {
      var amt = Number(document.getElementById(IDSv).value) || 0;
      if (amt == 10) { Mistake(); setFocus(IDSv); }
      var tot = amt * cost;
      document.getElementById(IDSt).value =  tot.toFixed(2);
      computeCost();
      return tot;
    }
    
    function Mistake() { 
      alert("See you in about 30 years.");
    }
    
    </script>
    
    </body>
    </html>
    lastly I need to find a way to determine which picture (in this case either Laptop.png or Computer.png) gets shown in the form.

    This is headscratchingly difficult for our class since the examples he gives are incomplete at best.

    Thanks in advance

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,633
    For flexibility, I would probably create a separate table for photos, that would include the file name (and also directory name if they might be in different directories), perhaps a title field (could be used in the alt attribute of an img tag?), a description field, and any other info you want.

    Then, if a given photo will only ever be associate with one product, you could have the product ID in another column of the photo table as your foreign key. If, however, there is any possibility that a photo could be associated with more than one product, then you would need a separate photo-to-product relation table.

    How I'd pull that info out would depend a lot on whether you're expecting just one photo per product, or possibly more than one (e.g. different colors, angles, whatever). In the latter case, I might bite the bullet and make it a separate query, using the product ID in the where clause.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Feb 2014
    Posts
    14
    could you give me an example of this? I need some sort of basis to plan on

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,633
    Probably over-simplistic, but here's the general idea.
    Code:
    photo
    ===========
    photo_id (int, PK, auto-inc)
    file_name (varchar)
    title (varchar)
    product_id (int, FK)
    PHP Code:
    // assuming we already have the product id in $product_id:
    $sql "SELECT file_name, title FROM photo WHERE product_id = :product_id";
    $stmt $pdo->prepare($sql);
    $stmt->execute(array(':product_id' => $product_id);
    while(
    $photo $pdoStmt->fetch(PDO::FETCH_ASSOC))
    {
        echo 
    "<img src='/path/to/photos/{$photo['file_name']}' alt='{$photo['title']}' />";

    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Feb 2014
    Posts
    14
    would this be suffecient for the first part

    Code:
    USE ComputerExamples;
    
    CREATE TABLE phot (
    
    photo_id int(4) primary key (prodno) AUTO_INCREMENT,
    file_name varchar(20)
    title varchar(20)
    PRODNO int(4) FOREIGN KEY (deptno)
    );
    
    INSERT INTO PHOT VALUES
    	(2013, 'Computer', 'Computer PNG', 1001);
    INSERT INTO PHOT VALUES
    	(2014, 'Laptop', 'Laptop PNG', 1002);
    and this for the second part?

    Code:
    $sql = "SELECT file_name, title FROM photodatabaseMySQL WHERE PRODNO = :PRODNO";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array(':PRODNO' => $PRODNO);
    while($photodatabaseMySQL = $pdoStmt->fetch(PDO::FETCH_ASSOC))
    {
        echo "<img src='C:WAMP\wamp\www\EX03{$photodatabaseMySQL['file_name']}' alt='{$photodatabaseMySQL['title']}' />";
    }
    Also, is the PHP segment a different file altogether? or plugged into an existing php file?

    This is all still new to me.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles