www.webdeveloper.com
Results 1 to 12 of 12

Thread: I just can't get this to work

  1. #1
    Join Date
    Feb 2006
    Location
    Pennsylvania
    Posts
    153

    I just can't get this to work

    Nightshift was nice enought to help me out with this project but I just can't get it to work so I thought I'd post it because doing it through a PM is just to difficult.

    Anyway, the deal is this. I have 5 tables in a Postgresql DB that I am accessing with a simple select * from command.

    Well now I would like to add a section that allows the user to do simple querys and display the info, update the information, add new rows, add new tables, and delete tables. I'm not sure if all of this is possible through a simple web interface.

    Even a text input window would be ok, I think. Anyway, this has expanded a little from what the code below allows for but in either case I can't get anything to work.

    Any help would be appriciated.


    PHP Code:
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">    
    <html>
    <head>
      <title>Alexamara Marina</title>

      <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

    </head>

    <body background="sky.jpg" text="white" link="white" vlink="white" alink="white">  

    <?PHP 
    unset($dTable); 
    IF (isset( 
    $_GET['tablename']) )  
      
    $dTable $_GET['tablename']; 
      IF (isset(
    $_GET['act']) ) 
        
    // Process each case 
        
    IF ($_GET['act'] == 'edit')  
          
    // 1. update/change 
        
    ELSEIF ($_GET['act'] == 'delete');  
          
    // 2. delete 
        
    ELSEIF ($_GET['act'] == 'add');  
          
    // 3. add 
        
    ENDIF; 
      ENDIF; 
    ENDIF;
    ?> 

    <div align="center"> 
    <p><h1><font face="Arial">Alexamara Marina</font></h1></p></div> 

    <table align="center" width="765" height="110" cellpadding="0" border="2" bordercolor="#ffffff"> 
    <tr> 
        <td height="80" width="740" cellpadding="0" border="4"> 
        <img src="header.jpg"/> 
        </td> 
    </tr> 
    </table> 
    </br> 

    <?php 
    $arrTableNames 
    = array(); 
    $arrTableNames[] = "Marina"
    $arrTableNames[] = "Owner"
    $arrTableNames[] = "MarinaSlip"
    $arrTableNames[] = "ServiceCategory"
    $arrTableNames[] = "ServiceRequest"
    ?> 
    <div align="center"> 
      <table> 
      <tbody> 
        <tr> 
        <?php 
        
    FOREACH ($arrTableNames as $key => $thisTABLE) : 
          print 
    "<td><a href='index.php?tablename=$thisTABLE&act=display' style='font-family:Arial;color: ffffff;'>$thisTABLE</a>&nbsp;&nbsp;&nbsp;</td>\n"
          print 
    "<td><a href='index.php?tablename=$thisTABLE&act=delete'>Delete</a></td>\n"
          print 
    "<td><a href='index.php?tablename=$thisTABLE&act=edit'>Delete</a></td>\n"
        ENDFOREACH; 
        
    ?> 
        </tr> 
        <tr><td>&nbsp;</td></tr> 
      </tbody> 
      </table> 
    </div> 

    <div align="center"> 
    <?PHP 

    if (isset($dTable)) { 
      echo (
    "<p><font face='Arial' color='#ffffff'>You have selected the <i>$dTable</i>table</font></p>"); 
      require(
    "psqlCommon.php"); 
      if( 
    strcmp($dTable,"marina") != 0) { 
        
    displayTable($dTable); 
      } else { 
        
    displaymarina($dTable); 
      } 
    } else { 
      echo(
    "<p><font face='Arial' color='#ffffff'>Welcome to the Alexamara Marina database web portal.</br> 
           Please select from the table options above to view there content.</font></p>"
    ); 

    ?> 
     
    </br>
    </br>
    </br>
    </br>
    </br>

    </div>

    <div align="center" vertical-align="bottom"><font face="Arial">Copyright 2007 Alexamara Marina | <a href="mailto:kebreeg@pointpark.edu">Contact Us</a> | <a href="http://students.pointpark.edu/kebreeg/">Home</a></font></div>
    </br>

    </body>
    </html>

  2. #2
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    It's possible but you need to go bottom-up.

    Write a simple script to display a form with the information.
    Then comes a script to insert information.
    Next add a script to modify the information.
    And on to a script to delete information.
    etc.

    Start off with each such script as a separate file. Once you have your directoin firmly set, you can combine them into a single script and joining the whole thing together.

  3. #3
    Join Date
    Feb 2006
    Location
    Pennsylvania
    Posts
    153
    Problem is I don't know PHP. I understand the SQL query's and HTML but not PHP coding. I don't even know where to start.

  4. #4
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    Create HTML forms for each of the scripts and we'll take it from there.

  5. #5
    Join Date
    Feb 2006
    Location
    Pennsylvania
    Posts
    153
    By forms do you mean something like this?

    HTML Code:
    <form action="query" method="post" target="_self"> 
    
        Enter a Query:<br/> 
        <TEXTAREA NAME="query" COLS=100 ROWS=6>TEXTAREA> 
        <BR><INPUT TYPE=SUBMIT VALUE="Run Query"> 
        </FORM>

  6. #6
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    Yes, plus any dressing you may want...

  7. #7
    Join Date
    Feb 2006
    Location
    Pennsylvania
    Posts
    153
    Here it is. I would basically place a link on the index page to an identical page that allowed for the input of an SQL query. I'm hoping that the query can be exicuted from there and either the proper data displayed or a message returned that the updates have been made. Is that possible?


    HTML Code:
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
    <head>
      <title>Alexamara Marina - Run Query</title>
    
    
      <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    
    </head>
    
    <body background="sky.jpg" text="white" link="white" vlink="white" alink="white">  
    
    <?PHP
    unset($dTable);
    if (isset( $_GET['tablename']) )
       {
         $dTable = $_GET['tablename'];
       }
    ?>	
    
    <div align="center">
    <p><h1><font face="Arial">Alexamara Marina</font></h1></p></div>
    
    <table align="center" width="765" height="110" cellpadding="0" border="2" bordercolor="#ffffff">
    <tr>
    	<td height="80" width="740" cellpadding="0" border="4">
    	<img src="header.jpg"/>
    	</td>
    </tr> 
    </table>
    </br>
    
    <?php 
    $arrTableNames = array(); 
    $arrTableNames[] = "Marina"; 
    $arrTableNames[] = "Owner"; 
    $arrTableNames[] = "MarinaSlip"; 
    $arrTableNames[] = "ServiceCategory"; 
    $arrTableNames[] = "ServiceRequest"; 
    ?> 
    <div align="center"> 
        <table> 
          <tbody> 
            <tr> 
            <?php 
            FOREACH ($arrTableNames as $key => $thisTABLE) : 
              print "<td><a href='index.php?tablename=$thisTABLE' style='font-family:Arial;color: ffffff;'>$thisTABLE</a>&nbsp;&nbsp;&nbsp;</td>\n"; 
            ENDFOREACH; 
            ?> 
        </td>
    	<td>
    	<font face="Arial"><a href="http://students.pointpark.edu/kebreeg/query">Run a Query</a></font>	
    	</td>
        </tr> 
        <tr><td>&nbsp;</td></tr> 
      </tbody> 
      </table> 
    </div> 
    
    <div align="center"> 
    <form action="query" method="post" target="_self"> 
    
        <font face="Arial">Enter a Query:</font><br/></br> 
    	
        <TEXTAREA NAME="query" COLS=100 ROWS=6>	</textarea>	</br></br>
     	<INPUT TYPE=SUBMIT VALUE="Run Query"> 
    	   
    </FORM>	
    
    <div align="center">
    <?PHP
    
     if(isset($dTable))
       {
          echo("<p><font face='Arial' color='#ffffff'>You have selected the <i>Alexamara.".$dTable." </i> table</font></p>");
    	  require("psqlCommon.php");
    	  if( strcmp($dTable,"marina") != 0)
    	    displayTable($dTable);
    	  else
    	    displaymarina($dTable);	  
       }
     else
       echo("<p><font face='Arial' color='#ffffff'>Welcome to the Alexamara Marina database web portal.</br>
    	Please select from the table options above to view there content.</font></p>");
    ?> 
    </br>
    </br>
    </br>
    </br>
    </br>
    
    </div>
    
    <div align="center" vertical-align="bottom"><font face="Arial">Copyright 2007 Alexamara Marina | <a href="mailto:kebreeg@pointpark.edu">Contact Us</a> | <a href="http://students.pointpark.edu/kebreeg/">Home</a></font></div>
    </br>
    
    
    
    </body>
    </html>

  8. #8
    Join Date
    Feb 2006
    Location
    Pennsylvania
    Posts
    153
    Did I forget to add something?

  9. #9
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    I'm asking myself if you really want to give visitors the opportunity to type in a query and then blindly executing it. There's a few very destructive commands in the SQL language.

    I can only recommend not to go that route...

  10. #10
    Join Date
    Feb 2006
    Location
    Pennsylvania
    Posts
    153
    I think the ultimate plan would be to have password security that allowed for certian things. But in this case this is just a class project. Our database instructor knows we don't know PHP and allows us to get help anyway we can. He's been trying to teach it to us but there is olnly so much he can teach in a couple weeks.

    So bottom line is this. I don't know the best way to code it. I agree with you that certain regulations need to be etablished but I wouldn't know how to go about doing it.

    I'm asking for help with the PHP part to allow the DB administrator to do everything they could possibley need to do. And it could be password protected.

  11. #11
    Join Date
    Feb 2006
    Location
    Pennsylvania
    Posts
    153
    Ok, I've found that we can simplify what we are doing. I'm only going to need to allow for the ability to update data and insert new data to only one table.

    I know how to pull up the data for the individual rows in the table but I don't know how to allow for an update or insert.

    The table name is: owner

    the column names are: ownernum, lastname, firstname, address, city, state, and zip.

    Can you help me with this?

  12. #12
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    PHP Code:
    <?php
    $ownernum  
    123;
    $lastname  "Last";
    $firstname "First";
    $address   "1 Main St.";
    $city      "Miami";
    $state     "FL";
    $zip       "23456";

    // Insert
    // Assuming "ownernum" is a unique ID, PK, auto_inc
    $sql  "INSERT INTO `owner` ";
    $sql .= "  SET `ownernum`  = 0, ";
    $sql .= "      `lastname`  = '$lastname', ";
    $sql .= "      `firstname` = '$firstname', ";
    $sql .= "      `address`   = '$address', ";
    $sql .= "      `city`      = '$city', ";
    $sql .= "      `state`     = '$state', ";
    $sql .= "      `zip`       = '$zip' ";
    $qry  mysql-query($sql) or die("SQL Error: $sql<br>" mysql_error());

    // Update
    $sql  "UPDATE `owner` ";
    $sql .= "   SET `lastname`  = '$lastname', ";
    $sql .= "       `firstname` = '$firstname', ";
    $sql .= "       `address`   = '$address', ";
    $sql .= "       `city`      = '$city', ";
    $sql .= "       `state`     = '$state', ";
    $sql .= "       `zip`       = '$zip' ";
    $sql .= " WHERE `ownernum`  = '$ownernum' ";
    $sql .= " LIMIT 1 ";
    $qry  mysql-query($sql) or die("SQL Error: $sql<br>" mysql_error());
    ?>

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