Nested INSERT in While Loop

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
dyluck
Forum Commoner
Posts: 54
Joined: Thu Jun 26, 2008 1:44 pm

Nested INSERT in While Loop

Post by dyluck »

Hi there... this is just killing me... The function requires that I do this particular action.
The server gives me a mysql syntax error... I have tried combing it and combing it, trying different ways of doing it and to no avail. Please see the code, maybe im doing something completely wrong (there is a better way to do it) or I'm missing something silly.

Code: Select all

$streamlinker = mysql_pconnect("localhost", "username", "passwordhere") or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db("database1", $streamlinker);
$moresqlstreamlink='SELECT * from link where active = "1" and uid = "'.$ref.'"';
$morestreamresult=mysql_query($moresqlstreamlink)
or die(mysql_error());
while($morerows = mysql_fetch_array($morestreamresult))
 {
 $streamid = $morerows['streamid'];
 $streamuid = $morerows['uid'];
 $order = $morerows['order'];
 $streamrefuid = $morerows['refuid'];
 $streamrefdata = $morerows['refdata'];
 $data = $morerows['data'];
 $sticky = $morerows['sticky'];
 
if (empty($data)) { $refdata = $streamrefdata; $refuid = $streamrefuid;} else { $refdata = $data; $refuid = $streamuid; }
 
//lets check to see if the stream needs to continue to be sticky from the upline
    if ($sticky <> "") {
    include 'dblink.php'; //the second database
    $link2 = mysql_pconnect($hostname, $un, $passwd) 
or trigger_error(mysql_error(),E_USER_ERROR);
    mysql_select_db($database, $link2);
    $sql2='SELECT id from users where (upline1 = "'.$sticky.'" or upline2 = "'.$sticky.'" or upline3 = "'.$sticky.'" or upline4 = "'.$sticky.'" or upline5 = "'.$sticky.'")';
    $streamresult=mysql_query($sql2)
    or die(mysql_error());
    if (mysql_num_rows($sql2) > 0) { $sticky = $sticky; } else { $sticky = ""; }}
 
//Insert the new rows into the table with the new user's default information
$streamlink = mysql_pconnect("localhost", "username", "passwordhere") or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db("database1", $streamlink);
mysql_query ("INSERT INTO link (streamid, order, uid, refuid, refdata, data, active, sticky) VALUES ('$streamid', '$order', '$uid', '$refuid', '$refdata', '', '1', '$sticky'")
or die(mysql_error());  
}
The error keeps saying the following:
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 'order, uid, refuid, refdata, data, active, sticky) VALUES ('1', '2', 'tvgxhksz30' at line 1
thanks for your help!
sureshmaharana
Forum Commoner
Posts: 30
Joined: Thu Jul 03, 2008 4:20 am
Contact:

Re: Nested INSERT in While Loop

Post by sureshmaharana »

Try it,

mysql_query ("INSERT INTO link (streamid, order, uid, refuid, refdata, data, active, sticky) VALUES ('$streamid', '$order', '$uid', '$refuid', '$refdata', '', '1', '$sticky')")
LBmtb
Forum Newbie
Posts: 23
Joined: Wed May 14, 2008 11:14 am

Re: Nested INSERT in While Loop

Post by LBmtb »

Sorry but I'm a lil tired to go through your code and debug. But just from reading your title "nested INSERT in while loop", I can recommend looking into prepared statements: http://dev.mysql.com/tech-resources/art ... ments.html
User avatar
dyluck
Forum Commoner
Posts: 54
Joined: Thu Jun 26, 2008 1:44 pm

Re: Nested INSERT in While Loop

Post by dyluck »

I think you may be my hero Suresh!
Let me try tonight, I see the missing bracket. Thought I would have noticed that but hey... Guess we can claim to be blind a few times in our life ;-)

Will let you know if it works
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Nested INSERT in While Loop

Post by EverLearning »

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 'order, uid, refuid, refdata, data, active, sticky) VALUES ('1', '2', 'tvgxhksz30' at line 1
I think your problem is in the highlighted column name, since ORDER is a reserved word in MySql. If you want to use it in sql statements put backticks around it - `order`
User avatar
dyluck
Forum Commoner
Posts: 54
Joined: Thu Jun 26, 2008 1:44 pm

Re: Nested INSERT in While Loop

Post by dyluck »

Oh my goodness, I wish I read your statement earlier EverLearning... I could have not wasted the last hour and a half. haha

I found it out by process of elimination then realized "order" is a mysql command...
came here to boast but you already took my glory :P
LOL Thanks for all your help guys, Works great now.
Post Reply