Eventually, I’d like to pull records from one table (lineup), perform a few calculations and insert the results into a different table (zippy_sample) as part of an administration/upkeep system. Right now, I’m trying to just pull the results and insert them. I’ve built the following code:
<?php
include 'dbcred.php';
$dbh = new PDO($dsn, $user, $password);
$dbh->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
try
{
$sql = "SELECT * FROM week WHERE marker = '2'";
$stmt = $dbh->prepare($sql);
$stmt->execute();
while ($result = $stmt->fetch(PDO::FETCH_ASSOC)){
$week = $result['week'];
print $week;
}
}
catch (PDOException $e)
{
echo 'Week selection off: ' . $e->getMessage();
}
try
{
$sql = 'SELECT week, league_id FROM lineup WHERE week = :week';
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':week', $week);
$stmt->execute();
while ($result = $stmt->fetch(PDO::FETCH_ASSOC)){
$query_result = implode(',', $result);
print_r ($query_result);
}
}
catch (PDOException $e)
{
echo 'Pull from lineup off: ' . $e->getMessage();
}
try
{
$sql = "SELECT COUNT(*) AS num_row FROM zippy_sample
WHERE week = :week";
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':week', $week);
$stmt->execute();
$submitcheck = $stmt->fetch(PDO::FETCH_ASSOC);
}
catch(Exception $e)
{
echo 'Select count' . $e->getMessage();
}
if ($submitcheck['num_row'] > 0)
{
try
{
$sql = "UPDATE zippy_sample
SET week = :week,
league_id = :league_id WHERE week = :week";
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':week', $week);
$stmt->bindParam(':league_id', $league_id);
$stmt->execute();
}
catch(Exception $e)
{
echo 'Updating' . $e->getMessage();
}
}
else
{
try
{
$sql = "INSERT INTO zippy_sample
(week, league_id)
VALUES (".$query_result.")";
$stmt = $dbh->prepare($sql);
$stmt->execute();
}
catch(Exception $e)
{
echo 'Inserting' . $e->getMessage();
}
}
?>
The results of the print_r ($query_result) gives me the the output of: 1,11,31,31,3 as expected. I think my problem lays in my INSERT statement. The current statement will insert the last value of 1,3. I need to have all the output values inserted.
I know my UPDATE statement is not correct either, I’ll tackle that after I get the INSERT working properly.
Any advise would be greatly appreciated.
Thanks in advance,
Craig
Based on his code I don’t see that happening, otherwise I would have taken a different approach.
hehe.
Only thing to consider, Cerium; if you push the rows into your array as strings, it becomes more difficult if you want to manipulate/use the data later. (You’d have to re-extract the data from the string; not a hard thing to do, but less efficient)
The reason you are seeing multiple values 1,11,31,31,3 is because of this:
while ($result = $stmt->fetch(PDO::FETCH_ASSOC)){
$query_result = implode(',', $result);
print_r ($query_result);
}
fetch returns one element at a time. So what you can do instead is the following:
$results = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
array_push($results, '(' . $row['week'] . ', ' . $row['league_id'] . ')');
}
Now you have an array of elements that look like this:
0 => (1, 100)
1 => (12, 236)
2 => (6, 4)
3 => (12, 45)
… etc…
And your insert
$sql = 'INSERT INTO zippy_sample (week, league_id) VALUES ' . implode(', ', $results);
Now your insert statement looks like this:
$sql = 'INSERT INTO zippy_sample (week, league_id) VALUES (12, 236), (6, 4), (12, 45)';
…And StarLion beat me to it 
I understand what you are saying about the current results of $query_result. I’m trying to insert four different sets of two values. (1,1 and 1,3 and 1,3 and 1,3) with one statement. The (1,11,31,31,3) value I get from trying to format the array results of my SELECT query earlier in the code using implode. Which I think is where my problem lays.
zippy_sample table is formatted as follows
Field Type Null
id int(11) No
week int(2) No
league_id int(11) No
Primary Key id, auto increment.
I’ll admit my knowledge of relational database design isn’t vast, but I think the format here is okay.
I haven’t looked over everything as you said you thought the INSERT statement was wrong, and it is.
If you believe you are having issues with an a SQL statement, try it out in your phpmyadmin and see if it returns an SQL error.
$sql = "INSERT INTO zippy_sample (week, league_id) VALUES (".$query_result.")";
You have two columns that you are inserting into. That means that 2 values should be placed in each column.
If $query_result returns multiple values (1,11,31,31,3), then you should rethink your database schema as you are working with an unnormalized table.
To insert in that way you need to construct your insert query something like this:
INSERT INTO `zippy_sample` (week, league_id) VALUES
(1,1),(1,3),(1,3),(1,3);
So to make it in that way, you need to change the way of constructing the $query_result variable also.
$query_result has the value of 1,11,31,31,3. When you do insert this is what your code looks like:
INSERT into `zippy_sample` (week, league_id) VALUES (1,11,31,31,3)
You should only have one value per column.
IE.
INSERT into `zippy_sample` (week, league_id) VALUES (1,11)
Without having much info on your database schema I’m unable to help but the best I can tell you is that you should read up on taking your unnormalized table through all normal forms until you hit the very minimum 4th normal form.
That’s what’s killing me. How do I get the values of $query_result from here to there?
I had one configuration where it inserted four records, but they were all 0,0. Been trying to recreate that and can’t come close.
I’ve changed the code to this:
<?php
include 'dbcred.php';
$dbh = new PDO($dsn, $user, $password);
$dbh->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
try
{
$sql = "SELECT * FROM week WHERE marker = '2'";
$stmt = $dbh->prepare($sql);
$stmt->execute();
while ($result = $stmt->fetch(PDO::FETCH_ASSOC)){
$week = $result['week'];
}
}
catch (PDOException $e)
{
echo 'Week selection off: ' . $e->getMessage();
}
try
{
$sql = 'SELECT week, league_id FROM lineup WHERE week = :week';
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':week', $week);
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
print_r ($row['week']);
$insert_week = ($row['week']);
print_r ($insert_week);
}
}
catch (PDOException $e)
{
echo 'Pull from lineup off: ' . $e->getMessage();
}
print_r ($insert_week);
try
{
$sql = "SELECT COUNT(*) AS num_row FROM zippy_sample
WHERE week = :week";
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':week', $week);
$stmt->execute();
$submitcheck = $stmt->fetch(PDO::FETCH_ASSOC);
}
catch(Exception $e)
{
echo 'Select count' . $e->getMessage();
}
if ($submitcheck['num_row'] > 0)
{
try
{
$sql = "UPDATE zippy_sample
SET week = :week,
league_id = :league_id WHERE week = :week";
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':week', $week);
$stmt->bindParam(':league_id', $row->league_id);
$stmt->execute();
}
catch(Exception $e)
{
echo 'Updating' . $e->getMessage();
}
}
else
{
try
{
$sql = "INSERT INTO zippy_sample
(week, league_id)
VALUES ???????????";
$stmt = $dbh->prepare($sql);
$stmt->execute();
}
catch(Exception $e)
{
echo 'Inserting' . $e->getMessage();
}
}
?>
print_r ($row[‘week’]) returns “5555” (I switched the number for readability). The first print_r ($insert_week) within the try block returns the same “5555”
The second print_r ($insert_week) outside the try block only returns “5” I’m assuming it’s the last record.
Is this because it is outside the try block? If so, how would I establish the loop outside the try block? Is this even possible?
When I run
INSERT into `zippy_sample` (week, league_id) VALUES (1,3)
In phpmyadmin, it inserts properly. The code itself actually inserts the last set of the implode $query_result properly. I’ve changed the value to test.
Cerium, I want the select to pull multiple rows. The ‘week’ value is not the primary key. Would this matter in the database set up?
My thinking was that the implode takes the results from the select query and formats them into a single array. If the select query returned x rows, the implode array would have x values and the insert of the implode would result in x rows being inserted.
My understanding of implode is limited as I have not used it in practice before. I may be quite far off base and there may be a better way of doing this. My reasoning for using the implode was that it seems to be the best method for inserting large numbers of rows. (Hopefully several thousand at a time) If anyone has a better method, please let me know.
Thanks
if $resultarray is your array of values formed by something like…
$sql = 'SELECT week, league_id FROM lineup WHERE week = :week';
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':week', $week);
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
$resultarray[] = $row;
}
//Then....
$values = "(".implode("),(",array_map(function ($line) { return implode(",",$line); },$resultarray)).")";
$sql = "INSERT INTO zippy_sample(week, league_id) VALUES $values";
$stmt = $dbh->prepare($sql);
$stmt->execute();
That worked! I’m pretty sure I follow what’s going on. I just needed to get the data into the correct form.
Thank you both so much for your help. Not being very experienced, it’s easy to know where you want to go, but how to get there (properly) is the tough part. I give myself 15 working hours to figure it out before asking for help. I don’t want to be using code I have no idea of the logic behind.
After the insert, I won’t be needing the data at all again in this script. So I should be okay.
Thank you both again.