Please Help - why is my data not writing to the table?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

ummhasan
Forum Commoner
Posts: 31
Joined: Sun Jun 13, 2010 7:24 pm
Location: USA

Please Help - why is my data not writing to the table?

Post by ummhasan »

Hi, below is my code........can someone please tell me why the data is NOT writing to the mySQL table. I created the tables using myPHPadmin that's why create table is not in my code.

Thanks in advance - below is the code:

Code: Select all

$con = mysql_pconnect("my_host","my_user_name","my_password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

$db_selected = mysql_select_db("ilmcompetition", $con);

if (!$db_selected)
  {
  die ("Can\'t use test_db : " . mysql_error());
  }


//create short variable names
	$name1=$_POST[name1];						$ph1=$_POST[ph1];				$dob1=$_POST[dob1];
	$parentName1=$_POST[parentName1];			$email1=$_POST[email1];			$address1=$_POST[address1];
	$city1=$_POST[city1];						$state1=$_POST[state1];			$zip1=$_POST[zip1]; 
	$name2=$_POST[name2];						$ph2=$_POST[ph2]; 				$dob2=$_POST[dob2];
	$parentName2=$_POST[parentName2];			$email2=$_POST[email2];			$address2=$_POST[address2];
	$city2=$_POST[city2];						$state2=$_POST[state2];			$zip2=$_POST[zip2];
	$levels=$_POST[levels];						$submit=$_POST[submit];
	
//add slashes before special charachters when input to database	
  if (!get_magic_quotes_gpc()) {
	$name1 = addslashes($name1);				$ph1 = addslashes($ph1);			$dob1 = addslashes($dob1);
	$parentName1 = addslashes($parentName1);	$email1 = addslashes($email1);		$address1 = addslashes($address1);
	$city1 = addslashes($city1);				$state1 = addslashes($state1);		$zip1 = addslashes($zip1);
	$name2 = addslashes($name2);				$ph2 = addslashes($ph2);			$dob2 = addslashes($dob2);
	$parentName2 = addslashes($parentName2);	$email2 = addslashes($email2);		$address2 = addslashes($address2);
	$city2 = addslashes($city2);				$state2 = addslashes($state2);		$zip2 = addslashes($zip2);
		}

//make short variable for radio buttons
   	$preE_status=$_POST[preE];						$elem_status=$_POST[elem];			$inter_status=$_POST[inter];
	$junior_status=$_POST[junior];					$senior_status=$_POST[senior];
			
	switch($levels)
{
   case "preE": 
   		$preE_status='checked';
   	   	$query="INSERT INTO pre_elementary (id, name, address, city, state, zip, phone, dob, parent, email, pName, pAddress, pCity, pState, pZip, pPhone, pParent, pEmail)
	VALUES	(NULL, '".$name1."','".$ph1."','".$dob1."','".$parentName1."','".$email1."','".$address1."','".$city1."','".$state1."','".$zip1."','".$name2."','".$ph2."','".$dob2."','".$parentName2."','".$email2."','".$address2."','".$city2."','".state2."','".$zip2."'".$pPhone."','".$pParent."','".pEmail."')";
		$sql = "SELECT * FROM pre_elementary";
		mysql_query($sql,$con);
	break;
   
   case "elem": 
   		$elem_status='checked'; 
   		$query="INSERT INTO elementary (name, address, city, state, zip, phone, dob, parent, email, pName, pAddress, pCity, pState, pZip, pPhone, pParent, pEmail)
	VALUES	(('".$name1."','".$ph1."','".$dob1."','".$parentName1."','".$email1."','".$address1."','".$city1."','".$state1."','".$zip1."','".$name2."','".$ph2."','".$dob2."','".$parentName2."','".$email2."','".$address2."','".$city2."','".state2."','".$zip2."'".$pPhone."','".$pParent."','".pEmail."')";
			  break;
   
   case "inter": 
   		$inter_status='checked';
   		$query="INSERT INTO intermediate (name, address, city, state, zip, phone, dob, parent, email, pName, pAddress, pCity, pState, pZip, pPhone, pParent, pEmail)
	VALUES	('".$name1."','".$ph1."','".$dob1."','".$parentName1."','".$email1."','".$address1."','".$city1."','".$state1."','".$zip1."','".$name2."','".$ph2."','".$dob2."','".$parentName2."','".$email2."','".$address2."','".$city2."','".state2."','".$zip2."'".$pPhone."','".$pParent."','".pEmail."')";
   	break;
	
   case "junior": 
   		$junior_status='checked';
   		$query="INSERT INTO junior (name, address, city, state, zip, phone, dob, parent, email, pName, pAddress, pCity, pState, pZip, pPhone, pParent, pEmail)
	VALUES	('".$name1."','".$ph1."','".$dob1."','".$parentName1."','".$email1."','".$address1."','".$city1."','".$state1."','".$zip1."','".$name2."','".$ph2."','".$dob2."','".$parentName2."','".$email2."','".$address2."','".$city2."','".state2."','".$zip2."'".$pPhone."','".$pParent."','".pEmail."')";
     break;
   
   case "senior": 
   		$senior_status='checked';   
	   	$query="INSERT INTO senior (name, address, city, state, zip, phone, dob, parent, email, pName, pAddress, pCity, pState, pZip, pPhone, pParent, pEmail)
	VALUES	('".$name1."','".$ph1."','".$dob1."','".$parentName1."','".$email1."','".$address1."','".$city1."','".$state1."','".$zip1."','".$name2."','".$ph2."','".$dob2."','".$parentName2."','".$email2."','".$address2."','".$city2."','".state2."','".$zip2."'".$pPhone."','".$pParent."','".pEmail."')";
   break;
   
   default:
   		echo "You did not select a level please go back and select a level.";
}

	//checks for data results then displays results to user and confirms submission
	
	
//field validation - if field is null user is asked to complete otherwise confirm and show entries
	 if (!$name1 || !$ph1 || !$dob1 || !$parentName1 || !$email1 || !$address1 || !$city1 || !$state1 || !$zip1) {
	 echo "You have not entered all the required information.  Please go back and fully complete the form.";
	 } elseif (!$levels) {
		 echo "You did not specify which level of competition you are entering.  Please go back and specify a level.";
	 } else {
	 echo "<h2>Jazak Allah Khair for registering, you will receive an email shortly with your registration number in sha Allah.</h2>";
	 echo "<p><strong>Your registration information is also below.</strong></p>";
	 echo "<table cellpadding='3'>";
	 echo "<tr><td>Name:&nbsp;".$name1."</td><td>Phone:&nbsp;".$ph1."</td><td>DOB:&nbsp;".$dob1."</td><td></td></tr>";
	 echo "<tr><td>Parent:&nbsp;".$parentName1."</td><td>Parent Email:&nbsp;".$email1."</td><td></td><td></td></tr>";
	 echo "<tr><td>Address:&nbsp;".$address1."</td><td>".$city1."</td><td>".$state1."</td><td>".$zip1."</td></tr></table>";
	 }
	 
	 if ($name2 || $ph2 || $dob2 || $parentName2 || $email2 || $address2 || $city2 || $state2 || $zip2) {
	 echo "<table cellpadding='3'><tr><td>Partners Name:&nbsp;".$name2."</td><td>Partners Phone:&nbsp;".$ph2."</td><td>Partners DOB:&nbsp;".$dob2."</td><td></td></tr>";
	 echo "<tr><td>Partners Parent:&nbsp;".$parentName2."</td><td>Partners Email:&nbsp;".$email2."</td><td></td><td></td></tr>";
	 echo "<tr><td>Partners Address:&nbsp;".$address2."</td><td>".$city2."</td><td>".$state2."</td><td>".$zip2."</td></tr>";
	 } else {
	 echo "<tr><td colspan='4'>You have choosed to enter without a partner - if you change your mind, please contact us via email.</td></tr></table>";
	 }
	
mysql_close($con);
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Please Help - why is my data not writing to the table?

Post by andyhoneycutt »

What version of PHP are you running? 8O
ummhasan
Forum Commoner
Posts: 31
Joined: Sun Jun 13, 2010 7:24 pm
Location: USA

Re: Please Help - why is my data not writing to the table?

Post by ummhasan »

PHP5
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Please Help - why is my data not writing to the table?

Post by andyhoneycutt »

Your code could use some serious refactoring, and maybe some outright rewriting. First, I'd suggest moving to a more modern approach to interacting with databases in PHP: PHP Data Objects. Addslashes isn't the best way to handle data sanitizing, at the very least you should be looking at mysqli_* stuff, using mysql_real_escape_string. Magic quotes are also a good way to ruin your data and/or provide massive headaches once you have the data...
Here's an example using PDO:

Code: Select all

$p = new PDO("mysql:host=my_host;dbname=ilmcompetition", my_username, my_password) 
  or 
    die("Couldn't connect to specificied datasource.");

// Create a tokenized query string to pass to our statement object.
$query = "
  INSERT INTO pre_elementary (
    id, name, address, city, state, zip, phone, dob, parent, email, 
    pName, pAddress, pCity, pState, pZip, pPhone, pParent, pEmail
  ) VALUES (
    NULL, :name, :address, :city, :state, :zipcode, :phone, :dob, :parent, :email,
    :name2, :address2, :city2, :state2, :zipcode2, :phone2, :parent2, :email2
  )
";

// prepare the statement for execution. When we execute the statement, we pass
// an array containing the parameters we wish to use for our query (in the query,
// these parameters are represented by tokens prefixed with ':')
//
// PDO will handle the data sanitizing for us with these two steps.

$my_statement   = $p->prepare($query);
$my_result      = $my_statement->execute(array(
    "name"     => $name1,
    "address"  => $address1,
    "city"     => $city1,
    "state"    => $state1,
    "zipcode"  => $zip1,
    "phone"    => $ph1,
    "dob"      => $dob1,
    "parent"   => $parentName1,
    "email"    => $email1,
    "name2"    => $name2,
    "address2" => $address2,
    "city2"    => $city2,
    "state2"   => $state2,
    "zipcode2" => $zip2,
    "phone2"   => $ph2,
    "parent2"  => $parentName2,
    "email2"   => $email2));

if( !$my_result )
{
  print_r($p->errorInfo());
} 
But, it would appear that the short answer to your question as to why it isn't working as intended is: Your query isn't built properly. Your columns aren't matching your values. You've transposed at least address1 with phone1. I would suggest looking at your mysql or php error logs.

Hope this helps,
Andy
ummhasan
Forum Commoner
Posts: 31
Joined: Sun Jun 13, 2010 7:24 pm
Location: USA

Re: Please Help - why is my data not writing to the table?

Post by ummhasan »

Wow thanks! I was using my php and mySQL developer's library book. I'm very new to PHP and I although I bought the book for this course it was full and couldn't get into it so I just took the book and tried to write the program with the basic understanding of programming.

However, since I couldn't get the program to execute with mysqli code as in the book I went out in search for other examples and starting copy/editing.

I will take your example if thats ok and try to implement it.

Do you know of a good tutorial of mysqli code?

Thanks again.
ummhasan
Forum Commoner
Posts: 31
Joined: Sun Jun 13, 2010 7:24 pm
Location: USA

Re: Please Help - why is my data not writing to the table?

Post by ummhasan »

oh, one more thing..........what about the fact that I need to write these values into 5 different tables since we want each level to have the same registration number such as pre 001, elem 001, inter 001, etc.......

I still need a switch statement right? And if so, can I place this quote into the switch statement?

Thanks
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Please Help - why is my data not writing to the table?

Post by andyhoneycutt »

I'm not sure why you are wanting to write it to five different tables, but one thing to consider is this: If the table structures are the same (or at least have all of the same fields that you're inserting), you could better write your switch as an array, and then just use the array in your insert statement for your table name:

Code: Select all

/**
 * Array, instead of a switch:
 */
$table_to_insert_to = array("preE"   => "pre_elementary",
                            "elem"   => "elementary",
                            "inter"  => "intermediate",
                            "junior" => "junior",
                            "senior" => "senior");

/**
 * Insert statement becomes:
 */

$query = "INSERT INTO {$table_to_insert_to[$levels]} ( ... ) VALUES ( ... )"; 
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Please Help - why is my data not writing to the table?

Post by andyhoneycutt »

Also, if it's all new to you, I would go with PDO. That is, if you're going to be starting from scratch and learning this stuff from the beginning, go with PDO. It may or may not be less headache for you, but it's a better package overall. Not to say there probably isn't something better than PDO, but it's a good thing to know.
ummhasan
Forum Commoner
Posts: 31
Joined: Sun Jun 13, 2010 7:24 pm
Location: USA

Re: Please Help - why is my data not writing to the table?

Post by ummhasan »

ok, thanks yet again!

Well, as I said, the reason I set up 5 tables is because the person i'm developing this site for wants the registrants to be assigned identical registration numbers.

For example, if a person enters into pre elementary level they will be automaticlly assigned (from the table auto incriment) 001 and his/her partner will auto receive pre 002. I will programaticly attach a pre to that 001 with an echo statement such as

echo "Your registration id is: pre" .id. ". Please make note of your registration number, you will need it on the day of the competition!";

likewise, if the registrant enters into the elementary level they will be auto assigned elem 001 and partner elem 002 as above for every level.

If you know of another way to do this in ONE table then please share! I really appreciate your time.

Keep in mind that each person will most likely have partner and each level will need to be able to return to the registrant an automated or auto incrimented registration number and the partner will need to be +1 of the 1st registrant. I have already set in the tables the auto incriment for both id and pid and set id to 000 and pid to 001

I am open to changing it if there is a better way.

and thanks for suggesting the PDO I will take your advise.
ummhasan
Forum Commoner
Posts: 31
Joined: Sun Jun 13, 2010 7:24 pm
Location: USA

Re: Please Help - why is my data not writing to the table?

Post by ummhasan »

ok i put what you said and tested it to see if it would work and got this:

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in /home/content/u/m/m/ummhasan/html/ilmComp/regConfirmation.php on line 55

Fatal error: Call to a member function errorInfo() on a non-object in /home/content/u/m/m/ummhasan/html/ilmComp/regConfirmation.php on line 59

here's the code:

Code: Select all

$con = new PDO("mysql:host=replaced_with_my_host;dbname=ilmcompetition", replaced_with_my_user_name, replaced_with_my_password) 
      or 
        die("Couldn't connect to specificied datasource.");

    // Create a tokenized query string to pass to our statement object.
/**
 * Array, instead of a switch:
 */
$table_to_insert_to = array("preE"   => "pre_elementary",
                            "elem"   => "elementary",
                            "inter"  => "intermediate",
                            "junior" => "junior",
                            "senior" => "senior");
 
$query = "INSERT INTO {$table_to_insert_to[$levels]} (registrant_num, name, address, city, state, zip, phone, dob, parent, email, 
        pName, pAddress, pCity, pState, pZip, pPhone, pParent, pEmail) VALUES (NULL, :name, :address, :city, :state, :zipcode, :phone, :dob, :parent, :email,
        :name2, :address2, :city2, :state2, :zipcode2, :phone2, :parent2, :email2)"; 

$my_statement   = $con->prepare($query);
$my_result      = $my_statement->execute(array(
        "name"     => $name1,
        "address"  => $address1,
        "city"     => $city1,
        "state"    => $state1,
        "zip"  => $zip1,
        "phone"    => $ph1,
        "dob"      => $dob1,
        "parent"   => $parentName1,
        "email"    => $email1,
        "name2"    => $name2,
        "address2" => $address2,
        "city2"    => $city2,
        "state2"   => $state2,
        "zip2" => $zip2,
        "phone2"   => $ph2,
        "parent2"  => $parentName2,
        "email2"   => $email2));

    if( !$my_result )
    {
      print_r($p->errorInfo());
    } 
mysql_close($con);

ummhasan
Forum Commoner
Posts: 31
Joined: Sun Jun 13, 2010 7:24 pm
Location: USA

Re: Please Help - why is my data not writing to the table?

Post by ummhasan »

argggghh

ok i figured out that some table variables were not named correctly in the php and one was missing so I fixed that and i fixed all the $p's that I had changed to $con to be clear for me.

now i'm getting the error:

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in /home/content/u/m/m/ummhasan/html/ilmComp/regConfirmation.php on line 56
Array ( [0] => 00000 )

any ideas?
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Please Help - why is my data not writing to the table?

Post by andyhoneycutt »

You had some improper referencing in your array, just the zipcodes:

Code: Select all

$my_result      = $my_statement->execute(array(
        "name"     => $name1,
        "address"  => $address1,
        "city"     => $city1,
        "state"    => $state1,
        "zipcode"  => $zip1, // You had it referencing "zip", a non-token
        "phone"    => $ph1,
        "dob"      => $dob1,
        "parent"   => $parentName1,
        "email"    => $email1,
        "name2"    => $name2,
        "address2" => $address2,
        "city2"    => $city2,
        "state2"   => $state2,
        "zipcode2" => $zip2, // again, referencing "zip2" instead of "zipcode"
        "phone2"   => $ph2,
        "parent2"  => $parentName2,
        "email2"   => $email2));
-Andy
ummhasan
Forum Commoner
Posts: 31
Joined: Sun Jun 13, 2010 7:24 pm
Location: USA

Re: Please Help - why is my data not writing to the table?

Post by ummhasan »

ok so those are NOT the names of my table fields - they are classes is that correct?
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Please Help - why is my data not writing to the table?

Post by andyhoneycutt »

ummhasan wrote:ok so those are NOT the names of my table fields - they are classes is that correct?
It's just an array, loaded with a key=>value pair that associates to the query we built above it. Those are the names of your fields, unless you built your query wrong :)

So, if you would be so kind, could you do a "show create table xxx" where xxx is the name of your table, and do this for each table you'll be updating, post the results? That, or a "describe table xxx", either would work great for me to help troubleshoot this.
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Please Help - why is my data not writing to the table?

Post by andyhoneycutt »

The names that you see in the array are associated with the tokens in the query (the items in the value predicate of the statement ":item1, :item2" <-- tokens). Their names don't have to match the names of your columns, the columns predicate of your query (INSERT INTO <table> (column1, column2, column3...) <-- columns predicate).

-Andy
Post Reply