Php mysql trigger creation help please.

[Mynight]

Hello all wondering if anyone could help. Client requests me to create a database that has a table for each user that signs up. The user gets to specify the table name. I know terrible idea but they refuse to consider other options and well I'm getting paid so ill do as they wish.

Right creating the table with the table name dynamically was easy. The difficult part is creating the trigger with dynamic table name.

So far I have;

$sql = mysql_query("CREATE TRIGGER 'triggername' AFTER INSERT ON '{$ID}' FOR EACH ROW UPDATE localhost@table SET Variable1 = Variable1+1 WHERE Variable2 = Variable2");

Where $ID is the same variable used to create the table in the first query.

Obviously being followed by an if/else to show result and to continue with the next set of instructions. However its failing

 

[planty]

Are you getting any errors? Where are you getting Variable2 from?

 

[Mynight]

Variable2 is in both tables sorry.

It works fine inputting manually but fails via the php. It doesn't give an error that I can see it just returns false

 

[planty]

Try calling, mysql_error() after your query fails. That should give you some more info.
I think you may also need to use 'new.variable2' in your where clause if you want to use the newly inserted value.

 

[Mynight]

Ill try that didnt think it was needed on a after insert but does make sense.

Ill try the mysql_error funtion when my damn server comes online again thank you

 

[Dj_Jestar]

WHERE Variable2 = Variable2

That translates to (pseudo) "WHERE TRUE", fyi.

 

[Mynight]

You are correct normally but I failed in my description. Essentially the first variable2 is the current table name and it needs to find the table name in the correct row on the second table.

The table name is dynamic so has to be stored as a variable to be compared its a horrible design but its what they want.

 

[Dj_Jestar]

qualify the column name.

WHERE table.Variable2

also, it's a column, not a variable

 

[Mynight]

I didnt know you could do that Thank you!

True sorry. I knew what I meant

 

[Mynight]

EDIT: all fixed.

Thank you so much