All Activity

Correct. That is an invalid mysql date. You can set a mode of mysql to accept that value, but that is not the way to handle this problem. Instead, make sure that the OTP expiry allows NULL. Then set the value to NULL. Personally, I would not design a system like this. Instead I would have a related table that only holds "events". I will usually have an user_event_type table that has different allowable authentication events. For example: user event type id | Description 1. Registration 2. Password Reset 3. One time Password 4. Login 5. Close account 6. Failed Login 7. Account locked etc. I don't know what your user table looks like but hopefully it has an ID other than "email". I'll assume you do. So you then have a user_event table with a structure like this: id | user_id | user_event_type_id | event_value | status | expire_date_time | created_at 100| 245 | 3 | xyzabc... | 0 | ..... | current_date There are a few reasons to do this. They include: - you have an audit trail of events - MySQL with InnoDB is optimized for insert queries, and they don't reduce concurrency unlike update queries. Instead of trying to overwrite the OTP, you can simply set the status from 0 to 1 (or whatever value you want). You could have several status values if you want fine grain control over the changes in status. Just to keep it simple, if the person logs in with the OTP, then it's used, so you set the status to 0. A subsequent query of "SELECT * FROM user_event WHERE user_id = 245 and user_event_type = 3 AND status = 0 ORDER BY created_at DESC LIMIT 1" will always find the most recent OTP request. You can then compare that with the OTP value. Making event_value a varchar of a specific length is no cost if the field is unused, as varchars will use the bytes required. So if you want to use event_value for additional data, you can do that, but if it's something simple like "login" event, you don't need to use it. Personally I would also have a client_ip field in a table like this, where I use a varbinary field to store the IP. This works for both IPv4 and IPv6 addresses, but there are some tricks to doing that, and it is not specifically related to your question. I mention it just to be complete.

What do you mean it works? It's not even in the list of Access SQL keywords: https://support.microsoft.com/en-us/office/sql-reserved-words-b899948b-0e1c-4b56-9622-a03f8f07cfc8 I also never mentioned single or double quotes. You can use either when you are working with string constants, but you aren't doing that. You either use [name] or the name by itself for table and columns. So table.column or [table].[column].

I don't think the "limit 3" is an issue, because this query does work: select id, name, type from Objects limit 3 I tried both single- and double-quotes and neither worked.

Microsoft access does not support Limit. It has a "TOP" keyword that does the same thing. So something like: SELECT TOP 3 * from Objects. It also does not support backtics. That is a MySQL feature. You use the square brackets, as you did in the final example. However, like MySQL's backtics you only need brackets if you have a non standard table or column name with perhaps spaces in it, or used a SQL keyword for the name. So your problem is most likely the use of LIMIT, which is not a keyword supported by Access.

Here are the mdbtools versions I have: mdbtools/noble,now 1.0.0+dfsg-1.2ubuntu1 amd64 [installed] odbc-mdbtools/noble,now 1.0.0+dfsg-1.2ubuntu1 amd64 [installed] There are 25 columns in the table, and 4 of them are defined as VARCHAR with a length of 16,777,216. If I try to include any of these columns that are VARCHAR [16,777,216] I get the segmentation fault or the out of memory error I posted above. The rest are all either Integer, SmallInt, Boolean, or VARCHAR that have much smaller lengths (150, 50, 70, etc.). However, some of the smaller VARCHAR fields also cause either the segmentation fault or the out of memory error. I'm not sure what's going on, but when I try to paste the image of the columns in it just shows a white box. I tried both copying & pasting the image in, and saving it as a local file and uploading, and the same thing happened in both cases.

I don't think backticks are the right quoting style, but brackets should have been. Or maybe not. Try single and double quotes too. Been a while but I think the LIMIT could be a problem too. Does SELECT TOP 3... instead work?

That insane number for how much memory it tried to allocate strongly indicates an internal problem. But with both pdo_odbc and the plain odbc extension? Now I'm suspecting MDBTools is the one at fault. Are you using the latest version of that? Can you identify exactly which column(s) is causing the problem? What data type is it? Are you able to run SELECT queries on other tables that have a column of the same type?

I'm using Ubuntu 24.04, PHP 8.3, and an Access database with ODBC. I have this: $user = ''; $password = ''; $conn = odbc_connect('my_db', $user, $password); echo 'conn: ';var_dump($conn); $sql = 'select id, name from Objects limit 3'; $q = odbc_prepare($conn, $sql); $success = odbc_execute($q, array()); echo 'success: ';var_dump($success); $res = odbc_exec($conn, $sql); echo 'res: ';var_dump($res); while($row = odbc_fetch_array($res)){ var_dump($row); } exit; which works perfectly. However, if I try any of the following: $sql = 'select * from Objects limit 3'; $sql = 'select objects.id, objects.name, sets.name from Objects inner join sets on sets.id = objects.setid limit 3'; $sql = 'select `objects`.`id`, `objects`.`name`, `sets`.`name` from `Objects` inner join `sets` on `sets`.`id` = `objects`.`setid` limit 3'; $sql = 'select [objects].[id], [objects].[name], [sets].[name] from [Objects] inner join [sets] on [sets].[id] = [objects].[setid] limit 3'; I get this error: Error at Line : syntax error syntax error Got no result for 'select objects.id, objects.name, sets.name from Objects inner join sets on sets.id = objects.setid limit 3' command PHP Warning: odbc_exec(): SQL error: Couldn't parse SQL , SQL state in SQLExecDirect Each attempt results in the "Couldn't parse SQL" error. I don't know how to format queries that have joins, "*", etc. for Access/ODBC/etc. to be happy. Note: I am able to run all of these queries in DBeaver and they all return correctly with no errors.

UPDATE: If I modified the query from "select *..." to "select id, name..." it worked! Do you know what would cause this? Is there any way I can get "select *" to work, or could this be a bug?

@requinix I added Pooling=No to the odbcinst.ini file in the [ODBC] section to look like this: [MDBTools] Description=MDBTools Driver Driver=libmdbodbc.so Setup=libmdbodbc.so #FileUsage=1 #UsageCount=1 [ODBC] Trace=yes TraceFile=/tmp/odbc.log Pooling=No and still got the segmentation fault. I was looking at other options and modified the code to try odbc_connect like this: $user = ''; $password = ''; $conn = odbc_connect('my_db', $user, $password); echo 'conn: ';var_dump($conn); $sql = 'select * from Objects limit 3'; $q = odbc_prepare($conn, $sql); $success = odbc_execute($q, array()); echo 'success: ';var_dump($success); $res = odbc_exec($conn, $sql); echo 'res: ';var_dump($res); while($row = odbc_fetch_array($res)){ var_dump($row); } exit; When I run that I get this: conn: resource(14) of type (odbc link) success: bool(true) res: resource(16) of type (odbc result) mmap() failed: [12] Cannot allocate memory mmap() failed: [12] Cannot allocate memory PHP Fatal error: Out of memory (allocated 4194304 bytes) (tried to allocate 2482716230683721760 bytes) in /home/raphael/code/get_mtg_data/import_mtg_data.php on line 17 Fatal error: Out of memory (allocated 4194304 bytes) (tried to allocate 2482716230683721760 bytes) in /home/raphael/code/get_mtg_data/import_mtg_data.php on line 17 Please let me know if you have any other ideas of things I could try. I don't know why it's trying to use all this memory. It seems like it's probably a setting somewhere?

We had a vote that was open to any and all active users. We get a lot of reading these days, but not as many posters as in years past. I appreciate your taking the time to post, but people that bothered to post, all voted to close it, so unfortunately your opinion comes a bit too late.

Looks good. I think you might be running into a bug related to this one, which seems like it was fixed in the v2.3.12 you're using so it's probably not that exactly. One way or another, the problem does seem to be about connection pooling. Pooling won't gain you anything on the CLI so try disabling it.

Here you go: (gdb) run myfile.php /f:"out/data__2025-05-04__22.12.12.json" Starting program: /usr/bin/php myfile.php /f:"out/data__2025-05-04__22.12.12.json" [Thread debugging using libthread_db enabled] Using host libthread_db library "/lib/x86_64-linux-gnu/libthread_db.so.1". warning: could not find '.gnu_debugaltlink' file for /lib/x86_64-linux-gnu/libtinfo.so.6 Program received signal SIGSEGV, Segmentation fault. Downloading source file /build/unixodbc-WNgXyL/unixodbc-2.3.12/cur/SQLConnect.c 0x00007ffff29ec0cd in CLDisconnect (connection_handle=0x555555dd19c0) at /build/unixodbc-WNgXyL/unixodbc-2.3.12/cur/SQLConnect.c:428 warning: 428 /build/unixodbc-WNgXyL/unixodbc-2.3.12/cur/SQLConnect.c: No such file or directory (gdb) bt #0 0x00007ffff29ec0cd in CLDisconnect (connection_handle=0x555555dd19c0) at /build/unixodbc-WNgXyL/unixodbc-2.3.12/cur/SQLConnect.c:428 #1 0x00007ffff2b9274b in close_pooled_connection (ptr=0x555555dd2f80) at /build/unixodbc-WNgXyL/unixodbc-2.3.12/DriverManager/SQLConnect.c:2916 #2 0x00007ffff2b9eb9a in __strip_from_pool (env=0x555555d3f630) at /build/unixodbc-WNgXyL/unixodbc-2.3.12/DriverManager/SQLConnect.c:3101 #3 __SQLFreeHandle (handle_type=<optimized out>, handle=0x555555d3f630) at /build/unixodbc-WNgXyL/unixodbc-2.3.12/DriverManager/SQLFreeHandle.c:283 #4 0x00007ffff2cecfd6 in ?? () from /usr/lib/php/20230831/pdo_odbc.so #5 0x00007ffff71adb80 in ?? () from /usr/lib/php/20230831/pdo.so #6 0x0000555555909a3f in zend_objects_store_del () #7 0x0000555555881a8e in zend_hash_reverse_apply () #8 0x000055555585b5ed in ?? () #9 0x000055555586cdbd in zend_call_destructors () #10 0x00005555557fdaed in php_request_shutdown () #11 0x000055555595f249 in ?? () #12 0x0000555555684791 in ?? () #13 0x00007ffff722a1ca in __libc_start_call_main (main=main@entry=0x555555684440, argc=argc@entry=3, argv=argv@entry=0x7fffffffda08) at ../sysdeps/nptl/libc_start_call_main.h:58 #14 0x00007ffff722a28b in __libc_start_main_impl (main=0x555555684440, argc=3, argv=0x7fffffffda08, init=<optimized out>, fini=<optimized out>, rtld_fini=<optimized out>, stack_end=0x7fffffffd9f8) at ../csu/libc-start.c:360 #15 0x0000555555685915 in _start () (gdb) Please let me know if this looks good.

Almost there. Do that, then when you get the gdb prompt enter "bt" and it will dump a bunch of numbered lines. So far, though, it looks like the problem is in unixODBC, which is surprising as you're already on the current version - that said, it's been a couple years so I'm not sure it's still maintained...

@requinix I have never used gdb before, but I just went through a tutorial on how to use it with php and here is the output. Please let me know if this is what you need: (gdb) run myfile.php /f:"out/data__2025-05-04__22.12.12.json" Starting program: /usr/bin/php myfile.php /f:"out/data__2025-05-04__22.12.12.json" [Thread debugging using libthread_db enabled] Using host libthread_db library "/lib/x86_64-linux-gnu/libthread_db.so.1". warning: could not find '.gnu_debugaltlink' file for /lib/x86_64-linux-gnu/libtinfo.so.6 Program received signal SIGSEGV, Segmentation fault. 0x00007ffff29ec0cd in CLDisconnect (connection_handle=0x555555dd1970) at /build/unixodbc-WNgXyL/unixodbc-2.3.12/cur/SQLConnect.c:428 warning: 428 /build/unixodbc-WNgXyL/unixodbc-2.3.12/cur/SQLConnect.c: No such file or directory @Random8 I tried using the full file path and got the same error.

First of all, I think you should follow the above recommendations about using a library like Symfony Mailer, PHPMailer or better using a service as Sendgrid or Resend. Regarding your concern, may I ask which version of PHP you're using? Since PHP 7.2, the mail() function accepts headers as an array—before that, it only accepted strings. Looking at example #5 in the documentation, you can see that they convert the array of headers into a string using the implode() function. Something like this: $headers = array( 'MIME-Version' => '1.0', 'Content-type' => 'text/html;charset=UTF-8', 'From' => '[email protected]', 'Reply-To' => '[email protected]' ); $sent = mail($to, $subject, $message, implode("\r\n", $headers));

First of all, I think you should follow the above recommendations about using prepared statements. It's very risky and bad practice to use plain values directly in a query. Regarding your concern, is there a specific reason for using 0000-00-00 00:00:00? This is an invalid date, and if you're using MySQL, the database might reject this value. I suggest setting the otp_expiry value to NULL—I think it's better for validation. Just make sure the database table schema allows null values.

Would you not need to use the full file path for $access_file?

Yeah, it's called "the internet". There's a whole bunch of it out there.

There must be open option to express someone's services/support offer at some place where user can find it easily. Discussions are the best approach to discuss such things. I am not in favor of closing such threads/category where vendor or user can discuss their issues.

Can you post a backtrace from gdb?

I have Ubuntu 24.04 and php 8.3. I'm trying to connect to an Access DB through PDO ODBC running on the command line. Here is what I have enabled: $ php -i | grep PDO PDO PDO support => enabled PDO drivers => mysql, odbc PDO Driver for MySQL => enabled PDO_ODBC PDO Driver for ODBC (unixODBC) => enabled code: <?php $access_file = './myfile.mdb'; $db=new PDO('odbc:Driver=MDBTools; DBQ=' . $access_file . ';'); ?> error: Segmentation fault (core dumped) That's all I get, no other information. I am able to connect to and run queries on this mdb file with DBeaver, so the file should be ok. I haven't been able to find what would cause a Segmentation fault just by creating the PDO connection. I tried mdbtools. If I run the mdb-ver it returns "JET4". If I run mdb-sql I can run queries and they return correctly. I also tried "describe table" and it worked correctly, too. So it looks like everything is working correctly with the mdbtools. I found some old .mdb files that I created years ago for an Access class. I was able to load and run queries on them in DBeaver and work with them using mdb-tools, but again I get the Segmentation fault with them when trying to open them with PHP. It seems to be something specific to PHP.

Yes, tcpdf does a great job and I've used it for really elaborate invoice documents and business check printing with barcodes. I didn't suggest it initially, because you indicated you wanted to try to come up with an html solution that would then be converted to pdf. I should point out the the developer of tcpdf has locked the project and has replaced it with a rewritten version with current PHP practices like namespacing. That project can be found here: https://github.com/tecnickcom/tc-lib-pdf

In the end I just went with tcpdf was quite simple and very fast to generate. PDF generation is much faster then my current PSA tool. Filesize is also small on the pdfs generated.

I'd take the easy way out and use the database with a single query DATA TABLE : season; TABLE : price +-----------+----------------+------------+------------+--------+ +----+--------+------+-------+ | season_id | name | start_date | end_date | tariff | | id | tariff | day | price | +-----------+----------------+------------+------------+--------+ +----+--------+------+-------+ | 1 | Winter 2024 | 2024-11-01 | 2024-12-23 | LO | | 1 | ST | 0 | 70 | day 0 = Monday | 2 | Christmas 2024 | 2024-12-24 | 2025-01-02 | PK | | 2 | ST | 1 | 70 | | 3 | Easter 2025 | 2025-04-11 | 2025-04-27 | PK | | 3 | ST | 2 | 70 | | 4 | Summer 2025 | 2025-06-15 | 2025-08-31 | HI | | 4 | ST | 3 | 70 | +-----------+----------------+------------+------------+--------+ | 5 | ST | 4 | 90 | | 6 | ST | 5 | 90 | | 7 | ST | 6 | 70 | day 6 = Sunday TABLE : tariff | 8 | LO | 0 | 55 | +--------+-------------+ | 9 | LO | 1 | 55 | | tariff | description | | 10 | LO | 2 | 55 | +--------+-------------+ | 11 | LO | 3 | 55 | | HI | High season | | 12 | LO | 4 | 75 | | LO | Low season | | 13 | LO | 5 | 75 | | PK | Peak season | | 14 | LO | 6 | 55 | | ST | Standard | | 15 | HI | 0 | 90 | +--------+-------------+ | 16 | HI | 1 | 90 | | 17 | HI | 2 | 90 | TABLE : booking | 18 | HI | 3 | 90 | +----+------------+---------+------------+ | 19 | HI | 4 | 110 | | id | booking_no | room_no | book_date | | 20 | HI | 5 | 110 | +----+------------+---------+------------+ | 21 | HI | 6 | 90 | | 1 | 1 | 1 | 2025-05-07 | | 22 | PK | 0 | 110 | | 2 | 1 | 1 | 2025-05-08 | | 23 | PK | 1 | 110 | | 3 | 1 | 1 | 2025-05-09 | | 24 | PK | 2 | 110 | | 4 | 2 | 5 | 2025-04-25 | | 25 | PK | 3 | 110 | | 5 | 2 | 5 | 2025-04-26 | | 26 | PK | 4 | 125 | | 6 | 2 | 5 | 2025-04-27 | | 27 | PK | 5 | 125 | | 7 | 2 | 5 | 2025-04-28 | | 28 | PK | 6 | 110 | | 8 | 2 | 5 | 2025-04-29 | +----+--------+------+-------+ | 9 | 2 | 5 | 2025-04-30 | +----+------------+---------+------------+ QUERY SELECT b.booking_no , MIN(b.book_date) as `from` , MAX(b.book_date) as until , SUM(p.price) as total FROM booking b LEFT JOIN season s ON b.book_date between s.start_date AND s.end_date LEFT JOIN price p ON coalesce(s.tariff, 'ST') = p.tariff -- tariff defaults to "ST" if no seasons match date AND p.day = weekday(b.book_date) GROUP BY b.booking_no; +------------+------------+------------+-------+ | booking_no | from | until | total | +------------+------------+------------+-------+ | 1 | 2025-05-07 | 2025-05-09 | 230 | | 2 | 2025-04-25 | 2025-04-30 | 570 | +------------+------------+------------+-------+