So I have an sql insert statement that has 45 columns. Is there some what in mysqli to create a prepared sql statement that doesn’t require me to have an insanely long query, and parameter bindings? I’m trying to avoid injection attacks but I just don’t see how to do it elegantly for such a long query.
Add your insanely long query and we’ll see what we can do about it. But yes you should still bind the params - to be sure.
The query as it stands is not long: $query=“INSERT INTO applications $columns VALUES $values”;
However, $columns is a string of 45 column names and $values is a string of 45 value variables. If I were to do this manually, it would be a mess:
$stmt = $mysqli->prepare(“INSERT INTO applications $columns VALUES (?,?,?,?,?,?,?,?,?..<n=45>)”);
$stmt->bind_param(‘sssssssssss…<n=45>’,and 45 variable names);
Can I do this using arrays instead?
A query builder function would be my goto in this situation, but the query in question would help.
Fellow experts, doesn’t 45 columns cry out to you, nay, scream at you that the OP’s database structure is wrong?
OP, please post an SQL dump of your DB. I believe you have bigger problems that need to be dealt with first. Also, your code and or form, where ever these values are coming from.
$columns is a string of 45 column names and $values is a string of 45 value variables
Not really, I have worked on tables that had ~100 unique columns that followed n3.
I still say we should see whats going on. I doubt most of the people that post for help know what n.x is
This is a dynamic query builder, but as Kevin said, it may be helpful to us and to you for us to see what you are working with:
[php]function queryBuilder($array, $table)
{
$arraySize = count($array);
$string = “INSERT INTO $table VALUES(”;
foreach ($array as $k => $v) {
$string .= " $k";
if ($v != end($array)) {
$string .= “, “;
}
}
$string .=”)”;
return $string;
}
$data = array(
‘:name’ => ‘John’,
‘:phone’ => ‘8005551234’,
‘:street’ => ‘Main’,
‘:street_num’ => ‘123’
);
$sql = queryBuilder($data, ‘Personal’);
$pdo = new PDO(‘mysql:host=localhost; dbname=sample’, ‘root’);
$stmt = $pdo->prepare( $sql );
$stmt->execute( $data );[/php]
Something like this might work, there’s obviously (as always) room for improvements, but it should get you going, and I don’t think it looks that bad.
[php]$columns = ‘id,name,email’;
// we’ll want the values as an array instead of a string
$values = explode(’,’, ‘1,“foo barson","[email protected]”’);
$query = 'INSERT INTO applications ’ . $columns . ’ VALUES ’ . getPlaceholderStringFromValues($values);
$stmt = $mysqli->prepare($query);
foreach ($values as $value)
{
$stmt->bind_param(‘s’, $value);
}
$stmt->execute();
/**
- Returns a placeholder string from an array of values
- @param mixed[] $values Array of values
-
@return string Returns a string of placeholders. ex ?,?,?
*/
function getPlaceholderStringFromValues($values)
{
$placeholders = array_fill(0, count($values), ‘?’);
return implode($placeholders, ‘,’);
}[/php]
Ah Jim, we think so much alike and aim for code reuse!
Thanks Jim L, that looks like just the ticket.
Kevin, I think we can call me an amateur at mysql and php…Here is the structure of the table in question(which is bound to be wanting in many ways). The app is a simple job application form. Data is written, then deleted at regular intervals.
-- phpMyAdmin SQL Dump
-- version 3.3.3
-- http://www.phpmyadmin.net
--
-- Generation Time: Oct 29, 2015 at 08:31 AM
-- Server version: 5.0.83
-- PHP Version: 5.3.26
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `jobapps`
--
-- --------------------------------------------------------
--
-- Table structure for table `applications`
--
CREATE TABLE `applications` (
`appdate` timestamp NOT NULL,
`id` mediumint(4) NOT NULL auto_increment,
`lastname` varchar(25) default NULL,
`firstname` varchar(25) NOT NULL default '',
`email` varchar(25) default NULL,
`altemail` varchar(50) default NULL,
`year` varchar(25) default NULL,
`reghours` varchar(4) NOT NULL default '',
`ws` varchar(3) NOT NULL,
`phone` varchar(13) default NULL,
`current` varchar(10) NOT NULL,
`job1dates` varchar(25) default NULL,
`job1desc` varchar(200) default NULL,
`job2dates` varchar(25) default NULL,
`job2desc` varchar(200) default NULL,
`job3dates` varchar(25) default NULL,
`job3desc` varchar(200) default NULL,
`preferred` varchar(255) default NULL,
`availablehours` varchar(25) default NULL,
`languages` varchar(50) default NULL,
`monday_st1` decimal(3,1) default NULL,
`monday_et1` decimal(3,1) default NULL,
`monday_st2` decimal(3,1) default NULL,
`monday_et2` decimal(3,1) default NULL,
`tuesday_st1` decimal(3,1) default NULL,
`tuesday_et1` decimal(3,1) default NULL,
`tuesday_st2` decimal(3,1) default NULL,
`tuesday_et2` decimal(3,1) default NULL,
`wednesday_st1` decimal(3,1) default NULL,
`wednesday_et1` decimal(3,1) default NULL,
`wednesday_st2` decimal(3,1) default NULL,
`wednesday_et2` decimal(3,1) default NULL,
`thursday_st1` decimal(3,1) default NULL,
`thursday_et1` decimal(3,1) default NULL,
`thursday_st2` decimal(3,1) default NULL,
`thursday_et2` decimal(3,1) default NULL,
`friday_st1` decimal(3,1) default NULL,
`friday_et1` decimal(3,1) default NULL,
`friday_st2` decimal(3,1) default NULL,
`friday_et2` decimal(3,1) default NULL,
`saturday_st1` decimal(3,1) default NULL,
`saturday_et1` decimal(3,1) default NULL,
`saturday_st2` decimal(3,1) default NULL,
`saturday_et2` decimal(3,1) default NULL,
`sunday_st1` decimal(3,1) default NULL,
`sunday_et1` decimal(3,1) default NULL,
`sunday_st2` decimal(3,1) default NULL,
`sunday_et2` decimal(3,1) default NULL,
`status` varchar(10) default 'Open',
`comments` varchar(255) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM ;what is appdate?
lastname, firstname, email, altemail (year, reghours, ws, phone, current) seems like they should be in a “user” table
what is jobxdates/desc? Seems to be so each application can have 3 jobs, what if they want 1? or 4? Should probably be in a separate “job” table and joined with the “application” table so one application can have many jobs
I guess it’s the same for the weekdays, whatever that is for.
comments could also be in a separate table, so each application could have many comments
Yeah, I agree, but at the moment I haven’t got time to remodel the table, so I’m stuck with what we have.
appdate = application date
jobdates/desc are previous employment dates and descriptions
the weekdays fields are available work hours.
Since each applicant will only be using the form once, we only need one comment field/user.
Hindsight is 20/20, :
If you are removing the data, why are you saving it to the database at all?
The data is searchable through a web app by about 20 departments. It is mainly used at the beginning of each semester. Students who want jobs apply using an online form. Supervisors search through the data and select new employees.
I’d consider doing a refactoring of the db scheme and adding a active flag so you can deactive applications when given assignments and/or semester end. That way you will also have history
If that could be helpful of course
Thank you. I appreciate all the timely help. If I ever get time to rewrite the whole shebang, I’ll definitely do as you suggest.
For some reason, using Jim’s code, I am failing at the bind_param stage…
[code]$valArray = explode(’,’, $values);
$query=‘INSERT INTO applications ’ . $columns . ’ VALUES (’ . getPlaceholderStringFromValues($valArray).’)’;
if(!$stmt = $mysqli->prepare($query)){
echo ('Error preparing query: ’ . $stmt->error);
}
foreach ($valArray as $val){
if(!$stmt->bind_param(‘s’, $val)){
echo ('Error binding query: ’ . $stmt->error);
}
}
[/code]
Do you get any errors?
No error message, but the bind_param statement is returning FALSE - it doesn’t even bind the first variable. I thought it might be because the values were empty, but it happens even when I fill the variables with foo data…
if I remove the error trap on the bind statement, I then get an “No data supplied for parameters in prepared statement” from the Execute statement.