[SOLVED] PHP and MySQL assistant

[Trium918]

 

Step 1: HTML Form

Step 2: PHP Script Handler

Step 3: Mysql Insert Into Database

 

Problem: The data isn't going into there create fields.

What is causing this problem.

 

<?php
// Connect to MySql
$db = mysql_connect("localhost") or die(mysql_error());

// selecting database
mysql_select_db("members") or die(mysql_error());

$query = "INSERT INTO members_info VALUES('NULL', '".$user_name."','".$email_address."','".$first_name."',
'".$last_name."','".$birth_month."','".$birth_day."','".$birth_year."','".$gender."','".$contact_number."')";

$result = mysql_query($query);
if ($result)
   echo "Data entered into database.";
else 
   echo "No data entered!!!";

?>	

[Barand]

Don't put quotes round NULL.

 

"No data entered" isn't very helpful when you are debugging. echo mysql_error() instead

[paul2463]

if you put this in

 

$result = mysql_query($query)or die ("Error in Query ". mysql_error());

 

[Trium918]

Problem: The data isn't going into there correct fields.

What is causing this problem?

 

This is how the table is set up

mysql> describe members_info;
+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| members_id     |int(10) unsigned |      | PRI | NULL    | auto_increment |
| user_name      |varchar(25)      |      |     |         |                |
| first_name      |varchar(25)      |      |     |         |                |
| last_name      | varchar(25)      |      |     |         |                |
| gender         | tinyint(6)       |      |     | 0       |                |
| birth_month    | varchar(9)       |      |     |         |                |
| birth_day      | tinyint(2)       |      |     | 0       |                |
| birth_year     | tinyint(4)       |      |     | 0       |                |
| contact_number | varchar(10)      |      |     |         |                |
| email_address  | varchar(100)     |      |     |         |                |
+----------------+------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

mysql>

 

<?php
// Connect to MySql
$db = mysql_connect("localhost") or die(mysql_error());

// selecting database
mysql_select_db("members") or die(mysql_error());

$query = "INSERT INTO members_info VALUES(NULL, '".$user_name."','".$email_address."','".$first_name."',
'".$last_name."','".$birth_month."','".$birth_day."','".$birth_year."','".$gender."','".$contact_number."')";

$result = mysql_query($query)or die ("Error in Query ". mysql_error());
if ($result)
   echo "Data entered into database.";
else 
   echo "No data entered!!!";

?>	

[Trium918]

Question: Could the order in which they are entered

from the html form to the php script cause this. I mean,

the Mysql database isn't in the same order as there entered.

[paul2463]

change the query to look like this

 

$query = "INSERT INTO tablename (COL1, COL2, COL3) VALUES ('$var1','$var2','$var3')";

 

$var1 will go into COL1 , $var2 will go into COL2 and so forth

 

$query = "INSERT INTO members_info (user_name,first_name,last_name,gender,birth_month,birth_day,birth_year,
contact_number,email_address)VALUES( '$user_name','$first_name','$last_name','$gender',
'$birth_month','$birth_day','$birth_year','$contact_number'','$email_address')";

 

you dont need the double ticks and periods around the variable names just single ticks will do and doing it this way you do not have to enter the null values for the autoincremented  id field, that is done automatically for you

[Trium918]

ok, but what if I have two different tables? Would I just run two

querts? Thanks for the first!!

[paul2463]

yes if you have two tables, run two queries, it makes code easier to read and understand, for me anyway, and the time difference between two queries as opposed to a difficult joined query is minuscule.

[Trium918]

Thanks.

[Trium918]

I am now running my second query.

 

Problem: Postal Code is showing up as zero. What may cause

this problem? Should members_id show up as a zero since it is

a Foreign Key?

 

Database table members_address

create table members_address(address_id int unsigned not null auto_increment primary key,
members_id int unsigned not null, street_address varchar(50) not null, 
city_county varchar(25) not null, state varchar(20) not null, postal_code int(5) not null,
index(members_id) );

 

<?php
$query2 = "INSERT INTO members_address VALUES(' ',' ','$street_address','$city_county','$state','$postal_code')";

$result2 = mysql_query($query2) or die ("Error in Query ". mysql_error());;
if ($result2)
	echo "Data entered into database.";
else 
	echo "No data entered!!!";	
?>

[Barand]

get into the habit of defining the field names for which you are supplying values

 

$query2 = "INSERT INTO members_address (address_id, members_id, street_address, city_county, state, postal_code)
            VALUES(NULL,'$member','$street_address','$city_county','$state','$postal_code')";

 

It makes it easier to maintain.

 

If you subsequently add a column to the table, or change the order of the columns, it won't fall over.

 

As member_id is a foreign key, it should have a valid value to maintain referential integrity.

 

Also, if you define the query as a string before calling mysql_query() you can easily echo it to see why problems are arising with variable values.

[Trium918]

get into the habit of defining the field names for which you are supplying values

 

$query2 = "INSERT INTO members_address (address_id, members_id, street_address, city_county, state, postal_code)
            VALUES(NULL,'$member','$street_address','$city_county','$state','$postal_code')";

 

It makes it easier to maintain.

 

If you subsequently add a column to the table, or change the order of the columns, it won't fall over.

 

As member_id is a foreign key, it should have a valid value to maintain referential integrity.

 

Also, if you define the query as a string before calling mysql_query() you can easily echo it to see why problems are arising with variable values.

 

Thanks, you are great.

Question: So the foreign key should have a value correct? Because it's producing 0 still.

[Barand]

I put $member there (which is probably the wrong variable name) just to illustrate that it should have a valid members id inserted. You original code just had ' '.

 

Change $member to the correct variable name that contains the id of the member for whom tou are inserting the address.

[Trium918]

Thanks Barand, but the results are the same. Foreign Key

$members_id inside of members_address table is zero instead of

auto_increment by 1 each insert.What it have something to do

with the way the structure of the database?

 

create table members_info(members_id int unsigned not null auto_increment primary key,
user_name varchar(25) not null, first_name varchar(25) not null, 
last_name varchar(25) not null, gender varchar(6) not null,
birth_month varchar(9) not null, birth_day tinyint(2) not null, birth_year int(4) not null,
contact_number varchar(10) not null, email_address varchar(100) not null  );

create table members_address(address_id int unsigned not null auto_increment primary key,
members_id int unsigned not null, street_address varchar(50) not null, 
city_county varchar(25) not null, state varchar(20) not null, postal_code int(5) not null,
index(members_id) );
[code[

[Barand]

Are you inserting a new members_info record and then inserting a members_address record for the new member?

 

If so, after you insert the member_info record, call

 

$member = mysql_insert_id();

 

then use the value that it gives for $member in the second insert .

[Trium918]

Are you inserting a new members_info record and then inserting a members_address record for the new member?

 

If so, after you insert the member_info record, call

 

$member = mysql_insert_id();

 

then use the value that it gives for $member in the second insert .

 

I am not sure what the H I am doing, still learning. Here is the code.

 

<?php
// Connect to MySql
$db = mysql_connect("localhost") or die(mysql_error());

// selecting database
mysql_select_db("members_super") or die(mysql_error());

$query = "INSERT INTO members_info (members_id,user_name,first_name,last_name,gender,birth_month,birth_day,
birth_year,contact_number,email_address) VALUES(NULL,'$user_name','$first_name','$last_name'
,'$gender','$birth_month','$birth_day','$birth_year','$contact_number','$email_address')";

$result = mysql_query($query) or die ("Error in Query ". mysql_error());;
if ($result)
	echo "Data entered into database.";
else 
	echo "No data entered!!!";	

$query2 = "INSERT INTO members_address (address_id, members_id, street_address, city_county, state, postal_code)
            VALUES(NULL,'$members_id','$street_address','$city_county','$state','$postal_code')";


$result2 = mysql_query($query2) or die ("Error in Query ". mysql_error());;
if ($result2)
	echo "Data entered into database.";
else 
	echo "No data entered!!!";	


?>

 

[Barand]

try

<?php
// Connect to MySql
$db = mysql_connect("localhost") or die(mysql_error());

// selecting database
mysql_select_db("members_super") or die(mysql_error());

$query = "INSERT INTO members_info (members_id,user_name,first_name,last_name,gender,birth_month,birth_day,
birth_year,contact_number,email_address) VALUES(NULL,'$user_name','$first_name','$last_name'
,'$gender','$birth_month','$birth_day','$birth_year','$contact_number','$email_address')";

$result = mysql_query($query) or die ("Error in Query ". mysql_error());
    
if ($result) {
            $members_id = mysql_insert_id();
        
    $query2 = "INSERT INTO members_address (address_id, members_id, street_address, city_county, state, postal_code)
                VALUES(NULL,'$members_id','$street_address','$city_county','$state','$postal_code')";


    $result2 = mysql_query($query2) or die ("Error in Query ". mysql_error());;
    
            echo "Data entered into database.";
        
    }
    else 
	echo "No data entered!!!";		

?>

[Trium918]

Thanks Barand. I figured it out.