select from one table insert to another

[GenSwat]

I am making my matchup.php

I am trying to take 2 names from one table and insert them to a new table in one row
like this now I am unsure what to do I know the foreach is probly incorrect

<?php
    $localhost = "localhost";
    $username  = "test";
    $password  = "test";
    $database  = "test";
    
        mysql_connect($localhost,$username,$password);
        mysql_select_db($database) or die( "Unable to select database");

  
    if (!mysql_select_db($database)) {
        echo "Unable to select db: " . mysql_error();
        exit;
    }

    $sql = "SELECT * FROM `users` ORDER BY RAND() limit 2";

    $result = mysql_query($sql);

    if (!$result) {
        echo "Could not successfully run query ($sql) from DB: " . mysql_error();
        exit;
    }

    if (mysql_num_rows($result) == 0) {
        echo "No rows found, nothing to print so am exiting";
        exit;
    }


    while ($row = mysql_fetch_assoc($result)) {
        extract($row);
      //echo $row["id"];
      //echo $row["username"];
        echo $username.'</br>';
    foreach($username as $username_1){
        mysql_query("INSERT INTO `matchups` ('tournament_id','username_1','username_2' ) VALUES ('',$username_1','$username_2')");
        
    }
}

    mysql_free_result($result);

?>

[Dominion]

ORDER BY RAND() <- you should never use order by RAND().

[jacek]

ORDER BY RAND() <- you should never use order by RAND().

True for large tables, but there are more pressing issues, like the use of a query in a while loop, which you should really never do.

The INSERT ... SELECT syntax may be able to do this in one single SQL query have a look here http://dev.mysql.com/doc/refman/5.0/en/ ... elect.html

[GenSwat]

Thanks did not know that, but this stumps me if no loop then how do I get all my usernames only from table users and insert them into new table matchups in columns username_1 and username_2

tried this and and column count mismatch I know this

<?php
    $localhost = "localhost";
    $username  = "testt";
    $password  = "test";
    $database  = "test";
    
        mysql_connect($localhost,$username,$password);
        mysql_select_db($database) or die( "Unable to select database");

  
    if (!mysql_select_db($database)) {
        echo "Unable to select db: " . mysql_error();
        exit;
    }

    //$sql1 = "INSERT INTO `matchups` ('username_1','username_2')SELECT * FROM `users` limit 2";
    $sql ="INSERT INTO `matchups` (username_1,username_2)SELECT users.*FROM users limit 2";

    $result = mysql_query($sql);

    if (!$result) {
        echo "Could not successfully run query ($sql) from DB: " . mysql_error();
        exit;
    }

    if (mysql_num_rows($result) == 0) {
        echo "No rows found, nothing to print so am exiting";
        exit;
    }


    
    
        mysql_query("INSERT INTO `matchups` ('tournament_id','username_1','username_2' ) VALUES ('',$username_1','$username_2')");
        
    


    mysql_free_result($result);

?>

what I am trying to do is pair up names that sign up so all the users in table users = 20 then matchups = 10 pairs

[jacek]

Okay this is an interesting problem which is a lame way of saying I am a bit stuck. So far I have this SQL

SET @row = 0;

SELECT
	`user_displayname`,
	`row_number`
FROM (
	SELECT
		`user_displayname`,
		@row := (@row + 1) AS `row_number`
	FROM `xhbb_users`
) AS `table`

Which just numbers the rows you get, the plan is to put the odd rows in the left column and the even ones in the right column ...

[jacek]

now have this

	SET @num = 0;
	SET @alt = 0;
	
	SELECT
		`users1`.`user_displayname` AS `left_name`,
		`users2`.`user_displayname` AS `right_name`
	FROM (
		SELECT `user_displayname`, `num`
		FROM (
			SELECT
				`user_displayname`,
				@num := if(@alt % 2 = 0, @num + 1, @num) AS `num`,
				@alt := (@alt + 1) AS `alt`
			FROM `xhbb_users`
		) AS `users`
		WHERE `alt` % 2 = 1
	) AS `users1`
	INNER JOIN (
		SELECT `user_displayname`, `num`
		FROM (
			SELECT
				`user_displayname`,
				@num := if(@alt % 2 = 0, @num + 1, @num) AS `num`,
				@alt := (@alt + 1) AS `alt`
			FROM `xhbb_users`
		) AS `users`
		WHERE `alt` % 2 = 0
	) AS `users2`
	ON `users1`.`num` = `users2`.`num`

Which I think the idea should work, but this returns 0 rows as @num just keeps going up... Also it's getting very complicated !

If you want to go with the php solution, you need to build up the query and run if after the loop, a single INSERT can inset any number of rows.

INSERT INTO `table` (`columns`, `go`, `here`)
VALUES
('rows', 'go', 'here'),
('comma', 'separated', 'list'),
('of', 'rows', '!')

To randomize them, it would be easiest to do it with array manipulation...

$result = mysql_query('SELECT `name` FROM `users`');

while ($row = mysql_fetch_assoc($result)){
	$users[] = $row['name'];
}

shuffle($users);

[GenSwat]

yeah i tried and tried this is a tough one I can get the id and username 1 of them to move but can't get 2 usernames 2 insert

[jacek]

yeah i tried and tried this is a tough one I can get the id and username 1 of them to move but can't get 2 usernames 2 insert

Well I worked out the select...

SET @num1 = 0;
SET @num2 = 1;
SET @alt1 = 0;
SET @alt2 = 0;

SELECT
        `users1`.`user_displayname` AS `left_name`,
        `users2`.`user_displayname` AS `right_name`
FROM (
        SELECT `user_displayname`, `num`
        FROM (
                SELECT
                        `user_displayname`,
                        @num1 := (@num1 + 1) AS `num`,
                        @alt1 := (@alt1 + 1) AS `alt`
                FROM `xhbb_users`
        ) AS `users`
        WHERE `alt` % 2 = 1
) AS `users1`
INNER JOIN (
        SELECT `user_displayname`, `num`
        FROM (
                SELECT
                        `user_displayname`,
                        @num2 := (@num2 + 1) AS `num`,
                        @alt2 := (@alt2 + 1) AS `alt`
                FROM `xhbb_users`
        ) AS `users`
        WHERE `alt` % 2 = 0
) AS `users2`
ON `users1`.`num` = `users2`.`num`

So you could try using that if you are feeling brave. Or post a more detailed description of the problem you have with the php method and I will try to help with that