Add a transaction automatically into mysql database at a specific date

Posted on 2003-02-27
Medium Priority
Last Modified: 2008-02-07
Good day everyone!

     Does anybody can show me how to add a transaction into mysql database. Any suggestion will be appreciated!

Thank you!

Question by:pangyen
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8039749
  $date = "28.02.2003"

  if(date("d.m.Y") == $date){
     $link = mysql_connect("localhost", "user", "pass");
     if($link) die("Could not connect!");
     mysql_select_db("dbname", $link) or die("Could not select db: ".mysql_error());
    $query = "INSERT INTO table VALUES('value 1', 'value 1', 'value 3', 'valua 4');";
    print("wrong date: ".date("d.m.Y"));

this is how it works!
LVL 15

Expert Comment

ID: 8040276
better :
$result=mysql_query($query,$link) or die("error in query '$query', error is '".mysql_error()."'");

// use the link or it's useless ;-) and use error, and memorize result so that (case being) you can perform $res=mysql_fetch_array($result) and afterwards use $res["fieldname"] to retrieve results

Expert Comment

ID: 8040316
ýou don't get a result that you can fetch from insert query.. so there is no reason why you should use $result
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 15

Expert Comment

ID: 8040362
I know.
But the original request was "show me how to add a transaction into mysql database. " so I'm trying to show a most complete request.

Expert Comment

ID: 8040535
the free version of mysql do not support transactions.
MySQL is offten called a SQL Database, but in fact it it quite incomplete.
LVL 15

Expert Comment

ID: 8040909
MySql (free) DOES SUPPORT TRANSACTIONS (on InnoDB tables)
not-free MySql doesn't exist.
you pay for "pro" support, that's all
see www.mysql.com

anyway, I don't think the asker wrote about "real" transactions, but more about queries, but I may be wrong.

Anyway, MySql (FREE, always FREE) DOES support transactions : Transactions and Atomic Operations

MySQL Server supports transactions with the InnoDB and BDB Transactional table handlers. See section 7 MySQL Table Types. InnoDB provides ACID compliancy.

However, the non-transactional table types in MySQL Server such as MyISAM follow another paradigm for data integrity called ``Atomic Operations.'' Atomic operations often offer equal or even better integrity with much better performance. With MySQL Server supporting both paradigms, the user is able to decide if he needs the speed of atomic operations or if he need to use transactional features in his applications. This choice can be made on a per-table basis.

How does one use the features of MySQL Server to maintain rigorous integrity and how do these features compare with the transactional paradigm?

In the transactional paradigm, if your applications are written in a way that is dependent on the calling of ROLLBACK instead of COMMIT in critical situations, transactions are more convenient. Transactions also ensure that unfinished updates or corrupting activities are not committed to the database; the server is given the opportunity to do an automatic rollback and your database is saved. MySQL Server, in almost all cases, allows you to resolve potential problems by including simple checks before updates and by running simple scripts that check the databases for inconsistencies and automatically repair or warn if such an inconsistency occurs. Note that just by using the MySQL log or even adding one extra log, one can normally fix tables perfectly with no data integrity loss.
More often than not, fatal transactional updates can be rewritten to be atomic. Generally speaking, all integrity problems that transactions solve can be done with LOCK TABLES or atomic updates, ensuring that you never will get an automatic abort from the database, which is a common problem with transactional databases.
Even a transactional system can lose data if the server goes down. The difference between different systems lies in just how small the time-lap is where they could lose data. No system is 100% secure, only ``secure enough.'' Even Oracle, reputed to be the safest of transactional databases, is reported to sometimes lose data in such situations. To be safe with MySQL Server, whether using transactional tables or not, you only need to have backups and have the update logging turned on. With this you can recover from any situation that you could with any other transactional database. It is, of course, always good to have backups, independent of which database you use.
The transactional paradigm has its benefits and its drawbacks. Many users and application developers depend on the ease with which they can code around problems where an abort appears to be, or is necessary. However, even if you are new to the atomic operations paradigm, or more familiar with transactions, do consider the speed benefit that non-transactional tables can offer on the order of three to five times the speed of the fastest and most optimally tuned transactional tables.

In situations where integrity is of highest importance, MySQL Server offers transaction-level or better reliability and integrity even for non-transactional tables. If you lock tables with LOCK TABLES, all updates will stall until any integrity checks are made. If you only obtain a read lock (as opposed to a write lock), reads and inserts are still allowed to happen. The new inserted records will not be seen by any of the clients that have a read lock until they release their read locks. With INSERT DELAYED you can queue inserts into a local queue, until the locks are released, without having the client wait for the insert to complete. See section 6.4.4 INSERT DELAYED Syntax.

``Atomic,'' in the sense that we mean it, is nothing magical. It only means that you can be sure that while each specific update is running, no other user can interfere with it, and there will never be an automatic rollback (which can happen with transactional tables if you are not very careful). MySQL Server also guarantees that there will not be any dirty reads.


Author Comment

ID: 8041803
Good day VGR and Jonza:

    Thank you for both of your answers. I have a question about Jonza's comment. What if on "28.02.2003" I forgot to access this page to activate the query you showed me.

$query = "INSERT INTO table VALUES('value 1', 'value 1', 'value 3', 'valua 4');";

Will records still be insterted into the database? I mean does'nt this way need me to access the page contain the query to activate the query? Wish you understand what I mean.

LVL 15

Accepted Solution

VGR earned 60 total points
ID: 8044076
the PHP script will only be activated if a client's browser accesses it
It may be you (slavery) each day performing the access... on localhost or not... Via your browser
Or it can be a script (PHP in CGI-executable mode via AT commands, for example) or scheduled execution via cron (*nix) or via my RobotOuaibe (Win32), still for example. Depending on your CLIENT available platforms, choose the one most suited for you... RobotOuaibe is free and available at www.edainworks.com

in a word as in one hundred, you have to access periodically the URL via the Web.


Featured Post

Tutorial: Introduction to Managing a Linux Server

In this tutorial on systemd, we will explore:
-OS/Distro Adoption
-chkconfig and Other Legacy Commands
-Summary and Key Commands

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question