Barand

Perhaps $STHA = $DBH2->query("exec pEmployeeGetData '1','2020' "); $row = $STHA->fetch();

I don't have SQL Server but the MySQL equivalent using PDO is $result = $db->query("call average_result(1, 2015)"); $row = $result->fetch(); echo "{$row['name']}<br>{$row['avresult']}%<br>"; // Adam Simms // 77.0% procedure: CREATE PROCEDURE `average_result`(IN pupil INT, IN year INT) BEGIN SELECT concat(fname, ' ', lname) as name , ROUND(AVG(pcent),1) as avresult FROM result r JOIN pupil p ON r.pupilid = p.pupilid WHERE r.pupilid = pupil AND r.schoolyear = year; END

That is PDO code. Isn't your $con a mysqli connection?

Good, it seems you've realized that SQL code is not PHP code, but do you really think that defining a string variable will cause the query to be run? It needs to be processed by the DB server. EG $con->query($player); Stop just throwing code at the page and think.

ROFL I give up.

I have no idea why you are getting a PHP error message on the SQL code, but then from 4,000 miles away it's difficult for me to see the code on your screen.

The purpose of the last query is to get the roster data from the temp table but the player_id from the player table by matching the players' names. You are trying to get the player id from the temp table - it ain't there!

No, it isn't what I posted. Look at mine again.

I was afraid you might be querying the player table to get the id. That means if you add a dozen players to the roster, you do 36 queries. (12 insert player, 12 select player, 12 insert roster) Doing it as I suggested does it in 3. Surely, after 13 years on this site, you know that that error message means the query failed.

The insert/select queries don't require parameter binding. From where do you get the value in $playerID

Here is the sequence of events. I am not too sure about whether fields like grade and level belong with the player or the roster (that is known only to you so, correct normalizition fo the data is up to you). The point is you have data in the csv file, some of which belong to the player table and some to the roster. In the roster table you require the players' ids and not names. To start with, we have 2 existing players in the player table, Tom and Laura. +-----------+-----------+----------+------+--------+----------+-------+-------+---------+ | player_id | nameFirst | nameLast | feet | inches | schoolID | grade | level | varsity | +-----------+-----------+----------+------+--------+----------+-------+-------+---------+ | 1 | Laura | Norder | 6 | 4 | 21 | 2021 | 1 | 42 | | 2 | Tom | DiCanari | 4 | 11 | 21 | 2021 | 1 | 42 | +-----------+-----------+----------+------+--------+----------+-------+-------+---------+ The csv file contains uniform,nameFirst,nameLast,position,height 10,Laura,Norder,PG,"6'4""" 20,Tom,DiCanari,SF,"4'11""" 30,Percy,Flage,PG,"6'3""" 40,Scott,Chegg,PG,"6'2""" 45,Drew,Peacock,SF,"6'1""" 46,Wanda,Lust,PG,"5'10""" PROCESS 1 - create and load temp_csv table CREATE TEMPORARY TABLE temp_csv ( id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, team int(11) DEFAULT NULL, grade int(11) DEFAULT NULL, uniform varchar(45) DEFAULT NULL, nameFirst varchar(45) DEFAULT NULL, nameLast varchar(45) DEFAULT NULL, position varchar(15) DEFAULT NULL, feet tinyint(4) DEFAULT NULL, inches tinyint(4) DEFAULT NULL, level int(11) DEFAULT NULL, varsity int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; LOAD DATA LOCAL INFILE 'c:/inetpub/wwwroot/test/roster2.csv' INTO TABLE temp_csv FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 ROWS (uniform,nameFirst,nameLast,position,@height) SET feet = substring_index(@height, '\'', 1) , inches = substring_index(substring_index(@height, '"', 1), '\'', -1) , team = 65 , grade = 2021 , level = 1, varsity = 42, school_id = 21; PROCESS 2 - add new players to player table INSERT IGNORE INTO player (nameFirst,nameLast,feet,inches,schoolID,grade,level,varsity) SELECT nameFirst , nameLast , feet , inches , school_id , grade , level , varsity FROM temp_csv; +-----------+-----------+----------+------+--------+----------+-------+-------+---------+ | player_id | nameFirst | nameLast | feet | inches | schoolID | grade | level | varsity | +-----------+-----------+----------+------+--------+----------+-------+-------+---------+ | 1 | Laura | Norder | 6 | 4 | 21 | 2021 | 1 | 42 | | 2 | Tom | DiCanari | 4 | 11 | 21 | 2021 | 1 | 42 | | 5 | Percy | Flage | 6 | 3 | 21 | 2021 | 1 | 42 | | 6 | Scott | Chegg | 6 | 2 | 21 | 2021 | 1 | 42 | | 7 | Drew | Peacock | 6 | 1 | 21 | 2021 | 1 | 42 | | 8 | Wanda | Lust | 5 | 10 | 21 | 2021 | 1 | 42 | +-----------+-----------+----------+------+--------+----------+-------+-------+---------+ PROCESS 3 - Add roster records (with player ids) INSERT IGNORE INTO roster (team,uniform,position,player_id) SELECT t.team , t.uniform , t.position , p.player_id FROM temp_csv t JOIN player p ON t.nameFirst = p.nameFirst AND t.nameLast = p.nameLast; +-----------+------+---------+----------+-----------+ | roster_id | team | uniform | position | player_id | +-----------+------+---------+----------+-----------+ | 1 | 65 | 10 | PG | 1 | | 2 | 65 | 20 | SF | 2 | | 3 | 65 | 30 | PG | 5 | | 4 | 65 | 40 | PG | 6 | | 5 | 65 | 45 | SF | 7 | | 6 | 65 | 46 | PG | 8 | +-----------+------+---------+----------+-----------+

Process 1a, with the multiple form records, is the only process that involves arrays. Once you've got the data into temp_csv, it's just a couple of queries.

That is what I said Process 2 does. INSERT IGNORE INTO player (...) SELECT ... FROM temp_csv;

The write the data from the form into the temp csv file. Then the process then on is exactly same, regardless of the data source.

Process 1 is a LOAD DATA LOCAL INFILE ... query to put the csv data into a temporary table. Process 2 inserts player data from the temp table (new records only). Process 3 matches the temp data with the player data using the names to get the player ids and inserts records into the roster table.

It should

Easiest way is a three-step process

if $player is a boolean (ie false) then the prepare failed. Check column names etc. Good to see you're finally putting that data into the player table instead of the roster table.

Then read this

That will only report php errors. Put this at the beginning of your code, preferably just before you connect to mysql mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

Why don't you output mysql's error messages and make it easier for you (and us)

Then it didn't find a record matching your data conditions.

No, just the query to use UNIX_TIMESTAMP() instead of CURDATE() since that is how your expire date is stored.

Then you definitely need to read @requinix's reply.