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


[syntax=php]<?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);

?>

[/syntax]

[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

[syntax=php]<?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);

?>[/syntax]

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

[syntax=sql]SET @row = 0;

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

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

[syntax=sql] 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`[/syntax]
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.

[syntax=sql]INSERT INTO `table` (`columns`, `go`, `here`)
VALUES
('rows', 'go', 'here'),
('comma', 'separated', 'list'),
('of', 'rows', '!')[/syntax]

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

[syntax=php]$result = mysql_query('SELECT `name` FROM `users`');

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

shuffle($users);[/syntax]

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

[syntax=sql]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`
[/syntax]

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