Page 1 of 3

Using PDO and running into problems

Posted: Mon Apr 30, 2012 4:21 pm
by Pavilion
Hello Everyone:

I am learning how to use PDO and have executed a few simple queries using the process. But... I am running into a problem with INSERT queries and arrays. In essence I am working on a project where users will be able to upload a csv file and insert the contents into a mySQL table.

I've figured out how to upload the file and extract the different columns in the csv file (using a foreach statement). Within a foreach statement I have the following:

Code: Select all

	$inner_array = fgetcsv($file);
		$col_1=$inner_array[0];
		$col_2=$inner_array[1];
		$col_3=$inner_array[2];
		$col_4=$inner_array[3];
		$col_5=$inner_array[4];
		$col_6=$inner_array[5];
		$col_7=$inner_array[6];
		
		echo "<br />";
		echo($col_1) . "<br />";
		echo($col_2) . "<br />";
		echo($col_7) . "<br /><br />";
The echos are working perflectly. Col_1 is a first name, col_2 is a last name, and col_7 is an email address. When I echo out the content, it echoes accurate data for each column variable.

Now to my problem.

I figured once I successfully extracted each column from the array, all I'd have to do for the INSERT Statement (using PDO) would be to bind PDO keys to each column variable, as follows:

Code: Select all

	try 
	{
		$prep_insert = $link->prepare($upload_query); // Prepare statement
		$prep_insert->execute(array(
			':fname'=>$col_1,
			':lname'=>$col_2,
			':title'=>$col_3,
			':d_phone'=>$col_4,
			':ext'=>$col_5,
			':c_phone'=>$col_6,
			':email'=>$col_7,
			':fcontact'=>$firstcontact,
			':user'=>$userid
		));
	}
Well - the insert statement - which did work as a regular mysql_query($query) or die(mysql_error()); - does NOT work with this PDO syntax.

So... to do some testing I added $test= to the $prep_insert->execut() statement as follows:

Code: Select all

$test=$prep_insert->execute(array(
':fname'=>$col_1,
':lname'=>$col_2,
':title'=>$col_3,
':d_phone'=>$col_4,
':ext'=>$col_5,
':c_phone'=>$col_6,
':email'=>$col_7,
':fcontact'=>$firstcontact,
':user'=>$userid
));
Then I inserted this var_dump:

Code: Select all

var_dump($test);
The result of this var_dump is as follows:
dump insert execute bool(false)
What am I doing wrong with this PDO statement? I'm following every syntax example I can find for binding PDO keys to variables, but it is almost as though the keys are not picking up the value of the variable. :? :?

Any ideas, any one has on this would be truly appreciated.

Thanks Much:

Pavilion

Re: Using PDO and running into problems

Posted: Mon Apr 30, 2012 5:20 pm
by Christopher
What does the SQL in $upload_query look like?

Re: Using PDO and running into problems

Posted: Mon Apr 30, 2012 6:08 pm
by Pavilion
Christopher wrote:What does the SQL in $upload_query look like?
Sure Christopher:

Code: Select all

$upload_query = "INSERT into UserTbl values('',:fname,:lname,:title,:d_phone,:ext,:c_phone,:email,'',:fcontact,'','0',:user);"; /// this line accommodates thetable structure. There is a blank field first to accommodate autoincrement field. Then there are inserted PDO binded variables for each additional field in the table. $firstcontact goes into the 1stContact field, '0' goes into "Active" field, $userid is inserted into "AssignTo".
_______________________

As to the bigger picture, when I first tried this exercise using the standard mySQL process, things worked great. But the query language was different. In essence I found some script that used an array within the sql itself. The original sql is as follows:

Code: Select all

$query = "insert into UserTbl values('','$linemysql','','$firstcontact','','0','$userid');"; /// this line accommodates your table structure. There is a blank field first to accommodate autoincrement field. $linemysql, is an array for 6 different fields in the table. Then there are insert quotes for each additional field in the table. $firstcontact goes into the 1stContact field. 
$queries .= $query . "\n";
@mysql_query($query);
Where $linemysql' is an array from the csv file. It populates (in order) 6 different fields in the table.

The code works perfectly. I decided to move to PDO only after complete testing in standard mySQL.

Once I moved to PDO, I couldn't figure out how to insert an array of values into the SQL statement, that is why I moved to parsing out the array and inserting individual keys.

If you've a way to to streamline my process, I'm all ears.

Thanks Much:

Pavilion

Re: Using PDO and running into problems

Posted: Mon Apr 30, 2012 7:27 pm
by Pavilion
Christopher:

I've been doing so more work on my foreach block. So you can see the whole context following is my script starting with defining the INSERT statement through the foreach block:

Code: Select all

// Define INSERT Statement
		$upload_query = "INSERT into UserTbl values('',:fname,:lname,:title,:d_phone,:ext,:c_phone,:email,'',:fcontact,'','0',:user);"; /// this line accommodates your table structure. There is a blank field first to accommodate autoincrement field. Then there are inserted PDO binded variables for each additional field in the table. $firstcontact goes into the 1stContact field, '0' goes into "Active" field, $userid is inserted into "AssignTo".

// Extract values from upload array
foreach(split($lineseparator,$csvcontent) as $line) {

		$inner_array = explode($fieldseparator,$line);
		
		$col_0=$inner_array[0];
		$col_1=$inner_array[1];
		$col_2=$inner_array[2];
		$col_3=$inner_array[3];
		$col_4=$inner_array[4];
		$col_5=$inner_array[5];
		$col_6=$inner_array[6];

// And insert extracted values into database table.
	try 
	{
		$prep_insert = $link->prepare($upload_query); // Prepare statement
		$prep_insert->execute(array(
			':fname'=>$col_0,
			':lname'=>$col_1,
			':title'=>$col_2,
			':d_phone'=>$col_3,
			':ext'=>$col_4,
			':c_phone'=>$col_5,
			':email'=>$col_6,
			':fcontact'=>$firstcontact,
			':user'=>$userid
		));
		echo $col_0 . " - " . $col_1 . " - " . $col_6 . "<br />";
			// $prep_insert->debugDumpParams();
	}
		// This block throws an error message if something goes wrong. PDO uses "exceoptions" to handle errors.
		catch(PDOException $e)
		{
		echo "For some reason your record was not added to the database. <br />";
		}
} // END foreach block
The echos of extracted values from the csv file work great. There is one echo line per record. All expected values show up for all records in the csv file.

So... it only follows that I am doing something wrong with the PDO.... unless you can see something in the larger context of my script???

Thanks so much - Pavilion

Re: Using PDO and running into problems

Posted: Mon Apr 30, 2012 7:40 pm
by Celauran
Have you looked at the output of PDO::errorInfo? Also, you probably want to prepare your query outside the foreach loop, then execute it inside.

Re: Using PDO and running into problems

Posted: Mon Apr 30, 2012 9:20 pm
by Pavilion
Celauran, thanks for jumping in here. You were right to point me to PDO, but this one is stumping me. :D
Celauran wrote:Have you looked at the output of PDO::errorInfo? Also, you probably want to prepare your query outside the foreach loop, then execute it inside.
OK - I've moved the prepare statement outside foreach loop and run the debugDumpParams(). Following is the script, as it stands now:

Code: Select all

// Define INSERT Statement
		$upload_query = "INSERT into UserTbl values('',:fname,:lname,:title,:d_phone,:ext,:c_phone,:email,'',:fcontact,'','0',:user);"; /// this line accommodates your table structure. There is a blank field first to accommodate autoincrement field. Then there are inserted PDO binded variables for each additional field in the table. $firstcontact goes into the 1stContact field, '0' goes into "Active" field, $userid is inserted into "AssignTo".
// Prepare INSERT query
		$prep_insert = $link->prepare($upload_query); // Prepare statement

// Extract values from upload array
foreach(split($lineseparator,$csvcontent) as $line) {

                $inner_array = explode($fieldseparator,$line);
               
                $col_0=$inner_array[0];
                $col_1=$inner_array[1];
                $col_2=$inner_array[2];
                $col_3=$inner_array[3];
                $col_4=$inner_array[4];
                $col_5=$inner_array[5];
                $col_6=$inner_array[6];

// And insert extracted values into database table.
        try
        {
                $prep_insert = $link->prepare($upload_query); // Prepare statement
                $prep_insert->execute(array(
                        ':fname'=>$col_0,
                        ':lname'=>$col_1,
                        ':title'=>$col_2,
                        ':d_phone'=>$col_3,
                        ':ext'=>$col_4,
                        ':c_phone'=>$col_5,
                        ':email'=>$col_6,
                        ':fcontact'=>$firstcontact,
                        ':user'=>$userid
                ));
                echo $col_0 . " - " . $col_1 . " - " . $col_6 . "<br />";
        }

                // This block throws an error message if something goes wrong. PDO uses "exceoptions" to handle errors.
                catch(PDOException $e)
                {
                echo "For some reason your record was not added to the database. <br />";
                }
} // END foreach block
	$prep_insert->debugDumpParams();
$prep_insert->debugDumpParams(); Returns the following:
SQL: [108] INSERT into UserTbl values('',:fname,:lname,:title,:d_phone,:ext,:c_phone,:email,'',:fcontact,'','0',:user); Params: 9 Key: Name: [6] :fname paramno=-1 name=[6] ":fname" is_param=1 param_type=2 Key: Name: [6] :lname paramno=-1 name=[6] ":lname" is_param=1 param_type=2 Key: Name: [6] :title paramno=-1 name=[6] ":title" is_param=1 param_type=2 Key: Name: [8] :d_phone paramno=-1 name=[8] ":d_phone" is_param=1 param_type=2 Key: Name: [4] :ext paramno=-1 name=[4] ":ext" is_param=1 param_type=2 Key: Name: [8] :c_phone paramno=-1 name=[8] ":c_phone" is_param=1 param_type=2 Key: Name: [6] :email paramno=-1 name=[6] ":email" is_param=1 param_type=2 Key: Name: [9] :fcontact paramno=-1 name=[9] ":fcontact" is_param=1 param_type=2 Key: Name: [5] :user paramno=-1 name=[5] ":user" is_param=1 param_type=2
Even though there is a catch block - it never throws any exceptions/errors - so the catch block isn't of much help.

I've no idea what to of the dump params return, it doesn't appear to be showing much besides keys.

Any thoughts that you (or Christopher) have on this puzzle are truly appreciated.

Thanks much - Pavilion

Re: Using PDO and running into problems

Posted: Tue May 01, 2012 7:40 am
by Pavilion
Well... I've figured out the immediate problem. The date value I was throwing at SQL was not acceptable. So I revised the script to send CURDATE() within the SQL statement itself. Now the INSERT is working. But... I've run into another problem. It's clean up stuff - but it has to be dealt with. The most important data point being imported by the user is the email addresses in each file. If there is NOT a valid email address, the line should not INSERT.

So... it seemed the most efficient solution would be to catch (and test) the $col_6 variable after it was extracted from the csv array. Then I could use an if() block to run the PDO. But, I'm not being successful in testing $col_6.

At this point the foreach block looks like this:

Code: Select all

foreach(split($lineseparator,$csvcontent) as $line) {
	$inner_array = explode($fieldseparator,$line);
				
                $col_0=$inner_array[0];
                $col_1=$inner_array[1];
                $col_2=$inner_array[2];
                $col_3=$inner_array[3];
                $col_4=$inner_array[4];
                $col_5=$inner_array[5];
                $col_6=$inner_array[6];
				$col_7=$userid;

		$email = filter_var($col_6, FILTER_VALIDATE_EMAIL); /// attempting to catch and test the email column ($col_6). Not succeeding.
		var_dump($email); // this dump returns bool(false) ... so I can't use an if block on it.
		echo $col_6 . "<br />"; // this echo works great. Returns all email address submissions - most of which are valid. One email address is not valid.
				

// And insert extracted values into database table.
        try
        {
                $prep_insert = $link->prepare($upload_query); // Prepare statement
                $prep_insert->execute(array(
                        ':fname'=>$col_0,
                        ':lname'=>$col_1,
                        ':title'=>$col_2,
                        ':d_phone'=>$col_3,
                        ':ext'=>$col_4,
                        ':c_phone'=>$col_5,
                        ':email'=>$col_6,
                        ':user'=>$$col_7
                ));

        }
                // This block throws an error message if something goes wrong. PDO uses "exceoptions" to handle errors.
                catch(PDOException $e)
                {
                echo "For some reason your record was not added to the database. <br />";
                }
			   //echo $col_0 . " - " . $col_1 . " - " . $col_6 . " - " . $col_7 . "<br />";
} // END foreach block
I showed the whole block so you can see where I placed the email test. But specifically the following comments should help you understand my problem.
$email = filter_var($col_6, FILTER_VALIDATE_EMAIL); /// attempting to catch and test the email column ($col_6). Not succeeding.
var_dump($email); // this dump returns bool(false) ... so I can't use an if block on it.
echo $col_6 . "<br />"; // this echo works great. Returns all email address submissions - most of which are valid. One email address is not valid.
When ever I try and use an if block with this method of testing - the script just stops executing. I'm assuming it's because filter_var($col_6, FILTER_VALIDATE_EMAIL); is not working. What am I doing wrong?

Thanks again - Pavilion

Re: Using PDO and running into problems

Posted: Tue May 01, 2012 7:56 am
by Celauran
Pavilion wrote:I'm not being successful in testing $col_6.
Can you elaborate on this?
Pavilion wrote:

Code: Select all

var_dump($email); // this dump returns bool(false) ... so I can't use an if block on it.
I don't understand. filter_var() is supposed to return FALSE on invalid data. Why can you not use an if block?
Pavilion wrote:When ever I try and use an if block with this method of testing - the script just stops executing. I'm assuming it's because filter_var($col_6, FILTER_VALIDATE_EMAIL); is not working. What am I doing wrong?
Can you show us the if block in question?
Pavilion wrote:

Code: Select all

':user'=>$$col_7
Is the $$ above intentional?

Re: Using PDO and running into problems

Posted: Tue May 01, 2012 8:00 am
by Celauran
This should work just fine:

Code: Select all

$prep_insert = $link->prepare($upload_query); // Prepare statement

foreach (split($lineseparator,$csvcontent) as $line)
{
    $inner_array = explode($fieldseparator,$line);

    $col_0=$inner_array[0];
    $col_1=$inner_array[1];
    $col_2=$inner_array[2];
    $col_3=$inner_array[3];
    $col_4=$inner_array[4];
    $col_5=$inner_array[5];
    $col_6=$inner_array[6];
    $col_7=$userid;

    $email = filter_var($col_6, FILTER_VALIDATE_EMAIL);

    if ($email)
    {
        // PDOStatement::Execute doesn't throw an exception, so try/catch is not needed.
        $prep_insert->execute(array(
            ':fname'=>$col_0,
            ':lname'=>$col_1,
            ':title'=>$col_2,
            ':d_phone'=>$col_3,
            ':ext'=>$col_4,
            ':c_phone'=>$col_5,
            ':email'=>$email,
            ':user'=>$col_7
        ));
    }
}

Re: Using PDO and running into problems

Posted: Tue May 01, 2012 8:29 am
by Pavilion
I don't understand. filter_var() is supposed to return FALSE on invalid data. Why can you not use an if block?
Sorry - Celauran - I should have been more specific. filter_var() is returning FALSE for EVERY email address - even the valid ones.

When the var_dump and the echo return I get:
bool(false) invalidaddress.com
bool(false) Pavilion@yahoo.com
bool(false) duck@yahoo.net
bool(false) mickey@yahoocom
bool(false) minnie@yahoo.com
ONLY the first address is INVALID and should return false, but they all return false. As a result - the if block (as you contributed) does not trigger.

Is placing the invalid address as the first address in the csv file, causing the following addresses to be invalid as well? If so, what's the work-around? Because users can easily contribute invalid addresses on any line.

Thanks much - have to work today - but will check back in tonight.

Pavilion

Re: Using PDO and running into problems

Posted: Tue May 01, 2012 8:52 am
by Celauran
Pavilion wrote:filter_var() is returning FALSE for EVERY email address - even the valid ones.

When the var_dump and the echo return I get:
bool(false) invalidaddress.com
bool(false) Pavilion@yahoo.com
bool(false) duck@yahoo.net
bool(false) mickey@yahoocom
bool(false) minnie@yahoo.com
OK, that's weird.

Code: Select all

<?php

$emails = array('invalidaddress.com',
    'Pavilion@yahoo.com',
    'duck@yahoo.net',
    'mickey@yahoocom',
    'minnie@yahoo.com',);

foreach ($emails as $email)
{
    var_dump(filter_var($email, FILTER_VALIDATE_EMAIL));
}
produces the following output for me:

Code: Select all

boolean false
string 'Pavilion@yahoo.com' (length=18)
string 'duck@yahoo.net' (length=14)
boolean false
string 'minnie@yahoo.com' (length=16)

Re: Using PDO and running into problems

Posted: Tue May 01, 2012 1:03 pm
by Pavilion
Yep - it is weird Celauran. I've still got more client work that has to take priority. But... I'll definitely be working on this problem tonight. It is truly baffling me.

Logically... the foreach statement constructed in my script should work - as I'm only throwing $col_6 at the filter_var() function one at a time, as it comes through the extraction routine.

But... and I'm just mussing here... do I need to nest another foreach around the filter_var()???? I really am wondering if it's grabbing the first invalid address and not processing anymore $col_6 variables as they are extracted??? Your example got me to thinking...

Code: Select all

<?php

$emails = array('invalidaddress.com',
    'Pavilion@yahoo.com',
    'duck@yahoo.net',
    'mickey@yahoocom',
    'minnie@yahoo.com',);

foreach ($emails as $email)
{
    var_dump(filter_var($email, FILTER_VALIDATE_EMAIL));
}
I know you just wrapped the var_dump in foreach because your array is constructed. But... do I need to also wrap my filter_var() routine in a foreach because it seems to be acting on the first invalid address and then stopping any further execution???

And if it is acting this way ... why????

Just questions ... the answers will come with testing (I suppose).

Thanks for your input though - it does help.

Pavilion

Re: Using PDO and running into problems

Posted: Tue May 01, 2012 1:12 pm
by Celauran
Pavilion wrote:do I need to nest another foreach around the filter_var()?
No, it's already inside a foreach. There's no need for another.

Re: Using PDO and running into problems

Posted: Tue May 01, 2012 3:02 pm
by Pavilion
Celauran wrote:
Pavilion wrote:do I need to nest another foreach around the filter_var()?
No, it's already inside a foreach. There's no need for another.
Yes... this is my gut instict as well.

I think - for testing - I'm going to remove the invalid email addresses, and test to see if it picks up the valid email addresses. I'll start from there and then add the invalid addresses back in.

Pavilion

Re: Using PDO and running into problems

Posted: Tue May 01, 2012 3:53 pm
by Celauran
Try calling trim() on the email addresses before validating them. ' duck@disney.com' is not a valid address (note the leading space).