checkbox foreach PDO multiple quires

[mjab92]

Hi Guys,

 

I have been stuck on this for the past eight hours and have been on many forums and help sites.

 

My issue seems to be when I get the POST data from OPTIONS i need to loop through it and select the ID from database1 and then database2 before inserting into database3.

 

I have tried several ways this is where i'm at, way of where I was, hence why the code looks a little over the place.

 

For this say the options array is:

 

 

 

 [options] => Array ( [0] => 1029 [1] => 1030 [2] => 1031

 

So we have 3 ID's that need selecting from database1, we then get data from databse2 and with this insert into database3. 

 

When I run the code it inserts 3 records but all the same ID!!

 

The code is simplified:

<?php
if(isset($_POST['options'])){
	$count = count($_POST['options']);
	$checkboxes = isset($_POST['options']) ? $_POST['options'] : array();
for($i=0; $i<$count; $i++) {	
	$dsn = 'mysql: host=localhost; dbname=dbname';
	$user = 'user';
	$password = 'pass';
try {
	$pdo = new PDO($dsn, $user, $password);
	$pdo ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
	catch (PDOException $e) {
	echo 'Connection failed: ' . $e->getMessage();
}
	$sql= "SELECT * FROM database1 WHERE id = :id";
	$stmt = $pdo->prepare($sql);
foreach($checkboxes as $value){
    $stmt->bindParam(':id', $value, PDO::PARAM_STR); 
	$stmt->execute();
}
$result = $stmt->fetchAll();
foreach($result as $row){
	$dt = $row['dt'];
	$sql= "SELECT * FROM database2 WHERE dt = :dt";
	$stmt = $pdo->prepare($sql);
	$stmt->bindParam(':dt', $dt, PDO::PARAM_STR); 
	$stmt->execute();
	$result = $stmt->fetchAll();
foreach($result as $rows){
	if($rows['row1'] == '1'|| $rows['row2'] == '2'){
			$startTime = $row['startTime'];
try {
	$sql = "INSERT INTO database3(startTime) VALUES (:startTime)";                          
	$stmt = $pdo->prepare($sql);   
	$stmt->bindParam(':startTime', $startTime, PDO::PARAM_STR);                                   
	$stmt->execute(); 
}
	catch (PDOException $e) {
	echo 'database3 failed: ' . $e->getMessage();
}
}else{
	//do nothing 
}
}
}
}//end for loop
}else{
	echo 'OPT NOT SET';
}
?>

If we can't debug from this, which I hope we can and i'm just missing something obvious I will post full code it is just a bit of a mouthful. 

 

Thanks In advance 

 

p.s. first time poster so tips on posting also helpfull.

[benanamen]

For starters, you need to use the correct terminology. What you are calling Databases are Tables. What is the point of bouncing all that data from table to table? It sounds like you have a very poorly designed Database and a lack of understanding of "Database Normalization".

Edited June 28, 2017 by benanamen

[mjab92]

For starters, you need to use the correct terminology. What you are calling Databases are Tables. What is the point of bouncing all that data from table to table? It sounds like you have a very poorly designed Database and a lack of understanding of "Database Normalization".

 

That is a valid point. The application changed with the data and how it handled it, and instead of re designing the tables I created another and it worked for the purpose intended until a few updates later and a shed load of stored data, it seemed a large task to re design the tables to reflect the changes as I had coded around it until now. 

 

I will work on a redesign now unless there is a simple fix.

[benanamen]

The database is the foundation of all the code you will ever write using it. If the database is wrong, then all the code you will ever write for it will be wrong or not scalable. Get the database correct before you even think about writing code. When you have the database done, post your schema here and we will review it.

 

If the DB is wrong, a "simple fix" is just one of many hacks to come.