Sfrgttk

Too soon for a plot twist?

What would be the most expensive material to an intergalactic society?

How spaceships determine each other's mass in space?

Short story about an infectious indestructible metal bar?

I've given my players a lot of magic items. Is it reasonable for me to give them harder encounters?

How does learning spells work when leveling a multiclass character?

Can Witch Sight see through Mirror Image?

Why does this boat have a landing pad? (SpaceX's GO Searcher) Any plans for propulsive capsule landings?

Should I file my taxes? No income, unemployed, but paid 2k in student loan interest

A running toilet that stops itself

How can I have x-axis ticks that show ticks scaled in powers of ten?

Vector-transposing function

Draw this image in the TIKZ package

Having the player face themselves after the mid-game

What can I do if someone tampers with my SSH public key?

Professor forcing me to attend a conference, I can't afford even with 50% funding

Where is the License file location for Identity Server in Sitecore 9.1?

How to write a chaotic neutral protagonist and prevent my readers from thinking they are evil?

Precision notation for voltmeters

Inorganic chemistry handbook with reaction lists

How to install "rounded" brake pads

How to distinguish easily different soldier of ww2?

Is this Paypal Github SDK reference really a dangerous site?

Why do we call complex numbers “numbers” but we don’t consider 2-vectors numbers?

If count >= row then insert row

Retrieving row count without using Count functionMySQL table design / architecture, table is too bigHow to get output from trigger on linked serverMySQL: can a table get stuck in the table cache?How can I simplify this UNION?Why is MySQL not saving inserted data?mysql custom query logging for inter-software data exchangeInserting rows with WHERE conditionUsing COUNT in triggers before insert into tableGet count of value row

0

I do 99% php/javascript work so getting my head around SQL is always a struggle, sorry if I seem dense.

So I've got a table that looks something like this:

post_id   |  meta_key    | meta_value

--------------------------------------

1         | requirement  | 3

2         | requirement  | 1

3         | requirement  | 1

1         | adder_id     | 3

1         | adder_id     | 2

1         | adder_id     | 69

2         | adder_id     | 42

2         | complete_date| 20190309040445

On an insert trigger I want to run a query that will for each post_id that dose not have a "complete date":

INSERT INTO this_table ( post_id, meta_key, meta_value )

VALUES

( var_post_id, 'complete_date',NOW());

But only if

SELECT COUNT(*)

FROM this_table

WHERE post_id = var_post_id 

AND meta_key = 'adder_id'

is >=

SELECT meta_value

FROM this_table

WHERE post_id = var_post_id

AND meta_key = 'requirement'

So post_id two is the intended effect (requires 1, has 1, got a complete date) while post_id one is an example of something that should trigger an update (requires 3, has 3, waiting on a complete date) and post_id three should be ignored (requires 1, has 0, just ignore).

Now this would be easy enough with an if/else and 2 or three variables but is this even possible with a mySQL statement? Is there an easier way of going about it within mySQL or should I try and find a PHP solution instead?

Thanks for any assistance, I don't usually ask for help here unless I'm properly stumped and this one really has me going.

mysql trigger count

share

asked 5 mins ago

Robert PorterRobert Porter

1

New contributor

Robert Porter is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

I do 99% php/javascript work so getting my head around SQL is always a struggle, sorry if I seem dense.

So I've got a table that looks something like this:

post_id   |  meta_key    | meta_value

--------------------------------------

1         | requirement  | 3

2         | requirement  | 1

3         | requirement  | 1

1         | adder_id     | 3

1         | adder_id     | 2

1         | adder_id     | 69

2         | adder_id     | 42

2         | complete_date| 20190309040445

On an insert trigger I want to run a query that will for each post_id that dose not have a "complete date":

INSERT INTO this_table ( post_id, meta_key, meta_value )

VALUES

( var_post_id, 'complete_date',NOW());

But only if

SELECT COUNT(*)

FROM this_table

WHERE post_id = var_post_id 

AND meta_key = 'adder_id'

is >=

SELECT meta_value

FROM this_table

WHERE post_id = var_post_id

AND meta_key = 'requirement'

So post_id two is the intended effect (requires 1, has 1, got a complete date) while post_id one is an example of something that should trigger an update (requires 3, has 3, waiting on a complete date) and post_id three should be ignored (requires 1, has 0, just ignore).

Now this would be easy enough with an if/else and 2 or three variables but is this even possible with a mySQL statement? Is there an easier way of going about it within mySQL or should I try and find a PHP solution instead?

Thanks for any assistance, I don't usually ask for help here unless I'm properly stumped and this one really has me going.

mysql trigger count

share

asked 5 mins ago

Robert PorterRobert Porter

1

New contributor

Robert Porter is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

I do 99% php/javascript work so getting my head around SQL is always a struggle, sorry if I seem dense.

So I've got a table that looks something like this:

post_id   |  meta_key    | meta_value

--------------------------------------

1         | requirement  | 3

2         | requirement  | 1

3         | requirement  | 1

1         | adder_id     | 3

1         | adder_id     | 2

1         | adder_id     | 69

2         | adder_id     | 42

2         | complete_date| 20190309040445

On an insert trigger I want to run a query that will for each post_id that dose not have a "complete date":

INSERT INTO this_table ( post_id, meta_key, meta_value )

VALUES

( var_post_id, 'complete_date',NOW());

But only if

SELECT COUNT(*)

FROM this_table

WHERE post_id = var_post_id 

AND meta_key = 'adder_id'

is >=

SELECT meta_value

FROM this_table

WHERE post_id = var_post_id

AND meta_key = 'requirement'

So post_id two is the intended effect (requires 1, has 1, got a complete date) while post_id one is an example of something that should trigger an update (requires 3, has 3, waiting on a complete date) and post_id three should be ignored (requires 1, has 0, just ignore).

Now this would be easy enough with an if/else and 2 or three variables but is this even possible with a mySQL statement? Is there an easier way of going about it within mySQL or should I try and find a PHP solution instead?

Thanks for any assistance, I don't usually ask for help here unless I'm properly stumped and this one really has me going.

mysql trigger count

share

asked 5 mins ago

Robert PorterRobert Porter

1

New contributor

Robert Porter is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

post_id   |  meta_key    | meta_value

--------------------------------------

1         | requirement  | 3

2         | requirement  | 1

3         | requirement  | 1

1         | adder_id     | 3

1         | adder_id     | 2

1         | adder_id     | 69

2         | adder_id     | 42

2         | complete_date| 20190309040445

INSERT INTO this_table ( post_id, meta_key, meta_value )

VALUES

( var_post_id, 'complete_date',NOW());

SELECT COUNT(*)

FROM this_table

WHERE post_id = var_post_id 

AND meta_key = 'adder_id'

SELECT meta_value

FROM this_table

WHERE post_id = var_post_id

AND meta_key = 'requirement'

share

asked 5 mins ago

Robert PorterRobert Porter

1

New contributor

Robert Porter is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 5 mins ago

Robert PorterRobert Porter

1

New contributor

Robert Porter is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 5 mins ago

Robert PorterRobert Porter

1

New contributor

Robert Porter is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 5 mins ago

Robert PorterRobert Porter

1