HTML/PHP form to MySQL Database

[xx_princess_xx]

I am really new to php and mysql,

 

I am doing a project for school work,

 

i am stuck at one particular point,

 

the database is of an estate agent, it has 4 tables,

agents table has an ID that is the primary key

this ID is the foriegn key in the properties table

 

i have created a html form that will insert property details into the table, it gives me an error something to do with the foriegn key,

 

in the form are you supposed you enter the foriegn key or is in supposed to be an option,

 

if so how do you do it.

 

any help will be really greatfull

[Ken2k7]

Hello princess,

Allowing people to enter in foreign keys is fine as long as they enter in the correct value. I much rather have them enter something else and use SQL to get the foreign key. But what's the error message?

 

Ken

[xx_princess_xx]

basically i want the user to be able to enter in property details from a form into the database

 

 

the error mesage i get is

 

Error: you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use neat "1" at line 3

 

[revraz]

Post the query and the echo of the query.

[xx_princess_xx]

This is the two tables i created in sql

 

CREATE TABLE agents (

  AID int(5) NOT NULL auto_increment,

  ANAME char(50) NOT NULL default 'unknown',

  ADDRESS char(200) NOT NULL default 'unknown',

  POST_CODE char(7) NOT NULL default 'unknown',

  PRIMARY KEY (AID)

) ENGINE=INNODB AUTO_INCREMENT=1;

 

 

CREATE TABLE properties (

  PID int(5) NOT NULL auto_increment,

  TYPE char(20) NOT NULL default 'unknown',

  PRICE int( NOT NULL,

  ADDRESS char(200) NOT NULL default 'unknown',

  POST_CODE char(7) NOT NULL default 'unknown',

  PHOTO blob NOT NULL,

  ISAVAILABLE char(3) NOT NULL,

  AID int(5) NOT NULL,

  PRIMARY KEY (PID),

  FOREIGN KEY (AID) references agents(AID)

  ON UPDATE CASCADE ON DELETE CASCADE

) ENGINE=INNODB AUTO_INCREMENT=1;

 

this form is for users to enter property details

 

<form action="properties_insert2.php" method="post">

<fieldset>

<p>Type: <input type="text" name="type"/></p>

<p>Price: <input type="text" name="price"/></p>

<p>Address: <input type="text" name="address"/></p>

<p>Post Code: <input type="text" name="post_code"/></p>

<p>Photo: <input type="text" name="photo"/></p>

<p>Available: <input type="text" name="isavailable"/></p>

<p><input type="submit"/></p>

</fieldset>

</form>

 

this code sends the form data to the database, but in my case it doesn't

 

<?php

$con = mysql_connect("localhost","root","");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

 

mysql_select_db("comreg098hassan", $con);

 

$sql="INSERT INTO Properties (Type, Price, Address, Post_Code, Photo, Isavailable)

VALUES

('$_POST[type]','$_POST[price]','$_POST[address]', '$_POST[post_code]', '$_POST[photo]', '$_POST[isavailable]'),";

 

if (!mysql_query($sql,$con))

  {

  die('Error: ' . mysql_error());

  }

echo "1 record added";

 

mysql_close($con)

?>

 

 

[kickstart]

Hi

 

You are inserting 6 values, but there are more than that many columns and they are set to "NOT NULL" . AID doesn't have a default value so it will object.

 

All the best

 

Keith

[Ken2k7]

Also, your table name is properties not Properties. Case-sensitive.

 

The best thing for those NOT NULL table column is to give them a DEFAULT value if applicable.

 

One more thing, you can't interpolating array entries into a string like that. I suggest string concatenation.

[xx_princess_xx]

sorry i dont understand what am i supposed to do to make the form send the data to the table

[Ken2k7]

There are a few things and a few options.

 

To address kickstart's issue, you can give the columns in properties that are set to NOT NULL a DEFAULT value. OR, insert something not null in your SQL.

 

To address the one I posted, you can change this"

$sql="INSERT INTO Properties (Type, Price, Address, Post_Code, Photo, Isavailable)
VALUES ('$_POST[type]','$_POST[price]','$_POST[address]', '$_POST[post_code]', '$_POST[photo]', '$_POST[isavailable]'),";

 

To:

$sql="INSERT INTO Properties (Type, Price, Address, Post_Code, Photo, Isavailable)
VALUES
('" . $_POST['type'] . "','" . $_POST['price'] . "','" . $_POST['address'] . "', '" . $_POST['post_code'] . "', '" . $_POST['photo'] . "', '" . $_POST[isavailable] . "'),";

 

OH!!! One more thing, give the submit button a name and check for it before running that INSERT SQL. You don't want to insert unless the form is submitted.

[xx_princess_xx]

ok i will try it hope it will work

[xx_princess_xx]

i tried what you suggested but am still getting the same error

 

have i set the primary and foriegn key correctly?

[Ken2k7]

To address kickstart's issue, you can give the columns in properties that are set to NOT NULL a DEFAULT value. OR, insert something not null in your SQL.

^ what did you do there?

[xx_princess_xx]

i added in test data directly into the database, but i dont think this is the issue,

 

all i want is the user to send the property details from the form to the database, this is not working, if i take out the foreign key it works, but my coursework says that it has to have this foreign key, what can i do to make this work?

[revraz]

echo your query...

 

echo $sql;

 

 

[Ken2k7]

What test value did you give your AID which is your FOREIGN KEY?

 

Edit, yeah can you echo your $sql and post an update version of your code? Thanks. By the way, please use

tags.

[xx_princess_xx]

sorry i am really lost i am really confused let me start over just give me a minute

[Ken2k7]

Woah hold up there hun!

 

That would just make us go in a circle. Can you just echo your $sql -

echo $sql;

 

Then copy and paste what you see here.

[xx_princess_xx]

I have 2 tables "agents" and "properties"

 

I have created a form on my web page for users to send data but it does not work, i get an error which says

 

Error: you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near "1" at line 3

 

CREATE TABLE agents (
  AID int(5) NOT NULL auto_increment,
  ANAME char(50) NOT NULL default 'unknown',
  ADDRESS char(200) NOT NULL default 'unknown', 
  POST_CODE char(7) NOT NULL default 'unknown',
  PRIMARY KEY (AID)
) ENGINE=INNODB AUTO_INCREMENT=1;

 

CREATE TABLE properties (
  PID int(5) NOT NULL auto_increment,
  TYPE char(20) NOT NULL default 'unknown',
  PRICE int( NOT NULL,
  ADDRESS char(200) NOT NULL default 'unknown',
  POST_CODE char(7) NOT NULL default 'unknown',
  PHOTO blob NOT NULL,
  ISAVAILABLE char(3) NOT NULL,
  AID int(5) NOT NULL,
  PRIMARY KEY (PID),
  FOREIGN KEY (AID) references agents(AID)
  ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB AUTO_INCREMENT=1;

 

<form action="properties_insert2.php" method="post">
<fieldset>
<p>Type: <input type="text" name="type"/></p>
<p>Price:	<input type="text" name="price"/></p>
<p>Address: <input type="text" name="address"/></p>
<p>Post Code: <input type="text" name="post_code"/></p>
<p>Photo: <input type="text" name="photo"/></p>
<p>Available: <input type="text" name="isavailable"/></p>
<p><input type="submit"/></p>
</fieldset>
</form>

 

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("comreg098hassan", $con);

$sql="INSERT INTO Properties (Type, Price, Address, Post_Code, Photo, Isavailable)
VALUES
('$_POST[type]','$_POST[price]','$_POST[address]', '$_POST[post_code]', '$_POST[photo]', '$_POST[isavailable]'),";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

mysql_close($con)
?>

 

when i add echo it gives me the same error just longer,

 

when i type in test data in my database for agents nothing appears in the foreign key in the property table,

 

sorry about the post before, i was just a little stressed ive been trying to solve this all day

[Ken2k7]

Um... you didn't do any of the changes we mentioned.

[revraz]

Change

 

die('Error: ' . mysql_error());

 

to

 

die("Error in $sql here" . mysql_error());

 

And copy/paste that result here.

[xx_princess_xx]

Error in INSERT INTO Properties (Type, Price, Address, Post_Code, Photo, Isavailable) VALUES ('detatched','100','123 brick lane', 'aw4 6yy', 'yes', 'yes'), hereYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

[Ken2k7]

In your SQL, change Properties to properties. Your table name is lowercased.

 

Also, you didn't include those other columns you set to be NOT NULL in your INSERT.

[xx_princess_xx]

I changed my SQL Properties to properties,

 

what other colums do you mean,

 

do you mean the primary key and foreighn key if so how to you include it

[Ken2k7]

The foreign key. You add it in you SQL. Problem is, how are you adding it? Like I know it references your other table, but are you supposed to determine that or the user who enters data into the form?

[xx_princess_xx]

  FOREIGN KEY (AID) references agents(AID)

 

this is the code in the sql

 

is it wrong?

 

how do i make it wright?

 

in the form do i add an option saying please select agent ID (a list of ID's from a drop menu)

then enter property details from a form

 

if so do you know how to do this or do you know a good tutorial