BetterPHP

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);

?>

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

Dominion wrote: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

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

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 ...

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);

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

GenSwat wrote: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