[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

bash curl php multiple tables entry

I asked this before but all I received were links to documentation which didn't help me. This question is asking for a solution, not documentation please.

I need to send data to two tables at the same time but I am not sure how to accomplish this.

On the sender side, I have;
start_time="$END_TIME"

$CURL -F function=out1 -F start_time="$START_TIME"
$CURL -F function=out2 -F start_time="$START_TIME"

Open in new window


On the php side, I have;

elseif ($_POST['function'] === 'out1') {
mysql_query('INSERT INTO out1 ( time, client_id ) VALUES ("' . $_POST['time'] . '","' . $clientid . '")' )
                or die('Query failed: ' . mysql_error());
                        }
elseif ($_POST['function'] === 'out2') {
mysql_query('INSERT INTO out2 ( time, client_id ) VALUES ("' . $_POST['time'] . '","' . $clientid . '")' )
                or die('Query failed: ' . mysql_error());
                        }

Open in new window


I need to insert the time into two tables at the same time but so far I have yet to find a way so am asking here.
0
projects
Asked:
projects
  • 14
  • 10
  • 3
  • +2
2 Solutions
 
nemws1Commented:
Can I ask for more details?  Why do you want them inserted at the same time?

The problem is that the web is stateless - it does not, normally, keep any data from connection to connection.  We use sessions ore similar functionality to emulate state, but in reality, the web is still stateless.  The reason I make this point is what happens if your first 'curl' command executes but then for some reason the second one does not?  What state do you want your database in?

Basically, it is impossible to do it "at the same time".  Just like a bathroom stall only allows one person to use it at a time (hope that doesn't gross you out, one of my profs in college always used that as an example).  A computer can only do *one* thing at a time.  It can do two things really, really, really closely together, but not at the same time (sorry if I'm being too literal).

Since you're sending the same value in both 'curl' commands, why not just use one 'curl' command and two INSERTs?

elseif ($_POST['function'] === 'out1') {
        mysql_query('INSERT INTO out1 ( time, client_id ) VALUES ("' . $_POST['time'] . '","' . $clientid . '")' )
                or die('Query failed: ' . mysql_error());
        mysql_query('INSERT INTO out2 ( time, client_id ) VALUES ("' . $_POST['time'] . '","' . $clientid . '")' )
                or die('Query failed: ' . mysql_error());
}

Open in new window


The usual solutions like to write it on the client side to use background jobs or threading or to use locked tables server side both have problems and will never be "at the same time".
0
 
GaryCommented:
As @nemws1 has said - this cannot happen.

Once MySQL receives a command INSERT,UPDATE etc it will lock the table while the sql finishes for MyISAM (InnoDB is slightly different)

Research table locking
0
 
Ray PaseurCommented:
I'm kind of mystified, too.  Why would you put the same data into two tables?  Which is the canonical table?  This violates the generally accepted DRY principle, "Don't Repeat Yourself."
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
projectsAuthor Commented:
Ok, so stateless and locked tables mean two different requests. I've tried that also by creating two functions on the php side and sending to each as individual curl commands but I cannot to get this to work either.

I need the same time stamp in two tables so that we can look data up based on time stamps for correlation.

Let me ask another way then. I can create two php functions but I need to send the same time stamp from the one function in the sending script.

Stacking curl commands doesn't work so how would I send two separate curl commands from one function in bash?
0
 
GaryCommented:
Create a variable to hold the current timestamp in your PHP page and use that in your SQL statements - you will obviously need to amend your sql to update the timestamp column

Simple!
0
 
projectsAuthor Commented:
That's why I'm posting here, because it's not been that simple. In my original post, I should what I'm doing at this point and that's not working.
0
 
GaryCommented:
Well it's not really obvious what the two queries are that you want to run at the same time but

<?php

$timestamp = date('Y/m/d h:i:s', time());

elseif ($_POST['function'] === 'out1') {
mysql_query('INSERT INTO out1 ( time, client_id, timestamp_field ) VALUES ("' . $_POST['time'] . '","' . $clientid . '"'.$timestamp.')' )
                or die('Query failed: ' . mysql_error());
                        }
elseif ($_POST['function'] === 'out2') {
mysql_query('INSERT INTO out2 ( time, client_id, timestamp_field ) VALUES ("' . $_POST['time'] . '","' . $clientid . '"'.$timestamp.')' )
                or die('Query failed: ' . mysql_error());
                        }


Just confirm the field type of your timestamp field
0
 
nemws1Commented:
Agree with Gary.  If you have a timestamp field and you do *not* specify that field in your insert, MySQL automatically inserts a time that will likely be different from a different INSERT.  However, you can specify the timestamp field in your INSERT along with a value and MySQL will respect that value and *not* automatically insert the current time.

A similar trick, if you want to UPDATE a field and don't want the timestamp field updated, you set the field to itself:

UPDATE table
    SET somefield = 'value'
    , timestamp_field = timestamp_field
WHERE somefield = 'blah';

I know that's unrelated, but I want to make the point that timestamps are only inserted/updated to the current time if you don't specify them.  If you *do* specify them, MySQL will respect the value you are providing.
0
 
projectsAuthor Commented:
The time stamp is coming from the sender as a variable.
0
 
GaryCommented:
Something is not quite adding up here, rereading the question and the code...

Your two curl commands from what I can tell are sending the same timestamp
start_time="$END_TIME"

$CURL -F function=out1 -F start_time="$START_TIME"
$CURL -F function=out2 -F start_time="$START_TIME"

Open in new window

Your two sql statements for both conditions (out1 and out2) should be inserting the rows with the same time but you are requesting in the sql $_POST['time'] but the value you are sending is start_time.

Should $_POST['time'] not be $_POST['start_time']?
0
 
projectsAuthor Commented:
Yes, but to two different functions in php, or so, that's what I'm trying to achieve.

The previous programmer or someone on here would have given me that code. Yes, I want to insert the exact same time in both tables, not the mysql time.
0
 
GaryCommented:
Well that should be happening but the posted value has the name 'start_time' and in your sql you are requesting 'time' - ergo the value you are using in your sql is blank

Change $_POST['time'] to $_POST['start_time']
0
 
Ray PaseurCommented:
Still not understanding the advantage you seek by putting the same data into two different tables.  Once you have it in one table, it is the canonical representation of the data and your application doesn't gain any value if you duplicate the data.

You might consider INSERT into only one table, then copying the data into the other table.

You might consider a transaction.  You can COMMIT or ROLLBACK depending on the success of the UPDATE.  

I think this application might be worth the time and money to consult a professional data base administrator.
0
 
projectsAuthor Commented:
The reason is that we need a marker in one table to denote when an event occurred in another table. According to the programmer, it can't be done any other way.
0
 
projectsAuthor Commented:
So let me understand something please.

Are you all saying that putting the SAME variable in two places is wrong or that this would not be a problem if I change the variable names?
For example, I need the start_time stamp in both databases so that we can know when certain events happened.

@Gary; Yes, I made a typo in what I pasted, $TIME and $START_TIME
Both tables need the $START_TIME of the event.

It has something to do with being able to click on one report to get a second report to display in that position.
0
 
Ray PaseurCommented:
Are you all saying that putting the SAME variable in two places is wrong...
Generally, that would be a code smell that indicates a design flaw.  If the data is the same in both places, there is no need to put the data in more than one place.  And if the data is expected to be the same but somehow becomes different, which is correct?  In other words, duplicate data cannot help you and can only hurt you, so we just don't do that.  This is the mariner's dilemma that is solved by the axiom, "Never go to sea with two chronometers."

need the start_time stamp in both databases
Whoa!  Earlier you said "two tables" and this is very different from two databases.  Please tell us which is correct, thanks.

How experienced is the programmer who has advised you to design the application this way?  Perhaps (s)he can shed some light on this design choice.  It's a very unusual approach to what seems like a very common design question.
0
 
projectsAuthor Commented:
Yes, I meant two tables, sorry.

Two tables hold information that we are interested in when there is an event.

The programmer says that we need a marker in one table in order to find events which have been recorded in another table.

This is in order to correlate the events, so that we can more easily find when something happened, trying to look up data in two tables.
0
 
GaryCommented:
Still doesn't explain the same information in two tables also it would be better to do the two inserts at the same time and grab the auto id in the first insert and use it in the second insert as the pointer
0
 
projectsAuthor Commented:
I'm not sure how else to explain it. He wants a pointer in one table in order to refer to it when there is an event. He needs to pull data from both tables but basically needs to know from when/where so he wants a pointer (which I called a marker)
0
 
GaryCommented:
And you are using start_time as that marker which from your original question will be the same in both tables
0
 
projectsAuthor Commented:
Yes, that was the plan
0
 
GaryCommented:
So why is it not working? Or rather what is not working?
Your code should be inserting the same time in both tables.
0
 
projectsAuthor Commented:
I don't know why, that was why I posted this question to start with. I believe it is because I am not using the proper syntax on both the sending script and the receiving php.

On the php side, I don't know if the second entry should be it's own function or not either.
To start testing, I simply sent two curl commands one stacked over the other.
On the php side, I just created a new function which the curl command would reference.
I wanted to save not having to do two curl sends which is another part I am wanting to resolve.

Practically every time I receive solutions on this site, I can usually re-use them in many ways but some of my questions have lead to long long threads without such solutions.
0
 
projectsAuthor Commented:
@nemws1
 
Agree with Gary.  If you have a timestamp field and you do *not* specify that field in your insert, MySQL automatically inserts a time that will likely be different from a different INSERT.  However, you can specify the timestamp field in your INSERT along with a value and MySQL will respect that value and *not* automatically insert the current time

Open in new window


I am using the 'datetime' field type in mysql because I don't want the time stamp to be from mysql, I want it to be from the sending script.

Are you saying I have something incorrectly set here?
0
 
GaryCommented:
In your php page can you do

print_r($_POST);

And post back here the result from terminal window
0
 
projectsAuthor Commented:
Array ( )
0
 
GaryCommented:
var_dump($_POST);
0
 
fiboCommented:
Looking at the exchange above:
- there are 2 tables, A and B
- you want 2 things:
1 - record in table A the time of an event
 2 - have a way to place in table B a reference to the same time.
- you tried to achieve that by writing the time in both table A and B
- although this will eventually work, you might consider something else, what in fact your programmer hinted you at: define a pointer to the place/ record where the timestamp is recorded
- this is precisely what relational databases are made for: creating relations between tables.

So, instead of having
Table A
timestamp, other infoX, other infoY...
2014-07-25, XXX5, YYY5...
2014-07-28, XXX8, YYY8...
Table B
timestamp, other infoZ, other infoW...
2014-07-25, ZZZ0, WWW0...
2014-07-27, ZZZ1,WWW1...
2014-07-28, ZZZ2,WWW2...

you would have
Table A
recordnumberA, timestamp, other infoX, other infoY...
58, 2014-07-25, XXX5, YYY5...
59, 2014-07-28, XXX8, YYY8...
Table B
recordnumberB,link_to_tableA,  other infoZ, other infoW...
305,58,ZZZ0, WWW0...
306,33, ZZZ1,WWW1...
307,59, ZZZ2,WWW2...

Thus you would write a record in tableA, get the corresponding record #, and place this value in tableB

Of course, if you want the time stamp of record 305 of table B above, you need to look at the corresponding record (58) in table A. This makes the SQL query slightly more complicated, but is the normal type of SQL queries and is what Ray would call the "non-smelling" solution
0
 
projectsAuthor Commented:
@Gary; array(0) { }

@Fibo; Thanks for the information. That sounds exactly like what I've been trying to describe.

I feel I should award you the solution then post a new question asking how to do that? :)
0
 
projectsAuthor Commented:
@Fibo, this is just what I am hoping to get as a solution. Can someone provide this please based on the code I posted.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 14
  • 10
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now