Jump to content

Barand

Moderators
  • Posts

    24,563
  • Joined

  • Last visited

  • Days Won

    822

Community Answers

  1. Barand's post in Upgrading PHP was marked as the answer   
    There are several FETCH modes in PDO.
    The most usual is to set the default to PDO::FETCH_ASSOC so the row arrays are indexed by field name. This is normally set in your connect options.
    However, even if the default is set it can be overridden when required. So...
    $row = $result->fetch(PDO::FETCH_NUM) will allow you to use $row[6].
     
  2. Barand's post in Selecting records of the last 24 hours was marked as the answer   
    Random dates test data (TABLE: orders)
    +----+---------------------+ | id | ordertimestamp | +----+---------------------+ | 1 | 06:00:00 10.30.2024 | | 2 | 09:00:00 10.28.2024 | | 3 | 01:00:00 10.31.2024 | | 4 | 11:00:00 10.29.2024 | | 5 | 14:00:00 10.29.2024 | | 6 | 00:00:00 10.29.2024 | | 7 | 00:00:00 10.30.2024 | | 8 | 16:00:00 10.30.2024 | | 9 | 12:00:00 10.28.2024 | | 10 | 06:00:00 10.29.2024 | | 11 | 18:00:00 10.29.2024 | | 12 | 20:00:00 10.30.2024 | | 13 | 11:00:00 10.30.2024 | | 14 | 18:00:00 10.28.2024 | | 15 | 10:00:00 10.30.2024 | +----+---------------------+ Now add a proper datetime column.
    ALTER TABLE orders ADD COLUMN newdatetime DATETIME; Now transfer the old timestamp data to new column, reformatting the data
    UPDATE orders SET newdatetime = STR_TO_DATE(ordertimestamp, '%H:%i:%s %m.%d.%Y'); Now we can list the data in date order
    SELECT * FROM orders WHERE newdatetime > NOW() - INTERVAL 24 HOUR ORDER BY newdatetime; +----+---------------------+---------------------+ | id | ordertimestamp | newdatetime | +----+---------------------+---------------------+ | 1 | 06:00:00 10.30.2024 | 2024-10-30 06:00:00 | | 15 | 10:00:00 10.30.2024 | 2024-10-30 10:00:00 | | 13 | 11:00:00 10.30.2024 | 2024-10-30 11:00:00 | | 8 | 16:00:00 10.30.2024 | 2024-10-30 16:00:00 | | 12 | 20:00:00 10.30.2024 | 2024-10-30 20:00:00 | | 3 | 01:00:00 10.31.2024 | 2024-10-31 01:00:00 | +----+---------------------+---------------------+  
  3. Barand's post in Help with query was marked as the answer   
    You can't reference alias in the field list part of the query (they aren't allocated until the later output phase of the query. For instance, you can order by final),
    You need
    SELECT v.Total AS total, SUM(vt.numberofstudents) AS alunos, (v.Total/SUM(vt.numberofstudents)) AS final ... [edit] P.S.
    To summarize
    +---------+---------------------------+ | I/O | Query Clauses | +---------+---------------------------+ | | | | INPUT | SELECT ... | Column aliases | | FROM ... | defined but not | | WHERE ... | referenced | | | +---------+---------------------------+ | | | | | GROUP BY ... | Column aliases | OUTPUT | HAVING ... | can be referenced | | ORDER BY ... | here | | | +---------+---------------------------+  
  4. Barand's post in Copy a row within same table was marked as the answer   
    the id is unique, so you can't insert a second id of 26.
    Omit the id column from the query then the rest of the columns can be inserted (unless any are defined UNIQUE in which case you need to omit those too)
    $sql = "INSERT INTO $table (company_name) SELECT company_name FROM $table WHERE id = 26"; You can call a lastInsertId() function to get the id of the new record.
  5. Barand's post in how to get data from more than 2 tables was marked as the answer   
    Why the Freckle didn't you post the code that actually used to get the results you are complaining about. Once I got the data loaded, your query wouldn't even run without corrections to column names.
    Anyway - the answer to your question...
    They are in the wrong order because you order by your generated qNo column. I'd give up on that method.

    If you are using MariaDB, you can ORDER BY NATURAL_SORT_KEY(Q_id)
    If MySQL (which doesn't have that function), use FetchAll() to get an array of your results then natsort($results) use a custom sort which does a strnatcmp() on the Q_id column
     
    $res = $pdo->query(" ... "); $result = $res->FetchAll(); usort($results, fn($a,$b)=>strnatcmp($a['Q_id'], $b['Q_id'])); (Using sort($results) would have sorted using the values of the first column in each row - I assumed natsort() would do the same (silly me) )
  6. Barand's post in Basics of sql is easy, but how did you learn advanced sql? How did you started tinkering with data? was marked as the answer   
    How did you learn advanced SQL?
    With practice. How did you start tinkering with data?
    By designing and building databases to support applications I needed to develop, then developing them using the database. Problem is I've no data to tinker with.
    Then create some. (See tutorials link in my signature)
  7. Barand's post in Confirmation of an UPDATE to table was marked as the answer   
    Check the number of rows affected by the query.
    https://www.php.net/manual/en/mysqli.affected-rows.php
  8. Barand's post in fileinfo extension not working PHP 8.3.1 was marked as the answer   
    Check the output from phpinfo() to make sure the php.ini file you edited is the one currently being used.
  9. Barand's post in Insert with prepared statement was marked as the answer   
    Because your table has 8 columns but you only want to insert into 3 of them, it will attempt to insert NULL values (or the columns' specified default values) into the remaining 5.
    Therefore, if a column is specified as NOT NULL, it should have a default value if you don't always populate it when inserting a new record.
    In addition, the id column should be an auto_incremented INT value by default and also excluded from the insert.
  10. Barand's post in collecting data from multiple html inputs was marked as the answer   
    Judicious application of array key names can greatly increase the efficiency and simplicity of your code. Consider this simplified version of the questions/options form code
    <form method='post' > <?php for ($qno=1; $qno<=2; $qno++) { echo <<<HTML <label> Sub Question $qno <span class="req">*</span> <textarea cols="46" rows="3" name="Q[$qno][question]" placeholder="Enter Sub question here.."></textarea> </label> <ul> HTML; for ($opt='A'; $opt<='D'; $opt++) { echo <<<HTML <li>Choice $qno$opt (text)&nbsp; <input type='text' name="Q[$qno][opts][$opt]" placeholder="Enter Choice A here.." size='40'> </li><br><br>\n HTML; } echo "</ul><hr>\n"; } ?> <input type='submit'> </form> producing...

    When the form is submitted, the POST array is like this...
    Array ( [Q] => Array ( [1] => Array ( [question] => aaaaaaaaaaaaaaaaaaaaaaaaaaa [opts] => Array ( [A] => aa [B] => bb [C] => cc [D] => dd ) ) [2] => Array ( [question] => bbbbbbbbbbbbbbbbbbbbbbbbb [opts] => Array ( [A] => ww [B] => xx [C] => yy [D] => zz ) ) ) ) Now you can easily iterate through the array to write the questions/options to you database
    foreach ( $_POST['Q'] as $qno => $qdata ) { write $qno and $qdata['question'] to question table save last insert id as $qid foreach ( $qdata['opts'] as $ono => $choice ) { write $qid, $ono, $choice to choice table } } Job Done.
  11. Barand's post in Formatting echo from array was marked as the answer   
    Use join() instead of the foreach loop.
    echo join(':', $numbers);  
  12. Barand's post in How can I replace a line feed with a br tag in a MySql sql statement from php? was marked as the answer   
    It looks as though it is two separate characters and not a linefeed character (linefeed = 0A)
    w e c h a r t e r e d a \ n f i s h i n g t r i p . 77 65 20 63 68 61 72 74 65 72 65 64 20 61 20 5C 6E 20 66 69 73 68 69 6E 67 20 74 72 69 70 2E ^^ ^^  
  13. Barand's post in ajax fails was marked as the answer   
    Select "Developer tools" in browser menu Open network tab. Instigate the AJAX request When ajax call appears in network window, click on it Select response tab to response or request tab to see request
  14. Barand's post in getting the feedback in php was marked as the answer   
    The purpose of a prepared statement is to avoid placing variable contents directly into the query (and thus avoid SQL injection attacks) by using placeholders and parameters.
    Using a stored procedure does the same thing.
    Therefore using a prepared statement inside a stored procedure is a "belt and braces" approach and unnecessary overkill.
    You should note that your use of prepare is incorrect as you are not using placeholders, but placing the values into the query via concatenation, and is therefore a waste of time anyway.
  15. Barand's post in My Old Threads was marked as the answer   
    In your profile page, click  in the top section of the page
  16. Barand's post in Days getting multiplied for each month was marked as the answer   
    I had to create my own test data (thanks for that) but naturally I don't know how it conforms with yours.
    TABLE: product TABLE: bookingItem +----+-------------+-----------+--------+ +----+-----------+---------------------+---------------------+----------+ | id | productName | category | status | | id | productid | startTime | endTime | quantity | +----+-------------+-----------+--------+ +----+-----------+---------------------+---------------------+----------+ | 1 | Room 1 | Guestroom | Active | | 1 | 1 | 2024-01-01 11:32:01 | 2024-01-02 11:32:59 | 1 | | 2 | Room 2 | Guestroom | Active | | 2 | 2 | 2024-02-01 11:34:08 | 2024-02-03 11:34:24 | 2 | | 3 | Room 3 | Guestroom | Active | | 3 | 3 | 2024-03-01 11:34:56 | 2024-03-04 11:35:08 | 3 | | 4 | Room 4 | Guestroom | NULL | | 4 | 2 | 2024-04-01 12:20:20 | 2024-04-07 12:20:41 | 6 | | 5 | Room 5 | Guestroom | NULL | | 5 | 3 | 2024-05-01 01:21:49 | 2024-05-05 12:21:58 | 4 | +----+-------------+-----------+--------+ | 6 | 5 | 2024-06-19 12:23:03 | 2024-06-29 12:23:28 | 10 | | 7 | 2 | 2024-06-01 13:02:51 | 2024-06-15 13:03:16 | 14 | +----+-----------+---------------------+---------------------+----------+ On running your code with my data I get these results for Q1 and Q2. I have written the correct totals in red. As you can see there is a distinct pattern - your totals are the correct totals squared. However, I could not spot any multiplication in the code
    (I ran as separate query to confirm the correct totals)
                        
    I have to say, in your code you really make a meal of those dates in the years and quarters considering that SQL can handle it easily. Here's my version...
    <?php ############################################### # CREATE YOUR OWN PDO DATABASE CONNECTION # # # require 'db_inc.php'; $pdo = mdbConnect('db1'); # # # # ############################################### $range = [ '2020-01-01', '2024-07-31' ]; $selectedYear = $_GET['year'] ?? 0; $whereYear = ''; if ($selectedYear) { $whereYear = 'AND YEAR(d.dt) = ?'; $range[] = $selectedYear; } $res = $pdo->prepare("WITH RECURSIVE dates(dt) AS ( SELECT ? UNION ALL SELECT dt + INTERVAL 1 MONTH FROM dates WHERE dt < ? ) SELECT YEAR(d.dt) AS yr , QUARTER(d.dt) as qtr , MONTHNAME(dt) AS mth , productName AS room , COALESCE(SUM(DATEDIFF(endTime, startTime)), '-') AS nights FROM product p CROSS JOIN dates d LEFT JOIN bookingitem b ON b.productid = p.id AND YEAR(d.dt) = YEAR(b.startTime) AND MONTH(d.dt) = MONTH(b.startTime) WHERE p.`status` = 'Active' $whereYear GROUP BY yr, qtr, MONTH(d.dt), p.id "); $res->execute($range); $results = $res->fetchAll(); $rooms = array_unique(array_column($results, 'room')); $theads = "<tr><th>Quarter</th><th>Month</th><th>" . join('</th><th>', $rooms) . "</th><th>Total</th></tr>\n"; ### RESTRUCTURE THE RESULTS ARRAY foreach ($results as $r) { $data[$r['yr']][$r['qtr']][$r['mth']][$r['room']] = $r['nights']; } ?> <!DOCTYPE html> <html lang='en'> <head> <meta charset="utf-8"> <title>Example</title> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <style type='text/css'> table { width: 100%; border-collapse: collapse; } th { background-color: #808080; color: white; padding: 8px; } td { padding: 4px 12px; text-align: right; } .ca { text-align: center; background-color: #EEE; } .la { text-align: left; background-color: #EEE; color: black; } </style> </head> <body> <header class='w3-indigo w3-padding w3-margin-bottom'> <h1>Guestroom Occupancy</h1> </header> <div class='w3-content w3-padding'> <?php ## OUTPUTFROM RESTRUCTURED ARRAY foreach ($data as $yr => $ydata) { echo "<h3>$yr</h3>\n <table border='1'> $theads "; foreach ($ydata as $qtr => $qdata) { $span = 3 + count($rooms); echo "<tr><th class='la' colspan='$span'>Quarter {$qtr}</th></tr>\n"; foreach ($qdata as $mth => $mdata) { echo "<tr><td>&nbsp;</td><td>$mth</td><td>" . join('</td><td>', $mdata) . "</td><td><b>" . array_sum($mdata) . "</b></td></tr>\n"; } } echo "</table>\n"; } ?> </div> </body> </html> Output

  17. Barand's post in hoto make prepared statement was marked as the answer   
    Not allowed. Only values can be passed as parameters, not table or column identifiers.
    Having variable table names is oftenindicative of a poor database design.
    Don't insert primary keys (Ans_id) in INSERT statements, let them be created automatically.
    $stmt = $pdo->prepare("INSERT INTO tablename (answer, answer_image) VALUES (?, ?)"); $stmt->execute( [ $answer, $image_path ] );  
  18. Barand's post in New to PHP Don't understand this syntax error. Please Help. was marked as the answer   
    When it encounters the "<p>" it is still in the php section of the code and the "<p>" isn't valid php code.
    You need to exit from php before you enter the html. Move the "?>" line ...

  19. Barand's post in Removing / editing certain keywords from array content was marked as the answer   
    One way is to find the positions of the "]" and and the "(" and grab the text between those points.
    $qa = [ "[Question - Geography Chapter2] How would you describe humans' relationship with the physical environment? (Page 42)", "[Question - Geography Chapter4] What is a natural resource? (Page 67)", "[Question - Geography Chapter3] What are two or three resources which you cannot do without? What are the reasons for your choices? (Page 52)" ]; foreach ($qa as $k => &$q) { $p1 = strpos($q, ']'); $p2 = strpos($q, '('); $q = trim(substr($q, $p1+1, $p2-$p1-1)); }  
  20. Barand's post in how to append data to end of array in php was marked as the answer   
    Not without any psychic abilities.
  21. Barand's post in adding minutes was marked as the answer   
    Example...
    DATA
    mysql> select * from worked_hours; +----+---------+---------------------+---------------------+ | id | user_id | start_time | finish_time | +----+---------+---------------------+---------------------+ | 1 | 1 | 2024-06-17 09:00:00 | 2024-06-17 17:26:00 | | 2 | 1 | 2024-06-18 09:00:00 | 2024-06-18 17:31:00 | | 3 | 1 | 2024-06-19 09:00:00 | 2024-06-19 17:12:00 | | 4 | 1 | 2024-06-20 09:00:00 | 2024-06-20 17:40:00 | | 5 | 1 | 2024-06-21 09:00:00 | 2024-06-21 17:01:00 | | 6 | 1 | 2024-06-22 09:00:00 | 2024-06-22 17:36:00 | | 7 | 1 | 2024-06-23 09:00:00 | 2024-06-23 17:47:00 | | 8 | 1 | 2024-06-24 09:00:00 | 2024-06-24 17:19:00 | | 9 | 2 | 2024-06-17 09:00:00 | 2024-06-17 17:20:00 | | 10 | 2 | 2024-06-18 09:00:00 | 2024-06-18 17:43:00 | | 11 | 2 | 2024-06-19 09:00:00 | 2024-06-19 17:45:00 | | 12 | 2 | 2024-06-20 09:00:00 | 2024-06-20 17:35:00 | | 13 | 2 | 2024-06-21 09:00:00 | 2024-06-21 17:26:00 | | 14 | 2 | 2024-06-22 09:00:00 | 2024-06-22 17:14:00 | | 15 | 2 | 2024-06-23 09:00:00 | 2024-06-23 17:55:00 | | 16 | 2 | 2024-06-24 09:00:00 | 2024-06-24 17:15:00 | | 17 | 3 | 2024-06-17 09:00:00 | 2024-06-17 17:40:00 | | 18 | 3 | 2024-06-18 09:00:00 | 2024-06-18 17:15:00 | | 19 | 3 | 2024-06-19 09:00:00 | 2024-06-19 17:35:00 | | 20 | 3 | 2024-06-20 09:00:00 | 2024-06-20 17:26:00 | | 21 | 3 | 2024-06-21 09:00:00 | 2024-06-21 17:38:00 | | 22 | 3 | 2024-06-22 09:00:00 | 2024-06-22 17:41:00 | | 23 | 3 | 2024-06-23 09:00:00 | 2024-06-23 17:00:00 | | 24 | 3 | 2024-06-24 09:00:00 | 2024-06-24 17:04:00 | +----+---------+---------------------+---------------------+ QUERY
    WITH hrs as ( SELECT user_id , DAYNAME(start_time) as day , TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30 as mins , SUM(TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30) over w1 as cum , SUM(TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30) over w1 - TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30 <= 1500 as include FROM worked_hours WINDOW w1 as (PARTITION BY user_id ORDER BY start_time) ) SELECT user_id , day , mins , cum FROM hrs WHERE include; RESULTS

  22. Barand's post in how to add strings to an associatve array was marked as the answer   
    $data['sub'] = 'math'; $data['level'] = 'valley';  
  23. Barand's post in looping through an array in php was marked as the answer   
    One way...
    <?php $data = array ( 0 => array ( 'Q_id' => '1.1.1|||1.1.2|||1.1.3|||1.1.4|||1.1.5|||1.1.6|||1.1.7|||1.1.8|||1.1.9|||1.1.10|||1.1.11|||1.1.12|||1.1.13|||1.1.14|||1.1.15|||1.1.16|||1.1.17|||1.1.18|||1.1.19|||1.1.20|||1.1.21|||1.1.22|||1.1.23|||1.1.24|||1.1.25|||1.1.26|||1.1.27|||1.1.28|||1.1.29|||1.1.30|||1.1.31|||1.1.32|||1.1.33|||1.1.34|||1.1.35|||1.1.36|||1.1.37|||1.1.38|||1.1.39|||1.1.40|||1.1.41|||1.1.42|||1.1.43|||1.1.44|||1.1.45|||1.1.46|||1.1.47|||1.1.48|||1.1.49|||1.1.50', 'QueNo' => NULL, 'qNo' => '1|||2|||3|||4|||5|||6|||7|||8|||9|||10|||11|||12|||13|||14|||15|||16|||17|||18|||19|||20|||21|||22|||23|||24|||25|||26|||27|||28|||29|||30|||31|||32|||33|||34|||35|||36|||37|||38|||39|||40|||41|||42|||43|||44|||45|||46|||47|||48|||49|||50', 'SECTION' => 'SECTION A', ), 1 => array ( 'Q_id' => '1.1.51|||1.1.51.2|||1.1.51.3|||1.1.51.4|||1.1.51.5|||1.1.51.6|||1.1.51.7|||1.1.51.8|||1.1.51.9|||1.1.51.10', 'QueNo' => 'b|||c|||d|||e|||f|||g|||h|||i|||j', 'qNo' => '51|||51|||51|||51|||51|||51|||51|||51|||51|||51','SECTION' => 'SECTION B', ), ) ; ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title>Example</title> <style type='text/css'> table { border-collapse: collapse; width: 500px; } td { padding: 8px } </style> </head> <body> <table border='1'> <?php foreach ($data as $k => $v) { foreach ($v as $k1 => $v1) { // do something (such as output) $vals = str_replace('|||', ', ', $v1); echo "<tr><td>$k</td><td>$k1</td><td>$vals</td></tr>"; } } ?> </table> </body> </html>
  24. Barand's post in XML File with tags was marked as the answer   
    Have you triedd using xpath()?
    <?php $str = '<xml><chapter num="17"> <verse num="1">And after six days Jesus taketh Peter, James, and John his brother, and bringeth them up into an high mountain apart,</verse> <verse num="2">And was transfigured before them: and his face did shine as the sun, and his raiment was white as the light.</verse> <verse num="3">And, behold, there appeared unto them Moses and Elias talking with him.</verse> <verse num="4">Then answered Peter, and said unto Jesus, Lord, it is good for us to be here: if thou wilt, let us make here three tabernacles; one for thee, and one for Moses, and one for Elias.</verse> <verse num="5">While he yet spake, behold, a bright cloud overshadowed them: and behold a voice out of the cloud, which said, This is my beloved Son, in whom I am well pleased; hear ye him.</verse> <verse num="6">And when the disciples heard <i>it,</i> they fell on their face, and were sore afraid.</verse> <verse num="7">And Jesus came and touched them, and said, <span class="j">Arise, and be not afraid. </span></verse> <verse num="8">And when they had lifted up their eyes, they saw no man, save Jesus only.</verse> <verse num="9">And as they came down from the mountain, Jesus charged them, saying, <span class="j">Tell the vision to no man, until the Son of man be risen again from the dead. </span></verse> <verse num="10">And his disciples asked him, saying, Why then say the scribes that Elias must first come?</verse> <verse num="11">And Jesus answered and said unto them, <span class="j">Elias truly shall first come, and restore all things. </span></verse> <verse num="12"><span class="j">But I say unto you, That Elias is come already, and they knew him not, but have done unto him whatsoever they listed. Likewise shall also the Son of man suffer of them. </span></verse> <verse num="13">Then the disciples understood that he spake unto them of John the Baptist.</verse> <verse num="14">And when they were come to the multitude, there came to him a <i>certain</i> man, kneeling down to him, and saying,</verse> <verse num="15">Lord, have mercy on my son: for he is lunatick, and sore vexed: for ofttimes he falleth into the fire, and oft into the water.</verse> <verse num="16">And I brought him to thy disciples, and they could not cure him.</verse> <verse num="17">Then Jesus answered and said, <span class="j">O faithless and perverse generation, how long shall I be with you? how long shall I suffer you? bring him hither to me. </span></verse> </chapter></xml> '; $xml = simplexml_load_string($str); $verses = $xml->xpath('//verse'); ?> <!DOCTYPE html> <html lang='en'> <head> <title>XML example</title> <meta charset='utf-8'> <style type='text/css'> .j { color: blue; font-style: italic; font-weight: 600; } </style> </head> <body> <?php foreach ($verses as $v) { echo "<p>{$v->asXML()}</p>"; } ?> </body> </html> Gives...

  25. Barand's post in I want to learn SQL for data analysis(I've understood company's database organization) was marked as the answer   
    There's a beginners' SQL tutorial in my forum signature. It may help.
×
×
  • 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.