Need help with scripting to store data on MySQL

[mideastgirl]

Now it is just saying mysql_error

Does this mean it could not connect to mysql or that the information could not be added for some reason???

[anfetienne]

im wondering if its because of these on your sql insert

{$_POST['email address']}

try put it within quotes like below and see if that helps? (note it is a single quote then double quote)

'"{$_POST['email address']}"'

or assign them to vars like i have done in this code below

<?
session_start();
if(!$_SESSION['myusername']){
header("location:index.php");
}

include ("admin-dbcon.php");

$rtrnURL = $_POST['rtrnURL'];
$tempID = $_POST['tempID'];
$htmlcss = $_POST['htmlcss'];
$header = $_POST['header'];
$nav = $_POST['nav'];
$titleWrap01 = $_POST['titleWrap01'];
$title = $_POST['title'];
$titleWrap02 = $_POST['titleWrap02'];
$subheadWrap01 = $_POST['subheadWrap01'];
$subheading = $_POST['subheading'];
$subheadWrap02 = $_POST['subheadWrap02'];
$flashWrap01 = $_POST['flashWrap01'];
$flashWrap02 = $_POST['flashWrap02'];
$flash01 = $_POST['flash01'];
$flashVar = $_POST['flashVar'];
$flash02 = $_POST['flash02'];
$flashWrap03 = $_POST['flashWrap03'];
$descriptionWrap01 = $_POST['descriptionWrap01'];
$description = $_POST['description'];
$descriptionWrap02 = $_POST['descriptionWrap02'];
$generalWrap01 = $_POST['generalWrap01'];
$general = $_POST['general'];
$generalWrap02 = $_POST['generalWrap02'];
$aboutWrap01 = $_POST['aboutWrap01'];
$about = $_POST['about'];
$aboutWrap02 = $_POST['aboutWrap02'];
$paymentWrap01 = $_POST['paymentWrap01'];
$payment01 = $_POST['payment01'];
$payment02 = $_POST['payment02'];
$paymentWrap02 = $_POST['paymentWrap02'];
$termsWrap01 = $_POST['termsWrap01'];
$terms = $_POST['terms'];
$termsWrap02 = $_POST['termsWrap02'];
$footer = $_POST['footer'];

// OPEN CONNECTION ---> 
mysql_connect($hostname,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

// Select column 1 from table name where column name = $your_var.
$sql = "SELECT * FROM savedTemps WHERE tempID = '{$tempID}'";
// If mysql_query returns false, we'll die with the error.
$res = mysql_query( $sql ) or die( mysql_error );
 
// If a there is a match
if ( mysql_num_rows( $res ) > 0 )
{

$query="

UPDATE savedTemps SET 

		    htmlcss='{$htmlcss}', 
            header='{$header}', 
            nav='{$nav}', 
            titleWrap01='{$titleWrap01}', 
            title='{$title}',
			titleWrap02='{$titleWrap02}',
            subheadWrap01='{$subheadWrap01}', 
            subheading='{$subheading}',
			subheadWrap02='{$subheadWrap02}', 
            flashWrap01='{$flashWrap01}', 	
            flashWrap02='{$flashWrap02}', 
            flash01='{$flash01}', 
            flashVar='{$flashVar}',
            flash02='{$flash02}', 
            flashWrap03='{$flashWrap03}',
			descriptionWrap01='{$descriptionWrap01}',
            description='{$description}', 
            descriptionWrap02='{$descriptionWrap02}',
			generalWrap01='{$generalWrap01}', 
            general='{$general}', 
            generalWrap02='{$generalWrap02}', 
			aboutWrap01='{$aboutWrap01}',
            about='{$about}', 
            aboutWrap02='{$aboutWrap02}',
			paymentWrap01='{$paymentWrap01}', 
            payment01='{$payment01}', 
            payment02='{$payment02}', 
            paymentWrap02='{$paymentWrap02}',
            termsWrap01='{$termsWrap01}', 
            terms='{$terms}', 
            termsWrap02='{$termsWrap02}',
			footer='{$footer}'

			WHERE tempID='{$tempID}'";
}
else
{
//  EXECUTE QUERY ---> 
$query="INSERT savedTemps (

		    tempID, 
		    htmlcss, 
            header, 
            nav, 
            titleWrap01, 
            title,
			titleWrap02,
            subheadWrap01, 
            subheading,
			subheadWrap02, 
            flashWrap01, 	
            flashWrap02, 
            flash01, 
            flashVar,
            flash02, 
            flashWrap03,
			descriptionWrap01,
            description, 
            descriptionWrap02,
			generalWrap01, 
            general, 
            generalWrap02, 
			aboutWrap01,
            about, 
            aboutWrap02,
			paymentWrap01, 
            payment01, 
            payment02, 
            paymentWrap02,
            termsWrap01, 
            terms, 
            termsWrap02,
			footer)

        VALUES(	
		    '".$tempID."', 
		    '".$htmlcss."', 
            '".$header."', 
            '".$nav."', 
            '".$titleWrap01."', 
            '".$title."',
			'".$titleWrap02."',
            '".$subheadWrap01."', 
            '".$subheading."',
			'".$subheadWrap02."', 
            '".$flashWrap01."', 	
            '".$flashWrap02."', 
            '".$flash01."', 
            '".$flashVar."', 
            '".$flash02."', 
            '".$flashWrap03."',
			'".$descriptionWrap01."',
            '".$description."', 
            '".$descriptionWrap02."',
			'".$generalWrap01."', 
            '".$general."', 
            '".$generalWrap02."', 
			'".$aboutWrap01."',
            '".$about."', 
            '".$aboutWrap02."',
			'".$paymentWrap01."', 
            '".$payment01."', 
            '".$payment02."', 
            '".$paymentWrap02."',
            '".$termsWrap01."', 
            '".$terms."', 
            '".$termsWrap02."',
			'".$footer."')";
}

			
//////-----> 
$result=mysql_query($query) or die("Error in query:".mysql_error()); 
//if ($result) 
    //echo mysql_affected_rows()." row inserted into the database effectively."; 

//  CLOSE CONNECTION ---> 
mysql_close(); 
?>
<script language="JavaScript">
<!--
window.location="<? print $rtrnURL?>";
//-->
</SCRIPT>

can you export you sql table to a text file and attach it to a post so i can test and see where you code is going wrong?

[mideastgirl]

SQL Table:

Database mideasthonors

Table structure for table add_member

Field Type Null Default
Institution varchar(45) No
Address 1 varchar(45) No
Address 2 varchar(45) No
City varchar(45) No
State varchar(2) No
Zip Code int(5) No 0
Website text No
Contact Name varchar(45) No
Position varchar(25) No
Phone Number int(12) No 0
Email text No

Dumping data for table add_member
Institution Address 1 Address 2 City State Zip Code Website Contact Name Position Phone Number Email

[Dormilich]

or assign them to vars like i have done in this code below

this would be a good place to apply mysql_real_esca pe_string() to prevent SQL injection

[anfetienne]

that is a good idea dorm....as always

im going to test this on mine

[mideastgirl]

Well I tried both of your suggestions and I keep getting the same result:


Parse error: syntax error, unexpected T_ENCAPSED_AND_ WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/content/m/i/d/mideasthonors/html/addmember_db.ph p on line 13

This is my script:
[code=php]
<?php
include ("admintasks-dbcon.php");
mysql_connect($ hostname,$usern ame, $password) OR DIE ('Unable to connect to database! Please try again later.');
mysql_select_db ($dbname);
$query = "SELECT * FROM add_member WHERE memberID = '{$memberID}'";
$result = mysql_query( $sql ) or die( mysql_error );

if ( mysql_num_rows( $res ) > 0 )
{

$query="
UPDATE add_member SET
$name = $_POST['name'],
$address 1 = $_POST['address 1'],
$address 2 = $_POST['address 2'],
$City= $_POST['city'],
$State = $_POST['state'],
$Zip Code = $_POST['zip code'],
$Website = $_POST['website'],
$Contact Name = $_POST['contact name'],
$Position = $_POST['position'],
$Phone Number = $_POST['phone number'],
$Email Address = $_POST['email address'])

WHERE memberID ='{$memberID}'" ;
}
else{

$query="

INSERT INTO add_member (

Member ID,
Name,
Address 1,
Address 2,
City,
State,
Zip Code,
Website,
Contact Name,
Position,
Phone Number,
Email Address)

VALUES

{$_POST['memberID']},
{$_POST['name']},
{$_POST['address 1']},
{$_POST['address 2']},
{$_POST['city']},
{$_POST['state']},
{$_POST['zip code']},
{$_POST['website']},
{$_POST['contact name']},
{$_POST['position']},
{$_POST['phone number']},
{$_POST['email address']})";

$result=mysql_q uery($query) or die("Error in query:".mysql_e rror());

if ( mysql_num_rows( $result ) > 0 )
echo mysql_affected_ rows()." row inserted into the database effectively.";

mysql_close();
}

session_start() ;
$_SESSION["Name"] = $_POST['name'];
header("locatio n:thankyouforbe comingamember.p hp");
?>
[/code]

[mideastgirl]

Dormilich,
Would I place it before each item that will be entered by the person becoming a member?

For example:
mysql_real_esca pe_string (name = $_POST['name']),

Would that we right?

[Dormilich]

Well I tried both of your suggestions and I keep getting the same result:


Parse error: syntax error, unexpected T_ENCAPSED_AND_ WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/content/m/i/d/mideasthonors/html/addmember_db.ph p on line 13

of course, look again at anfetienne's sample code and try to figure out the differences you made (despite the variable names)

[Dormilich]

mysql_real_esca pe_string (name = $_POST['name']),

Would that we right?

nope, this way you'd not use mysql_real_esca pe_string() at all (beside getting a warning for undefined constants). refer to the manual for the correct syntax.

sorry for the rather inconvenient way of my answer, but you need to learn those basic syntax stuff.

[Dormilich]

that is a good idea dorm....as always

im going to test this on mine

I'll stick to Prepared Statements--much safer than only escaping the data.

although PHP Filters are quite useful too.

[mideastgirl]

Noticed the difference between the two was the semi-colons at the end instead of the commas I used. I have replaced the commas with semi-colons but the problem is still happening!:(

[Dormilich]

one more hint.

check where anfetienne defined his variables.

[anfetienne]

one thing that has been bugging my brains on this is......is this a 1st process sign up?

i.e. is this page used to sign members up? if so there may not be a need for the UPDATE....you just do a straight INSERT

[Dormilich]

is this page used to sign members up?

according to the page.... yes

[anfetienne]

if its a sign up page then there is no need for the UPDATE query, it should be SELECT to check if the member exists

ok one problem of yours lies here, you have variables where the names of you table cloumns should be. take the dollar sign off and that may work.

 UPDATE add_member SET
             $name = $_POST['name'], 
             $address 1 = $_POST['address 1'], 
             $address 2 = $_POST['address 2'],
             $City= $_POST['city'], 
             $State = $_POST['state'], 
             $Zip Code = $_POST['zip code'],
             $Website = $_POST['website'],
             $Contact Name = $_POST['contact name'],
             $Position = $_POST['position'],
             $Phone Number = $_POST['phone number'],
             $Email Address = $_POST['email address'])

            WHERE memberID ='{$memberID}'";

secondly when inserting you really should use quotes where the values are....i.e. where you have {$_POST['email address']} is should be within quotes

INSERT INTO add_member (
  
             memberID,    
             Name,
             Address 1,
             Address 2,
             City, 
             State, 
             Zip Code, 
             Website, 
             Contact Name, 
             Position, 
             Phone Number, 
             Email Address)
  
 VALUES 
  
             {$_POST['memberID']},
             {$_POST['name']},
             {$_POST['address 1']},
             {$_POST['address 2']},
             {$_POST['city']},
             {$_POST['state']},
             {$_POST['zip code']},
             {$_POST['website']},
             {$_POST['contact name']},
             {$_POST['position']},
             {$_POST['phone number']},
             {$_POST['email address']})";

thirdly the sql you gave did not have a memerID column, you are running a SQL UPDATE to a specified column that doesn't exist. also on the UPDATE you have $memberID but no values assigned to the variable.