PHP While loop inside SQL Insert

[ebolt007]

I have a bunch of checkboxes I am pulling from a database, like so.

<?$true_query = mssql_query("SELECT * FROM checkbox_datbase ORDER BY ID ASC");
                  	while ($true_row = mssql_fetch_assoc($true_query))
                  	{
                  		$eth_id = $true_row['ID'];
                  		$eth_name = $true_row['Value'];
                  		echo "<input type=\"checkbox\" name=\"eth_$eth_id\" value=\"1\" class=\"input\" id=\"Ethnic_01\"/>$eth_name <br />";
                  		}
?>

How do I put these into a database that I have settup where it needs to match up with a userid. I am trying to do this, and can get it to look correctly by echoing out the various parts, but I can't put while loops into a variable, right? So how would I get all of this into one line in my $sql variable?

 

echo "INSERT INTO new_database (UserId,"; 
                  $true_query = mssql_query("SELECT * FROM checkbox_datbase ORDER BY ID ASC");
                  	while ($true_row = mssql_fetch_assoc($true_query))
                  	{
                  		$eth_id = $true_row['ID'];
                  		$eth_eth_id = $_POST['eth_' . $eth_id . ''];
                       echo " $eth_id, ";
                      }
                      echo "DateUpdated) VALUES ('$user_ID', ";
                     
                    $true_query = mssql_query("SELECT * FROM checkbox_datbase ORDER BY ID ASC");
                  	while ($true_row = mssql_fetch_assoc($true_query))
                  	{
                  		$eth_id = $true_row['ID'];
                  		$eth_eth_id = $_POST['eth_' . $eth_id . ''];
                       echo "'$eth_eth_id', ";
                      }
                     echo "'$currenttime')";

 

This echoes something that looks like this

INSERT INTO new_database (UserId, 1, 2, 3, 4, 5, 6, 7, 8, DateUpdated) VALUES ('259', '', '', '1', '', '1', '', '', '', '2012-01-23 13:24:18 PM')

, which I need the actual statement to be

$sql = "INSERT INTO new_database (UserId, 1, 2, 3, 4, 5, 6, 7, 8, DateUpdated) VALUES ('259', '', '', '1', '', '1', '', '', '', '2012-01-23 13:24:18 PM')";

 

Can anyone help me with this?

[kickstart]

Hi

 

Not quite sure what you are asking.

 

Cleaning up your code to set up a variable with the SQL insert statement in it you can have this:-

 

$FieldArray = array();
$ValueArray = array();
$true_query = mssql_query("SELECT * FROM checkbox_datbase ORDER BY ID ASC");
while ($true_row = mssql_fetch_assoc($true_query))
{
$FieldArray[] = $true_row['ID'];
$ValueArray[] = $_POST['eth_' . $true_row['ID']];
}

$sql = "INSERT INTO new_database (UserId,".implode(',',$FieldArray)."DateUpdated) VALUES ('$user_ID', ".implode("','",$ValueArray).",'$currenttime')";

 

However I am a but confused as to what it is you really want. Do you really have multiple columns, with one for each of the different check boxes? Normally would be better to have a table with one row per check box and user.

 

All the best

 

Keith

[ebolt007]

Awesome Kickstart,

 

Very close! That's exactly what I'm looking for, except instead of insert, I'm actually going to do updates and just do an insert on an earlier page of blank lines. And yes your right, sorry I do have a table with one row per checkbox, and then each checkbox when selected puts a one in the correct row in the table where the userID ='s the correct logged in user. So I tried something like

$FieldArray = array();
      $ValueArray = array();
      $true_query = mssql_query("SELECT * FROM checkbox_datbase ORDER BY ID ASC");
      while ($true_row = mssql_fetch_assoc($true_query))
      {
      	$FieldArray[] = $true_row['ID'];
      	$ValueArray[] = $_POST['eth_' . $true_row['ID']];
      }
      
      $sql2 = "UPDATE new_database SET ".implode("".implode("='",$ValueArray)."",$FieldArray).",  DateUpdated='$currenttime' WHERE UserID='$user_ID'";
		$insert_result = mssql_query($sql2);

 

And while this is close, I can't get the arrays to implode correctly, especially inside itself. How would I do this? Your code is correct with inserts tho, thanks so much.

[fenway]

Why not echo $sql2 so we can see.

[ebolt007]

Here is the echo of $sql2 if I use the code below. I'm close. This is if I check 3 of the checkboxes, hence the 111 after each number because I have the values of the checkboxes set to 1 if they are checked. The other problem is, how do I get the ='number' after the 8th checkbox?

 

UPDATE new_database SET 1='111', 2='111', 3='111', 4='111', 5='111', 6='111', 7='111', 8, DateUpdated='2012-01-24 13:14:36 PM' WHERE UserID='9'

 

$FieldArray = array();
      $ValueArray = array();
      $true_query = mssql_query("SELECT * FROM checkbox_datbase ORDER BY ID ASC");
      while ($true_row = mssql_fetch_assoc($true_query))
      {
      	$FieldArray[] = $true_row['ID'];
      	$ValueArray[] = $_POST['eth_' . $true_row['ID']];
      }
       $ValueArray[] = $_POST['eth_' . $true_row['ID']];
      $sql2 = "UPDATE new_database SET ".implode("='".implode("",$ValueArray)."', ",$FieldArray).",  DateUpdated='$currenttime' WHERE UserID='$user_ID'";
		$insert_result = mssql_query($sql2);

[kickstart]

Hi

 

No need with an UPDATE to use 2 arrays, just use a single one.

 

<?php

 

$OutArray = array();

$true_query = mssql_query("SELECT * FROM checkbox_datbase ORDER BY ID ASC");

while ($true_row = mssql_fetch_assoc($true_query))

{

$OutArray[] = $true_row['ID']."='".$_POST['eth_' . $true_row['ID']]."'";

}

$sql2 = "UPDATE new_database SET ".implode(",",$OutArray).",  DateUpdated='$currenttime' WHERE UserID='$user_ID'";

$insert_result = mssql_query($sql2);

 

?>

 

However you appears to have a column per check box.

 

All the best

 

Keith

[ebolt007]

I guess so. I have my tables like this.

I have a table where I pull the checkboxes from with ID's. ie

Ethnicity table

ID      Name

1      1st checkbox

2      2nd checkbox

 

and what your saying is, I should then have one table for when these are checked, like

 

USERID------------CheckboxID------------CheckboxOn

1st user---------------1---------------------------0

1st user---------------2---------------------------1

2nd user--------------1---------------------------1

2nd user--------------2---------------------------1

 

The above would be that the 1st user only selected the 2nd checkbox, and the 2nd user selected both. Is this a better way to do this in a table then

UserID----------1stcheckbox--------------2ndcheckbox

1st User-------------0---------------------------1

2nd User------------1---------------------------1

 

I guess I can't use just the checkbox ID as the column name, like where 1stcheckbox is on the 2nd example as 1, so this would be hard to match up unless I had names. So the 1st example is the way to go?

 

Thanks for the help! Then how would I write to this using basically the same array system?

 

 

[kickstart]

Hi

 

This is the way I would do it:-

 

and what your saying is, I should then have one table for when these are checked, like

 

USERID------------CheckboxID------------CheckboxOn
1st user---------------1---------------------------0
1st user---------------2---------------------------1
2nd user--------------1---------------------------1
2nd user--------------2---------------------------1

 

Unique key on userid and checkbox id, and when the value changes use an insert with ON DUPLICATE KEY UPDATE.

 

All the best

 

Keith

[ebolt007]

Thanks for all the help Keith. One last question, you say insert ON DUPLICATE KEY UPDATE. Just curious how youd write something like this? Would it be thru the array, and then inside the array do an

 

$true_query = mssql_query("SELECT * FROM checkbox_database ORDER BY ID ASC");
while ($true_row = mssql_fetch_assoc($true_query))
      {
$main_checkbox = $true_row1['ID'];

$true_query = mssql_query("SELECT * FROM new_database ORDER BY ID ASC");
      while ($true_row = mssql_fetch_assoc($true_query))
      {
      	$checkboxID = $true_row['CheckboxID'];

if($main_checkbox != $checkboxID ){
$checkboxvalue = $_POST['eth_' . $main_checkbox ];
INTO new_database (UserId, CheckboxOn, DateUpdated) VALUES ('$user_ID', '$checboxvalue','$currenttime')";
}else{
$checkboxvalue = $_POST['eth_' . $main_checkbox ];
$sql2 = "UPDATE new_database SET UserID='$USERID',  CheckboxOn='$checboxvalue',  DateUpdated='$currenttime' WHERE CheckboxID='$main_checkbox'";
		$insert_result = mssql_query($sql2);
}
}
}

 

Or is there a better way to do this? Since I'm not using arrays now. Just trying to figure out the most effecient way to write this type of thing. I know the above won't work, I just wrote it fast, and it won't have the ID update based on UserID unless I count the userID's used in new_database and then update that, so it couldn't be an auto increment ID. And doing Unique key on UserID wouldn't work, because this will have like 8 of the same UserID's if someone checks all 8 of the checkboxes.

 

 

 

 

[ebolt007]

So here's what I came up for this to work,

$true_query2 = mysql_query("SELECT ID FROM true_ethnicity ORDER BY ID ASC");
    while ($true_row = mysql_fetch_assoc($true_query2))
    {
    $NewID= $true_row['ID'];
    $Value = $_POST['eth_' . $NewID];
    
    if ($Value == NULL){
     $Value = '0';
    }
    
    $sql = "SELECT UserID FROM user_ethnicity where UserID = '$user_ID' AND eth_ID = '$NewID'";
    $sql_result = mysql_query($sql);
    $login_row = mysql_fetch_assoc($sql_result);
          $user_ID2 = $login_row['UserID'];
          
      if ($user_ID2==NULL){
       $ValueArray[] = $_POST['eth_' . $true_row['ID']];
      $sql2 = "INSERT INTO user_ethnicity (UserID, eth_ID, Value) VALUES ('$user_ID', '$NewID', '$Value')";
      $insert_result = mysql_query($sql2);
      }
      else{
     $sql2 = "UPDATE user_ethnicity SET Value='$Value' WHERE UserID='$user_ID' AND eth_ID='$NewID'";
		$insert_result = mysql_query($sql2);
     
	  }	
	}

 

but is there a better and faster way for the server to look thru it faster with what you said "Unique key on userid and checkbox id, and when the value changes use an insert with ON DUPLICATE KEY UPDATE."

[kickstart]

Hi

 

You use ON DUPLICATE KEY UPDATE within an insert statement.

 

For example, assuming UserID and eth_ID is a unique key:-

 

INSERT INTO user_ethnicity (UserID, eth_ID, Value) 
VALUES ('$user_ID', '$NewID', '$Value')
ON DUPLICATE KEY UPDATE Value=VALUES(Value)

 

All the best

 

Keith