Jump to content

Barand

Moderators
  • Posts

    24,558
  • Joined

  • Last visited

  • Days Won

    822

Community Answers

  1. Barand's post in using foreach to loop over resultset from fetch_assoc(); was marked as the answer   
    I showed you exactly how to do it in your previous topic. How do you still manage to keep screwing it up?
  2. Barand's post in Another database structure was marked as the answer   
    These two are useful for showing us your table structures:
    SHOW CREATE TABLE order_item;
    CREATE TABLE `order_item` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` int(11) DEFAULT NULL, `product_id` int(11) DEFAULT NULL, `qty` int(11) DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
    or, using the mysql CLI,
    mysql> DESCRIBE order_item;
    +------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | order_id | int(11) | YES | | NULL | | | product_id | int(11) | YES | | NULL | | | qty | int(11) | YES | | NULL | | | price | decimal(10,2) | YES | | NULL | | +------------+---------------+------+-----+---------+----------------+  
  3. Barand's post in Setting up foreign key was marked as the answer   
    Yes. The referential integrity enforced by the FK will allow you to insert car or vacation records only if the parent person exists.
     
    No, they can be used in updates and deletions too.
  4. Barand's post in generating invoice and getting values of quantity into an array was marked as the answer   
    i would name my qty fields using the product id as the key
    <td><input type="text" id="qty" name="qty[<?=$d_row['id']?>]"></td> then your $_POST['qty'] array for products 1, 4 and 7 will look like
    Array ( 1 => 5, 4 => 15, 7 => 10 )  - you're products and ids in one bundle.
  5. Barand's post in Problem comparing dates, but not getting results. was marked as the answer   
    Remove the ";" from the end of that line. It is saying "while(...) do nothing"
    PS
    If you only expect a single row for a date, use "if()" instead of "while()". Prepare your statement once before the loop - execute within the loop with new value
  6. Barand's post in Problem Query was marked as the answer   
    If you use
    sum(t1.refag_importo) AS Total without a GROUP BY clause it will give you a single row with the total for all the selected records.
    if, for example, you have
    SELECT t2.ui_company , sum(t1.refag_importo) AS Total FROM . . . GROUP BY ui.company then you get the total for each company
  7. Barand's post in Subtract eexpense.txt from balance.txt and print result was marked as the answer   
    I thought you would have realised in your initial code that you had the total_receivables from your array_sum() function call. If in doubt what a function is doing, there is always the manual
    Getting total expenses would be very similar.
    Subtracting one from the other is an easy bit.
  8. Barand's post in Database reference material was marked as the answer   
    So just, for example, "SELECT id,name, email FROM ..." so you only retrieve what you need. That's why you shouldn't use "SELECT * " in queries as that retrieves every column, need it or not, and slow your queries.
    [EDIT]
    PS That's one of the reasons not to use SELECT *.
  9. Barand's post in database structure was marked as the answer   
    Store the test dates - that will tell you who got there first.
  10. Barand's post in working with time/date was marked as the answer   
    Always store in the correct format (yyyy-mm-dd hh:ii:ss).
    Retrieval is flexible...
    mysql> SELECT name, submitted FROM test WHERE submitted BETWEEN '2022-05-05 09:05:00' AND '2022-05-05 13:30:00'; +-------+---------------------+ | name | submitted | +-------+---------------------+ | Curly | 2022-05-05 10:46:30 | | Larry | 2022-05-05 12:20:30 | +-------+---------------------+ mysql> SELECT name, submitted FROM test WHERE submitted BETWEEN '20220505090500' AND '20220505133000'; +-------+---------------------+ | name | submitted | +-------+---------------------+ | Curly | 2022-05-05 10:46:30 | | Larry | 2022-05-05 12:20:30 | +-------+---------------------+ mysql> SELECT name, submitted FROM test WHERE DATE(submitted) = '2022-05-05' AND TIME(submitted) BETWEEN '09:05:00' AND '13:30:00'; +-------+---------------------+ | name | submitted | +-------+---------------------+ | Curly | 2022-05-05 10:46:30 | | Larry | 2022-05-05 12:20:30 | +-------+---------------------+ mysql> SELECT name, submitted FROM test WHERE DATE(submitted) = '20220505' AND TIME(submitted) BETWEEN '090500' AND '133000'; +-------+---------------------+ | name | submitted | +-------+---------------------+ | Curly | 2022-05-05 10:46:30 | | Larry | 2022-05-05 12:20:30 | +-------+---------------------+  
  11. Barand's post in using array_replace on a 2 dimensional array was marked as the answer   
    With similar...
    $new = [ 1 => ['age' => 33], 5 => ['fname' => 'Sandra'], 3 => ['lname' => 'Cunningham'] ]; $table = array_replace_recursive($table, $new); Original $table $new resulting $table
  12. Barand's post in Drop Down List was marked as the answer   
    The only condition you have for not inserting is if the reason has an error.
    You should prevent insertion if there are any errors.
  13. Barand's post in using for loop to display elements from array was marked as the answer   
    How many arrays do you now have?
    $table = array ( 0 => array ( 'fname' => 'Peter', 'lname' => 'Smith', 'age' => '37' ), 1 => array ( 'fname' => 'Paul', 'lname' => 'Hartley', 'age' => '48' ), 2 => array ( 'fname' => 'Mary', 'lname' => 'Baker', 'age' => '42' ), 3 => array ( 'fname' => 'Jane', 'lname' => 'Doe', 'age' => '51' ) ); $newdata = array ( 4 => array ( 'fname' => 'Jon', 'lname' => 'Atkins', 'age' => '27' ), 5 => array ( 'fname' => 'Phil', 'lname' => 'Jones', 'age' => '14' ), 6 => array ( 'fname' => 'Frank', 'lname' => 'Lampard', 'age' => '48' ), 7 => array ( 'fname' => 'Toney', 'lname' => 'Brentford', 'age' => '25' ) ); $table = array_merge($table, $newdata); foreach ($table as $row => $data) { echo "<p><b>Row number $row</b></p>"; echo "<ul>"; foreach ($data as $col) { echo "<li>$col</li>"; } echo "</ul>"; } giving

  14. Barand's post in creating a shopping list and then delete item with double click was marked as the answer   
    Your deleteList() function (as it says on the tin) deletes the whole list. You need to to delete just the list item that is doubleclicked
    Here's my version
    <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script> <script type='text/javascript'> $().ready(function() { $("#addNew").click(function () { let txt = $("#addItem").val() // get the item text let li = $("<li>", {"text":txt, "class":"sItem"}) // create an <li> item $(li).dblclick(function() { // add dblclick event handler $(this).remove() $("#addItem").focus() }) $("#sList").append(li); // append li to ol item $("#addItem").val("").focus() // clear input field and ready for next input }) $("#addItem").focus() }) </script> [edit] PS Yours always deletes the first item in the list; mine deletes the one that is doubleclicked.
  15. Barand's post in PHP divide multidimensional array into two part with comparing another array was marked as the answer   
    Try this for size.
    (Note that my item_price table uses DATE types and not DATETIME. If you are using DATETIME, use NOW() where I have used CURDATE() and use "- INTERVAL 1 SECOND" where I have used "- INTERVAL 1 DAY" so the previous price ends just before the new one comes into effect)
    Starting with the current item_price table
    item_price +---------------+-------------+-----------+------------+-------------+---------+ | item_price_id | sales_price | our_price | valid_from | valid_until | item_id | +---------------+-------------+-----------+------------+-------------+---------+ | 30 | 175.00 | 0.00 | 2021-08-08 | 2021-08-07 | 30 | | 79 | 175.00 | 0.00 | 2021-08-08 | NULL | 30 | | 31 | 100.00 | 0.00 | 2021-08-08 | NULL | 31 | | 86 | 105.00 | 0.00 | 2022-04-09 | NULL | 31 | | 32 | 65.00 | 0.00 | 2021-08-08 | NULL | 32 | | 33 | 65.00 | 0.00 | 2021-08-08 | NULL | 33 | | 34 | 75.00 | 0.00 | 2021-08-08 | NULL | 34 | | 35 | 85.00 | 0.00 | 2021-08-08 | NULL | 35 | +---------------+-------------+-----------+------------+-------------+---------+ Write you POST data to a temporary table (new_price) instead of to an array
    new_price +---------+-------------+-----------+ | item_id | sales_price | our_price | +---------+-------------+-----------+ | 30 | 180.00 | 0.00 | changed | 34 | 75.00 | 0.00 | no change | 40 | 90.00 | 80.00 | new | 41 | 80.00 | 70.00 | new | 42 | 110.00 | 0.00 | new | 43 | 70.00 | 60.00 | new +---------+-------------+-----------+ If the new prices are the same as those that are still current then they can be ignored, so we can remove those, creating a separate temporary table (changed_price) which will be used to update the item_price table
    CREATE TEMPORARY TABLE changed_price SELECT n.item_id , n.sales_price , n.our_price , CURDATE() as valid_from FROM new_price n LEFT JOIN item_price p ON n.item_id = p.item_id AND CURDATE() BETWEEN p.valid_from AND coalesce(p.valid_until, '9999=12-31') AND n.sales_price = p.sales_price AND n.our_price = p.our_price WHERE p.item_id IS NULL; changed_price +---------+-------------+-----------+------------+ | item_id | sales_price | our_price | valid_from | +---------+-------------+-----------+------------+ | 30 | 180.00 | 0.00 | 2022-04-27 | | 40 | 90.00 | 80.00 | 2022-04-27 | | 41 | 80.00 | 70.00 | 2022-04-27 | | 42 | 110.00 | 0.00 | 2022-04-27 | | 43 | 70.00 | 60.00 | 2022-04-27 | +---------+-------------+-----------+------------+ Update the item price table setting the valid until to just before our valid from for the new price
    UPDATE item_price p JOIN changed_price c ON p.item_id = c.item_id AND c.valid_from BETWEEN p.valid_from AND coalesce(p.valid_until, '9999-12-31') SET p.valid_until = c.valid_from - INTERVAL 1 DAY; then add the new prices to the item price table
    INSERT INTO item_price(item_id, sales_price, our_price, valid_from) SELECT * FROM changed_price; Our resulting ite price table is now
    +---------------+-------------+-----------+------------+-------------+---------+ | item_price_id | sales_price | our_price | valid_from | valid_until | item_id | +---------------+-------------+-----------+------------+-------------+---------+ | 30 | 175.00 | 0.00 | 2021-08-08 | 2021-08-07 | 30 | | 79 | 175.00 | 0.00 | 2021-08-08 | 2022-04-25 | 30 | ** updated | 120 | 180.00 | 0.00 | 2022-04-26 | NULL | 30 | ** new | 31 | 100.00 | 0.00 | 2021-08-08 | NULL | 31 | | 86 | 105.00 | 0.00 | 2022-04-09 | NULL | 31 | | 32 | 65.00 | 0.00 | 2021-08-08 | NULL | 32 | | 33 | 65.00 | 0.00 | 2021-08-08 | NULL | 33 | | 34 | 75.00 | 0.00 | 2021-08-08 | NULL | 34 | | 35 | 85.00 | 0.00 | 2021-08-08 | NULL | 35 | | 121 | 90.00 | 80.00 | 2022-04-26 | NULL | 40 | ** new | 122 | 80.00 | 70.00 | 2022-04-26 | NULL | 41 | ** new | 123 | 110.00 | 0.00 | 2022-04-26 | NULL | 42 | ** new | 124 | 70.00 | 60.00 | 2022-04-26 | NULL | 43 | ** new +---------------+-------------+-----------+------------+-------------+---------+ This should execute far faster than creating and manipulating arrays then looping through those arrays to run queries.
  16. Barand's post in Group matching and unique array elements (PHP) was marked as the answer   
    Here's one method
    $bidders = ['bobby', 'kevin', 'bill', 'kevin', 'brian', 'bobby', 'bobby' ]; $uniq = array_unique($bidders); for ($i=1; $i<=count($uniq); $i++) { $anon[] = "Bidder$i"; } $trans = array_combine($uniq, $anon); $anonBidders = array_map( function($v) use ($trans) { return $trans[$v]; }, $bidders ); to test
    echo '<pre>'; vprintf("Bidders | %-8s | %-8s | %-8s | %-8s | %-8s | %-8s | %-8s <br>", $bidders); vprintf("anonBidders | %-8s | %-8s | %-8s | %-8s | %-8s | %-8s | %-8s <br>", $anonBidders); giving
    Bidders | bobby | kevin | bill | kevin | brian | bobby | bobby anonBidders | Bidder1 | Bidder2 | Bidder3 | Bidder2 | Bidder4 | Bidder1 | Bidder1  
  17. Barand's post in echo javascript inside anchor element was marked as the answer   
    Curly braces should do the trick
    $d_row['FormId'] = '98765'; echo "<td><a href='delete.php?user_id={$d_row['FormId']}' onclick=\"return confirm('Are you sure?')\">Delete</a></td>"; giving
    <td><a href="delete.php?user_id=98765" onclick="return confirm('Are you sure?')">Delete</a></td>  
  18. Barand's post in deriving age from someone's birthday was marked as the answer   
    You should've changed your initial query, adding in the age calculation (simple as that, just like the examples I've shown you!)
    $query = "SELECT Form.FormId, Form.FirstName, Form.LastName, Form.Email, TIMESTAMPDIFF(YEAR, Form.Birthdate, CURDATE()) as age2, Form.Birthdate, Form.FavLanguage, GROUP_CONCAT(Vehicle.VehSelection SEPARATOR ', ') AS VehSelection FROM Vehicle RIGHT JOIN Form ON Form.FormId = Vehicle.FormId GROUP BY Form.FormId"; Now you have $d_row['age2']
  19. Barand's post in date not displaying on populated edit form was marked as the answer   
    You can change the format of your d-m-Y dates to the correct Y-m-d format with this query
    UPDATE date_test SET birthday = str_to_date(birthday, '%d-%m-%Y') WHERE locate('-', birthday) = 3; For example
    mysql> CREATE TABLE `date_test` ( -> `date_test_id` int(11) NOT NULL AUTO_INCREMENT, -> `the_date` varchar(15) DEFAULT NULL, -> PRIMARY KEY (`date_test_id`) -> ) ; Query OK, 0 rows affected (0.28 sec) mysql> INSERT INTO `josen2`.`date_test` (`the_date`) VALUES ('2022-01-01'), ('01-01-2022'); Query OK, 2 rows affected (0.14 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM josen2.date_test; +--------------+------------+ | date_test_id | the_date | +--------------+------------+ | 1 | 2022-01-01 | | 2 | 01-01-2022 | +--------------+------------+ 2 rows in set (0.00 sec) mysql> UPDATE date_test -> SET the_date = str_to_date(the_date, '%d-%m-%Y') -> WHERE locate('-', the_date) = 3; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM josen2.date_test; +--------------+------------+ | date_test_id | the_date | +--------------+------------+ | 1 | 2022-01-01 | | 2 | 2022-01-01 | +--------------+------------+ 2 rows in set (0.00 sec) Once that has been done and all dates in the correct format you can change the column from varchar to date. (The ALTER query will fail if any dates have an incorrect format)
    mysql> ALTER TABLE `josen2`.`date_test` -> CHANGE COLUMN `the_date` `the_date` DATE NULL DEFAULT NULL; Query OK, 2 rows affected (0.87 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM josen2.date_test; +--------------+------------+ | date_test_id | the_date | +--------------+------------+ | 1 | 2022-01-01 | | 2 | 2022-01-01 | +--------------+------------+ 2 rows in set (0.01 sec)  
  20. Barand's post in Is there a better alternative way to reduce the below code? was marked as the answer   
    <?php $test['0000'] = ['address' =>'tes0']; $test['1111'] = ['id'=>'tes1', 'name'=>'tes11', 'address' =>'tes111']; $test['2222'] = ['id'=>'tes2', 'name'=>'tes22', 'address' =>'tes222']; $test['3333'] = ['id'=>'tes3', 'name'=>'tes33']; $nil = ['id'=>'NIL', 'name'=>'NIL', 'address' =>'NIL']; $test = array_map( function($v) use ($nil) { return array_replace($nil, $v); } , $test); PS Definite feeling of deja vu here
  21. Barand's post in displaying all check boxes from database even those that have not been selected was marked as the answer   
    Let's shoot this horse and put it out of its misery.
    There's still work to do but it illustrates another solution to your main problem.
    $vehicles = [ 'Plane', 'SuperCar', 'Yacht' ]; $res = $db->query("SELECT f.formid , f.firstname , f.lastname , v.vehselection FROM form f LEFT JOIN vehicle v USING (formid) ORDER BY lastname "); $data = []; foreach ($res as $r) { // process results // for each person create an array element // that contains an array of the vehicles owned // by that person if (!isset($data[$r['formid']])) { $data[$r['formid']] = [ 'name' => $r['firstname'] . ' ' . $r['lastname'], 'vehTypes' => [] ]; } $data[$r['formid']]['vehTypes'][] = $r['vehselection']; } // the data array looks like this // // $data = Array ( // // [193] => Array // ( // [name] => John Atkins // [vehTypes] => Array // ( // [0] => SuperCar // [1] => Plane // ) // // ) // // [192] => Array // ( // [name] => Frank Lampard // [vehTypes] => Array // ( // [0] => Yacht // [1] => Plane // ) // // ) // // ... // ) ?> <style type='text/css'> table { border-collapse: collapse; width: 600px; margin: 20px auto; } td, th { padding: 8px; } </style> <table border='1'> <tr> <th>Name</th> <th>Vehicles</th> </tr> <?php foreach ($data as $fid => $person) { echo "<tr><td>{$person['name']}</td><td>"; // now loop through the $vehicles array (top of script) // outputting a checkbox for each whic is checked // if the person owns one of that type foreach ($vehicles as $vtype) { $chk = in_array($vtype, $person['vehTypes']) ? 'checked' : ''; echo "<label> <input type='checkbox' name='vehicle[$fid][]' value='$vtype' $chk> $vtype </label> <br>"; } echo "</td></tr>\n"; } ?> </table>
  22. Barand's post in Can't delete duplicate rows in a table was marked as the answer   
    As all records in a relational DB should have a primary key I'll assume yours do.
    BEFORE
    +---------------------+------+----------------+ | 1 | hide | 20211123073000 | | 2 | hide | 20211123073000 | | 3 | hide | 20211123074500 | | 4 | hide | 20211123074500 | | 5 | | 20211123080000 | | 6 | hide | 20211123080000 | | 7 | | 20211123081500 | | 8 | hide | 20211123081500 | | 9 | | 20211123083000 | | 10 | hide | 20211123083000 | +---------------------+------+----------------+ QUERY
    DELETE teacher_schedule FROM teacher_schedule JOIN ( SELECT code , sched_time , MAX(teacher_schedule_id) as teacher_schedule_id FROM teacher_schedule WHERE code = 'hide' GROUP BY code, sched_time HAVING count(*) > 1 ) dupes USING (teacher_schedule_id); AFTER
    +---------------------+------+----------------+ | teacher_schedule_id | code | sched_time | +---------------------+------+----------------+ | 1 | hide | 20211123073000 | | 3 | hide | 20211123074500 | | 5 | | 20211123080000 | | 6 | hide | 20211123080000 | | 7 | | 20211123081500 | | 8 | hide | 20211123081500 | | 9 | | 20211123083000 | | 10 | hide | 20211123083000 | +---------------------+------+----------------+  
  23. Barand's post in Query problem. was marked as the answer   
    Use an explicit join ( trk_races JOIN drivers) instead of trk_races,drivers
    SELECT a.race_winner, w.wins, SUBSTRING_INDEX(a.race_winner, ' ', -1) AS last_name, a.race_name, DATE_FORMAT(a.race_date, '%m/%d') AS race_date, d.driver_num FROM trk_races a JOIN drivers d ON a.season = d.driver_season AND a.race_winner = d.driver_name LEFT OUTER JOIN ( SELECT race_winner, COUNT(race_date) AS wins FROM trk_races WHERE race_winner > '' AND Season=2021 GROUP BY race_winner ) w ON w.race_winner = a.race_winner WHERE a.race_winner > '' AND a.Season = 2021 +-------------+------+-----------+-----------+-----------+------------+ | race_winner | wins | last_name | race_name | race_date | driver_num | +-------------+------+-----------+-----------+-----------+------------+ | G Harrison | 2 | Harrison | Race 1 | 01/01 | 38 | | G Harrison | 2 | Harrison | Race 2 | 01/08 | 38 | | J Lennon | 1 | Lennon | Race 3 | 01/15 | 37 | | R Starr | 1 | Starr | Race 4 | 01/22 | 39 | | P McCartney | 1 | McCartney | Race 5 | 01/29 | 36 | +-------------+------+-----------+-----------+-----------+------------+  
  24. Barand's post in How to pre fill an array with missings keys? was marked as the answer   
    $test = [ '0000' => [2 =>'tes000'], '1111' => [0=>'tes1', 1=>'tes11', 2 =>'tes111'], '2222' => [0=>'tes2', 2 =>'tes333'] ] ; $test = array_map( function($v) { return array_replace(array_fill_keys(range(0,2), 'NIL'), $v); } , $test); echo '<pre>' . print_r($test, 1) . '</pre>'; giving
    Array ( [0000] => Array ( [0] => NIL [1] => NIL [2] => tes000 ) [1111] => Array ( [0] => tes1 [1] => tes11 [2] => tes111 ) [2222] => Array ( [0] => tes2 [1] => NIL [2] => tes333 ) )  
  25. Barand's post in FPDF Problem was marked as the answer   
    Simple. Triple the page width and offset each label.
    require 'code128.php'; $data = ['item_name' => 'Fuel Vapour Hose' ,'code_purchase' => 'ABC-2342' ,'code_sale' => 'DFS-4312' ,'item_code' => '47900001' ]; class Barcode_Label extends PDF_Code128 { protected $data; //constructor public function __construct() { parent::__construct('L','mm',[190, 35]); } public function printLabel($data) { $this->setMargins(5,5,5); $this->SetAutoPageBreak(0); $this->AddPage(); $this->setFont('Times', 'B', 10); for ($lab=0; $lab<3; $lab++) { $offset = $lab * 65; $this->setXY($offset, 5); $this->Cell(50, 5, $data['item_name'], 0, 2, 'C'); $this->Cell(25, 5, $data['code_purchase'], 0, 0, 'C'); $this->Cell(25, 5, $data['code_sale'], 0, 2, 'C'); $barcode = $this->Code128($offset + 5,15,$data['item_code'],50,10); $this->setXY($offset, 25); $this->Cell(50, 5, $data['item_code'], 0, 1, 'C'); } } } #Barcode_Label $label= new Barcode_Label(); for ($i=0; $i<3; $i++) { $label->printLabel($data); } $label->Output();
    [edit] PS I don't know your label dimensions so you may have to adjust offset, page size and margins
×
×
  • 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.