Page 1 of 3

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

Posted: Mon Jun 14, 2010 11:24 am
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);

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

Posted: Mon Jun 14, 2010 11:42 am
by andyhoneycutt
What version of PHP are you running? 8O

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

Posted: Mon Jun 14, 2010 11:45 am
by ummhasan
PHP5

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

Posted: Mon Jun 14, 2010 12:24 pm
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

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

Posted: Mon Jun 14, 2010 3:14 pm
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.

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

Posted: Mon Jun 14, 2010 3:16 pm
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

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

Posted: Mon Jun 14, 2010 3:39 pm
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 ( ... )"; 

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

Posted: Mon Jun 14, 2010 3:41 pm
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.

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

Posted: Mon Jun 14, 2010 4:12 pm
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.

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

Posted: Mon Jun 14, 2010 4:21 pm
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);


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

Posted: Mon Jun 14, 2010 4:36 pm
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?

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

Posted: Mon Jun 14, 2010 5:17 pm
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

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

Posted: Mon Jun 14, 2010 10:01 pm
by ummhasan
ok so those are NOT the names of my table fields - they are classes is that correct?

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

Posted: Tue Jun 15, 2010 9:20 am
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.

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

Posted: Tue Jun 15, 2010 9:21 am
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