Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,584

  • Joined

  • Last visited

  • Days Won

    826

 Content Type 

Posts posted by Barand

    to loop or not to loop?

    in MySQL Help

    Posted

    I fell foul of that this morning. It worked fine yesterday when I posted the query but when I loaded some more data and ran it again after midnight I got nothing. The last date I had was 2025-01-26 so -7 DAY worked fine on the 2nd Feb but not on the 3rd.

    On the subject of temporary tables, have a look at WITH RECURSIVE. There may be occasions when you want to list, say, total sales for each day last month and show a zero total for days with no sales. You can't just sum, grouping by the dates, as you won't get output if there is no data fora  date. You need a table containing all the dates in the month and use a left join. WITH RECURSIVE gives a great way of create such a temporary date table on the fly.

    • Great Answer 1

    to loop or not to loop?

    in MySQL Help

    Posted

    1 hour ago, jodunno said:

    I thought that i should be loading the forums according to the board structure, so i implemented your sql concept to arrive with the following query

    You don't need a separate table to specify the structure, your board table already contains the structure by virtue of the parentid column. Just needs a recursive function to resolve... 

    include 'db_inc.php' ; 
$pdo = mdbConnect('jodunno');         // connect to database

$res = $pdo->query("SELECT parentID, boardID, title
                    FROM wbb1_1_board
                    ORDER BY parentID, title");
$brds = [];
foreach ($res as $r)  {
    $brds[$r['parentID']][] = [ 'id' => $r['boardID'], 'title' => $r['title']];
}
// echo '<pre>' . print_r($brds, 1) . '</pre>';

echo "<ul>\n";
outputBoard($brds, 0);
echo "</ul>\n";

function outputBoard(&$brds, $parent)
{
    foreach ($brds[$parent] as $b)  {
        echo "<li>{$b['title']}</li>\n";
        if (isset($brds[$b['id']])) {                // if this is a parent board
            echo "<ul>\n";                           
            outputBoard($brds, $b['id']);            // recursively output the child boards
            echo "</ul>\n";
        }
    }
}

    image.thumb.png.66fb04909751e987c4cedc326da05dff.png

    • Like 1

    to loop or not to loop?

    in MySQL Help

    Posted

    After about 3 hours of waiting I killed the process as the SQL server never showed a status other than "idle". I think it hit a problem and got itself into an infinite loop doing nothing.

    To give myself some data to work with, I managed to extact the table structure , the first 3,400 and the last 600 records from the sql file. I could have done all of them but it (thankfully) uses multiple row inserts (1700 at a time) and it takes an age scrolling through the text to find  each block's start and end then select the block. There are about 200 such blocks and each takes about 2.5 seconds to load the data - so the whole load should have taken 8-9 minutes.

    Enough of the excuses. I finally came up with a solution using the post table.

    • The first part (WITH ...) creates a temporary table called "plast" which contains a row for each threadID with the latest date of all the posts for the thread.
    • The main part of the query (SELECT ...) matches the threadid/latest date with the post table to find the matching post and also joins to the thread table to pick uo thread info.
    • Finally, I limit the output to just those dates in the last 7 days.

    (Apologies for screwing up the text encoding along the way - eg "Jürgen Peters". It's only test data.) 

    WITH plast AS (
    SELECT threadID
         , MAX(time) as latest
    FROM wbb1_1_post
    GROUP BY threadID
   )
SELECT t.threadID
     , t.topic
     , p.userID
     , p.username
     , p.postid
     , FROM_UNIXTIME(p.time) AS time
     , FROM_UNIXTIME(plast.latest) AS latest
FROM wbb1_1_post p
    JOIN wbb1_1_thread t ON p.threadID = t.threadID
    JOIN plast ON plast.threadid = p.threadid
               AND plast.latest = p.time
WHERE FROM_UNIXTIME(p.time) > CURDATE() - INTERVAL 7 DAY
;

+----------+----------------------------------------------------------------+--------+------------------+--------+---------------------+---------------------+
| threadID | topic                                                          | userID | username         | postid | time                | latest              |
+----------+----------------------------------------------------------------+--------+------------------+--------+---------------------+---------------------+
|   131549 | welche Spinne? --> eventuell  Lepthyphantes sp.                |   5455 | Manfred Zapf     | 507256 | 2025-01-26 10:08:19 | 2025-01-26 10:08:19 |
|   131698 | Baumwanze                                                      |   1397 | zobel            | 507259 | 2025-01-26 12:48:43 | 2025-01-26 12:48:43 |
|    56659 | Grüne Futterwanze?                                             |  15196 | Christine        | 507261 | 2025-01-26 14:57:09 | 2025-01-26 14:57:09 |
|   131576 | Kleine schwarze Spinne --> Enoplognatha cf. thoracica          |  15395 | Bernd 07         | 507263 | 2025-01-26 16:01:45 | 2025-01-26 16:01:45 |
|   131307 | Amaurobius fenestralis? --> bestätigt                          |  15395 | Bernd 07         | 507264 | 2025-01-26 16:08:09 | 2025-01-26 16:08:09 |
|   131701 | Unbekannte Schneckenart                                        |  15395 | Bernd 07         | 507267 | 2025-01-26 16:43:50 | 2025-01-26 16:43:50 |
|   131702 | Encyrtidae?                                                    |  11406 | JohnEs81         | 507268 | 2025-01-26 17:00:37 | 2025-01-26 17:00:37 |
|   131700 | Welche Wanze ist das? --> Arocatus longiceps                   |  15395 | Bernd 07         | 507272 | 2025-01-26 17:36:02 | 2025-01-26 17:36:02 |
|   131699 | Tegenaria --> nein sondern Amaurobius similis/fenestralis      |   1999 | Klaus Fritz      | 507274 | 2025-01-26 17:48:07 | 2025-01-26 17:48:07 |
|   131683 | Grüne Larve -> Geometridae Art                                 |  11406 | JohnEs81         | 507280 | 2025-01-26 20:41:49 | 2025-01-26 20:41:49 |
|   131703 | eine Acericerus heydenii?                                      |      1 | Jürgen Peters   | 507282 | 2025-01-26 20:45:32 | 2025-01-26 20:45:32 |
|   131687 | Zygina nivea?                                                  |  15392 | Sascha_N         | 507286 | 2025-01-26 21:15:21 | 2025-01-26 21:15:21 |
|   131686 | Welcher Schnellkäfer? --> Melanotus sp.                        |  15395 | Bernd 07         | 507287 | 2025-01-26 21:29:11 | 2025-01-26 21:29:11 |
|   131693 | Lispocephala brachialis --> bestätigt                          |  15800 | Bernd Cogel      | 507293 | 2025-01-26 22:06:16 | 2025-01-26 22:06:16 |
|   131704 | Cantharis paradoxa? --> Cantharis sp., ein schwarzer, immerhin |  15335 | Simeon Indzhov   | 507295 | 2025-01-26 22:29:15 | 2025-01-26 22:29:15 |
|   131695 | Peyerimhoffina gracilis?                                       |  15335 | Simeon Indzhov   | 507296 | 2025-01-26 22:36:08 | 2025-01-26 22:36:08 |
|   131705 | Phytoecia coerulescens?                                        |      1 | Jürgen Peters   | 507297 | 2025-01-26 22:52:48 | 2025-01-26 22:52:48 |
+----------+----------------------------------------------------------------+--------+------------------+--------+---------------------+---------------------+

    Are you planning on rebuilding the database?

    • Like 1
    • Great Answer 1

    to loop or not to loop?

    in MySQL Help

    Posted

    1 hour ago, jodunno said:

    surely this db could be designed better, yes?

    Yes!

    • If you have each post with its time_posted then you can alway s find the times of first and last posts without storing those dates. Basically, do not store derived data like those dates or totals.
    • Also the thread table contains usierID and username - username belongs only in the user table, not repeated in every thread record for that user.
    • The dats/times are stored as unix timestamps. Horrible and completely unreadable. Use the inbuilt date/time/datetime/timestamp type columns.
    • Like 1

    to loop or not to loop?

    in MySQL Help

    Posted

    From what I have been given, I came up with this ... 

    SELECT b.boardID
     , b.title
     , t.threadID
     , t.topic
     , t.userID
     , t.username
     , FROM_UNIXTIME(t.time) AS time
     , FROM_UNIXTIME(t.lastPostTime) AS lastPostTime

FROM wbb1_1_board b
     JOIN wbb1_1_thread t 
          USING (boardid)
     JOIN wbb1_1_board_last_post l
          USING (boardid, threadid)

ORDER BY boardID, threadid, time;

    Hovever, it seems to me that there should be a "post" table containing the post details, including the time_posted. Then you could just extract the posts containing the latest timestamp for each thread.

    • Great Answer 1

    Array key question

    in PHP Coding Help

    Posted

    The ability to use strings as array keys (associative keys) has always been a feature of PHP.

    The drawbacks are the same as when using string columns as keys in DB tables...

    • they tend to be longer and therefore less efficient
    • great care has to taken to ensure consistency of case, puctuation and spelling

    SQL join and sum

    in MySQL Help

    Posted

    The last 2 lines of your query are ...

    On 1/9/2025 at 2:34 PM, radarman2000 said:

     INNER JOIN form_filters
                    ON element_options.option_id = form_filters.filter_keyword

    where you are joining options and filters tables.

    Your option_id column contains values between 1 and 12. 

    select * from ap_element_options LIMIT 15;
+--------+---------+------------+-----------+----------+---------------+-------------------+------------------+------+
| aeo_id | form_id | element_id | option_id | position | option        | option_is_default | option_is_hidden | live |
+--------+---------+------------+-----------+----------+---------------+-------------------+------------------+------+
|    431 |   10556 |          1 |         4 |        1 | MCSO          |                 0 |                0 |    1 |
|    432 |   10556 |          1 |         5 |        2 | CCSO          |                 0 |                0 |    1 |
|    433 |   10556 |          1 |         6 |        3 | LCSO          |                 0 |                0 |    1 |
|    434 |   10556 |          1 |         7 |        4 | DPD           |                 0 |                0 |    1 |
|    435 |   10556 |          1 |         8 |        5 | FPHM          |                 0 |                0 |    1 |
|    436 |   10556 |          1 |         9 |        6 | FHPC          |                 0 |                0 |    1 |
|    437 |   10556 |          1 |        10 |        7 | FHPL          |                 0 |                0 |    1 |
|    438 |   10556 |          1 |        11 |        8 | AUTO CLUB     |                 0 |                0 |    1 |
|    439 |   10556 |          1 |        12 |        9 | OWNER         |                 0 |                0 |    1 |
|    446 |   10556 |          2 |         4 |        1 | F550          |                 0 |                0 |    1 |
|    447 |   10556 |          2 |         5 |        2 | F350          |                 0 |                0 |    1 |
|    448 |   10556 |          2 |         6 |        3 | International |                 0 |                0 |    1 |
|    449 |   10556 |          2 |         7 |        4 | Ranger        |                 0 |                0 |    1 |
|    453 |   10556 |          4 |         1 |        1 | Credit Card   |                 0 |                0 |    1 |
|    454 |   10556 |          4 |         2 |        2 | Cash          |                 0 |                0 |    1 |
+--------+---------+------------+-----------+----------+---------------+-------------------+------------------+------+

    Which of these values are you expecting to match the filter_keyword Tow ? 

    select * from ap_form_filters;
+--------+---------+---------+--------------------+--------------+------------------+----------------+
| aff_id | form_id | user_id | incomplete_entries | element_name | filter_condition | filter_keyword |
+--------+---------+---------+--------------------+--------------+------------------+----------------+
|    312 |   10556 |       3 |                  0 | element_6    | is               | Tow            |
+--------+---------+---------+--------------------+--------------+------------------+----------------+

    I suggest you rethink your design and come back when you have something that could work.

    • Haha 1

    Inserting rows without adding duplicates...

    in MySQL Help

    Posted

    Earlier in this thread you said that this ... 

    +----+-----------+-----------+
| id | slug      | tag_check |
+----+-----------+-----------+
|  1 | hugh-jass |      1    |
|  2 | hugh-jass |      2    |
+----+-----------+-----------+

    ... was acceptable. Your "solution" would not permit this.

    Secondly, you are using a "dependent subquery" which means for every record inserted you must query all the existing lookig for a duplicate. This can be extremely slow and should be avoided when writing queries.

    Changing your unique key to ... 

    UNIQUE INDEX `unq_name` (`name`) USING BTREE,

    ... and using this query would have same result ... 

    INSERT IGNORE into wp_terms (name,slug)
SELECT concat(nameFirst,' ',nameLast)
     , lower(concat(nameFirst,'-',nameLast))
FROM a_players
;

     

    • Great Answer 1

    Inserting rows without adding duplicates...

    in MySQL Help

    Posted

    Is it necessary to include the tag_check in the unique key?

    Would this be acceptable... 

    +----+-----------+-----------+
| id | slug      | tag_check |
+----+-----------+-----------+
|  1 | hugh-jass |      1    |
|  2 | hugh-jass |      2    |
+----+-----------+-----------+

    or can there be only one "hugh-jass" in the table?

    Inserting rows without adding duplicates...

    in MySQL Help

    Posted

    Your UNIQUE key is the combination of (slug, tag_check). NULL values are ignored and your insert query always writes NULL to tag_check column. 

    CREATE TABLE `dupe_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `slug` varchar(50) NOT NULL DEFAULT '',
  `tag_check` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `player_tag_check` (`slug`,`tag_check`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

      

    select * from dupe_test;

+----+-----------+-----------+
| id | slug      | tag_check |
+----+-----------+-----------+
|  1 | hugh-jass |      NULL |
|  2 | hugh-jass |      NULL |
|  3 | hugh-jass |      NULL |
|  4 | hugh-jass |         1 |
+----+-----------+-----------+

    Add another... 

    MariaDB [test]> insert into dupe_test (slug) values ('hugh-jass');
Query OK, 1 row affected (0.051 sec)

MariaDB [test]> select * from dupe_test;
+----+-----------+-----------+
| id | slug      | tag_check |
+----+-----------+-----------+
|  1 | hugh-jass |      NULL |
|  2 | hugh-jass |      NULL |
|  3 | hugh-jass |      NULL |
|  6 | hugh-jass |      NULL |  << NEW
|  4 | hugh-jass |         1 |
+----+-----------+-----------+
5 rows in set (0.000 sec)

    Now try adding again but with tag_check = 1 then 2... 

    MariaDB [test]> insert into dupe_test (slug, tag_check) values ('hugh-jass', 1);
ERROR 1062 (23000): Duplicate entry 'hugh-jass-1' for key 'player_tag_check'

MariaDB [test]> insert into dupe_test (slug, tag_check) values ('hugh-jass', 2);
Query OK, 1 row affected (0.073 sec)

MariaDB [test]> select * from dupe_test;
+----+-----------+-----------+
| id | slug      | tag_check |
+----+-----------+-----------+
|  1 | hugh-jass |      NULL |
|  2 | hugh-jass |      NULL |
|  3 | hugh-jass |      NULL |
|  6 | hugh-jass |      NULL |
|  4 | hugh-jass |         1 |
|  8 | hugh-jass |         2 |
+----+-----------+-----------+
6 rows in set (0.000 sec)

    So either

    • insert a tag_check value too, or
    • exclude tag_check from the UNIQUE key
    • Great Answer 1

    SQL join and sum

    in MySQL Help

    Posted

    3 hours ago, radarman2000 said:

    Making any sense?

    Not a lot. "It doesn't work" tells us nothing. What is ir doing that it shouldn't or what is it not doing that it should?

    • If element_5 contains a price, why not call it "price". Same goes for element_6 (option_id).
    • I can't see what do_query() is doing but as you're passing an array of parameters I assume you are trying to use a prepared statement - but you have no placeholders in the query string for those parameters.
    • If $filter_keyword contains a column name that could be the cause - you can only pass values as parameter.
    • If any of your joins match more than 1 row in any of the tables, the resultant sum would be multiplied by the number of rows.

    If you need more help, a dump of the structures and data for those tables would enable me to recreate the problem at my end a get you a working query.

    extracting info from database using php

    in PHP Coding Help

    Posted

    You need to unserialise the data. 

    $dbdata = 'a:5:{s:16:"WPFormsDB_status";s:6:"unread";s:4:"Name";s:13:"Lional Hewitt";s:14:"Contact Number";s:10:"0763229844";s:5:"Email";s:22:"[email protected]";s:18:"Comment or Message";s:5:"test2";}';
$data = unserialize($dbdata);

    echo "Name : {$data['Name']}<br>";
    echo "Contact : {$data['Contact Number']}<br>";
    // etc

    Alternatively 

    $dbdata = 'a:5:{s:16:"WPFormsDB_status";s:6:"unread";s:4:"Name";s:13:"Lional Hewitt";s:14:"Contact Number";s:10:"0763229844";s:5:"Email";s:22:"[email protected]";s:18:"Comment or Message";s:5:"test2";}';
$data = unserialize($dbdata);

foreach ($data as $key => $value)   {
    echo "<b>$key</b> : $value<br>";
}
×
×
  • 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.