Please help! I go live tomorrow! (MySQL/PHP unknown error!

[jessejarvis]

_{You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '["rid"]., SELECT refid1 FROM oto_members WHERE id='13', SELECT refid2 FR' at line 5}

 

With this script area:

 

^{$affiliate1=('.$_REQUEST["rid"].');}

^{$affiliate2=("SELECT refid1 FROM oto_members WHERE id='".$_REQUEST["rid"]."'");}

^{$affiliate3=("SELECT refid2 FROM oto_members WHERE id='".$_REQUEST["rid"]."'");}

^{$affiliate4=("SELECT refid3 FROM oto_members WHERE id='".$_REQUEST["rid"]."'");}

^{$affiliate5=("SELECT refid4 FROM oto_members WHERE id='".$_REQUEST["rid"]."'");}

^{$affiliate6=("SELECT refid5 FROM oto_members WHERE id='".$_REQUEST["rid"]."'");}

^{$affiliate7=("SELECT refid6 FROM oto_members WHERE id='".$_REQUEST["rid"]."'");}

^{$affiliat8e=("SELECT refid7 FROM oto_members WHERE id='".$_REQUEST["rid"]."'");}

^{$affiliate9=("SELECT refid8 FROM oto_members WHERE id='".$_REQUEST["rid"]."'");}

^{$affiliate10=("SELECT refid9 FROM oto_members WHERE id='".$_REQUEST["rid"]."'");}

 

 

^{$qry="INSERT INTO ".$prefix."members(firstname,lastname,email,address,city,state,postcode,country,telephone,username,password,refid1,refid2,refid3,refid4,refid5,refid6,refid7,refid8,refid9,refid10,geo,paypal_email,joindate,mtype,groupid,cb_id,status,signupip)}

 

^{VALUES('".$_REQUEST["firstname"]."','".$_REQUEST["lastname"]."','".$_REQUEST["email"]."','".$_REQUEST["address"]."','".$_REQUEST["city"]."','".$_REQUEST["state"]."','".$_REQUEST["postcode"]."','".$_REQUEST["country"]."','".$_REQUEST["telephone"]."','".$_REQUEST["username"]."','".md5($_REQUEST["password"])."',}

 

^$affiliate1,

^$affiliate2,

^$affiliate3,

^$affiliate4,

^$affiliate5,

^$affiliate6,

^$affiliate7,

^$affiliate8,

^$affiliate9,

^$affiliate10

 

^{,'".$_REQUEST["geo"]."','".$_REQUEST["paypal_email"]."',NOW(),".$_REQUEST["mtid"].",$eogroup,'".$_REQUEST["cb_id"]."','$memberstatus','$signupip')";}

[batwimp]

Echo out your $qry variable and post the output here.

[jessejarvis]

INSERT INTO oto_members(firstname,lastname,email,address,city,state,postcode,country,telephone,username,password,refid1,refid2,refid3,refid4,refid5,refid6,refid7,refid8,refid9,refid10,geo,paypal_email,joindate,mtype,groupid,cb_id,status,signupip) VALUES('Joe','','[email protected]','','','','','','','joesmith','788f2df64bb1d67573e319567ea3fbc2', .$_REQUEST["rid"]., SELECT refid1 FROM oto_members WHERE id='13', SELECT refid2 FROM oto_members WHERE id='13', SELECT refid3 FROM oto_members WHERE id='13', SELECT refid4 FROM oto_members WHERE id='13', SELECT refid5 FROM oto_members WHERE id='13', SELECT refid6 FROM oto_members WHERE id='13', , SELECT refid8 FROM oto_members WHERE id='13', SELECT refid9 FROM oto_members WHERE id='13' ,'','',NOW(),1,1,'','Unverified','66.79.235.188')You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '["rid"]., SELECT refid1 FROM oto_members WHERE id='13', SELECT refid2 FR' at line 5

[batwimp]

Your sub-queries should be wrapped in parenthesis. You also have two commas in a row. Is that deliberate?

[jessejarvis]

Your sub-queries should be wrapped in parenthesis. You also have two commas in a row. Is that deliberate?

 

What do you mean wrapped in parenthesis?

 

Also no that is not deliberate, just noticed refid 7 is missing in between this 2 commas, will investigate further.

[jessejarvis]

An e is in there where it shouldn't have been... typo as usual.

 

Here is the latest output:

 

_{INSERT INTO oto_members(firstname,lastname,email,address,city,state,postcode,country,telephone,username,password,refid1,refid2,refid3,refid4,refid5,refid6,refid7,refid8,refid9,refid10,geo,paypal_email,joindate,mtype,groupid,cb_id,status,signupip) VALUES('Joe','','[email protected]','','','','','','','joesmith','788f2df64bb1d67573e319567ea3fbc2', .$_REQUEST["rid"]., SELECT refid1 FROM oto_members WHERE id='13', SELECT refid2 FROM oto_members WHERE id='13', SELECT refid3 FROM oto_members WHERE id='13', SELECT refid4 FROM oto_members WHERE id='13', SELECT refid5 FROM oto_members WHERE id='13', SELECT refid6 FROM oto_members WHERE id='13', SELECT refid7 FROM oto_members WHERE id='13', SELECT refid8 FROM oto_members WHERE id='13', SELECT refid9 FROM oto_members WHERE id='13' ,'','',NOW(),1,1,'','Unverified','66.79.235.188')You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '["rid"]., SELECT refid1 FROM oto_members WHERE id='13', SELECT refid2 FR' at line 5}

[batwimp]

INSERT INTO oto_members(firstname,lastname,email,address,city,state,postcode,country,telephone,username,password,refid1,refid2,refid3,refid4,refid5,refid6,refid7,refid8,refid9,refid10,geo,paypal_email,joindate,mtype,groupid,cb_id,status,signupip) VALUES('Joe','','[email protected]','','','','','','','joesmith','788f2df64bb1d67573e319567ea3fbc2', .$_REQUEST["rid"]., (SELECT refid1 FROM oto_members WHERE id='13'), (SELECT refid2 FROM oto_members WHERE id='13'), (SELECT refid3 FROM oto_members WHERE id='13'), (SELECT refid4 FROM oto_members WHERE id='13'), (SELECT refid5 FROM oto_members WHERE id='13'), (SELECT refid6 FROM oto_members WHERE id='13'), , (SELECT refid8 FROM oto_members WHERE id='13'), (SELECT refid9 FROM oto_members WHERE id='13') ,'','',NOW(),1,1,'','Unverified','66.79.235.188')

 

Parenthesis. It's a place to start. See what happens when you do that, and post any more errors here. I'll let the others in the forum tell you of the pitfalls of using sub-queries, for I am not that knowledgeable.

[jessejarvis]

It says this:

 

_{INSERT INTO oto_members(firstname,lastname,email,address,city,state,postcode,country,telephone,username,password,refid1,refid2,refid3,refid4,refid5,refid6,refid7,refid8,refid9,refid10,geo,paypal_email,joindate,mtype,groupid,cb_id,status,signupip) VALUES('Joe','','[email protected]','','','','','','','joesmith','788f2df64bb1d67573e319567ea3fbc2',' (SELECT refid1 FROM oto_members WHERE id='13'), (SELECT refid2 FROM oto_members WHERE id='13'), (SELECT refid3 FROM oto_members WHERE id='13'), (SELECT refid4 FROM oto_members WHERE id='13'), (SELECT refid5 FROM oto_members WHERE id='13'), (SELECT refid6 FROM oto_members WHERE id='13'), (SELECT refid7 FROM oto_members WHERE id='13'), (SELECT refid8 FROM oto_members WHERE id='13'), (SELECT refid9 FROM oto_members WHERE id='13'), (SELECT refid10 FROM oto_members WHERE id='13') ,'','',NOW(),1,1,'','Unverified','66.79.235.188')You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '13'), (SELECT refid2 FROM oto_members WHERE id='13'), (SELECT refid3 FRO' at line 3}

[jessejarvis]

It ended up saying this:

 

_{INSERT INTO oto_members(firstname,lastname,email,address,city,state,postcode,country,telephone,username,password,refid1,refid2,refid3,refid4,refid5,refid6,refid7,refid8,refid9,refid10,geo,paypal_email,joindate,mtype,groupid,cb_id,status,signupip) VALUES('Joe','','[email protected]','','','','','','','joesmith','788f2df64bb1d67573e319567ea3fbc2', (SELECT refid1 FROM oto_members WHERE id='13'), (SELECT refid2 FROM oto_members WHERE id='13'), (SELECT refid3 FROM oto_members WHERE id='13'), (SELECT refid4 FROM oto_members WHERE id='13'), (SELECT refid5 FROM oto_members WHERE id='13'), (SELECT refid6 FROM oto_members WHERE id='13'), (SELECT refid7 FROM oto_members WHERE id='13'), (SELECT refid8 FROM oto_members WHERE id='13'), (SELECT refid9 FROM oto_members WHERE id='13'), (SELECT refid10 FROM oto_members WHERE id='13') ,'','',NOW(),1,1,'','Unverified','66.79.235.188')You can't specify target table 'oto_members' for update in FROM clause}

 

with this:

 

_{$affiliate1=('.$_REQUEST["rid"].');}

_{$affiliate2=("SELECT refid1 FROM oto_members WHERE id='".$_REQUEST["rid"]."'");}

_{$affiliate3=("SELECT refid2 FROM oto_members WHERE id='".$_REQUEST["rid"]."'");}

_{$affiliate4=("SELECT refid3 FROM oto_members WHERE id='".$_REQUEST["rid"]."'");}

_{$affiliate5=("SELECT refid4 FROM oto_members WHERE id='".$_REQUEST["rid"]."'");}

_{$affiliate6=("SELECT refid5 FROM oto_members WHERE id='".$_REQUEST["rid"]."'");}

_{$affiliate7=("SELECT refid6 FROM oto_members WHERE id='".$_REQUEST["rid"]."'");}

_{$affiliate8=("SELECT refid7 FROM oto_members WHERE id='".$_REQUEST["rid"]."'");}

_{$affiliate9=("SELECT refid8 FROM oto_members WHERE id='".$_REQUEST["rid"]."'");}

_{$affiliate10=("SELECT refid9 FROM oto_members WHERE id='".$_REQUEST["rid"]."'");}

 

_{echo $qry="INSERT INTO ".$prefix."members(firstname,lastname,email,address,city,state,postcode,country,telephone,username,password,refid1,refid2,refid3,refid4,refid5,refid6,refid7,refid8,refid9,refid10,geo,paypal_email,joindate,mtype,groupid,cb_id,status,signupip)}

 

_{VALUES('".$_REQUEST["firstname"]."','".$_REQUEST["lastname"]."','".$_REQUEST["email"]."','".$_REQUEST["address"]."','".$_REQUEST["city"]."','".$_REQUEST["state"]."','".$_REQUEST["postcode"]."','".$_REQUEST["country"]."','".$_REQUEST["telephone"]."','".$_REQUEST["username"]."','".md5($_REQUEST["password"])."',}

 

_{(SELECT refid1 FROM oto_members WHERE id='13'),}

_{(SELECT refid2 FROM oto_members WHERE id='13'),}

_{(SELECT refid3 FROM oto_members WHERE id='13'),}

_{(SELECT refid4 FROM oto_members WHERE id='13'),}

_{(SELECT refid5 FROM oto_members WHERE id='13'),}

_{(SELECT refid6 FROM oto_members WHERE id='13'),}

_{(SELECT refid7 FROM oto_members WHERE id='13'),}

_{(SELECT refid8 FROM oto_members WHERE id='13'),}

_{(SELECT refid9 FROM oto_members WHERE id='13'),}

_{(SELECT refid10 FROM oto_members WHERE id='13')}

 

_{,'".$_REQUEST["geo"]."','".$_REQUEST["paypal_email"]."',NOW(),".$_REQUEST["mtid"].",$eogroup,'".$_REQUEST["cb_id"]."','$memberstatus','$signupip')";}

[batwimp]

Basically this is saying you can't use information from the table you are about to write to. There are a few ways to do this, but I only remember a couple(I'm not a MySQL expert). I think the easiest way to do this is to assign an alias to the table name you are pulling information from. So for each of your sub-queries, you need to put a table alias, such as this:

 

(SELECT refid5 FROM oto_members as om WHERE id='13')

[jessejarvis]

I am trying to make a 10 level affiliate system so that when somebody signs up, their sponsor becomes their refid1, refid2 = the sponsor's sponsor, refid3= the sponsor's sponsor's sponsor, etc until 10 levels.

 

Know what I mean?

[batwimp]

I know what you mean. There is probably an easier way to do it with your database. If you don't know about Relational Databases, I would suggest you read up, because it may just save you a lot of heartache in the future. You could even do what you are saying in a single table (though I wouldn't recommend it) if every person only has a single sponsor. You would only need a field that contains the sponsor ID, and everything else could be done easier. Example:

 

Joe's sponsor ID is 5 (Sally)

Sally's sponsor ID is 12 (Robert)

Robert's sponsor ID is 6 (Duane)

Duane's sponsor ID is 144 (Mary)

 

etc... ten levels deep. If each person can have more than a single sponsor, then you just add a new table and use a one-to-many or many-to-many relationship in the database. Seriously, study up on it and it will make your life a LOT easier.

[jessejarvis]

Thank you! You have just potentionally made me millions

 

I believe I have figured it out massively with your help, adding the " as om" seems to have made the registration go through

 

Sincerely, Jesse Jarvis.

[batwimp]

You're welcome.

[jessejarvis]

One small issue.

 

In:

 

_{(SELECT refid2 FROM oto_members as om WHERE id='13'),}

 

id='13' should actually get the 13 from

 

"WHERE id='.md5($_REQUEST["rid"]).'"

 

How can I put this in?

 

_{VALUES('".$_REQUEST["firstname"]."','".$_REQUEST["lastname"]."','".$_REQUEST["email"]."','".$_REQUEST["address"]."','".$_REQUEST["city"]."','".$_REQUEST["state"]."','".$_REQUEST["postcode"]."','".$_REQUEST["country"]."','".$_REQUEST["telephone"]."','".$_REQUEST["username"]."','".md5($_REQUEST["password"])."','".md5($_REQUEST["rid"])."',}

           

            _{(SELECT refid2 FROM oto_members as om WHERE id='13'),}

            _{(SELECT refid3 FROM oto_members as om WHERE id='13'),}

            _{(SELECT refid4 FROM oto_members as om WHERE id='13'),}

            _{(SELECT refid5 FROM oto_members as om WHERE id='13'),}

            _{(SELECT refid6 FROM oto_members as om WHERE id='13'),}

            _{(SELECT refid7 FROM oto_members as om WHERE id='13'),}

            _{(SELECT refid8 FROM oto_members as om WHERE id='13'),}

            _{(SELECT refid9 FROM oto_members as om WHERE id='13'),}

            _{(SELECT refid10 FROM oto_members as om WHERE id='13')}

[batwimp]

From PHP.net:

 

MySQL MD() will not give you the same hash if character set is different.

ex :

<?php

#suppose table_name CHARSET=UTF8

#$md5 = md5('Städte'); # will give you a different hash than MySQL MD5()

#instead use

$md5 = md5(utf8_encode('Städte'));

$r = mysql_query("SELECT *, MD5(`word`) FROM `table_name` WHERE MD5(`word`) LIKE '{$md5}'");

if($r)

    while( $row= mysql_fetch_assoc($r) )

        print_r($row);

?>

[jessejarvis]

That's doesn't really help me, what do you mean? I can't do what I am trying to do?

[batwimp]

Sorry. How did the value get into the database? If they were hashed using the PHP MD5 function, then you will have to find a way to pull it out of the DB and hash it with the PHP MD5 function again, before querying the database with it. If they were hashed with the MySQL MD5 function, it will be easier to compare to the database because you can include it in your query.

 

So I guess I'm asking: how did your values get hashed when they went into the database?

[jessejarvis]

The referral ids are not hashed at all, they are int(11) unsigned.

[batwimp]

Then why are you MD5-ing them?

[kicken]

Using INSERT SELECT might work better than trying to do a bunch of sub-selects.  Really, your DB should be designed differently thought I believe.

 

Also, you need to make sure you are properly escaping everything so your not vulnerable to SQL Injection.

 

$sql = "INSERT INTO {$prefix}members (
firstname,
lastname,
email,
address,
city,
state,
postcode,
country,
telephone,
username,
password,
refid1,
refid2,
refid3,
refid4,
refid5,
refid6,
refid7,
refid8,
refid9,
refid10,
geo,
paypal_email,
joindate,
mtype,
groupid,
cb_id,
status,
signupip
) 
SELECT
'".$_REQUEST["firstname"]."',
'".$_REQUEST["lastname"]."',
'".$_REQUEST["email"]."',
'".$_REQUEST["address"]."',
'".$_REQUEST["city"]."',
'".$_REQUEST["state"]."',
'".$_REQUEST["postcode"]."',
'".$_REQUEST["country"]."',
'".$_REQUEST["telephone"]."',
'".$_REQUEST["username"]."',
'".md5($_REQUEST["password"])."',
id,
refid1,
refid2,
refid3,
refid4,
refid5,
refid6,
refid7,
refid8,
refid9,
'".$_REQUEST["geo"]."',
'".$_REQUEST["paypal_email"]."',
NOW(),
".$_REQUEST["mtid"].",
$eogroup,
'".$_REQUEST["cb_id"]."',
'$memberstatus',
'$signupip'
FROM members
WHERE
id=".$_REQUEST["rid"]."
)";

[samshel]

2 cents. You can simplify this by dividing this into 2 steps.

 

1) fetch all rid for id = 13

2) build simple insert without subqueryies

 

something like:

$strSelect	=	"SELECT * FROM oto_members WHERE id='".$_REQUEST["rid"]."'";
$objResult	=	mysql_query($strSelect);
$arrResult	=	mysql_fetch_array($objResult);

$qry="INSERT INTO ".$prefix."members(firstname,lastname,email,address,city,state,postcode,country,telephone,username,password,refid1,refid2,refid3,refid4,refid5,refid6,refid7,refid8,refid9,refid10,geo,paypal_email,joindate,mtype,groupid,cb_id,status,signupip)

VALUES('".$_REQUEST["firstname"]."','".$_REQUEST["lastname"]."','".$_REQUEST["email"]."','".$_REQUEST["address"]."','".$_REQUEST["city"]."','".$_REQUEST["state"]."','".$_REQUEST["postcode"]."','".$_REQUEST["country"]."','".$_REQUEST["telephone"]."','".$_REQUEST["username"]."','".md5($_REQUEST["password"])."',
'".$_REQUEST["rid"]."',
'".$arrResult['refid1']."',
'".$arrResult['refid2']."',
'".$arrResult['refid3']."',
'".$arrResult['refid4']."',
'".$arrResult['refid5']."',
'".$arrResult['refid6']."',
'".$arrResult['refid7']."',
'".$arrResult['refid8']."',
'".$arrResult['refid9']."',
'".$_REQUEST["geo"]."','".$_REQUEST["paypal_email"]."',NOW(),".$_REQUEST["mtid"].",$eogroup,'".$_REQUEST["cb_id"]."','$memberstatus','$signupip')";

PS : Code not tested.

[jessejarvis]

Then why are you MD5-ing them?

 

It's a software I am using, just noticed where it is MD5'ing it :/

 

From what I notice in:

 

_{(SELECT refid2 FROM oto_members as om WHERE id='".md5($_REQUEST["rid"])."'),}

 

The _{.md5($_REQUEST["rid"]).} Gets the info from the web browser url:

 

_{http://www.website.com/?rid=929}

 

With 929 being the referral ID.

 

That's why when I see .md5($_REQUEST["rid"]). I believe it's getting the referral ID.

 

So in other words:

 

_{(SELECT refid2 FROM oto_members as om WHERE id='".md5($_REQUEST["rid"])."'),}

 

Select Refferal ID FROM Member's table where id= Refferal's ID.

 

Does this make sense?

 

However _{(SELECT refid2 FROM oto_members as om WHERE id='".md5($_REQUEST["rid"])."'),}

            _{(SELECT refid3 FROM oto_members as om WHERE id='".md5($_REQUEST["rid"])."'),}

            _{(SELECT refid4 FROM oto_members as om WHERE id='".md5($_REQUEST["rid"])."'),}

            _{(SELECT refid5 FROM oto_members as om WHERE id='".md5($_REQUEST["rid"])."'),}

            _{(SELECT refid6 FROM oto_members as om WHERE id='".md5($_REQUEST["rid"])."'),}

            _{(SELECT refid7 FROM oto_members as om WHERE id='".md5($_REQUEST["rid"])."'),}

            _{(SELECT refid8 FROM oto_members as om WHERE id='".md5($_REQUEST["rid"])."'),}

            _{(SELECT refid9 FROM oto_members as om WHERE id='".md5($_REQUEST["rid"])."'),}

            _{(SELECT refid10 FROM oto_members as om WHERE id='".md5($_REQUEST["rid"])."'),}

 

Ends up entering "0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL"

 

Although it should be "13,12,11,4,0,0,0,0,0"

[kicken]

The _{.md5($_REQUEST["rid"]).} Gets the info from the web browser url:

 

_{http://www.website.com/?rid=929}

 

With 929 being the referral ID.

 

That's why when I see .md5($_REQUEST["rid"]). I believe it's getting the referral ID.

 

$_REQUEST['rid'] will get your number, 929.

md5() will change that number into a value such as: 0d0871f0806eae32d30983b62252da50

 

which means your doing WHERE id='0d0871f0806eae32d30983b62252da50'

 

 

I doubt that is what you want.

[batwimp]

kicken is right. It doesn't make any sense to have an md5 there. MD5 is a one-way encryption algorithm that is used mostly for password verification. You should read the sticky at the top of the forum posts titled "What is the point of MD5?" and you might see why it doesn't make any sense to use md5 there. Did someone else write that code, and you are just trying to decipher it and get it to work?