[RESOLVED] MySQL While { stuff }

[LiL|aaron]

Hi,

Seem to be having a small probelm, if i run the script below, works fine all the users shows up


<?

//DB STUFF

$connection = mysql_connect($dbhost, $dbuser, $dbpasswd);
mysql_select_db($dbname);

$sql ="SELECT * FROM PHPAUCTION_users";
$result = mysql_query($sql) or die(mysql_error());
while ($sql = mysql_fetch_object($result))

{

$user = $sql -> nick;
$pass = $sql -> password;
$email = $sql -> email;

echo "$user - $pass - $email<BR>";

}

?>

but if i run this one it doesnt do every user, only the first one


<?

//DB STUFF

$connection = mysql_connect($dbhost, $dbuser, $dbpasswd);
mysql_select_db($dbname);

$sql ="SELECT * FROM PHPAUCTION_users";
$result = mysql_query($sql) or die(mysql_error());
while ($sql = mysql_fetch_object($result))

{

$user = $sql -> nick;
$pass = $sql -> password;
$email = $sql -> email;

$results = "INSERT INTO phpbb_users (user_id, username, user_password, user_email)
VALUES ('$user_id', '".$user."', '".$pass."', '$email')";
$ress=mysql_query ($results) or die(mysql_error());

$sql ="SELECT * FROM phpbb_users";
$result = mysql_query($sql) or die(mysql_error());
while ($sql = mysql_fetch_object($result))

{

$user_id = $sql -> user_id;

}


$results1 = "INSERT INTO phpbb_groups (group_name, group_description, group_single_user, group_moderator)
VALUES ('', 'Personal User', 1, 0)";
$resss=mysql_query ($results1) or die(mysql_error());

$results2 = "INSERT INTO phpbb_user_group (user_id, group_id, user_pending)
VALUES ('$user_id', '', '0')";
$ressss=mysql_query ($results2) or die(mysql_error());

echo "$user - $pass - $email<BR>";

}

?>

Thanks for any help!
Aaron

[The Little Guy]

How about something like this:
<?php

//DB STUFF

$connection = mysql_connect($dbhost, $dbuser, $dbpasswd);
mysql_select_db($dbname);

$sql ="SELECT * FROM PHPAUCTION_users";
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_array($result))
{
echo"$row[column1] $row[column2]<br>";
}
?>

[LiL|aaron]

Hi

I tried that, and it is still only doing the 1, it should be doing every one of them


<?

// DB STUFF

$connection = mysql_connect($dbhost, $dbuser, $dbpasswd);
mysql_select_db($dbname);

$sql ="SELECT * FROM PHPAUCTION_users";
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_array($result))
{

$user = $row[nick];
$pass = $row[password];
$email = $row[email];

$results = "INSERT INTO phpbb_users (user_id, username, user_password, user_email)
VALUES ('$user_id', '".$user."', '".$pass."', '$email')";
$ress=mysql_query ($results) or die(mysql_error());

$sql ="SELECT * FROM phpbb_users";
$result = mysql_query($sql) or die(mysql_error());
while ($sql = mysql_fetch_object($result))

{

$user_id = $sql -> user_id;

}


$results1 = "INSERT INTO phpbb_groups (group_name, group_description, group_single_user, group_moderator)
VALUES ('', 'Personal User', 1, 0)";
$resss=mysql_query ($results1) or die(mysql_error());

$results2 = "INSERT INTO phpbb_user_group (user_id, group_id, user_pending)
VALUES ('$user_id', '', '0')";
$ressss=mysql_query ($results2) or die(mysql_error());

echo"$row[nick] - $row[password] - $row[email]<br>";

}

?>


Thanks
Aaron

[The Little Guy]

You need to put
echo"$row[nick] - $row[password] - $row[email]<br>";

into a while loop

<?

// DB STUFF

$connection = mysql_connect($dbhost, $dbuser, $dbpasswd);
mysql_select_db($dbname);

$sql ="SELECT * FROM PHPAUCTION_users";
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_array($result))
{

$user = $row[nick];
$pass = $row[password];
$email = $row[email];

$results = "INSERT INTO phpbb_users (user_id, username, user_password, user_email)
VALUES ('$user_id', '".$user."', '".$pass."', '$email')";
$ress=mysql_query ($results) or die(mysql_error());

$sql ="SELECT * FROM phpbb_users";
$result = mysql_query($sql) or die(mysql_error());
while ($sql = mysql_fetch_object($result))

{

$user_id = $sql -> user_id;

}


$results1 = "INSERT INTO phpbb_groups (group_name, group_description, group_single_user, group_moderator)
VALUES ('', 'Personal User', 1, 0)";
$resss=mysql_query ($results1) or die(mysql_error());

$results2 = "INSERT INTO phpbb_user_group (user_id, group_id, user_pending)
VALUES ('$user_id', '', '0')";
$ressss=mysql_query ($results2) or die(mysql_error());

$get_rows ="SELECT * FROM phpbb_users";
$query_rows = mysql_query($get_rows) or die(mysql_error());
while ($row = mysql_fetch_array($query_rows))
{
echo"$row[nick] - $row[password] - $row[email]<br>";
}
}

?>

[LiL|aaron]

I am not sure if your understanding what i am trying to do

for every

$row[nick] - $row[password] - $row[email]

its needs to run this:


$results = "INSERT INTO phpbb_users (user_id, username, user_password, user_email)
VALUES ('$user_id', '".$user."', '".$pass."', '$email')";
$ress=mysql_query ($results) or die(mysql_error());

$sql ="SELECT * FROM phpbb_users";
$result = mysql_query($sql) or die(mysql_error());
while ($sql = mysql_fetch_object($result))

{

$user_id = $sql -> user_id;

}


$results1 = "INSERT INTO phpbb_groups (group_name, group_description, group_single_user, group_moderator)
VALUES ('', 'Personal User', 1, 0)";
$resss=mysql_query ($results1) or die(mysql_error());

$results2 = "INSERT INTO phpbb_user_group (user_id, group_id, user_pending)
VALUES ('$user_id', '', '0')";
$ressss=mysql_query ($results2) or die(mysql_error());


thats why i had the code like this, the $row[nick] - $row[password] - $row[email]


<?

// DB STUFF

$connection = mysql_connect($dbhost, $dbuser, $dbpasswd);
mysql_select_db($dbname);

$sql ="SELECT * FROM PHPAUCTION_users";
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_array($result))
{

$user = $row[nick];
$pass = $row[password];
$email = $row[email];

$results = "INSERT INTO phpbb_users (user_id, username, user_password, user_email)
VALUES ('$user_id', '".$user."', '".$pass."', '$email')";
$ress=mysql_query ($results) or die(mysql_error());

$sql ="SELECT * FROM phpbb_users";
$result = mysql_query($sql) or die(mysql_error());
while ($sql = mysql_fetch_object($result))

{

$user_id = $sql -> user_id;

}


$results1 = "INSERT INTO phpbb_groups (group_name, group_description, group_single_user, group_moderator)
VALUES ('', 'Personal User', 1, 0)";
$resss=mysql_query ($results1) or die(mysql_error());

$results2 = "INSERT INTO phpbb_user_group (user_id, group_id, user_pending)
VALUES ('$user_id', '', '0')";
$ressss=mysql_query ($results2) or die(mysql_error());

echo"$row[nick] - $row[password] - $row[email]<br>";

}

?>


is already in the while loop, basically what i am saying its not looping, its only do it once.

[The Little Guy]

Then, instead of a while, maybe you would like to try a foreach loop instead

Not Sure If this will work or not

$rows = mysql_fetch_array($result)
foreach($rows as $row)
{
#your code here
}

[LiL|aaron]

Nah... its just giving me the same row, over and over again :(

[The Little Guy]

I made the same thing, almost, only mine just displays the users, and here is what I did:


$user = "SELECT * FROM users";
$user_query = mysql_query($user);
while($row = mysql_fetch_array($user_query)){
echo"$row[user] - $row[pass] - $row[email]<br>";

}
and here is what it prints out(there are real things where the x's are, I just removed them for privacy reasons):
admin - xxx - xxx@gmail.com<br>
kanos - xxx - xxx@yahoo.com<br>
demo - demo - demo@demo.com<br>


You could change this

$user = $row[nick];
$pass = $row[password];
$email = $row[email];

$results = "INSERT INTO phpbb_users (user_id, username, user_password, user_email)
VALUES ('$user_id', '".$user."', '".$pass."', '$email')";

to this
$results = "INSERT INTO phpbb_users (user_id, username, user_password, user_email)
VALUES ('$user_id', '$row[nick]', '$row[password]', '$row[email]')";

[LiL|aaron]

Its still only doing the first row, the first code in the last message that works fine for me too, its shows them all, but when i add the mysql inserts it only wants to do it once and thats it, and not looping back to get row 2 ect ect


<?

//DB STUFF

$connection = mysql_connect($dbhost, $dbuser, $dbpasswd);
mysql_select_db($dbname);

$sql ="SELECT * FROM PHPAUCTION_users";
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_array($result))
{

$results = "INSERT INTO phpbb_users (user_id, username, user_password, user_email)
VALUES ('$user_id', '$row[nick]', '$row[password]', '$row[email]')";
$ress=mysql_query ($results) or die(mysql_error());

$sql ="SELECT * FROM phpbb_users";
$result = mysql_query($sql) or die(mysql_error());
while ($sql = mysql_fetch_object($result))
{
$user_id = $sql -> user_id;
}

$results1 = "INSERT INTO phpbb_groups (group_name, group_description, group_single_user, group_moderator)
VALUES ('', 'Personal User', 1, 0)";
$resss=mysql_query ($results1) or die(mysql_error());

$results2 = "INSERT INTO phpbb_user_group (user_id, group_id, user_pending)
VALUES ('$user_id', '', '0')";
$ressss=mysql_query ($results2) or die(mysql_error());

echo "$row[nick] - $row[password] - $row[email]<br>";

}

?>

[NogDog]

You're using $result to store the result of both the initial query and the second query inside of the main while loop. Therefore that second query is overwriting the first, and you loop through to the end of it in the inner while loop, thus the main outer loop thinks it's done when it checks to see if it needs another iteration. Try using different (descriptive) variable names to differntiate the two query result sets, and see if that clears up your problem.

[LiL|aaron]

Ya thanks that worked!!

but....

Fatal error: Maximum execution time of 30 seconds exceeded

Is there a way i can set where the mysql selects restarts from? like...


$sql ="SELECT * FROM PHPAUCTION_users WHERE nick='name'";
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_array($result))
{

[LiL|aaron]

Hi... i worked it out...


$sql ="SELECT * FROM PHPAUCTION_users LIMIT 1147, 373";
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_array($result))
{


Thanks for all the help guys!!
Aaron