Weird behaviour that I cannot figure out
Posted: Wed 24 Apr 2013 12:11
Having coded a Closure database system in PHP, I wanted to transfer some of the functionality to a Desktop application. So far everything has been going well, apart from this one problem that I cannot pinpoint a solution to.
When creating a new card holder in with a registration page in PHP, the user enters a referral number of a user that referred them. This referral is saved in a relationship table that stores the entire hierarchical relationship between the new user, the referrer, their referrer, etc. up to 4 levels deep.
In PHP I use this code to create these entries automatically:
It works perfectly and generates 3 entries into the MySQL table called "card_holders_relations" namely
Where 229 is the id of the new user, ancestor 4 is the referrer and ancestor 3 is the referrer of ancestor 4. The length field serves as the depth calculator.
Now transfering this to my desktop application, I use the following code in Delphi:
The code looks exactly the same, but when MyDAC runs the query, it just inserts one entry into the table, which is the Level 0 one.
Do you know why this happens?
==========================
EDIT:
As a side note, I did create an ugly solution which looks something like this:
When creating a new card holder in with a registration page in PHP, the user enters a referral number of a user that referred them. This referral is saved in a relationship table that stores the entire hierarchical relationship between the new user, the referrer, their referrer, etc. up to 4 levels deep.
In PHP I use this code to create these entries automatically:
Code: Select all
$new_user_id = mysql_insert_id();
$insert_query = "INSERT card_holders_relations (ancestor, descendant, length)
SELECT ancestor, " . $new_user_id . ", length+1
FROM card_holders_relations
WHERE descendant = " . $refid . "
UNION ALL SELECT " . $new_user_id . ", " . $new_user_id . ", 0";
Code: Select all
relid ancestor descendant length
539 3 229 2
540 4 229 1
541 229 229 0
Now transfering this to my desktop application, I use the following code in Delphi:
Code: Select all
pid := fieldbyname('refid').AsString;
newuserid := fieldbyname('id').AsString;
SQL.Clear;
SQL.Add('INSERT card_holders_relations (ancestor, descendant, length)');
SQL.Add(' SELECT ancestor, '+newuserid+', length+1');
SQL.Add(' FROM card_holders_relations');
SQL.Add(' WHERE descendant = '+pid);
SQL.Add(' UNION ALL SELECT '+newuserid+', '+newuserid+', 0');
Code: Select all
relid ancestor descendant length
542 229 229 0
Do you know why this happens?
==========================
EDIT:
As a side note, I did create an ugly solution which looks something like this:
Code: Select all
depth := 0;
tempid := newuserid;
d[0] := newuserid;
repeat
with form1.myqrych do
begin
SQL.Clear;
SQL.Add('SELECT refchid1 FROM card_holders WHERE id='+tempid);
ExecSQL;
active := true;
tempid := fieldbyname('refchid1').AsString;
if tempid <> '0'
then begin
inc(depth);
d[depth] := tempid;
end;
end;
until((depth = 4) or (tempid = '0'));
for a := 0 to depth do
begin
with myqry do
begin
SQL.Clear;
SQL.Add('INSERT INTO card_holders_relations (ancestor, descendant, length) VALUES ('+d[a]+','+newuserid+','+inttostr(a)+')');
ExecSQL;
end;
end;