Jump to content


  • Posts

  • Joined

  • Last visited

Everything posted by shades

  1. No. And yes even changing it in php file did not do the trick i actually had to json stringify while passing through AJAX and then had to decode it on my php file.
  2. Ok i found out the issue when i pass the array using ajax it shows 4800, when i do a count on the received array it only shows 2500. So, I finally found the culprit it was in php.ini file. ; How many GET/POST/COOKIE input variables may be accepted max_input_vars = 2500 damn.
  3. Yea got it working partially now, the delete happens within few milliseconds, but I am not understanding why it deletes only 2500 rows. Is there any setting which I need to change? I did try executing the query with all 4800 ids directly using MySQL workbench and all rows were deleted. So, I feel there is something from the client side which is limiting this.
  4. I got the data table with primary key as a running number and when I display data to the user I pick these ids as well. So when the user checks the boxes the id's get picked and all id's are stored in the id array. So this delete query is used to delete the rows with matching id's. Sorry, my English ain't good so I understood your first question in a different way.
  5. It is a table which displays those rows and user has checkboxes against each row and also a select all option so if they select all rows are selected, so in my example, i had 4800 rows
  6. Hi Guys, I have a form where the user can select rows and delete them. But my problem is when I select around 4800 rows to delete it takes too long and also only 2500 rows get deleted. So, could someone help me with the below code so it does not take more time and also deletes all the rows? Note: 1. I use InnoDB 2. I use ajax with async set to false <?php include("dbconnect.php"); ini_set ( 'max_execution_time', 20000); if(isset($_POST['id'])){ foreach ($_POST['id'] as $ids) { $stmt = $dbconnect->prepare("DELETE FROM datatable WHERE id=:id"); $stmt -> bindValue(':id', $ids); $stmt -> execute(); } $data =1; echo $data; } ?> I see in the above code my delete query is executed again and again through the loop and I would like to know if there is a way to execute only one statement and get all the ids inside the statement. Thank you.
  7. Hi Guys, I ran the below code on my laptop and it was working fine and I used Chrome and WAMP for the development. But when I uploaded the file on the server and tested it gave the below error. I tried including ob_start(), and ob_end_flush(); but it did not work, so could someone point out the mistake in code?? Error: Cannot modify header information - headers already sent by index.php Code: <div class="maincolumnheader"> <span class="btn btn-primary">Groups</span> <Span><img src="/images/arrow.jpg" class="arrow" alt=">>"></Span> <a class="btn btn-primary">Dimensions</a> <Span><img src="/images/arrow.jpg" class="arrow" alt=">>"></Span> <a class="btn btn-primary">Vignettes</a> <Span><img src="/images/arrow.jpg" class="arrow" alt=">>"></Span> <a class="btn btn-success">Illogical Cases</a> <Span><img src="/images/arrow.jpg" class="arrow" alt=">>"></Span> <a class="btn btn-primary">Random Sample</a> <Span><img src="/images/arrow.jpg" class="arrow" alt=">>"></Span> <a class="btn btn-primary">Decks</a> <Span><img src="/images/arrow.jpg" class="arrow" alt=">>"></Span> <a class="btn btn-primary">Export</a> </div> <div class="maincolumnbody" > <?php include("dbconnect.php"); if(isset($_POST["submit"])) { if(!empty($_POST["search"])) { // $query = str_replace(" ", "+", $_POST["search"]); $query = implode("+", $_POST["search"]); header("location:index.php?page=removevigtext&groupname=".$_GET["groupname"]."&search=" . $query); } } ?> <form class="form-horizontal" method="post"> <div class="panel panel-default"> <div class="panel-heading"><h4>Select the Dimension Level/s</h4></div> <div class="panel-body"> <div class="row"> <div class="col-xs-6 col-lg-6"> <?php $dimgroupname = $_GET["groupname"]; $stmtcount = $dbconnect -> prepare("SELECT COUNT(DISTINCT dimid) as dcount FROM dimensionlevel l INNER JOIN dimensiongroup g ON g.groupid = l.dimgroupid WHERE g.groupname = :groupname"); $stmtcount -> bindValue(':groupname',$dimgroupname); $stmtcount -> execute(); $rowcount = $stmtcount -> fetch(); if ($rowcount['dcount']==0){ echo "No level texts are avaialable for selection!!!"; } else { for ($i=1; $i <= $rowcount['dcount']; $i++){ // echo '<label for="search['.$i.']">Select Level'.$i.' text &nbsp</label>'; echo '<label for="search['.$i.']">Select Level &nbsp<select name="search['.$i.']">'; echo '<option value=""> </option>'; $stmt = $dbconnect -> prepare ("SELECT dimlevelvalue FROM dimensionlevel l INNER JOIN dimensiongroup g ON g.groupid = l.dimgroupid WHERE g.groupname = :groupname"); $stmt -> bindValue(':groupname',$dimgroupname); $stmt -> execute(); while ($row = $stmt->fetch()) { echo '<option value="'.$row['dimlevelvalue'].'">'.$row['dimlevelvalue'].'</option>'; } echo '</select></label>'; echo '<br>'; } } ?> </div> </div> </div> </div> <input type="submit" name="submit" class="btn btn-info" value="Search" /> </form> <br /> <div class="cspan"> </div> <form class="form form-horizontal" name="removevig_form" id="removevig_form" > <table id="maintable2" class="table table-striped table-bordered"> <thead id="table2head"> <tr> <th class="textview" id="table2head1"><label for="masterid">Select All &nbsp</label><input type="checkbox" id="masterid"></th> <th class="textview" id="table2head2">Vignettes</th> </tr> </thead> <tbody id="table2body"> <?php if(isset($_GET["search"])) { $dimgroupname = $_GET["groupname"]; $keywords = explode(" ", $_GET["search"]); $totalKeywords = count($keywords); $sqlquery = "SELECT * FROM vignettetext vig INNER JOIN dimensiongroup dim ON dim.groupid = vig.groupid WHERE dim.groupname = ? AND vigtext LIKE binary ? "; //use binary for exact match // dim.groupname = ? AND for($i=1 ; $i < $totalKeywords; $i++){ $sqlquery .= " AND vigtext LIKE binary ? "; } $stmt=$dbconnect->prepare($sqlquery); $stmt->bindValue(1, $dimgroupname); foreach($keywords as $key => $keyword){ $stmt->bindValue($key+2, '%'.$keyword.'%'); } $stmt->execute (); $count=1; while($row = $stmt -> fetch()) { echo '<tr><td><label for="id['. $row["id"].']">'.$count.'&nbsp</label><input type="checkbox" class="sub_chk" id="id['. $row["id"].']" value="'. $row["id"].'" ></td><td>'.$row["vigtext"].'</td></tr>'; $count++; } } ?> </tbody> </table> <div class="row"> <div class="col-xs-6 col-lg-6"> <a href="/index.php?page=dimvigview&groupname=<?php echo $_GET["groupname"]; ?>"><input type="button" class="btn goback" name="goback" value="Back"/></a> </div> <div class="col-lg-6 submitbtn"> <a data-popup-open="popup-1" href="#"> <input type="button" class="btn btn-primary" name="remvig" id="remvig" value="Remove Vignettes" /> <a href="/index.php?page=vigrandomsample&groupname=<?php echo $_GET["groupname"]; ?>"><input type="button" name="vigrandom" id="vigrandom" class="btn btn-primary" value="Random Sample"/></a> </a> <div class="popup" data-popup="popup-1"> <div class="popup-inner"> <h3>Are you sure you want to delete the texts ??</h3> <a data-popup-close="popup-1" href="#"><input type="button" class="btn btn-danger" value="NO" /></a> <a href="/index.php?page=removevigtext&groupname=<?php echo $_GET["groupname"]; ?>"><input type="button" name="removevig" id="removevig" class="btn btn-primary" value="Yes"/></a> <a class="popup-close" data-popup-close="popup-1" href="#">x</a> </div> </div> </div> </div> </form> </div> <span id="error_message" class="text-danger"><span class="glyphicons glyphicons-ok"></span></span> <span id="success_message" class="text-success"></span> Thank you.
  8. Right now I am using the below db connection. PHP version is 5.6 $host = ''; $db = ''; $user = ''; $pass = ''; $charset = 'utf8mb4'; $dsn = "mysql:host=$host;dbname=$db;charset=$charset"; $opt = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, ]; $dbconnect = new PDO($dsn, $user, $pass, $opt); So, i guess it should work if i change to utf8, but i did change and check the below string: ~ @ ! # $ % ^ & * ( ) _ + } { " | \ /.,\ ?>|": but in my CSV file it shows : ~ @ ! # $ % ^ & * ( ) _ + } { " | \ /.,\ ?>|":
  9. Hi, I got a string with special characters and in the table when i see the text, all the texts are inserted properly with the special characters. But when I use the below code to generate CSV file, for few of the special characters like - , ' and ... it displays –, ’ and …. So could someone guide me what changed I have to do in order to get the characters get displayed properly in my CSV file ? <?php include("dbconnect.php"); if(isset($_POST['groupname'], $_POST['decks'], $_POST['rows'])){ $groupname = $_POST['groupname']; $decks = $_POST['decks']; $rows = $_POST['rows']; $pickdeckrows = $rows/$decks; $zip = new ZipArchive(); $zipname = $groupname.'.zip'; $zip -> open($zipname, ZipArchive::CREATE); $j=1; for($i=1; $i<=$decks; $i++){ $stmt = $dbconnect->prepare("SELECT v.deckid, LPAD(v.decknumber,3,0) as decknumber, v.text FROM texttable v WHERE v.groupname=:groupname ORDER BY v.id LIMIT :pickdeckrows"); $stmt->bindValue(':groupname', $groupname); $stmt->bindValue(':pickdeckrows', $pickdeckrows); $stmt->execute(); $output = fopen('php://temp/maxmemory:1048576', 'w'); if (false === $output) { die('Failed to create temporary file'); } while($row = $stmt -> fetch()){ $length += fputcsv($output, [$groupname.$i.$row["decknumber"], $row["text"] ]); } rewind($output); $zip->addFromString($groupname.'Deck '.$j.'.csv', stream_get_contents($output) ); fpassthru($output); fclose($output); $j++; } $zip->close(); /* To download the zip file from browser use below commented code */ header('Content-Type: application/zip'); header('Content-disposition: attachment; filename='.$zipname); header('Content-Length: '. filesize($zipname)); header('Expires: 0'); header('Cache-Control: private'); header('Pragma: private'); ob_clean(); flush(); readfile($zipname); unlink($zipname); } else { echo "Not set!!!"; } ?> Thanks.
  10. Hi, Sorry for reposting on this question. With reference to your solution, could you please explain me what exactly is happening in the below condition ? I understood the following code but this, I am not able to understand. if (!$tail) { return array_map(function ($item) { return [$item]; }, $head); } Thanks
  11. Hi, I am running the PHP server on my laptop. So, far I am able to create a zip file and extract too, but when i extract i gave path to my c:/ folder.The issue is it extracts to the C:/ folder on my laptop where the php server is running and if a user extracts from a different machine I don't think it will extract to the client C:/folder or does it ? or is there any other way to specify the client path ? Also client can use linux system then i cannot use this path. So, I would like to know is there any way to achieve this ? Note: I have multiple CSV files which i have zipped and It is a must that I have to extract and provide the files to the user. Thanks.
  12. Okay. Understood as my export page is part of my Index page, the contents of index page were getting dumped. So to resolve this i pointed my export to a standalone page and now the download to csv is working perfect. I also read from another forum that "I cannot spit out multiple files on the fly for download. That's a limitation of the http protocol and PHP can not work around that unless I save multiple files to the disk and then either zip them and offer the zip file OR use a series of header redirects to grab the files in sequential order." So, I guess I got no other option than to zip all the files and later maybe unzip and provide it to the user.
  13. Using your solution, when i export, it is exporting as index.php and not as csv file.. Why is that ??
  14. Thanks for information. Yes, I actually need no formatting and multiple sheets. I found out that the application can only supports CSV file format. The main requirement is not just exporting into multiple files. User later selects one of the generated files into their application. So, if i generate it into an archive then again i have to unpack and provide it to them for selection. So, could you please tell me if this is the only way I could do it ? Or is their any other way to do it ? Thanks.
  15. Okay I got what you meant. I see my excel file has data but it's just dumping the HTML Table and not in the format of a spreadsheet. I see there are PHP Excel Libraries. I will use it but again my requirement is to generate multiple excel files and user cannot do it manually as there will be like more than 10 excel files and they want it to be generated automatically.
  16. Actually i did not include the code to delete the the rows which are exported. Below is the code which i tried and its working fine for exporting once. But I would like to have it export until the loop ends. if(isset($_GET['groupname'], $_GET['decks'], $_GET['rows'])) { $groupname = $_GET['groupname']; $decks = $_GET['decks']; $rows = $_GET['rows']; $pickdeckrows = $rows/$decks; // for($i=1; $i<=$decks; $i++){ $stmt = $dbconnect->prepare("SELECT v.decknumber, v.vtext FROM decks v WHERE v.groupname =:groupname LIMIT :pickdeckrows"); $stmt -> bindValue(':groupname', $groupname); $stmt -> bindValue(':pickdeckrows', $pickdeckrows); $stmt -> execute(); ob_end_clean(); $output = ' <table class="table" bordered="1"> <tr> <th>GroupId</th> <th>Text</th> </tr> '; while($row = $stmt -> fetch()) { $output .= ' <tr> <td>'.$row["decknumber"].'</td> <td>'.$row["vtext"].'</td> </tr> '; } $output .= '</table>'; header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment; filename='.$groupname.'deck.xls'); echo $output; $stmtdelete = $dbconnect->prepare("DELETE FROM decks WHERE groupname =:groupname LIMIT :pickdeckrows"); $stmtdelete -> bindValue(':groupname', $groupname); $stmtdelete -> bindValue(':pickdeckrows', $pickdeckrows); $stmtdelete -> execute(); // } }
  17. Hi, Firstly the below loop is not working as expected, I am not able to export to multiple excel files. It exports and writes to the same file. Secondly after exporting I got below errors inside the file. My requirement is to export data to a separate excel file on each iteration of the loop. So, could someone help me with these issue I am not able figure out where is the problem. if(isset($_GET['groupname'], $_GET['decks'], $_GET['rows'])) { $groupname = $_GET['groupname']; $decks = $_GET['decks']; $rows = $_GET['rows']; $pickdeckrows = $rows/$decks; for($i=1; $i<=$decks; $i++){ $stmt = $dbconnect->prepare("SELECT v.decknumber, v.vtext FROM decks v WHERE v.groupname =:groupname LIMIT :pickdeckrows"); $stmt -> bindValue(':groupname', $groupname); $stmt -> bindValue(':pickdeckrows', $pickdeckrows); $stmt -> execute(); ob_end_clean(); $output = ' <table class="table" bordered="1"> <tr> <th>GroupId</th> <th>VignetteText</th> </tr> '; while($row = $stmt -> fetch()) { $output .= ' <tr> <td>'.$row["decknumber"].'</td> <td>'.$row["vtext"].'</td> </tr> '; } $output .= '</table>'; header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment; filename='.$groupname.'deck'.$i.'.xls'); echo $output; } } else { echo "Not set!!!"; } Errors: 1. Notice: ob_end_clean(): failed to delete buffer. No buffer to delete 2. Warning: Cannot modify header information - headers already sent by (first header) 3. Warning: Cannot modify header information - headers already sent by (second header) Thanks.
  18. Thank you. The solution is perfect.
  19. Hi, Can someone point out the mistake ? I tested the query in MySQL workbench and it works fine but in the prepare statement it is not executing and also I get no error. if(isset( $_POST['name'], $_POST['samplecount'], $_POST['decks'])) { $name = $_POST['name']; $sample = $_POST['samplecount']; $decks = $_POST['decks']; $rows = ($sample/$decks); for ($i=0; $i<$decks; $i++){ for ($j=1; $j<=$rows; $j++) { $stmt = $dbconnect -> prepare(" INSERT INTO decks (id, dnumber, vtext) ( SELECT :id, :dnumber, (SELECT r.vtext FROM rsample r WHERE r.groupname = :groupname AND r.vtext NOT IN (SELECT vtext FROM decks) ) ORDER BY rand() LIMIT 1 ) "); $stmt -> bindValue(':id', $i); $stmt -> bindValue(':dnumber', $j); $stmt -> bindValue(':groupname',$name) $stmt -> execute (); } } } In my code I use name = "test" value of samplecount = 8 and decks = 2. So $rows will be 4. My Requirement: Out of the 8 texts. I want to get the data like below and I want the texts to be in random order. DeckId DeckNumber Text D1 1 CC D1 2 AA D1 3 ZZ D1 4 JJ D2 1 FF D2 2 BB D2 3 CC D2 4 WW Any help will be greatly appreciated. Thanks
  20. It is working fine but only issue was that the MEMORY does not support BLOB/TEXT so had to manually specify varchar(16000).
  21. Ahh got it thanks for the Information. I will try your solution and will update if things are working fine.
  22. Wow that's an interesting technique, never thought in that way. Yea delete or truncate is better ? I read Truncate is very fast.
  23. The flow is : I got a bigger set (1000 rows) --> get a subset (around 300 rows) randomly ---> select again random rows from these rows and then partition these rows with separate partition id (say like 10 partitions each having 30 rows (this also is randomly selected ). And finally export each partition as an excel file. So I will have 10 excel file. I was thinking about using two temporary files. First to select 300 rows and display. Second to insert randomly selected rows with partition id. So finally i can export it to the excel files with partition id and other columns. I hope i explained it well :/
  24. For example: 1. I have a permanent table with 1000 rows. 2. Next i randomly select a fixed(provided in an input form) number of rows and insert into a temporary table 3. Select and display the inserted data from the temporary table 4. Finally i group few of the rows in the temporary table and export into an excel file. So, is there an option to have persistent connect till the final step ? I have read about cookies and session. But I haven't used them so far.
  • 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.