What value would that be? I don’t think I’ve ever had to do that before… what typically goes there?
I’ve heard that bindParam is very useful in preventing SQL Injection. Is this true?
The concept of prepared statements, of which bindParam is a part, can help in that regard, yes. It also helps by not making you do all the work if your data might contain quotes, for example.
The values that you want to insert in place of all the parameters that you put into your query.
1 Like
Well, I know I have to perform a bindParam() for :name, :residential_address, :mailing_address, :precinct, :age, :ethnicity, :gender, :party, :race, :phone, but what goes in execute()?
For example, if I load my user edit api from a different file, I have it coded like this:
// Update
$stmt = $pdo->prepare("UPDATE users SET role_id = :role_id, first_name = :first_name, last_name = :last_name, email = :email, username = :username, status = :status WHERE id = :id");
$stmt->bindParam(':role_id', $role_id);
$stmt->bindParam(':first_name', $first_name);
$stmt->bindParam(':last_name', $last_name);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':username', $username);
$stmt->bindParam(':status', $status);
$stmt->bindParam(':id', $id);
// Update User Info
if(isset($_POST['btn_save'])) {
$role_id = $_POST["role_id"];
$first_name = $_POST["first_name"];
$last_name = $_POST["last_name"];
$email = $_POST["email"];
$username = $_POST["username"];
$status = $_POST["status"];
$id = $_POST["id"];
$stmt->execute();
header('Location: ../../users.php');
}
So, obviously, I’m binding the parameters in the statement to the variable that is created later when the user clicks the Save button, but notice execute() is blank…
You can use bindParam() as in your code, or you can specify an array of values in execute:
$stmt->execute(array("role_id"=>1, "first_name"=>"Hugh"));
and so on. Obviously it’d be easier for you to build your array separately with the correct index names.
But seriously, execute() has full documentation that you can read, links provided further up the thread.
So it’s easier to use this:
$stmt->execute(array("role_id"=>1, "first_name"=>"Hugh"));
rather than this?
$stmt = $pdo->prepare("UPDATE users SET role_id = :role_id, first_name = :first_name, last_name = :last_name, email = :email, username = :username, status = :status WHERE id = :id");
$stmt->bindParam(':role_id', $role_id);
$stmt->bindParam(':first_name', $first_name);
$stmt->bindParam(':last_name', $last_name);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':username', $username);
$stmt->bindParam(':status', $status);
$stmt->bindParam(':id', $id);
Well, it is a lot less typing 
No, I meant if your array was already in that format:
array(2) {
["role_id"]=>
int(1)
["first_name"]=>
string(4) "Hugh"
}
all you’d have to do is:
$stmt->execute($arry);
Here is my current data output:
Array ( [0] => DOE, JOHN A [1] => 1234 ALEXANDER BLVD. RICHMOND, VA 39568 [2] => 1234 ALEXANDER BLVD. RICHMOND, VA 39568 [3] => 02-03 [4] => 22 [5] => NL [6] => M [7] => REP [8] => W [9] => 123-456-7890 )
I’ve already added this:
$stmt->bindParam(':name', $name);
$stmt->bindParam(':residential_address', $residential_address);
$stmt->bindParam(':mailing_address', $mailing_address);
$stmt->bindParam(':precinct', $precinct);
$stmt->bindParam(':age', $age);
$stmt->bindParam(':ethnicity', $ethnicity);
$stmt->bindParam(':gender', $gender);
$stmt->bindParam(':party', $party);
$stmt->bindParam(':race', $race);
$stmt->bindParam(':phone', $phone);
Would I have to implode() the array and combine it into a string?
Nope! This is enough
But how do I take this
Array ( [0] => DOE, JOHN A [1] => 1234 ALEXANDER BLVD. RICHMOND, VA 39568 [2] => 1234 ALEXANDER BLVD. RICHMOND, VA 39568 [3] => 02-03 [4] => 22 [5] => NL [6] => M [7] => REP [8] => W [9] => 123-456-7890 )
and import it into MySQL? You can’t import an array directly into a table…can you?
Ah I assumed you created those variables from your array.
If not, it should become something like
$stmt->bindParam(':name', $row[0]);
And so on and so forth for all the columns.
First column of the CSV is $row[0], second one is $row[1], etc
Well, I currently have this:
$stmt->bindParam(':name', $name);
$stmt->bindParam(':residential_address', $residential_address);
$stmt->bindParam(':mailing_address', $mailing_address);
$stmt->bindParam(':precinct', $precinct);
$stmt->bindParam(':age', $age);
$stmt->bindParam(':ethnicity', $ethnicity);
$stmt->bindParam(':gender', $gender);
$stmt->bindParam(':party', $party);
$stmt->bindParam(':race', $race);
$stmt->bindParam(':phone', $phone);
What if I did something like this:
$name = $row[0];
$residential_address = $row[1];
$mailing_address=$row[2];
…and so on? Or do you think I should change the bindParam code to what you posted?
Both work, but one of them is twice as many lines of code as the other one.
I’d personally prefer to feed the elements of the array to bindParam directly, but that’s preference. To each their own.
I see. Well, I changed it anyway:
$stmt->bindParam(':name', $row[0]);
$stmt->bindParam(':residential_address', $row[1]);
$stmt->bindParam(':mailing_address', $row[2]);
$stmt->bindParam(':precinct', $row[3]);
$stmt->bindParam(':age', $row[4]);
$stmt->bindParam(':ethnicity', $row[5]);
$stmt->bindParam(':gender', $row[6]);
$stmt->bindParam(':party', $row[7]);
$stmt->bindParam(':race', $row[8]);
$stmt->bindParam(':phone', $row[9]);
So, now it’s safe to execute the statement, right?
Yup!
Is there anything I need to do after the execution or anything else I need to add to the while loop? My current while loop looks like this:
while (! feof($file)) {
print_r(fgetcsv($file));
$stmt->execute();
}
Also, the $file variable shows this:
$file = fopen("demo.csv","r");
How do I allow the user to upload a .csv file with another name? I’d like to have a variable that determines what the filename is before the file is opened.
$filename = (code here)
$file = fopen($filename, "r");
Can you post the full code please?
<?php
include('dbconnect.php');
$file = fopen("demo.csv","r");
// Read and throw away the header row
fgetcsv($file);
$stmt = $pdo->prepare("INSERT INTO members (name, residential_address, mailing_address, precinct, age, ethnicity, gender, party, race, phone) VALUES (:name, :residential_address, :mailing_address, :precinct, :age, :ethnicity, :gender, :party, :race, :phone)");
$stmt->bindParam(':name', $row[0]);
$stmt->bindParam(':residential_address', $row[1]);
$stmt->bindParam(':mailing_address', $row[2]);
$stmt->bindParam(':precinct', $row[3]);
$stmt->bindParam(':age', $row[4]);
$stmt->bindParam(':ethnicity', $row[5]);
$stmt->bindParam(':gender', $row[6]);
$stmt->bindParam(':party', $row[7]);
$stmt->bindParam(':race', $row[8]);
$stmt->bindParam(':phone', $row[9]);
while (! feof($file)) {
fgetcsv($file);
$stmt->execute();
}
fclose($file);
?>
Three questions:
- Do I need to add anything else?
- Do I need to change anything?
- How do I make a variable for the file name rather than just having it stay as
demo.csv?
The binding of variables should be done within the while loop. Please read back a few posts to the approach I posted there.
Stop worrying about the filename for now. We can tackle it once the rest works. Solve two problems at a time you’ll get poor solutions for both.
I didn’t think that was necessarily the case. I was under the impression that you can prepare() and bindParam() (but not bindValue()) before the loop, and calling execute() inside the loop will use the current value of the bound variables. I do that in some places, but I do make sure that the variables exist, even with null values, at the point that I call bindParam - I’ve never tested to see whether that’s required or not.
As I see it, the problem is that this line:
fgetcsv($file);
should read
$row = fgetcsv($file);
to populate the array prior to calling execute().