Skip navigation
Currently Being Moderated

Database issue

Nov 6, 2013 4:27 AM

Hi All,

I have a small problem with the database (mysql) that i have set up. So to start off the website is for a funeral directors, within this site is a page where people can donate (http://www.milesfunerals.com/donations_2.php ), they put the name of the deceased into the search box and it brings bring up the name of the deceased with a url link. This url link then takes them to another site called justgiving, a donations and fundraising portal site, here the donations can then be made. Just to clarify, the database used by the funeral directors is a separate setup to justgiving. The data uploaded to this database is done by the staff after having arranged the funeral. Once they have found out details of charities etc the staff then create a donations page for that deceased person, chooses a url (also through justgiving) then uploads both the name of deceased along with url to the miles funeral database.

 

The problem i have is, justgiving only allows the one charity per page so if the family have chosen to have more than the one charity there would need to be 2 pages created under the same name, therefor 2 urls are needed BUT only the one url per name can be entered into the funeral database. Is there some way that 2 url links can present? Below is the php code for the insert data page. I have stared out parts for security reasons.

 

<?php require_once('../Connections/*****.php'); ?>

<?php

if (!isset($_SESSION)) {

  session_start();

}

$MM_authorizedUsers = "";

$MM_donotCheckaccess = "true";

 

 

// *** Restrict Access To Page: Grant or deny access to this page

function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) {

  // For security, start by assuming the visitor is NOT authorized.

  $isValid = False;

 

 

  // When a visitor has logged into this site, the Session variable MM_Username set equal to their username.

  // Therefore, we know that a user is NOT logged in if that Session variable is blank.

  if (!empty($UserName)) {

    // Besides being logged in, you may restrict access to only certain users based on an ID established when they login.

    // Parse the strings into arrays.

    $arrUsers = Explode(",", $strUsers);

    $arrGroups = Explode(",", $strGroups);

    if (in_array($UserName, $arrUsers)) {

      $isValid = true;

    }

    // Or, you may restrict access to only certain users based on their username.

    if (in_array($UserGroup, $arrGroups)) {

      $isValid = true;

    }

    if (($strUsers == "") && true) {

      $isValid = true;

    }

  }

  return $isValid;

}

 

 

$MM_restrictGoTo = "index.php";

if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {  

  $MM_qsChar = "?";

  $MM_referrer = $_SERVER['PHP_SELF'];

  if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";

  if (isset($_SERVER['QUERY_STRING']) && strlen($_SERVER['QUERY_STRING']) > 0)

  $MM_referrer .= "?" . $_SERVER['QUERY_STRING'];

  $MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);

  header("Location: ". $MM_restrictGoTo);

  exit;

}

?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

  if (PHP_VERSION < 6) {

    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  }

 

 

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

 

 

  switch ($theType) {

    case "text":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;   

    case "long":

    case "int":

      $theValue = ($theValue != "") ? intval($theValue) : "NULL";

      break;

    case "double":

      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

      break;

    case "defined":

      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

      break;

  }

  return $theValue;

}

}

 

 

$editFormAction = $_SERVER['PHP_SELF'];

if (isset($_SERVER['QUERY_STRING'])) {

  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);

}

 

 

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

  $insertSQL = sprintf("INSERT INTO donation (name, url) VALUES (%s, %s)",

                       GetSQLValueString($_POST['name'], "text"),

                       GetSQLValueString($_POST['url'], "text"));

 

 

  mysql_select_db($database_*******, $*****);

  $Result1 = mysql_query($insertSQL, $*******) or die(mysql_error());

 

 

  $insertGoTo = "insert_success.php";

  if (isset($_SERVER['QUERY_STRING'])) {

    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";

    $insertGoTo .= $_SERVER['QUERY_STRING'];

  }

  header(sprintf("Location: %s", $insertGoTo));

}

?>

 

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

<title>Insert data</title>

<style type="text/css">

@import url("../Style sheet.css");

a:link {

          color: #D91327;

}

a:visited {

          color: #D91327;

}

a:hover {

          color: #CEB239;

}

a:active {

          color: #D91327;

}

</style>

</head>

 

 

<body>

<div id="container"><img src="../banner.jpg" width="1082" height="187" alt="banner" />

<p> </p>

<p class="admin_heading">Insert Record to Database</p>

<p> </p>

<form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">

  <table align="center">

    <tr valign="baseline">

      <td height="25" align="right" nowrap="nowrap" bgcolor="#E6CC7F">Deceased Name:</td>

      <td height="25" bgcolor="#E6CC7F"><input type="text" name="name" value="" size="70" /></td>

    </tr>

    <tr valign="baseline">

      <td height="25" align="right" nowrap="nowrap" bgcolor="#F5E7B8">Deceased Unique Url:</td>

      <td height="25" bgcolor="#F5E7B8"><input type="text" name="url" value="" size="70" /></td>

    </tr>

    <tr valign="baseline">

      <td nowrap="nowrap" align="right"> </td>

      <td><input type="submit" class="submit" value="Insert record" /></td>

    </tr>

  </table>

  <input type="hidden" name="MM_insert" value="form1" />

</form>

 
Replies
  • Currently Being Moderated
    Nov 6, 2013 4:52 AM   in reply to Ness_quick

    Create another column in your database called url_2 then update the mysql as below:

     

    if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

      $insertSQL = sprintf("INSERT INTO donation (name, url, url_2) VALUES (%s, %s, %s)",

                           GetSQLValueString($_POST['name'], "text"),

                           GetSQLValueString($_POST['url'], "text"));

                           GetSQLValueString($_POST['url_2'], "text"));

     

     

    Then you will have to create another input field in your form named url_2 (or whatever you want to name it).

     

     

    <td height="25" align="right" nowrap="nowrap" bgcolor="#F5E7B8">Deceased Unique Url_2:</td>

          <td height="25" bgcolor="#F5E7B8"><input type="text" name="url_2" value="" size="70" /></td>

     

     

    Now you need to include the url_2 information from the database (if a url_2 has been inserted into it) on the deceased donations page.

     

    Post that page code here and I can have a look and see how to work it in.

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 6, 2013 4:59 AM   in reply to Ness_quick

    Ok, my mistake I think. The double )) and ; after the first url  should be a single bracket and single comma as below:

     

     

    GetSQLValueString($_POST['name'], "text"),

    GetSQLValueString($_POST['url'], "text"),

    GetSQLValueString($_POST['url_2'], "text"));

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 6, 2013 5:22 AM   in reply to Ness_quick

    You have some code incorrect in your current table (I'm sure you can see that in Dreamweaver) Copy the below table and replace the current table code. If there is a second url link in the database column url_2 then another row will be added to the table showing the url_2. If the url_2 column is empty then only a tablewith one row will show.

     

     

    <table width="690" border="0" align="center" cellpadding="4" cellspacing="0">

    <tr>

    <td style="border-right: #c8a263 thin solid;" width="345" bgcolor="#EDD994" ><?php echo $row_rsDonationResults['name']; ?></td>

    <td bgcolor="#EDD994" width="345"><a href="http://<?php echo $row_rsDonationResults['url']; ?>">http://<?php echo $row_rsDonationResults['url']; ?></a></td>

    </tr>

    <?php if(!empty($row_rsDonationResults['url_2'])) { ?>

    <tr>

    <td style="border-right: #c8a263 thin solid;" width="345" bgcolor="#EDD994" > </td>

    <td bgcolor="#EDD994" width="345"><a href="http://<?php echo $row_rsDonationResults['url_2']; ?>">http://<?php echo $row_rsDonationResults['url_2']; ?></a></td>

    </tr>

    <?php } ?>

    </table>

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 6, 2013 5:41 AM   in reply to Ness_quick

    NO, not your first 'search' table, the table which shows the results (between the  php show region below)

     

     

    <?php if ($totalRows_rsDonationResults > 0) { // Show if recordset not empty ?>

     

     

     

     

     

     

      <?php } // Show if recordset not empty ?>

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 6, 2013 6:12 AM   in reply to Ness_quick

    You need to update this bit as well (like below)

     

    UPDATE donation SET name=%s, url=%s, url_2=%s WHERE donationID=%s

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 6, 2013 6:24 AM   in reply to Ness_quick

    Ness_quick wrote:

     

    Oh yes i see. The last thing, i'll need to add another field to the database. Should all the settings be the same as the first url field but with the name/title, url_2?

     

    Yes.

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 6, 2013 6:55 AM   in reply to Ness_quick

    Ness_quick wrote:

     

    Ive just tested it and it works perfectly thank you.

     

     

    Great, thanks for the confirmation.

     

     

    Ness_quick wrote:

     

    When you helped me develop this function some time ago, there were comments from different posts informing me that the way this system is set up it might be dropped. Do you have any knowledge of when this will happen or if its even definite?

     

    Don't worry its not likely to happen in the foreseeable future. It's just a heads up to start looking at a better more streamlined way to connect and retrieve data from a database mysqli the 'i' stands for improved.

     

    I suspect if and when it happens you will get a lot of hosts offering previous versions of php which will go on supporting mysql for some years to come.

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 6, 2013 10:08 AM   in reply to Ness_quick

    >The problem i have is, justgiving only allows the one charity per page so if the

    >family have chosen to have more than the one charity there would need to be

    >2 pages created under the same name, therefor 2 urls are needed BUT only

    >the one url per name can be entered into the funeral database. Is there some

    >way that 2 url links can present?

     

    And what happens if they need to donate to 3 charities? Or 4? That's why you should never have repeating columns (url1, url2, etc) in your tables. The correct solution is to create a new table that stores the urls and is linked to the other table.

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 6, 2013 12:53 PM   in reply to bregent

    bregent wrote:

     

     

    And what happens if they need to donate to 3 charities? Or 4? That's why you should never have repeating columns (url1, url2, etc) in your tables. The correct solution is to create a new table that stores the urls and is linked to the other table.

    Youre right but I think its a little beyond the OP's capabilities at the moment. Its beyond mine as well to be honest. I see no reason NOT to have url1 url2 url3 url4 etc. In my opinion its much easier to work with if one has limited knowledge and capabilities. Once youre more experienced with relationship tables then maybe. It's something I havent explored greatly as yet. Most of what I encounter can be done using one table.

     

    Also I dont know if DW is currently capable of choosing/updating data from/for more than one table without having to write it manually, so for some one table is the easy option and works well.

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 6, 2013 1:49 PM   in reply to osgood_

    >I see no reason NOT to have url1 url2 url3 url4 etc.

     

    Well, there are several problems. First, just how many fields do you decide to include in the table? What do you base that number on? If you have 3, someone will want 4. Every time you decide you need more, you have to modify the database table, and modify the php code display the column value. And analyzing the data -which will be a requirement later on I guarantee- is much simpler with normalized data.

     

     

    >In my opinion its much easier to work with if

    >one has limited knowledge and capabilities.

     

    It's like moving from html table layout to CSS. There's a bit of a learning curve but once you know it, it's much simpler. Working with properly organized data is always easier that working with a flawed design.

     

    >Also I dont know if DW is currently capable of

    >choosing/updating data from/for more than one table

    >without having to write it manually,

     

    That's certainly a problem if it's not supported by the limited functionality of DW server behaviors - but it's just a simple repeating region. At some point, if you're going to working on dynamic sites you need to learn code.

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 7, 2013 12:46 AM   in reply to bregent

    bregent wrote:

     

    >I see no reason NOT to have url1 url2 url3 url4 etc.

     

    Well, there are several problems. First, just how many fields do you decide to include in the table? What do you base that number on? If you have 3, someone will want 4. Every time you decide you need more, you have to modify the database table, and modify the php code display the column value. And analyzing the data -which will be a requirement later on I guarantee- is much simpler with normalized data.

     

     

    I agree but I feel you're making a mountain out of a mole hill. Even if there were ten url fields it's still a simple process to include all these in one table. I'm not saying it's the most effiecient way by any means but you also have to take into consideration peoples abilities and skills to be able to implement solutions when providing them.

     

    Learning about foreign keys and relationship tables is going to take more time and a bigger leaning curve if you only know basic msql or none at all. Once you move over to this area you need to hand code everything and that's a lot of work for some people.

     

     

    bregent wrote:

     

    >In my opinion its much easier to work with if

    >one has limited knowledge and capabilities.

     

    It's like moving from html table layout to CSS. There's a bit of a learning curve but once you know it, it's much simpler. Working with properly organized data is always easier that working with a flawed design.

     

    Yes I do agree but when it's not strictly necessary use a table.

     

    Why mess around with some kind of css definition list to layout data or a form when a table does it more efficiently.

     

     

    bregent wrote:

     

    That's certainly a problem if it's not supported by the limited functionality of DW server behaviors - but it's just a simple repeating region. At some point, if you're going to working on dynamic sites you need to learn code.

     

    Learning hand-code msql/php IS the biggest problem. Those who use Dreamweaver and produce dynamic sites relied extensively on the limited options that the software server behaviours provided.

    Certainly learning more is key to becoming more competent but then again you need to assess at what level you are and what kind of sites you are building. As yet I have had no need for any more that one database table to store information relating to specific information. Its worked like a dream so far BUT there is a possibility at some stage in the future I might well need to use 2 tables to break up the information because it gets to long/complex.

     

    For instance if as you say you have 30/40/50 urls that's where I could see the solution would be useful but for a project that is unlikely to have any more that a handful of options one table does the job just fine in my opinion.

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 8, 2013 9:06 AM   in reply to osgood_

    >For instance if as you say you have 30/40/50

    >urls that's where I could see the solution

     

    I would never put repeating columns in a table - not even for two. Once you understand how to work with related tables, it's much easier to code and maintain than working with poorly normalized tables.

     

    However, I do understand the need to get the job done vs. doing it the right way. Unfortunately, the DW server behaviors are so limited, you either need to resort to kludges like this, or spend the time to learn to write code by hand.

     

    Don't be afraid to work with databases with multiple tables. To put things into perspective, I'm currently working on a system with an estimated 125,000 tables!

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 8, 2013 9:17 AM   in reply to bregent

    bregent wrote:

     

    >For instance if as you say you have 30/40/50

    >urls that's where I could see the solution

     

    I would never put repeating columns in a table - not even for two. Once you understand how to work with related tables, it's much easier to code and maintain than working with poorly normalized tables.

     

     

    I understand.

     

     

    bregent wrote:

     

     

    However, I do understand the need to get the job done vs. doing it the right way. Unfortunately, the DW server behaviors are so limited, you either need to resort to kludges like this, or spend the time to learn to write code by hand.

     

     

    It's a trade off when one has limited knowledege or doesn't have the budget to employ someone else to do it.

     

     

    bregent wrote:

     

    Don't be afraid to work with databases with multiple tables. To put things into perspective, I'm currently working on a system with an estimated 125,000 tables!

     

    Eeeek! sounds a little scary to me. Good luck.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points