Jump to content

retrieve data and insert or update

zimmo

By zimmo
in PHP Coding Help

 Share

Recommended Posts

zimmo Regular Member

zimmo

Posted
Posted

I have a problem with my script. I have been modifying it to get it to work but without success. I am not sure of the best way to do this.

 

I need to query the database to see if something exists, if it does to enter the data into the form fields, if it does not to just show the blank form.

 

Then I need it to process the form.

 

As you can see I have changed the form as I am using mysql 5 so doing a duplicate query.

 

Here is how I have it set up at present

<?php
session_start();
if (!(isset($_SESSION['username']) && $_SESSION['username'] != '')) {
header ("Location: fishery_login.html");
exit;
}
// Include the connections script to make a database connection.
include("inc/connect.php");

if ( $_POST['submit'] ) {
$primary_contact = $_POST['primary_contact'];
$address_1 = $_POST['address_1'];
$address_2 = $_POST['address_2'];
$address_3 = $_POST['address_3'];
$town = $_POST['town'];
$county = $_POST['county'];
$postcode = $_POST['postcode'];
$email = $_POST['email'];
$telephone_1 = $_POST['telephone_1'];
$telephone_2 = $_POST['telephone_2'];
$fax = $_POST['fax'];

if ( empty($primary_contact) ) {
  $error['primary_contact_error'] = '<div class="formerror">Please enter your Primary Contact Name.</div>';
}
if ( empty($town) ) {
  $error['town_error'] = '<div class="formerror">Please Enter your Town/City.</div>';
}
if ( empty($county) ) {
  $error['county_error'] = '<div class="formerror">Please Select your County.</div>';
}
if ( empty($postcode) ) {
  $error['postcode_error'] = '<div class="formerror">Please enter your Postcode.</div>';
}
if ( empty($telephone_1) ) {
  $error['telephone_1_error'] = '<div class="formerror">Please enter your Main Telephone Number.</div>';
}
if (!$error) {
  # setup SQL statement

  $SQL = " INSERT INTO fishery_b_details (primary_contact, address_1, address_2, address_3, town, county, postcode, email, telephone_1, telephone_2, fax) VALUES (primary_contact = '$_POST[primary_contact]', address_1 = '$_POST[address_1]', address_2 = '$_POST[address_2]', address_3 = '$_POST[address_3]', town = '$_POST[town]', county = '$_POST[county]', postcode = '$_POST[postcode]', email = '$_POST[email]', telephone_1 = '$_POST[telephone_1]', telephone_2 = '$_POST[telephone_2]', fax = '$_POST[fax]') ON DUPLICATE KEY UPDATE primary_contact = '$_POST[primary_contact]', address_1 = '$_POST[address_1]', address_2 = '$_POST[address_2]', address_3 = '$_POST[address_3]', town = '$_POST[town]', county = '$_POST[county]', postcode = '$_POST[postcode]', email = '$_POST[email]', telephone_1 = '$_POST[telephone_1]', telephone_2 = '$_POST[telephone_2]', fax = '$_POST[fax]' ";
  #execute SQL statement
  $result = mysql_db_query( *****,"$SQL",$connection );

  # check for error
  if (!$result) { 
   echo("ERROR: " . mysql_error() . "\n$SQL\n");  
  } else {
  header("Location: http://www.*****.com/development/fisheries_admin/fishery_details_view.html");
  }
}
}
?>

 

Then within my form I have the following:

<?
$sql = "SELECT * FROM fishery_b_details WHERE fishery_id = '$_SESSION[fishery_id]' "; 
$sql_result = mysql_query($sql); 

if (mysql_num_rows($sql_result) >0)
{
echo ("\n");
} 
else {
       while ($row = mysql_fetch_array($sql_result)){
        $primary_contact = $row["primary_contact"];
        $address_1 = $row["address_1"];
        $address_2 = $row["address_2"];
        $address_3 = $row["address_3"];
        $town = $row["town"];
        $county = $row["county"];
        $postcode = $row["postcode"];
        $email = $row["email"];
        $telephone_1 = $row["telephone_1"];
        $telephone_2 = $row["telephone_2"];
        $fax = $row["fax"];
?>

 

As you can see the echo ("\n"); is causing the form to not display. And without me putting the whole form in that echo it skips it.

 

Is there an easier way?

Link to comment
Share on other sites
zimmo Regular Member

zimmo

Posted
  • Author
Posted

I have worked this out by placing the html inside:

<? ob_start(); ?>

<html>

<? echo ob_get_clean(); ?>

 

So have managed to get it to work. Now I have issues with the data being inserted, I am using mysql version 5.0.32 which I understand supports the INSERT INTO and ON DUPLICATE KEY UPDATE.

 

My data is not updating correctly at all. When I checked in most fields it is inserting the number 1 (this is the id number) and not the data from the form.

 

The code I have for the query is:

  $SQL = " INSERT INTO fishery_b_details (primary_contact, address_1, address_2, address_3, town, county, postcode, email, telephone_1, telephone_2, fax) VALUES (primary_contact = '$_POST[primary_contact]', address_1 = '$_POST[address_1]', address_2 = '$_POST[address_2]', address_3 = '$_POST[address_3]', town = '$_POST[town]', county = '$_POST[county]', postcode = '$_POST[postcode]', email = '$_POST[email]', telephone_1 = '$_POST[telephone_1]', telephone_2 = '$_POST[telephone_2]', fax = '$_POST[fax]') ON DUPLICATE KEY UPDATE primary_contact = '$_POST[primary_contact]', address_1 = '$_POST[address_1]', address_2 = '$_POST[address_2]', address_3 = '$_POST[address_3]', town = '$_POST[town]', county = '$_POST[county]', postcode = '$_POST[postcode]', email = '$_POST[email]', telephone_1 = '$_POST[telephone_1]', telephone_2 = '$_POST[telephone_2]', fax = '$_POST[fax]' ";

 

The fishery_id in the database is a primary key.

Link to comment
Share on other sites
zimmo Regular Member

zimmo

Posted
  • Author
Posted

I checked and it is inserting the fishery id into all the fields I am entering data into. Its not inserting the id into the correct field or the venue name. I checked and they were not in the query so I changed it but still the same.

 

Also, if I then resbmit the form and enter the data it actually submits.?

Link to comment
Share on other sites
AdRock Advanced Member

AdRock

Posted
Posted

A function from w3schools to prevent against database attacks

<?php
function check_input($value)
{
// Stripslashes
if (get_magic_quotes_gpc())
  {
  $value = stripslashes($value);
  }
// Quote if not a number
if (!is_numeric($value))
  {
  $value = "'" . mysql_real_escape_string($value) . "'";
  }
return $value;
}
?>

 

then change to 

$primary_contact = check_input($_POST['primary_contact']);
$address_1 =check_input( $_POST['address_1']);
$address_2 = check_input($_POST['address_2']);
$address_3 = check_input($_POST['address_3']);
$town = check_input($_POST['town']);
$county = check_input($_POST['county']);
$postcode = check_input($_POST['postcode']);
$email = check_input($_POST['email']);
$telephone_1 = check_input($_POST['telephone_1');
$telephone_2 =check_input( $_POST['telephone_2']);
$fax = check_input($_POST['fax']);

 

your query should be like

$SQL = " INSERT INTO fishery_b_details (primary_contact, address_1, address_2, address_3, town, county, postcode, email, telephone_1, telephone_2, fax) VALUES
($primary_contact, $address_2, address_3 = '$_POST[address_3]', $town, $county, $postcode, $email, $telephone_1, $telephone_2, $fax) ON DUPLICATE KEY UPDATE 
primary_contact='$primary_contact', address_1='$address_1', address_2='$address_2', address_3='$address_3', town='$town', county='$county', postcode='$postcode', email='$email', telephone_1='$telephone_1', telephone_2='$telephone_2', fax='$fax' ";

 

this line should be

$sql = "SELECT * FROM fishery_b_details WHERE fishery_id = '".$_SESSION['fishery_id']."' ";

 

Have you got the form to display now?

 

Fix these errors and see what happens

 

It should be easy to fix your problems

 

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.