0

i am trying to insert my form data into my table 'supplier_registration', however this is not working, i am getting mysql 'ERROR', I am not sure why this is happening.

I am also wanting to insert the same form data into a second table 'supplier_session', but this time only inserting specific bits of the form data, '$cname' and '$creg'.

When a user lands on the page there ip address is inserted into the table 'supplier_session' so when this form is submitted i also want the supplier_session table to update an enum value 'form1_complete' from no to yes, where that user's ip or 'user_IP' exists in the table row. i am trying to d this by running three querys but its not working, please can someone show me where i am going wrong. Thanks

Database:

ID(AI) | company_name | company_reg_number | company_incorp_date | company_address | company_postcode | contact_name | contact_number | contact_email | company_vat_number | date_time | user_IP

Code:

<?php
session_start();

$db_hostname = 'localhost';
$db_database = 'hewden1'; 
$db_username = 'root';
$db_password = '';

$db_server = mysql_connect($db_hostname, $db_username, $db_password)    
        or die("Unable to connect to MySQL: " . mysql_error());

mysql_select_db($db_database)   
or die("Unable to select database: " . mysql_error());

 $cname       = $_POST['cname'];
   $creg      = $_POST['creg'];  
   $address      = $_POST['address'];  
   $post      = $_POST['post'];  
   $contactn      = $_POST['contactn'];
   $contactt      = $_POST['contactt'];
  $contacte      = $_POST['contacte'];
   $vat     = $_POST['vat'];
    $incorp      = $_POST['incorp'];

    $ipaddress = $_SERVER["REMOTE_ADDR"];




$sql="INSERT INTO supplier_registration (company_name, company_reg_number, company_incorp_date, company_address, company_postcode, contact_name, contact_number, contact_email, company_vat_number, date_time, user_ip)
    VALUES ('$cname', '$creg', '$incorp', $address', '$post', '$contactn', '$contactt', '$contacte', '$vat', NOW(), '$ipaddress')";

$result = mysql_query($sql); 


$qry2 = "INSERT INTO supplier_session (company_reg_number, company_name) VALUES('$creg','$cname') WHERE user_IP = '$ipaddress'";

 $result = @mysql_query($qry2);

$qry3="UPDATE supplier_session SET form1_completed = 'Yes' WHERE form1_completed = 'No' AND user_IP = '$ipaddress'";

$result = mysql_query($q); 



if($result){

header("Location: index.php?registration=sucess");


}else {
echo "ERROR";
}
?>

html: 

<form name="myForm" id="myform" action="company_info_process.php" onsubmit="return validateForm()" method="post">

<input type="text" id="field_cname" name="cname" class="field_cname">

<input type='text' name='creg' id="field_creg">

<input type='text' name='incorp' id="field_incorp">

<input type='text' name='vat' id="field_vat">

<input type='text' name='contactn' id="field_contactn">

<input type="text" name="contactt" id="field_contactt">

<input type="text" id="field_contacte" name="contacte">

<input type="text" id="field_address" name="address">

<input type="text" id="field_post" name="post"/>
John Taylor
  • 1,467
  • 2
  • 11
  • 15
  • "i am getting mysql 'ERROR'" Post the specific error in your question. – Patrick Q Apr 30 '14 at 16:06
  • You can't use a `WHERE` clause in an `INSERT` statement. `INSERT` is for adding new rows, not modifying existing rows, so how would the `WHERE` clause be used? – Barmar Apr 30 '14 at 16:08
  • that is the ERROR, mysql either executes or echo's out error – John Taylor Apr 30 '14 at 16:09
  • 1
    Your script is echoing `ERROR`. You should get the actual error message from MySQL with `echo mysql_error()`. – Barmar Apr 30 '14 at 16:10
  • Before you go any further, you should really learn about [SQL injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). – Patrick Q Apr 30 '14 at 16:13
  • Simple, change `$result = mysql_query($q);` to `$result = mysql_query($qry3);` @JohnTaylor There's your error. However, using the same variable `$result = mysql_query` for all your queries may also contribute to the problem. – Funk Forty Niner Apr 30 '14 at 16:18

2 Answers2

1

Firstly, you are calling the wrong variable in your third query.

Change $result = mysql_query($q); to $result = mysql_query($qry3);

Also, with the INSERT() syntax, you cannot have a WHERE() clause. The only time you will find INSERT that has a WHERE clause is when you are using an INSERT INTO...SELECT statement.

Besides that, the WHERE clause is mostly used when doing a SELECT() or UPDATE().

Quick sidenote: I noticed you don't have a closing </form> tag. If that is your actual code, add it.

Use error reporting at the top of your page(s):

error_reporting(E_ALL);
ini_set('display_errors', 1);

Plus, use:

else{
    echo mysql_errno($db_server) . ": " . mysql_error($db_server) . "\n";
    }

Instead of:

else {
echo "ERROR";
}

Visit PHP.net for more information on error reporting.

Footnotes:

mysql_* functions deprecation notice:

http://www.php.net/manual/en/intro.mysql.php

This extension is deprecated as of PHP 5.5.0, and is not recommended for writing new code as it will be removed in the future. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API.

These functions allow you to access MySQL database servers. More information about MySQL can be found at » http://www.mysql.com/.

Documentation for MySQL can be found at » http://dev.mysql.com/doc/.

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
-1

Where you run your call to mysql_query, output any error messages it generates:

$result = mysql_query($sql) or die(mysql_error());

Should give some indication of which query is causing the error and why.

Edit: Also, Noticed you have a WHERE clause within an INSERT statement, you can't do that.

Ross McLellan
  • 1,872
  • 1
  • 15
  • 19