Page 1 of 1

MySQL query inside foreach loop not working

Posted: Sat Mar 28, 2009 11:02 pm
by ViserExcizer
I'm doing a script that'll modify a table's record based on the record of another table


The table 'replace' is structured like this

Code: Select all

 
 referrer  referral 
---------+---------
|  user1   | usera   |
|  user2   | userb   |
|  user2   | userc   |
|  user2   | userd   |
---------+---------
 
 
the 'users' table is

Code: Select all

 
 
  username   referrer      refrented             refend                level2       identifier
+----------+----------+---------+----------------------+---------+----------+
| user2      |  user8      |    yes    | 27-04-2009 08:21:54  |      1     |              | 
| usera      |  user2      |    yes    | 27-04-2009 08:21:54  |      1     |              | 
| userb      |  user2      |    yes    | 27-04-2009 08:21:54  |      1     |              | 
| userc      |  user2      |    yes    | 27-04-2009 08:21:54  |      1     |              | 
| userd      |  user2      |    yes    | 27-04-2009 08:21:54  |      1     |              | 
| userp      |   NULL      |    NUL   |          NULL               |    NULL   |    NULL    |
| userq      |   NULL      |    NUL   |          NULL               |    NULL   |    NULL    |
| users      |   NULL      |    NUL   |          NULL               |    NULL   |    NULL    |
 
So the end result would look like this:

Code: Select all

 
 
  username   referrer      refrented             refend                level2       identifier
+----------+----------+---------+----------------------+---------+----------+
| user2      |  user8      |    yes    | 27-04-2009 08:21:54  |      1     |              | 
| usera      |  user2      |    yes    | 27-04-2009 08:21:54  |      1     |              | 
| userb      |   NULL      |   NULL   |            NULL             |   NULL    |             | 
| userb      |   NULL      |   NULL   |            NULL             |   NULL    |             |
| userb      |   NULL      |   NULL   |            NULL             |   NULL    |             | 
| userp      |  user2      |    yes    | 27-04-2009 08:21:54  |      1     |              | 
| userq      |  user2      |    yes    | 27-04-2009 08:21:54  |      1     |              | 
| users      |  user2      |    yes    | 27-04-2009 08:21:54  |      1     |              | 
 
 

The null is actualy a blank value, its because the white space doesnt look nice when typed here.

This is the code. Its supposed to select the referral record from the first table where the referrer is e.g 'user2'
so $que1 below would have selected 3 entries, userb, userc, and userd

The code after that is supposed to alter the 'users' table, updating the entry userb,userc and userd
and assigning the entries userp, userq and users with the referrer,refrented,refend, value of userb,userc, and userd.


but it isnt working


I've tried every combination of loops and mysql functions that i know and the echo $val; doesnt return

userb
userc
userd

like it should. I the problem must be in the use of foreach loop

Code: Select all

 
$que1 = mysql_query("SELECT referral FROM replace WHERE referrer='$referrer'"); 
$res1 = mysql_fetch_array($que1);
 
 
foreach($res1 as $val){
echo $val ."<br>";
 
 
$que2 = mysql_query("SELECT * FROM users WHERE username='". $val ."'"); 
$res2 = mysql_fetch_array($que2);
$resa = $res2["referer"];
$resb = $res2["refrented"];
$resc = $res2["refend"];
$resd = $res2["level2"];
 
$rel1 = mysql_query("UPDATE yob_users SET identifier='6' WHERE refrented='' AND ((referer='') AND (username!='')) LIMIT 1");
$rel2 = mysql_query("UPDATE yob_users SET referer='$resa', refrented='$resb', refend='$resc', level2='$resd' WHERE identifier='6'");
$rel3 = mysql_query("UPDATE yob_users SET identifier='' WHERE identifier='6'"); 
 
$nex1 = mysql_query("UPDATE yob_users SET referer='', refrented='', refend='', level2='' WHERE username='$val'");
 
$last = mysql_query("DELETE FROM replace WHERE referrer='$resa'");
}
So whats the alternative, as the mysql_fetch_array array funtion doesnt work as the argument for the foreach loop? Thank you

Re: MySQL query inside foreach loop not working

Posted: Sun Mar 29, 2009 12:09 am
by tech603
Inf your foreach loop you have this line

Code: Select all

$que2 = mysql_query("SELECT * FROM users WHERE username='". $val ."'");
it should be this

Code: Select all

$que2 = mysql_query("SELECT * FROM users WHERE username='$val');
Hope that helps

Re: MySQL query inside foreach loop not working

Posted: Sun Mar 29, 2009 12:25 am
by ViserExcizer
nope, thats not the problem either. Its to do with the mysql query not executing in the foreach loop. Either that or the mysql_fetch_array is not the kind of array needed for the foreach loop. Anyone?

Re: MySQL query inside foreach loop not working

Posted: Sun Mar 29, 2009 12:28 am
by ViserExcizer
nope, thats not the problem either. Its to do with the mysql query not executing in the foreach loop. Either that or the mysql_fetch_array is not the kind of array needed for the foreach loop. Anyone?