Jump to content

Barand

Moderators
  • Content Count

    19,274
  • Joined

  • Last visited

  • Days Won

    372

Everything posted by Barand

  1. Barand

    Insert into database at all at once

    Why are you using an intermediate form to move data from one table to another? Is this a guessing game where we have to guess how the form sending the above POST data is getting its data from your "temporal" table? - Sorry, I don't play games.
  2. Barand

    Insert into database at all at once

    What does the data being posted to the form look like. IE What does this output... echo '<pre>', print_r($_POST, 1), '</pre>'; ?
  3. MySql manual - triggers
  4. Barand

    submitting form data to a PHP script

    Your output depends on there being an input with the name "submit". There isn't one. As it is a bad idea to name an input "submit" rely on a button name to check for submissions of data (if you hit return the button name may not be sent depending on the browser) A better approach to test for posted data is if ($_SERVER['REQUEST_METHOD'] == 'POST' ) { // process posted data }
  5. An alternative approach is to create an array during your first pass of the data. This array would contain arrays of records for each rep. EG $salesdata = [ 'Sales_rep1' => [ 0 => [ 'Col1', 'Col2', 'Col3' ], 1 => [ 'Col1', 'Col2', 'Col3' ], 2 => [ 'Col1', 'Col2', 'Col3' ] ], 'Sales_rep2' => [ 0 => [ 'Col1', 'Col2', 'Col3' ], 1 => [ 'Col1', 'Col2', 'Col3' ], 2 => [ 'Col1', 'Col2', 'Col3' ], 3 => [ 'Col1', 'Col2', 'Col3' ] ], ]; You can then loop through the array creating your sheets for each rep. foreach ($salesdata as $rep => $sales) { create new sheet foreach ($sales as $recno => $recdata) { add new row foreach ($recdata as $col) { output column } } }
  6. Does this fit your requirements? The test_project has two fields for which changes are to be recorded (monitorA and monitorB). Two log tables are maintained, one for each of the columns. When a value changes, the original and new values are logged along with the datetime of the change, so you have record of the changes and when. CREATE TABLE `test_project` ( `test_project_id` int(11) NOT NULL AUTO_INCREMENT, `proj_name` varchar(45) DEFAULT NULL, `monitorA` varchar(45) DEFAULT NULL, `monitorB` varchar(45) DEFAULT NULL, `creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`test_project_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; CREATE TABLE `test_loga` ( `test_logA_id` int(11) NOT NULL AUTO_INCREMENT, `proj_id` int(11) DEFAULT NULL, `old_a` varchar(45) DEFAULT NULL, `new_a` varchar(45) DEFAULT NULL, `change_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`test_logA_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; CREATE TABLE `test_logb` ( `test_logb_id` int(11) NOT NULL AUTO_INCREMENT, `proj_id` int(11) DEFAULT NULL, `old_b` varchar(45) DEFAULT NULL, `new_b` varchar(45) DEFAULT NULL, `change_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`test_logb_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; Trigger... CREATE TRIGGER `test`.`test_project_AFTER_UPDATE` AFTER UPDATE ON `test_project` FOR EACH ROW BEGIN IF (old.monitorA != new.monitorA) THEN INSERT INTO test_loga (proj_id, old_a, new_a) VALUES (new.test_project_id, old.monitorA, new.monitorA); END IF; IF (old.monitorB != new.monitorB) THEN INSERT INTO test_logb (proj_id, old_b, new_b) VALUES (new.test_project_id, old.monitorB, new.monitorB); END IF; END Insert a project record then update monitorA twice then monitorB once INSERT INTO test_project (proj_name, monitorA, monitorB) VALUES ('Project One', 'AAA', 'BBB'); UPDATE test_project SET monitorA = 'AA2' WHERE test_project_id = 1; UPDATE test_project SET monitorA = 'AA3' WHERE test_project_id = 1; UPDATE test_project SET monitorB = 'BB4' WHERE test_project_id = 1; Log tables... mysql> select * from test_loga; +--------------+---------+-------+-------+---------------------+ | test_logA_id | proj_id | old_a | new_a | change_date | +--------------+---------+-------+-------+---------------------+ | 1 | 1 | AAA | AA2 | 2019-06-14 20:13:44 | | 2 | 1 | AA2 | AA3 | 2019-06-14 20:14:34 | +--------------+---------+-------+-------+---------------------+ 2 rows in set (0.00 sec) mysql> select * from test_logb; +--------------+---------+-------+-------+---------------------+ | test_logb_id | proj_id | old_b | new_b | change_date | +--------------+---------+-------+-------+---------------------+ | 1 | 1 | BBB | BB4 | 2019-06-14 20:54:05 | +--------------+---------+-------+-------+---------------------+ 1 row in set (0.00 sec) For reports, subqueries find the latest dates for each project in the log files. If no changes, the COALESCE will show the project creation date as the latest date. SELECT test_project_id , proj_name , monitorA , COALESCE(latesta, creation_date) as latest_A , monitorB , COALESCE(latestb, creation_date) as latest_B FROM test_project p LEFT JOIN ( SELECT proj_id , MAX(change_date) as latesta FROM test_loga GROUP BY proj_id ) a ON p.test_project_id = a.proj_id LEFT JOIN ( SELECT proj_id , MAX(change_date) as latestb FROM test_logb GROUP BY proj_id ) b ON p.test_project_id = b.proj_id +-----------------+-------------+----------+---------------------+----------+---------------------+ | test_project_id | proj_name | monitorA | latest_A | monitorB | latest_B | +-----------------+-------------+----------+---------------------+----------+---------------------+ | 1 | Project One | AA3 | 2019-06-14 20:14:34 | BB4 | 2019-06-14 20:54:05 | | 2 | Project Two | CCC | 2019-06-14 22:01:30 | DDD | 2019-06-14 22:01:30 | +-----------------+-------------+----------+---------------------+----------+---------------------+
  7. I see five distinct data items +----------+------------+-----------+---------+-----------------------------------------------------------------------------+ | Date | Book | Chapter | Verse | Text | +----------+------------+-----------+---------+-----------------------------------------------------------------------------+ | 01/01/19 | Lukas | 16 | 19 | Es war ein reicher Mann, der kleidete sich in Purpur und kostbares Leinen | | 01/02/19 | Kolosser | 3 | 13 | Ertrage einer den andern und vergebt euch untereinander` | | 01/03/19 | 1. Petrus | 5 | 10 | Der Gott aller Gnade, der euch berufen` | +----------+------------+-----------+---------+-----------------------------------------------------------------------------+
  8. That may be because your ajax request is sent to "cart.php"
  9. If your connection fails then $link is not a valid connection value - so you cannot use mysqli_error($link) which requires a valid $link argument. I told you what to use instead some days ago. Read the replies if you can't read the manual.
  10. Barand

    Check if exists

    Not a problem Adding your 3 new records... mysql> INSERT IGNORE INTO skyranger (addy, queue) VALUES -> ('email1@account.com', 'Accounting'), -> ('email2@account.com', 'Tech Support'), -> ('email1@account.com', 'Tech Support'); Query OK, 3 rows affected (0.05 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM skyranger ORDER BY addy, queue; +----+--------------------+--------------+ | id | addy | queue | +----+--------------------+--------------+ | 6 | email1@account.com | Accounting | | 1 | email1@account.com | Helpdesk | | 8 | email1@account.com | Tech Support | | 3 | email2@account.com | Accounting | | 7 | email2@account.com | Tech Support | | 2 | email3@account.com | Tech Support | +----+--------------------+--------------+ 6 rows in set (0.00 sec) Because the constraint is on the two columns then it is combinations of those columns that are unuque. So, for example, you cannot have two "email2/Accounting" records.
  11. Barand

    Check if exists

    The efficient way ... Put a UNIQUE CONSTRAINT on addy and queue columns so they cannot be duplicated CREATE TABLE `skyranger` ( `id` int(11) NOT NULL AUTO_INCREMENT, `addy` varchar(50) DEFAULT NULL, `queue` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `addyq` (`addy`,`queue`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; We now start off with your two existing records SELECT * FROM skyranger; +----+--------------------+--------------+ | id | addy | queue | +----+--------------------+--------------+ | 1 | email@account.com | Helpdesk | | 2 | email3@account.com | Tech Support | +----+--------------------+--------------+ Now we attempt to insert all three of your new inputs (ignoring duplicate key errors) mysql> INSERT IGNORE INTO skyranger (addy, queue) values -> ('email@account.com', 'Helpdesk'), -> ('email2@account.com', 'Accounting'), -> ('email3@account.com', 'Tech Support'); Query OK, 1 row affected, 2 warnings (0.12 sec) Records: 3 Duplicates: 2 Warnings: 2 mysql> SELECT * FROM skyranger; +----+--------------------+--------------+ | id | addy | queue | +----+--------------------+--------------+ | 3 | email2@account.com | Accounting | | 2 | email3@account.com | Tech Support | | 1 | email@account.com | Helpdesk | +----+--------------------+--------------+ Just the email2@account.com (id 3) record has been inserted, which is what was required. (All done with a single query. No looping of queries to check if a record already exists and then performing separate inserts)
  12. Barand

    Check if exists

    Your foreach() loop runs 3 queries. You then check and process the results of the final query. Put the rowcount checking and output inside the loop too, so it is performard for each query. IE foreach (...) { query check result output depending on check }
  13. Depends on what you want to accomplish. Take your pick from these
  14. Barand

    MATCH AGAINST (multiple columns)

    The columns in the MATCH() must match the columns in the index definition. You could either ...WHERE MATCH MATCH (all_title) AGAINST ('shrek' IN NATURAL LANGUAGE MODE) OR MATCH MATCH (all_tags) AGAINST ('shrek' IN NATURAL LANGUAGE MODE) OR MATCH MATCH (all_directors) AGAINST ('shrek' IN NATURAL LANGUAGE MODE) or add a fulltext text index on (all_title, all_tags, all_directors)
  15. Not completely certain what you are asking for. Is it those perfect squares with a value between 1 and 100 inclusive (ie 1, 4, 9, 25, 36, 49, 64, 81, 100), or the squares of all integers from 1 to 100 (ie 1, 4, 9, 25, 36, 49, 64, 81, 100, 121, 169, 196, 225, ... , 9801, 10000
  16. Barand

    Text Area Entry

    A better solution is to stop using text areas for data input (other than for blocks of text that are to be treated as blocks of text) and to use separate input fields for each separate pice of data.
  17. I'd argue that the normal way is to use foreach(). try $arr = [ 2, 3, 4 ]; function square($n) { return $n * $n; } function my_array_map($callback, $a ) { $new = []; foreach ($a as $k => $v) { $new[$k] = $callback($v); } return $new; } $new = my_array_map("square", $arr ); print_r($new); // Array ( [0] => 4 [1] => 9 [2] => 16 )
  18. In other words, how can one get someone else to do my assignment for me?
  19. Here's some sample code. Note the "[ ]" in the name of the dropdown in the form so multiple values are posted as an array <?php include("db_inc.php"); // defines DB credentials (HOST etc) $db = pdoConnect('jimr'); // connects to database // // PROCESS POSTED SELECTIONS // if ($_SERVER['REQUEST_METHOD']=='POST') { $placeholders = array_fill(0, count($_POST['square']), '?' ); $placeStr = join(',', $placeholders); // puts a "?" in placeStr for each selected square $params[] = $_POST['name']; // put name in params array foreach ($_POST['square'] as $s) $params[] = $s; // add squares to params array $stmt = $db->prepare("UPDATE square SET user_id = ? WHERE square_id IN ($placeStr)"); $stmt->execute( $params ); } /** * converts 1-100 to ROW - COL format (0-0 to 9-9) */ function squareOptions() { $opts = ''; foreach (range(1,100) as $s) { $sqr = sprintf("%d – %d", intdiv($s-1,10), ($s-1)%10 ); $opts .= "<option value='$s'>$sqr</option>\n"; } return $opts; } ?> <html> <head> <meta http-equiv="content-language" content="en"> <meta name="generator" content="PhpED 18.0 (Build 18044, 64bit)"> <title>Example</title> </head> <body> <form method="post"> Name <select name='name' > <option value=''>-select your name -</option> <option value='1'>Curly</option> <option value='2'>Larry</option> <option value='3'>Mo</option> </select> <br> Choose squares <br> <select name='square[]' multiple="multiple" size='10'> <?=squareOptions()?> </select> <br><br> <input type="submit" name="btnSubmit" value="Submit"> </form> </body> </html>
  20. You have 100 squares (1 - 100). Squares 5, 10, and 15 get taken. It isn't rocket science to work out that 1-4, 6-9, 11-14 and 16-100 are still available. Create an array with keys 1 - 100. As squares are taken, store the square number and the user. Query the stored data and allocate the users to the array using the square number as the key. Empty array elements are still available. Store square user ------ --------- 5 Curly 10 Larry 15 Mo Load into array squares = Array ( [1] => [2] => [3] => [4] => [5] => Curly [6] => [7] => [8] => [9] => [10] => Larry [11] => [12] => [13] => [14] => [15] => Mo [16] => [17] => [18] => [19] => [20] => . . [100] => ) You and Fred visit the site at the same time. You both see square 25 is available. It's your birthday so you pick it for luck. DB table updated to "25 - Jim". On Fred's screen it is still available and he picks 25. DB now overwrites Jim with Fred for square 25. When the results come in and 25 has won, how do you prove you had it first? After all, the DB says it's Fred's square.
  21. Barand

    Read folder

    It was something that would be useful to you When you do a dir listing EG Directory of C:\inetpub\wwwroot\test\images 14/03/2019 18:37 <DIR> . 14/03/2019 18:37 <DIR> .. 29/04/2012 00:41 1,735 badlogo.PNG 29/04/2012 12:58 233,305 banner.png 29/05/2015 10:32 374,455 cats.jpg 14/05/2012 18:41 1,739 download.png 22/02/2014 20:34 11,907 emplogo.png the first two names (with the dots) are actually diectories (current and parent). You are only intersted in the files, hence "is_file()" to check each file before you attempt to output it as an image <?php $dir = "pics/"; $file =""; // Open a directory, and read its contents if ($dh = opendir($dir)){ while (($file = readdir($dh)) !== false){ if (is_file($dir.$file)) { // <-- it goes here echo "<img src=\"$dir$file\" width=\"120px\" height=\"120px\"/>"; } } closedir($dh); } ?>
  22. Barand

    Read folder

    or you could have RTFM using the link @requinix gave you then if (is_file($dir.$file)) { echo "<img src=\"$dir$file\" width=\"120px\" height=\"120px\"/>"; }
  23. Barand

    Read folder

    I'm guessing that the two broken ones are the filenames "." and ".." Test for these and ignore them.
  24. As you put your data inside HTML tags I assue that by text file you mean an html text file. (Neither plain text nor PDF use html tags. The easier option is the text one. Add this code $mycontent = <<<TEXT <!DOCTYPE=html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Sample output</title> </head> <body> $mimiId $mimiBody </body> </html> TEXT; header('Content-Type: text/html'); header('Content-Disposition: attachment; filename="mytest.html" '); header('Pragma: no-cache'); header('Expires: 0'); echo $mycontent; if you want pdf, that is more complicated. I suggest you visit the FPDF site. Lots of example there.
  25. https://www.php.net/manual/en/mysqli.connect-error.php
×

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.