Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,565

  • Joined

  • Last visited

  • Days Won

    822

 Content Type 

Everything posted by Barand

  1. Barand
    As you can see from the definition of pdoConnect() function, the optional argument is the name of the database you want to use. In this case, I put the test tables I used in a database called "josen".
  2. Barand
    Very close, though that should have generated a few error messages. Your query calling and foreach() syntax are wrong. It should be something like $results = $mysqli->query($sql); foreach ($results as $row) { then reference $row['town'], $row['email'] etc. My solution was (note I use and recommend PDO) (Included file - db_inc.php) const HOST = 'localhost'; const USERNAME = '????'; const PASSWORD = '????'; const DATABASE = 'test'); // default db function pdoConnect($dbname=DATABASE) { $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); return $db; } Code <?php require 'db_inc.php'; $db = pdoConnect('josen'); // // QUERY THE DATABASE TO GET REQUIRED DATA // $res = $db->query("SELECT t.town , t.postcode , concat(m.first_name, ' ', m.last_name) as name , m.email , m.phone , c.value as phone2 FROM hfwji_towns t LEFT JOIN hfwji_members_towns mt ON t.id = mt.town_id LEFT JOIN hfwji_swpm_members_tbl m USING (member_id) LEFT JOIN hfwji_swpm_form_builder_custom c ON m.member_id = c.user_id AND c.field_id = 33 WHERE m.first_name IS NOT NULL ORDER BY town, last_name "); // // PROCESS THE QUERY RESULTS // $prev_town = ''; $output = ''; foreach ($res as $r) { if ($r['town'] != $prev_town) { // new town value? $output .= "<h3>{$r['town']} ({$r['postcode']})</h3>\n"; $prev_town = $r['town']; // set new previous town name } $output .= <<<MEM <div class="member"> {$r['name']}<br> {$r['email']}<br> {$r['phone']}<br> {$r['phone2']} </div> MEM; } ?> <!DOCTYPE html> <html lang="en"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta name="generator" content="PhpED 19.5 (Build 19515, 64bit)"> <title>Example</title> <style type='text/css'> body { font-family: verdana, sans-serif; font-size: 10pt; } .member { width: 300px; border-bottom: 1px solid #CCC; margin: 10px 0 10px 50px; padding: 10px; } </style> </head> <body> <?=$output?> </body> </html>
  3. Barand
    The main difference that I can see is that mine gave the 2 records you were looking for and yours gave none mysql> SELECT -> pda.* -> FROM -> production_data_archive pda -> INNER JOIN( -> SELECT MAX(id) AS max_id, order_id FROM production_data_archive GROUP BY order_id -> ) maxtbl -> ON pda.id = maxtbl.order_id -> ORDER BY `pda`.`job_number` ASC; Empty set (0.00 sec) Better use dates/times to determine "latest" rather than rely on ids. The approach is broadly similar though. I think yours is matching the wrong id values in the join.
  4. Barand
    Oops! Forgot to omit towns with no members SELECT t.town , t.postcode , concat(m.first_name, ' ', m.last_name) as name , m.email , m.phone , c.value as phone2 FROM hfwji_towns t LEFT JOIN hfwji_members_towns mt ON t.id = mt.town_id LEFT JOIN hfwji_swpm_members_tbl m USING (member_id) LEFT JOIN hfwji_swpm_form_builder_custom c ON m.member_id = c.user_id AND c.field_id = 33 WHERE m.first_name IS NOT NULL -- ADD THIS LINE ORDER BY town, name;
  5. Barand
    DATA hfwji_towns; hfwji_members_towns; hfwji_swpm_form_builder_custom; +----+-----------------+----------+-----------+ +----+-----------+---------+ +----------+----------+---------+------------+ | id | town | postcode | region_id | | id | member_id | town_id | | value_id | field_id | user_id | value | +----+-----------------+----------+-----------+ +----+-----------+---------+ +----------+----------+---------+------------+ | 1 | Bedlington | NE | 2 | | 1 | 1 | 1 | | 1 | 33 | 2 | 0123456789 | | 2 | Bishop Aukland | DL | 2 | | 2 | 1 | 3 | +----------+----------+---------+------------+ | 3 | Blaydon-on-Tyne | NE | 2 | | 3 | 2 | 4 | | 4 | Barnard Castle | BA | 2 | | 4 | 3 | 1 | +----+-----------------+----------+-----------+ | 6 | 4 | 3 | +----+-----------+---------+ hfwji_swpm_members_tbl; +-----------+-----------+------------+-----------+------------------+------------+ | member_id | user_name | first_name | last_name | email | phone | +-----------+-----------+------------+-----------+------------------+------------+ | 1 | cheggs | Scott | Chegg | [email protected] | 134567890 | | 2 | norderl | Laura | Norder | [email protected] | 2345678901 | | 3 | canrit | Tom | DiCanari | [email protected] | 6543219878 | | 4 | peted | Peter | Dowt | [email protected] | 9876543210 | +-----------+-----------+------------+-----------+------------------+------------+ QUERY SELECT t.town , t.postcode , concat(m.first_name, ' ', m.last_name) as name , m.email , m.phone , c.value as phone2 FROM hfwji_towns t LEFT JOIN hfwji_members_towns mt ON t.id = mt.town_id LEFT JOIN hfwji_swpm_members_tbl m USING (member_id) LEFT JOIN hfwji_swpm_form_builder_custom c ON m.member_id = c.user_id AND c.field_id = 33 ORDER BY town, name; RESULTS +-----------------+----------+--------------+------------------+------------+------------+ | town | postcode | name | email | phone | phone2 | +-----------------+----------+--------------+------------------+------------+------------+ | Barnard Castle | BA | Laura Norder | [email protected] | 2345678901 | 0123456789 | | Bedlington | NE | Scott Chegg | [email protected] | 134567890 | NULL | | Bedlington | NE | Tom DiCanari | [email protected] | 6543219878 | NULL | | Bishop Aukland | DL | NULL | NULL | NULL | NULL | | Blaydon-on-Tyne | NE | Peter Dowt | [email protected] | 9876543210 | NULL | | Blaydon-on-Tyne | NE | Scott Chegg | [email protected] | 134567890 | NULL | +-----------------+----------+--------------+------------------+------------+------------+ To process prev_town = "" foreach results if town != prev_town echo town heading prev_town = town end if echo member data end foreach
  6. Barand
    Try TABLE: production_data_archive; +----+----------+------------+-----------+---------------------+ | id | order_id | job_number | line_item | insert_time | +----+----------+------------+-----------+---------------------+ | 1 | 16824 | 22000412 | A | 2021-03-26 00:00:00 | | 2 | 16824 | 22000412 | A | 2021-03-30 00:00:00 | | 3 | 16824 | 22000412 | A | 2021-04-09 00:00:00 | | 4 | 16825 | 22000412 | B | 2021-03-26 00:00:00 | | 5 | 16825 | 22000412 | B | 2021-03-29 00:00:00 | | 6 | 16825 | 22000412 | B | 2021-04-06 00:00:00 | +----+----------+------------+-----------+---------------------+ SELECT a.id , a.order_id , a.job_number , a.line_item , a.insert_time FROM production_data_archive a LEFT JOIN production_data_archive b ON a.job_number = b.job_number AND a.line_item = b.line_item AND a.insert_time < b.insert_time WHERE b.job_number IS NULL; +----+----------+------------+-----------+---------------------+ | id | order_id | job_number | line_item | insert_time | +----+----------+------------+-----------+---------------------+ | 3 | 16824 | 22000412 | A | 2021-04-09 00:00:00 | | 6 | 16825 | 22000412 | B | 2021-04-06 00:00:00 | +----+----------+------------+-----------+---------------------+
  7. Barand
    1 ) never run queries inside loops like that. Use joins to connect related records from tables. 2) Don't use "SELECT * , list the column names you need. Then people like me (and any one else who needs to maintain the code later) can see what data you are retrieving from each table and help you construct a better query. Post your table structures.
  8. Barand
    ... WHERE Date >= CURDATE() AND DAYOFWEEK(Date) = 5 ... https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_dayofweek
  9. Barand
    https://www.php.net/manual/en/features.file-upload.multiple.php
  10. Barand
    With the exception of mysqli_connect(), all your other db code is using the obsolete mysql_ library. I would recommend using PDO instead of mysqli.
  11. Barand
    I hope that doesn't mean that you are looping through the results of another query (eg shopping cart) and querying the prices one by one.
  12. Barand
    SELECT fruit , MIN(price) as price FROM ( SELECT fruit, price FROM fruit1 UNION ALL SELECT fruit, price FROM fruit2 ) x WHERE fruit = 'grape'; +-------+------------+ | fruit | price | +-------+------------+ | grape | 3.00 | +-------+------------+
  13. Barand
    SELECT fruit , MIN(price) FROM ( SELECT fruit, price FROM fruit1 UNION ALL SELECT fruit, price FROM fruit2 ) both_tables GROUP BY fruit; +-------+------------+ | fruit | MIN(price) | +-------+------------+ | grape | 3.00 | | lemon | 4.00 | | melon | 1.00 | +-------+------------+
  14. Barand
    The most efficient way would be a single table. Whay have you got two tables holding the same data structure?
  15. Barand
    That's what I added, but processing it now gives $value as an array and not as an object as it was originally being processed. I get the feeling a chunk has just been copied out of the original value (as there is only a single customer) and then pasted here. I suggest that @Thomas_L writes a function to process $value->users and return whatever he wants in that column, thus... '</td><td> <a href="">' . getUserData($value->users) . "</a></td></tr>"; Example function getUserData($udata) { $res = ''; foreach ($udata as $u) { $res .= "Name : {$u['firstName']} {$u['lastName']} <br>"; } return $res; }
  16. Barand
    json_last_error_msg() says there's a syntax error when decoding the above. I shall have to scrutinize it. (I think I have a can of liquid scrute somewhere)
  17. Barand
    Using attempted = [5,6] with the code I posted, I get... <ul id="list"> <li class="pagination-link" id="1">1</li> <li class="pagination-link" id="2">2</li> <li class="pagination-link" id="3">3</li> <li class="pagination-link" id="4">4</li> <li class="pagination-link is-current" id="5">5</li> <li class="pagination-link is-current" id="6">6</li> </ul>
  18. Barand
    If you just want the print-r() output (???) then '</td><td> <a href="">' . print_r($value->users, true) . "</a></td></tr>";
  19. Barand
    Also it would help more if you post the the json string $token_results instead of the print_r() output (which isn't processable by us)
  20. Barand
    If you have allQst = [1,2,3,4,5,6] attempted = [5,6] then the indexes of 5 and 6 are different in those two arrays so I used the items as the id values function func(item, index) { var li = $('<li class="pagination-link"></li>').html(item).attr('id', item); ul.append(li); } function func2(item, index) { $("#"+item).addClass("is-current"); }
  21. Barand
    For the sake of having a solution to this thread, put each in its own form. form input /form form input /form ...
  22. Barand
    Not shorter with only two links, but less repetition foreach(['F','N'] as $x) $link["be{$x}1LP"]="https://www.site.com";
  23. Barand
    As you did not define a field terminator it looks like it may have defaulted to "tab" separators. I prefer to use a php script to produce csvs and download them. MySql usually doesn't play nice when writing to files on the server. TEST FILE mysql> select * from client; +-----------+-------+----------+----------+---------------+----------+-------------+---------------+ | client_id | title | fname | lname | address | postcode | phone | email | +-----------+-------+----------+----------+---------------+----------+-------------+---------------+ | 3 | Mr | James | Bond | Thames House | W1 2QQ | 01232343456 | [email protected] | | 4 | Mrs | Margaret | Thatcher | Gotham City | GM1 3GG | NULL | | | 5 | Mr | Scott | Chegg | Coronation St | NULL | 16162554567 | | +-----------+-------+----------+----------+---------------+----------+-------------+---------------+ DOWNLOADED OUTPUT "client.csv" client_id,title,fname,lname,address,postcode,phone,email 3,Mr,James,Bond,"Thames House","W1 2QQ",01232343456,[email protected] 4,Mrs,Margaret,Thatcher,"Gotham City","GM1 3GG",, 5,Mr,Scott,Chegg,"Coronation St",,16162554567, CODE // // sample usage // $con = new mysqli(HOST,USERNAME,PASSWORD,'tutorial2'); $sql_query = "SELECT * FROM client"; sql2csv ($con, $sql_query, 'client.csv', 1); //***************************************************************************** // CSV download function //***************************************************************************** function sql2csv($con, $sql, $filename='', $headings=1) /** * Parameters * $con - connection * $sql - the sql query to be executed * $filename - name of download file (default "download_yymmddhhii.csv") * $headings - 1 if fieldname headings required (default), 0 if not required */ { if (!$filename) $f = 'download_' . date('ymdhi') . '.csv'; else $f = $filename; $fp = fopen('php://output', 'w'); // so you can fputcsv to STDOUT if ($fp) { $res = $con->query($sql); if ($res) { header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="'.$f.'"'); header('Pragma: no-cache'); header('Expires: 0'); $row = $res->fetch_assoc(); if ($headings) { fputcsv($fp, array_keys($row)); } do { fputcsv($fp, $row); } while ($row = $res->fetch_assoc()); } else echo "Error in query"; fclose($fp); } }
  24. Barand
    What symbol?
  25. Barand
    Are you going to share the fix with others who may be reading this because they have a similar error?
×
×
  • 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.