MySQL query inside foreach loop not working

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ViserExcizer
Forum Newbie
Posts: 24
Joined: Tue Nov 25, 2008 1:17 pm

MySQL query inside foreach loop not working

Post 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
tech603
Forum Commoner
Posts: 84
Joined: Thu Mar 19, 2009 12:27 am

Re: MySQL query inside foreach loop not working

Post 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
ViserExcizer
Forum Newbie
Posts: 24
Joined: Tue Nov 25, 2008 1:17 pm

Re: MySQL query inside foreach loop not working

Post 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?
ViserExcizer
Forum Newbie
Posts: 24
Joined: Tue Nov 25, 2008 1:17 pm

Re: MySQL query inside foreach loop not working

Post 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?
Post Reply