Loop pulling two rows?

[Zepo.]

I have a loop that I need to pull two rows. I'm really confused

Basically i have a table with a ton of players and i have a loop to match them up, i need to be able to have a loop match two rows then insert and delete. Heres what I got now that just has a normal loop.

	if($timenow1 == $timenow5 && $timenow2 >= $timenow6){
            mysql_query("UPDATE leagues SET status='1' WHERE id='$id'");
			$update = mysql_query("SELECT * FROM leagueplayers WHERE league='$id' ORDER BY ");
   			 while ($row = mysql_fetch_array($update)) {
	        //Insert
		//Delete
		}
        }
    }

Thank you

[Psycho]

I'm confused too because I'm not understanding what you are trying to accomplish. Are you just wanting to match up all the players in pairs randomly? I would assume the insert is used to save a record for the association of each pair, but I'm not understanding what the delete would be for.

 

It should be possible to create all the pairs records with a single query - i.e. no PHP processing. But, I'm hesitant to invest any time into this since I'm not sure if what i think is what you really want.

[Zepo.]

Well, I cant insert them as pairs as it's a list of people joining at different times, but i want to pull two players at a time from the tables run an insert using details from both, then delete both once the match is created since its basically a waiting list. I'm just not sure how to deal with two rows at a time in a loop.

[joel24]

i'm not entirely sure what you're after either,

however you can update/delete more than one row at a time?

i.e.

mysql_query("UPDATE leagues SET status='1' WHERE id='$id1' OR id='$id2'");

 

but i suspect you want to select two affiliated rows and then do something with them.

[Psycho]

I still don't understand what you really want to do and I suspect there is a VERY easy solution with the proper query. But, I'll give you a solution based on what you already have.

 

while ($player1 = mysql_fetch_array($update) && $player2 = mysql_fetch_array($update))
{
    //Each iteration of the loop will pull two records from the
    //results and assign to $player1 and $player2.
    //Note: if uneven number of records are returned
    //the last record won't be processed

    //Insert
    //Delete
}

[Zepo.]

I still don't understand what you really want to do and I suspect there is a VERY easy solution with the proper query. But, I'll give you a solution based on what you already have.

 

while ($player1 = mysql_fetch_array($update) && $player2 = mysql_fetch_array($update))
{
    //Each iteration of the loop will pull two records from the
    //results and assign to $player1 and $player2.
    //Note: if uneven number of records are returned
    //the last record won't be processed

    //Insert
    //Delete
}

I believe this will work great thank you so much.

[dezkit]

I'm not sure but I think another solution can be a sql join, is that correct?

[Zepo.]

hmm so I have this:

if($timenow1 == $timenow5 && $timenow2 >= $timenow6){
            mysql_query("UPDATE leagues SET status='0' WHERE id='$id'");
			$update = mysql_query("SELECT * FROM leagueplayers WHERE league='$id'");
			while ($player1 = mysql_fetch_array($update) && $player2 = mysql_fetch_array($update))
			{
			echo $player1[id];
			echo"-";
			echo $player2[id];
		}
        }

And i got -5, the first ones not pulling anything...hmm

[dezkit]

if($timenow1 == $timenow5 && $timenow2 >= $timenow6){
            $update1 = mysql_query("UPDATE leagues SET status='0' WHERE id='$id'");
			$update2 = mysql_query("SELECT * FROM leagueplayers WHERE league='$id'");
			while ($player1 = mysql_fetch_array($update1) && $player2 = mysql_fetch_array($update2))
			{
			echo $player1[id];
			echo"-";
			echo $player2[id];
		}
        }

try this

[Zepo.]

No, the first update was working fine, player1[id] just wasnt displaying 4 like it should have.

[dezkit]

Wait are you trying to get two different rows?

 

 

 

Sorry, I'm confused at what your doings.

[Zepo.]

Insert another row with data from two rows, of the loop. What I posted almost works and is what I want but the first row doesnt seem to be working while the second row does. Its been a while since I've used PHP so i'm a bit rusty sorry

[Zepo.]

So player1 doesnt pull any info here:

	if($timenow1 == $timenow5 && $timenow2 >= $timenow6){
            mysql_query("UPDATE leagues SET status='0' WHERE id='$id'");
			$update = mysql_query("SELECT * FROM leagueplayers WHERE league='$id'");
			while ($player1 = mysql_fetch_array($update) && $player2 = mysql_fetch_array($update))
			{
			echo $player1[id];
			echo"-";
			echo $player2[id];
		}
        }

[joel24]

you've got a update query not assigned to any variable.

 

   if($timenow1 == $timenow5 && $timenow2 >= $timenow6){
            mysql_query("UPDATE leagues SET status='0' WHERE id='$id'"); //assign this to a variable?
//where is $id coming from?
          $update = mysql_query("SELECT * FROM leagueplayers WHERE league='$id'");

            while ($player1 = mysql_fetch_array($update) && $player2 = mysql_fetch_array($update)) //these are running the same query, so both variables in the loop will return the same result. 
            {
            echo $player1[id];
            echo"-";
            echo $player2[id];
         }
        }

 

you need something like

   if($timenow1 == $timenow5 && $timenow2 >= $timenow6){
            $update = mysql_query("UPDATE leagues SET status='0' WHERE id='$id'");
          $select1 = mysql_query("SELECT * FROM leagueplayers WHERE league='$id'");
$select2 = mysql_query("SELECT * FROM leagueplayers WHERE league='$id'");


            while ($player1 = mysql_fetch_array($select1) && $player2 = mysql_fetch_array($select2)) //these can't run the same query.
            {
            echo $player1[id];
            echo"-";
            echo $player2[id];
         }
        }

 

i'm still a bit baffled as to what you're trying to achieve. you want to run a loop and update player IDs within the loop? and somewhere in there you want to have it look at 2 players simultaneously?

[Zepo.]

Heres what I have so far

    //Start any leagues
$leagues=mysql_query("SELECT id,ladder,datestart,hourstart FROM leagues");
while(list($id,$ladderid,$datestart,$hourstart)=mysql_fetch_row($leagues)){

    $timenow = date('N', time());
    $timenow2 = date('H',time());	
if($timenow == $datestart && $timenow2 == $hourstart){
			$updatel = mysql_query("SELECT * FROM leagueplayers WHERE league='$id'");
		$update2 = mysql_query("SELECT * FROM leagueplayers WHERE league='$id'");
while ($player1 = mysql_fetch_array($updatel) && $player2 = mysql_fetch_array($update2))
			{
			$totalcountleag=mysql_query("SELECT COUNT(*) FROM leagueplayers WHERE league='$id'");
			$totalcountleag=mysql_fetch_array($totalcountleag);
			$totalcountleag="$totalcountleag[0]";

		if($totalcountleag >= 2){

			$playerd=mysql_query("SELECT * FROM teams WHERE id='$player1[team]'");
			$playerd=mysql_fetch_array($playerd);

			$player2d=mysql_query("SELECT * FROM teams WHERE id='$player2[team]'");
			$player2d=mysql_fetch_array($player2d);


			mysql_query("INSERT INTO challenges (ladderid, challgrid, challgdid, challgrname, challgdname, matchfinder) VALUES (
			'$ladderid','{$player1['team']}','{$player2['team']}','{$playerd['name']}','{$player2d['name']}','1')");

		//	$challenge_id=mysql_query("SELECT challid FROM challenges WHERE challgrname='$playerd[name]' AND challgdname='$player2d[name]'");
		//	$challenge_id=mysql_fetch_array($challenge_id);

		//	mysql_query("INSERT INTO chall_chat (chall_id,challenger,challenged,type)
	 	//	VALUES('$challenge_id[challid]','$player1[team]','$player2[team]','1')");

           // mysql_query("DELETE FROM leagueplayers WHERE id='$player1[id]'");
            //mysql_query("DELETE FROM leagueplayers WHERE id='$player2[id]'");
			}
		}
        }
    }

But the problem is it creates two rows in challenges when it should only create one. The zero on the first one should be the 405 and the first blank should be Gravy Boat and the second row shouldnt exist....

12107 54 0 407   (Blank) bling bling 2010-07-15 04:14:25 1

12108 54 0 405   (Blank) Gravy Boat 2010-07-15 04:14:25 1

[joel24]

as dezkit said, you should look into using a mysql JOIN, i think it will make your code a lot simpler

http://dev.mysql.com/doc/refman/5.0/en/join.html

[Zepo.]

I thought JOIN was for two different tables....

[joel24]

you can join the same table to itself, although this is only needed in certain situations and I'm not entirely sure if you need it. You need to describe your mysql table layout/schema and exactly what you're trying to achieve. i.e. user inserts row then row updates xxxx table to have xy field = valueX.

i.e.

$sql = @mysql_query("SELECT t1.*, t2.* FROM table1 t1 JOIN table1 t2 ON t1.columnLink = t2.columnLink");

 

 

[Zepo.]

Pull two rows from same table that have ID and Name.

Create a row in different table with both ids and names from first two.

Delete the original two rows

Make sense?

[joel24]

How are you selecting those two rows from the table, what denotes that those two rows are matched to one another and should be listed in the different table.

i.e. is there a field in the table say LinkID which contains the ID of the linked row?!

[Zepo.]

Took a break but still have this problem, the two matched rows are random where leagueid=whateever

So when a league is started it will go throught and randomly insert into the table from two of the players then delete.........

[Zepo.]

Anyone please? This seems so easy its just i cant get it to insert without inserting two rows.

[Zepo.]

Bump anone?

[dezkit]

IN REPLY TO:

Pull two rows from same table that have ID and Name.

Create a row in different table with both ids and names from first two.

Delete the original two rows

Make sense?

 

<?php
$query1 = "SELECT * FROM example1 LIMIT 0, 2"; 
$result1 = mysql_query($query1) or die(mysql_error());

while($row1 = mysql_fetch_array($result1)){
$query2 = "INSERT INTO example2 (id, name) VALUES('".$row1["id"]."', '".$row1["name"]."' ) "; 
$result2 = mysql_query($query2) or die(mysql_error());
$query3 = "DELETE FROM example1 WHERE id='".$row1["id"]."'"; 
$result3 = mysql_query($query3) or die(mysql_error());
}
?>

 

Is this what you wanted?

[Zepo.]

Thank you professor badass, were almost there but it should look similar to this, i know it doesnt work but maybe youll get the idea.

<?php
$query1 = "SELECT * FROM example1 LIMIT 0, 2"; 
$result1 = mysql_query($query1) or die(mysql_error());

while($row1 = mysql_fetch_array($result1)){
$query2 = "INSERT INTO example2 (id, name, id2, name2) VALUES('".$row1["id"]."', '".$row1["name"].",".$row2["id"].","$row2["name"]"' ) "; 
$result2 = mysql_query($query2) or die(mysql_error());
$query3 = "DELETE FROM example1 WHERE id='".$row1["id"]."'"; 
$query3 = "DELETE FROM example1 WHERE id='".$row2["id"]."'"; 

$result3 = mysql_query($query3) or die(mysql_error());
}
?>