Jump to content

Barand

Moderators
  • Posts

    24,299
  • Joined

  • Last visited

  • Days Won

    791

Everything posted by Barand

  1. You might have read it but you totally ignored it. Good bye.
  2. Can you post the output you are now getting from echo '<pre>', print_r($_POST, 1), '</pre>';
  3. I would use virtually tthe same query for each of the above. When checking for existence, get the count. The advantage is there will always be a row returned, even if the count is zero. This avoids checking for reults prior to checking the value. $color = 'red'; // posted input $res = $db->prepare("SELECT COUNT(*) FROM cars WHERE color = ? "); $res->execute([$color]); then if you just want a yes/no answer echo $res->fetchColumn() > 0 ? 'Yes' : 'No'; but if you want to know how many echo $res->fetchColumn() . " cars found"; The plate search will be exactly the same. The difference is that a key search will be faster as the index can be used instead of the query processor having to read every record. All those scenarios are single value queries. The difference would come when you you need more info, such as "What make and color is Peter's car?" or "What make and color is Mary's car?" (doesn't exist). Assuming each driver has only one car... $name = 'Mary'; // posted input $res = $db->prepare("SELECT color , make FROM cars WHERE driver = ? "); $res->execute([$name]); $row = $res->fetch(); if (!$row) { echo "$name has no car"; } else { echo "$name has a {$row['color']} {$row['make']}"; }
  4. Just a comment on your tables. Data items like product_name, category, price are properties of the product and should, therefore, only be in the product table. The shopping table should hold just the product_id. (Read up on "data normalization") +-----------------+ | product | +-----------------+ | product_id |-------+ | product_name | | | category | | +------------------+ | size | | | shopping | | price | | +------------------+ +-----------------+ | | shopping_id | | | trans_ref | +--------<| product_id | | qty | | date | +------------------+
  5. Your problem is the input names. You have multiple items with the same name so, when posted, the last value with that name has overwritten all the previous ones. Only the final row is posted. You need to append "[ ]" to your input names so they are posted in arrays (EG name="category[]" ) . To process ... if ($_SERVER['REQUEST_METHOD']=='POST') { $stmt = $pdo->prepare("INSERT INTO shopping( trans_ref, category, product_name, item_type, item_size, item_qty, item_price, price, date ) VALUES( :trans_ref, :category, :product_name, :item_type, :item_size, :item_qty, :item_price, :price, CURDATE() ) "); foreach ($_POST['category'] as $k => $category) { $record = [ 'trans_ref' => $_SESSION['trans_ref'], 'category' => $category, 'product_name' => $_POST['product_name'][$k], 'item_type' => $_POST['item_type'][$k], 'item_size' => $_POST['item_size'][$k], 'item_qty' => $_POST['item_qty'][$k], 'item_price' => $_POST['item_price'][$k], 'price' => $_POST['item_price'][$k] * $_POST['item_qty'][$k], ]; $stmt->execute($record); } } That should fix your code. Now to fix your method. Firstly, don't store derived values. You can calculate the total price when required (SELECT qty * price as totalprice, ...) Secondly, a single INSERT..SELECT SQL query will replace all the above code (two if you count the check that data exists to be transferred). Here's how I would do it (no form required) $res = $pdo->prepare("SELECT SUM(item_qty * item_price) FROM temp_shopping WHERE trans_ref = ? "); $res->execute([$_SESSION['trans_ref']]); $total_price = $res->fetchColumn(); if ($total_price == 0) { echo "NO RECORDS TO TRANSFER<br>"; } else { $res = $pdo->prepare("INSERT INTO shopping( trans_ref, category, product_name, item_type, item_size, item_qty, item_price, date ) SELECT trans_ref, category, product_name, item_type, item_size, item_qty, item_price, CURDATE() FROM temp_shopping WHERE trans_ref = ? "); $res->execute([$_SESSION['trans_ref']]); echo "Total value transferred : " . number_format($total_price,2) . '<br>;'; }
  6. I agree with @ginerjm. If your first code is "working" it's because you have more than one record with the found email value. The best use I have found for fetchColumn() is when getting results from single-value queries EG $result = $db->query("SELECT COUNT(*) FROM user"); $user_count = $result->fetchColumn();
  7. 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.
  8. What does the data being posted to the form look like. IE What does this output... echo '<pre>', print_r($_POST, 1), '</pre>'; ?
  9. 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 }
  10. 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 } } }
  11. 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 | +-----------------+-------------+----------+---------------------+----------+---------------------+
  12. 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` | +----------+------------+-----------+---------+-----------------------------------------------------------------------------+
  13. That may be because your ajax request is sent to "cart.php"
  14. 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.
  15. 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.
  16. 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)
  17. 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 }
  18. Depends on what you want to accomplish. Take your pick from these
  19. 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)
  20. 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
  21. 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.
  22. 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 )
  23. In other words, how can one get someone else to do my assignment for me?
×
×
  • 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.