Jump to content

Weird foreach loop multiple DB insert problem


sford999
Go to solution Solved by sford999,

Recommended Posts

I have the following form which lists the most downloaded images from the site. and using checkboxes I am selecting specific items to show on the home page.
 

<form action="file_selection.php" method="post" enctype="multipart/form-data" name="file_selection" id="file_selection">
				  <table width="100%">
					<tr>
					  <td width="5%" align="center" class='fileTable regular'><?php echo adminFunctions::t('newsfeeder_fileid', 'File ID'); ?></td>
					  <td width="35%" align="center" class='fileTable regular'><?php echo adminFunctions::t('newsfeeder_original_filename_editable', 'Original Filename (Editable)'); ?></td>
					  <td width="30%" align="center" class='fileTable regular'><?php echo adminFunctions::t('newsfeeder_url', 'URL'); ?></td>
					  <td width="10%" align="center" class="fileTable regular"><?php echo adminFunctions::t('newsfeeder_filetype', 'File Type'); ?></td>
					  <td width="10%" align="center" class='fileTable regular'><?php echo adminFunctions::t('newsfeeder_filesize', 'File Size'); ?></td>
					  <td width="5%" align="center" class='fileTable regular'><?php echo adminFunctions::t('newsfeeder_downloads', 'Downloads'); ?></td>
					  <td width="5%" align="center" class='fileTable regular'><?php echo adminFunctions::t('newsfeeder_select', 'Select'); ?></td>
					</tr>
					<?php
					// Get most popular files by download
					$sql = "SELECT * FROM file WHERE statusId = '1' ORDER BY visits DESC LIMIT 0, 50";					
					$files = $db->getRows($sql);
					if ($files)
					{
						foreach ($files AS $file)
						{
							$addedFilename = '';
							$originalFilename = wordwrap($file['originalFilename'], 60, "<br/>", true);
							if (SITE_CONFIG_FILE_URL_SHOW_FILENAME == 'yes')
							{
								$addedFilename = '/'.slugify($file['originalFilename']);
							}
							echo '<tr>
								  <td width="5%" align="center" class="discreet">'.$file['id'].'</td>
								  <td width="35%" class="discreet"><input name="originalFilename" type="text" id="originalFilename" value="'.$originalFilename.'" size="50" /></td>
								  <td width="30%" class="discreet"><a href="'._CONFIG_SITE_PROTOCOL.'://'._CONFIG_SITE_FILE_DOMAIN.'/'.$file['shortUrl'].$addedFilename.'" target="_blank">'._CONFIG_SITE_PROTOCOL.'://'._CONFIG_SITE_FILE_DOMAIN.'/'.$file['shortUrl'].$addedFilename.'</a>
								  <input name="url" type="hidden" id="url" value="'._CONFIG_SITE_PROTOCOL.'://'._CONFIG_SITE_FILE_DOMAIN.'/'.$file['shortUrl'].$addedFilename.'" /></td>
								  <td width="10%" align="center" class="discreet">';

							if(!$file['extension'] || !ctype_alnum($file['extension']))
							{
								echo '<img src="'.SITE_IMAGE_PATH.'/file_icons/16px/_page.png" width="16" height="16" title="Unknown file"/>';
							}
							else
							{
								echo '<img src="'.SITE_IMAGE_PATH.'/file_icons/16px/'.$file['extension'].'.png" width="16" height="16" title="'.$file['extension'].' file"/>';
							}

							echo '<input name="filetype" type="hidden" id="filetype" value="'.$file['extension'].'" /></td>
									  <td width="10%" class="discreet">'.formatSize($file['fileSize']).'<input name="filesize" type="hidden" id="filesize" value="'.$file['fileSize'].'" /></td>
									  <td width="5%" align="center" class="discreet">'.$file['visits'].'</td>
									  <td width="5%" align="center" class="discreet"><input type="checkbox" name="fileid[]" id="fileid[]" value="'.$file['id'].'" onclick="return addCheck(this);" /></td>
									</tr>';
						}
					}
					else
					{
						adminFunctions::setError(adminFunctions::t("newsfeeder_no_files", "Error: No files to display."));
					}
					?>
					<tr>
					  <td colspan="6" align="right" class="discreet"><?php echo adminFunctions::t('newsfeeder_selectall', 'Select All'); ?></td>
					  <td width="5%" align="center" class="discreet"><input type='checkbox' name='checkall' onclick='checkedAll();'></td>
					</tr>
				  </table>
				  <br/><br/>				
					<input type="submit" name="submit" value="<?php echo adminFunctions::t('newsfeeder_button_use_selected_files', 'Use Selected Files'); ?>" class="button blue"/>
				</form>

The following code processes the form

 

if (isset($_POST['submit']))
{
	if(!$_REQUEST['fileid'])
	{
		adminFunctions::setError(adminFunctions::t("newsfeed_error_no_files_selected", "Error: You did not select any files."));
	}
	// Get post variables
	$fileid		= $_REQUEST['fileid'];
	$filename	= $_REQUEST['originalFilename'];
	$url		= $_REQUEST['url'];
	$filetype	= $_REQUEST['filetype'];
	$filesize	= $_REQUEST['filesize'];
	 
	foreach ($fileid as $key => $id)
	{
		$info[] = $id.",".$filename[$key].",".$url[$key].",".$filetype[$key].",".$filesize[$key];

		if(!in_array($key, $filename))
		{
			continue;
		}
	}
	// check for demo mode
	if (_CONFIG_DEMO_MODE == true)
    {
        adminFunctions::setError(adminFunctions::t("no_changes_in_demo_mode"));
    }
	// if no errors, redirect and show success banner
	if (adminFunctions::isErrors() == false)
    {
		foreach ($info as $file_info) 
		{
			$all = explode(",",$file_info);
			$id 			= $all[0];
			$filename		= $all[1];
			$url	    	= $all[2];
			$filetype   	= $all[3];
			$filesize  		= $all[4];

			$db->query("INSERT INTO newsfeeder_files (fileId, originalFilename, shortUrl, fileType, fileSize) VALUES ('".$id."','".$filename."','".$url."','".$filetype."', '".$filesize."')");
		}
		adminFunctions::redirect('file_selection.php?s=1');
    }   
}

 

However it's not inserting the information into the database properly as you can see from the image below

 

 

 

I have tried adding [] to the form fields

<input name="filetype" type="hidden" id="filetype" value="'.$file['extension'].'" />

to:

<input name="filetype[]" type="hidden" id="filetype[]" value="'.$file['extension'].'" />

 

but when I do the above and submit the form, it only adds the first 5 files (unchecked) from the 50 and not the 5 I have checked using the checkboxes.

 

I have been scratching my head over this for the past 2-3 hours without finding a solution, so I'm hoping someone would help me out.

 

Thanks

Link to comment
Share on other sites

I havent really looked at it properly but if a checkbox is not checked it doesnt turn up in the post array (i think) and this might have some consequence to your methods when processing the data, have you tried checking everybox to see if that works correctly?

 

When every box is checked, all 50 entries are added to the database.

Link to comment
Share on other sites

Yeah,

 

If I select 3 random files from the list of 14, the fileId column is always correct, it is the originalFilename, shortUrl, fileType, fileSize columns which are always the first rows from the form

INSERT INTO newsfeeder_files (fileId, originalFilename, shortUrl, fileType, fileSize) 
VALUES ('720','bitcoin_FHS_mod.zip','http://www.xvid.so/rq/bitcoinFHSmod.zip','zip','9680421');
INSERT INTO newsfeeder_files (fileId, originalFilename, shortUrl, fileType, fileSize) 
VALUES ('721','Bitcoin_SCI_original.7z','http://www.xvid.so/rr/BitcoinSCIoriginal.7z','7z','164985');
INSERT INTO newsfeeder_files (fileId, originalFilename, shortUrl, fileType, fileSize) 
VALUES ('724','check_host.zip','http://www.xvid.so/ru/checkhost.zip','zip','3535');

 

If you check the image below, you will see the table with the fileId, originalFilename, shortUrl, fileType, fileSize in the columns.

 

 

 

In the SQL queries above it shows

Fileid = 720

Filename = bitcoin_FHS_mod.zip

Fileid = 721

Filename = Bitcoin_SCI_original.7z

Fileid = 724

Filename = check_host.zip

 

When it should be:

Fileid = 720

Filename = site-offline-online.zip

Fileid = 721

Filename = site-offline-online-1.1.zip

Fileid = 724

Filename = web-icons-by-studiomx.zip

 

The shortUrl, fileType, fileSize columns are also listing the first 3 files in the form and not the actual checked files.

 

var_dump() of each of the columns show:

array(3) { [0]=> string(3) "720" [1]=> string(3) "721" [2]=> string(3) "724" }
 
array(14) { [0]=> string(19) "bitcoin_FHS_mod.zip" [1]=> string(23) "Bitcoin_SCI_original.7z" [2]=> string(14) "check_host.zip" [3]=> string(23) "contact_us_v2.x_1.0.zip" [4]=> string(18) "css_editor_1.0.zip" [5]=> string(18) "css_editor_1.1.zip" [6]=> string(18) "css_editor_1.2.zip" [7]=> string(20) "delete-users-1.0.zip" [8]=> string(17) "icon_mainsite.zip" [9]=> string(14) "myriad-pro.zip" [10]=> string(16) "Navbar Files.rar" [11]=> string(23) "site-offline-online.zip" [12]=> string(27) "site-offline-online-1.1.zip" [13]=> string(25) "web-icons-by-studiomx.zip" }
 
array(14) { [0]=> string(39) "http://www.xvid.so/rq/bitcoinFHSmod.zip" [1]=> string(43) "http://www.xvid.so/rr/BitcoinSCIoriginal.7z" [2]=> string(35) "http://www.xvid.so/ru/checkhost.zip" [3]=> string(42) "http://www.xvid.so/rx/contactusv2.x1.0.zip" [4]=> string(38) "http://www.xvid.so/ry/csseditor1.0.zip" [5]=> string(38) "http://www.xvid.so/rz/csseditor1.1.zip" [6]=> string(38) "http://www.xvid.so/rA/csseditor1.2.zip" [7]=> string(42) "http://www.xvid.so/rB/delete-users-1.0.zip" [8]=> string(38) "http://www.xvid.so/rC/iconmainsite.zip" [9]=> string(36) "http://www.xvid.so/rI/myriad-pro.zip" [10]=> string(38) "http://www.xvid.so/rJ/Navbar-Files.rar" [11]=> string(45) "http://www.xvid.so/rK/site-offline-online.zip" [12]=> string(49) "http://www.xvid.so/rL/site-offline-online-1.1.zip" [13]=> string(47) "http://www.xvid.so/rO/web-icons-by-studiomx.zip" }
 
array(14) { [0]=> string(3) "zip" [1]=> string(2) "7z" [2]=> string(3) "zip" [3]=> string(3) "zip" [4]=> string(3) "zip" [5]=> string(3) "zip" [6]=> string(3) "zip" [7]=> string(3) "zip" [8]=> string(3) "zip" [9]=> string(3) "zip" [10]=> string(3) "rar" [11]=> string(3) "zip" [12]=> string(3) "zip" [13]=> string(3) "zip" }
 
array(14) { [0]=> string(7) "9680421" [1]=> string(6) "164985" [2]=> string(4) "3535" [3]=> string(5) "37320" [4]=> string(5) "25957" [5]=> string(5) "25546" [6]=> string(5) "25992" [7]=> string(6) "122146" [8]=> string(3) "336" [9]=> string(6) "585170" [10]=> string(4) "7703" [11]=> string(5) "69453" [12]=> string(5) "71303" [13]=> string(7) "1672340" } 

So I know why it is getting the wrong items for the originalFilename, shortUrl, fileType, fileSize columns as the form is sending all the form inputs to the $_POST, and not just the selected ones, but I have no idea how to fix it

Edited by sford999
Link to comment
Share on other sites

Change this part of the script,

 

 

 foreach ($info as $file_info)

        {

            $all = explode(",",$file_info);

            $id             = $all[0];

            $filename        = $all[1];

            $url         = $all[2];

            $filetype     = $all[3];

            $filesize         = $all[4];



            $db->query("INSERT INTO newsfeeder_files (fileId, originalFilename, shortUrl, fileType, fileSize) VALUES ('".$id."','".$filename."','".$url."','".$filetype."', '".$filesize."')");

        }

 

to

 

 

$i = 0;
 foreach ($info as $file_info)

        {

            $all = explode(",",$file_info);

            $id             = $all[$i];

            $filename        = $all[$i];

            $url         = $all[$i];

            $filetype     = $all[$i];

            $filesize         = $all[$i];



            $db->query("INSERT INTO newsfeeder_files (fileId, originalFilename, shortUrl, fileType, fileSize) VALUES ('".$id."','".$filename."','".$url."','".$filetype."', '".$filesize."')");
           $i++

        }

 

By the way, running a sql query in the loop is a very bad practice.

 

 

EDIT:

 

OR

 

 

 
$i = 0;
 foreach ($info as $file_info)

        {

            $all = explode(",",$file_info);

            $id             = $all[0][$i];

            $filename        = $all[1][$i];

            $url         = $all[2][$i];

            $filetype     = $all[3][$i];

            $filesize         = $all[4][$i];



            $db->query("INSERT INTO newsfeeder_files (fileId, originalFilename, shortUrl, fileType, fileSize) VALUES ('".$id."','".$filename."','".$url."','".$filetype."', '".$filesize."')");
           $i++

        }
Edited by jazzman1
Link to comment
Share on other sites

The first suggestion does the following:

 

INSERT INTO newsfeeder_files (fileId, originalFilename, shortUrl, fileType, fileSize) 
VALUES ('700','700','700','700', '700');

INSERT INTO newsfeeder_files (fileId, originalFilename, shortUrl, fileType, fileSize) 
VALUES ('','','','', '');

INSERT INTO newsfeeder_files (fileId, originalFilename, shortUrl, fileType, fileSize) 
VALUES ('http://www.xvid.so/ru/checkhost.zip','http://www.xvid.so/ru/checkhost.zip','http://www.xvid.so/ru/checkhost.zip','http://www.xvid.so/ru/checkhost.zip', 'http://www.xvid.so/ru/checkhost.zip');

Second code does this

INSERT INTO newsfeeder_files (fileId, originalFilename, shortUrl, fileType, fileSize) VALUES ('7','','h','z', '9');
INSERT INTO newsfeeder_files (fileId, originalFilename, shortUrl, fileType, fileSize) VALUES ('1','','t','z', '6');
INSERT INTO newsfeeder_files (fileId, originalFilename, shortUrl, fileType, fileSize) VALUES ('4','','t','p', '3');

 

Heres the full script if you want to have a look?

 

 

Edited by sford999
Link to comment
Share on other sites

Well, let's see the array and its indexes before to loop it. 

 

Above the foreach put it that:

 

 

echo '<pre'>.print_r($info, true).'</pre>'; exit;
$i = 0;

 foreach ($info as $file_info)



        {



            $all = explode(",",$file_info);



            $id             = $all[$i];



            $filename        = $all[$i];



            $url         = $all[$i];



            $filetype     = $all[$i];



            $filesize         = $all[$i];







            $db->query("INSERT INTO newsfeeder_files (fileId, originalFilename, shortUrl, fileType, fileSize) VALUES ('".$id."','".$filename."','".$url."','".$filetype."', '".$filesize."')");

           $i++



        }

 

 

Link to comment
Share on other sites

  • Solution

I have actually just got it working as you posted your reply.
 
Instead of using the fileId on the checkboxes, I used the following:
 

$count=0;
foreach ($files AS $file)
{
	$addedFilename = '';
	$originalFilename = wordwrap($file['originalFilename'], 60, "<br/>", true);
	if (SITE_CONFIG_FILE_URL_SHOW_FILENAME == 'yes')
	{
		$addedFilename = '/'.slugify($file['originalFilename']);
	}
	echo '<tr>
		  <td width="5%" align="center" class="discreet">
		  <input name="fileid[]" type="hidden" id="fileid[]" value="'.$file['id'].'" />'.$file['id'].'</td>
		  <td width="35%" class="discreet">
		  <input name="originalFilename[]" type="text" id="originalFilename[]" value="'.$originalFilename.'" size="50" /></td>
		  <td width="30%" class="discreet"><a href="'._CONFIG_SITE_PROTOCOL.'://'._CONFIG_SITE_FILE_DOMAIN.'/'.$file['shortUrl'].$addedFilename.'" target="_blank">'._CONFIG_SITE_PROTOCOL.'://'._CONFIG_SITE_FILE_DOMAIN.'/'.$file['shortUrl'].$addedFilename.'</a>
		  <input name="url[]" type="hidden" id="url[]" value="'._CONFIG_SITE_PROTOCOL.'://'._CONFIG_SITE_FILE_DOMAIN.'/'.$file['shortUrl'].$addedFilename.'" /></td>
		  <td width="10%" align="center" class="discreet">';

	if(!$file['extension'] || !ctype_alnum($file['extension']))
	{
		echo '<img src="'.SITE_IMAGE_PATH.'/file_icons/16px/_page.png" width="16" height="16" title="Unknown file"/>';
	}
	else
	{
		echo '<img src="'.SITE_IMAGE_PATH.'/file_icons/16px/'.$file['extension'].'.png" width="16" height="16" title="'.$file['extension'].' file"/>';
	}

	echo '<input name="filetype[]" type="hidden" id="filetype[]" value="'.$file['extension'].'" /></td>
		  <td width="10%" class="discreet">'.formatSize($file['fileSize']).'<input name="filesize[]" type="hidden" id="filesize[]" value="'.$file['fileSize'].'" /></td>
		  <td width="5%" align="center" class="discreet">'.$file['visits'].'</td>
		  <td width="5%" align="center" class="discreet"><input type="checkbox" name="count[]" id="count[]" value="'.$count.'" onclick="return addCheck(this);" /></td>
		</tr>';
	$count++;
}

And to build the MySQL query (no more loops, just one query now)

	$fileid		= $_REQUEST['fileid'];
	$filename	= $_REQUEST['originalFilename'];
	$url		= $_REQUEST['url'];
	$filetype	= $_REQUEST['filetype'];
	$filesize	= $_REQUEST['filesize'];
	$count		= $_REQUEST['count'];

	foreach ($count as $key => $id)
	{
		$fid[] = "('".$fileid[$id]."','".$filename[$id]."','".$url[$id]."','".$filetype[$id]."', '".$filesize[$id]."')";
	}
	$output = implode(', ', $fid);
	$db->query("INSERT INTO newsfeeder_files (fileId, originalFilename, shortUrl, fileType, fileSize) VALUES ".$output.";");

Thanks for your help :thumb-up: :thumb-up: :thumb-up:

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.