Jump to content

Barand

Moderators
  • Content Count

    19,099
  • Joined

  • Last visited

  • Days Won

    361

Everything posted by Barand

  1. <?php define("HOST",'localhost'); define("USERNAME",'????'); define("PASSWORD",'????'); define("DATABASE", "????"); function pdoConnect() { $dsn = "mysql:dbname=".DATABASE."; host=".HOST."; charset=utf8"; $db = new pdo($dsn, USERNAME, PASSWORD, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::MYSQL_ATTR_LOCAL_INFILE => true ]); return $db; } function toCurrency($number) { return '$' . number_format($number,2); } $db = pdoConnect(); $customer = $_GET['customer'] ?? ''; // you want data for display only, so use GET instead of POST $tdata = ''; if ($customer) { $res = $db->prepare("SELECT work_order , customer , description , value , balance , status , notes FROM jobtest WHERE customer = ? "); $res->execute([$customer]); $tot_value = $tot_balance = 0; foreach ($res as $row) { $tot_value += $row['value']; $tot_balance += $row['balance']; // accumulate totals as they are fetched $tdata .= "<tr><td>{$row['work_order']}</td> <td>{$row['customer']}</td> <td>{$row['description']}</td>"; $tdata .= '<td>' . toCurrency($row['value']) . '</td>'; $tdata .= '<td>' . toCurrency($row['balance']) . '</td>'; $tdata .= "<td>{$row['status']}</td> <td>{$row['notes']}</td></tr>"; } $tdata .= "<tr><td colspan='3'>Totals</td><td>" . toCurrency($tot_value) . '</td><td>' . toCurrency($tot_balance) . "</td><td colspan='2'></td></tr>"; } ?> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta name="creation-date" content="03/23/2019"> <title>Sample</title> </head> <body> <form> <input type="text" name="customer"> <input type="submit" name="btnSubmit" value="Search"> </form> <hr> <table border='1' style='border-collapse:collapse'> <tr> <th>Work Order #</th> <th>Customer</th> <th>Description</th> <th>Value</th> <th>Balance</th> <th>Status</th> <th>Notes</th> </tr> <?=$tdata?> </body> </html>
  2. Barand

    auto increment question

    see mysql integer types
  3. Barand

    auto increment question

    99,999,999,999 ? Maximum unsigned int is 4,294,967,295
  4. Just create a pdo connection of your own, however you do it. I left that in just to show it was using PDO.
  5. Barand

    auto increment question

    It will go back to 0. If it really worries you you can move to BIGINT UNSIGNED. Then, if you add 1,000,000 records every second it will take 584,942 years to run out of ids.
  6. Barand

    In need of assistance...

    ($d <= $vault[$i-1]-1 && $d >= $vault[$i-1]+1) // original ($vault[$i-1]-1 <= $d && $d <= $vault[$i-1]+1) // now mt_rand is a better randomising function than rand(). It doesn't hang for me when I use your countIf() function. Must be something else causing it.
  7. Barand

    In need of assistance...

    Had the -1 and +1 the wrong way round. Should be ($vault[$i-1]-1 <= $d && $d <= $vault[$i-1]+1)
  8. Yes, it already searches for the customer in $_GET['customer']. Just add <form> <input type="text" name="customer"> <input type="submit" name="btnSubmit" value="Search"> </form>
  9. Barand

    In need of assistance...

    Commented version function generate($n) { $vault = [ mt_rand(1,99) ]; // put 1st random number into the array for ($i=1; $i<$n; $i++) { do { // keep adding numbers until there are $n of them $d = mt_rand(1,99); // get a new random number } while (in_array($d, $vault) || ($d <= $vault[$i-1]-1 && $d >= $vault[$i-1]+1)); // if it is in the array or within 1 of the previous number, choose another $vault[$i] = $d; // add the new number to the array } return join(', ', $vault); // return the array as a comma-sparated string. }
  10. Barand

    In need of assistance...

    Are you looking for something like this? function generate($n) { $vault = [ mt_rand(1,99) ]; for ($i=1; $i<$n; $i++) { do { $d = mt_rand(1,99); } while (in_array($d, $vault) || ($d <= $vault[$i-1]-1 && $d >= $vault[$i-1]+1)); $vault[$i] = $d; } return join(', ', $vault); } // Test it for ($i=0; $i<20; $i++) { echo generate(5) . '<br>'; }
  11. You only need a single connection per page (unless you want to connect to multiple servers. Also you only need one query $db = pdoConnect(); $customer = $_GET['customer'] ?? ''; // you want data for display only, so use GET instead of POST $tdata = ''; if ($customer) { $res = $db->prepare("SELECT work_order , customer , description , value , balance , status , notes FROM jobtest WHERE customer = ? "); $res->execute([$customer]); $tot_value = $tot_balance = 0; foreach ($res as $row) { $tot_value += $row['value']; $tot_balance += $row['balance']; // accumulate totals as they are fetched $tdata .= "<tr><td>{$row['work_order']}</td> <td>{$row['customer']}</td> <td>{$row['description']}</td>"; $tdata .= '<td>' . toCurrency($row['value']) . '</td>'; $tdata .= '<td>' . toCurrency($row['balance']) . '</td>'; $tdata .= "<td>{$row['status']}</td> <td>{$row['notes']}</td></tr>"; } $tdata .= "<tr><td colspan='3'>Totals</td><td>" . toCurrency($tot_value) . '</td><td>' . toCurrency($tot_balance) . "</td><td colspan='2'></td></tr>"; } function toCurrency($number) { return '$' . number_format($number,2); } ?> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta name="creation-date" content="03/23/2019"> <title>Sample</title> </head> <body> <table border='1' style='border-collapse:collapse'> <tr> <th>Work Order #</th> <th>Customer</th> <th>Description</th> <th>Value</th> <th>Balance</th> <th>Status</th> <th>Notes</th> </tr> <?=$tdata?> </body> </html>
  12. Without knowing your your current structures and what you propose to put in the "derived table" then I cannot say. If you are storing totals that is already derived data and should be avoided. Instead you should query the data that makes up the totals to get them when required. Sorry I am not clairvoyant. All you showed was a link to an array and a link to what you said was you table data.
  13. Using a cut-down version of your array $array = [ 'Aecon' => [ 0 => [ 'total_value' => '4991732.24' ] ], 'All Weld' => [ 0 => [ 'total_value' => '341518.00' ] ], 'Wolverine' => [ 0 => [ 'total_value' => '4875.00' ] ] ]; $total = 0; foreach ($array as $company => $cdata) { $total += $cdata[0]['total_value']; } echo $total; //--> 5338125.24 A more sensible structure for that data would be $array = [ 'Aecon' => 4991732.24, 'All Weld' => 341518.00, 'Wolverine' => 4875.00 ]; $total = array_sum($array); echo $total; //--> 5338125.24 Now, about that data in your database table. Do not store formatted numbers. The numeric value of "$372,568.00" is 0. You may as well try to get a total of your descriptions. Store that as column type DECIMAL (eg decimal(12,2) ) with a value of 372568.00. Once you have corrected the data then SELECT customer , SUM(value) as total_value , SUM(balance) as total_balance FROM mytable GROUP BY customer;
  14. That appears to be a far cry from the problem that you posted. I see you are starting a table subquery with "join (". What I can't see is a ") aliasname" where the subquery ends.
  15. try SELECT partgroup , partid , quantity , total , round(quantity / q12 * 100, 2) as pcent FROM part JOIN ( SELECT partgroup , quantity as q12 FROM part WHERE RIGHT(partid,2) = '12' ) as p12 USING (partgroup);
  16. Barand

    Says

    Have you connected to the database server? And is that connection $mysqli?
  17. Barand

    Help needed with PHP 5.6 Umlaut Problem

    Steps Make sure your database charset is utf8 (check tables and individual columns are not overriding the default) Make sure your db connection is utf8 with a Latin1 connection I get "Name: �bermann" with utf8 connection I get "Name: Übermann" Make sure you html is using utf-8
  18. The time column in your db table needs to be DATETIME type and not INT. You also need to change your query so you have ... WHERE `time` BETWEEN = '2019-03-20 00:00:00' AND '2019-03-20 23:59:00' instead of using 1553040000 and 1553126340
  19. Barand

    import csv in mysql db using ajax and php

    - Now you have impoerted your data you should nomalize the data, so you would have +---------------+ +---------------+ | student | | subject | +---------------+ +---------------+ | student_id |---+ +-----| subject_id | | name | | +---------------+ | | name | | etc | | | score | | +---------------+ +---------------+ | +---------------+ | +---<| student_id | | | subject_id |>---+ | score | | exam_date | +---------------| and the data in those tables would look like this +-----+----------+ +-------------+----------------+---------+-----------------+ +--------------+-----------------+ | Id | name | | student_id | subject_id | score | exam_date | | subject_id | name | +-----+----------+ +-------------+----------------+---------+-----------------+ +--------------+-----------------+ | 1 | Name1 | | 1 | 1 | 10 | 2019-03-01 | | 1 | Physics | | 2 | Name2 | | 1 | 2 | 25 | 2019-03-01 | | 1 | Maths | +-----+----------+ | 1 | 3 | 35 | 2019-03-01 | | 1 | Chemistry | | 2 | 1 | 80 | 2019-03-01 | | | | | 2 | 2 | 45 | 2019-03-01 | | 3 | 3 | 45 | 2019-03-01 | | ... | ... | ... | ... |
  20. Barand

    Do you have a pet?

    A good idea. You still get your exercise and the dog gets out and about to experience all the local scents (his daily newspaper)
  21. I see your philosophy is "Why use one function when the job can be done with four" Examples: $end = strtotime(date('M')." ".date('d').", ".date('Y')." 11:59pm"); // is equivalent to $end = mktime(23,59,0); if (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 31) { $array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0 ,'29' => 0 ,'30' => 0 ,'31' => 0); }elseif (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 30) { $array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0 ,'29' => 0 ,'30' => 0); }elseif (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 29) { $array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0 ,'29' => 0); }elseif (cal_days_in_month(CAL_GREGORIAN, date('m'), date('Y')) == 28) { $array = array('01' => 0 ,'02' => 0 ,'03' => 0 ,'04' => 0 ,'05' => 0 ,'06' => 0 ,'07' => 0 ,'08' => 0 ,'09' => 0 ,'10' => 0 ,'11' => 0 ,'12' => 0 ,'13' => 0 ,'14' => 0 ,'15' => 0 ,'16' => 0 ,'17' => 0 ,'18' => 0 ,'19' => 0 ,'20' => 0 ,'21' => 0 ,'22' => 0 ,'23' => 0,'24' => 0 ,'25' => 0 ,'26' => 0 ,'27' => 0 ,'28' => 0); } // is equivalent to $array = array_fill_keys(range(1, date('t')), 0); Stop using unix style timestamps and use MySQL datetime formats. (Y-m-d H:i:s)
  22. Barand

    Creating Google Charts with dynamic data

    We've got out sync. My line 35 hasn't got one of those.
  23. Nobody noticed, honest!
  24. You have single quotes arond "time" so it is being treated as a string literal. Use backticks like you have around the previous one.
  25. Barand

    Creating Google Charts with dynamic data

    Try deleting line 36 and then reentering it in case there's a hidden character lurking.
×

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.