[SOLVED] looping question

[Custer]

I currently working on a loop, which I know is selecting the information correctly, but I can't seem to put the insert commands in.

 

$num=rand(1, 20);
$result = mysql_query("SELECT * FROM initialstats WHERE id='$num'");
$initial_stats = mysql_fetch_assoc($result);

while($info = mysql_fetch_assoc( $result )) 
{  
INSERT INTO user_resources (user_id, restype, resamount)
VALUES('$id', '.$info['restype'] .', '.$info['resamount'].');
} 

 

This is waht I'm trying to do..but it isn't inserting anything.

 

But, when I try this code:

 

$num=rand(1, 20);
$result = mysql_query("SELECT * FROM initialstats WHERE id='$num'");
$initial_stats = mysql_fetch_assoc($result);

while($info = mysql_fetch_assoc( $result )) 
{  
Print "<th>id:</th> ".$info['id'] . ""; 
Print "<th>restype:</th> <td>".$info['restype'] . ""; 
Print "<th>Market Sell:</th> ".$info['resamount'] . ""; 
} 

 

It displays the array of the information, and it is all there, so how do I go from showing the information to inserting all of it?

[pocobueno1388]

You have to actually execute the command with mysql_query().

 

<?php

$num=rand(1, 20);
$result = mysql_query("SELECT * FROM initialstats WHERE id='$num'");
//$initial_stats = mysql_fetch_assoc($result); <---Why do you have this line?

while($info = mysql_fetch_assoc( $result )) 
{  
$query = "INSERT INTO user_resources (user_id, restype, resamount)
VALUES('$id', '{$info['restype']} ', '{$info['resamount']}'";

mysql_query($query)or die(mysql_error());
} 

?> 

 

 

[clearstatcache]

try dis 1....

 

$num=rand(1, 20);

$result = mysql_query("SELECT * FROM initialstats WHERE id='$num'");

$initial_stats = mysql_fetch_assoc($result);

 

while($info = mysql_fetch_assoc( $result ))

{ 

mysql_query(" INSERT INTO user_resources (user_id, restype, resamount)

                    VALUES('$id', '.$info['restype'] .', '.$info['resamount'].')

                  ");

}

[Custer]

Yes, I had tried that one before:

 

mysql_query(" INSERT INTO user_resources (user_id, restype, resamount)
                    VALUES('$id', '.$info['restype'] .', '.$info['resamount'].')
                   ");

 

And still didn't get any luck..:s

[pocobueno1388]

Did you try my code?

[Custer]

Yes, just did and was given this:

 

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2

 

but line 2 of my register script is this:

 

include ('configure.php');

 

and that was never a problem before....

[pocobueno1388]

That error just means there is a problem with your syntax in your query. I accidentally missed a closed parenthesis.

 

<?php

$num=rand(1, 20);
$result = mysql_query("SELECT * FROM initialstats WHERE id='$num'");
//$initial_stats = mysql_fetch_assoc($result); <---Why do you have this line?

while($info = mysql_fetch_assoc( $result )) 
{  

$query = "INSERT INTO user_resources (user_id, restype, resamount)
VALUES('$id', '{$info['restype']}', '{$info['resamount']}')";

mysql_query($query)or die(mysql_error());
} 

?> 

 

[Custer]

Okay, that cleared up, but now I have this error:

 

Unknown column 'restype' in 'field list'

 

Which is odd because it showed up when I used the Print command...

[pocobueno1388]

You might want to re-check your spelling, because that means that there is no such column as "restype" in your table "user_resources". I would go double check that.

[Custer]

Nope, it's right...Here's the entire code, before I added the loop, everything worked perfectly, the buildings inserted with the correct ID, but now they won't even work...

 

<?php
include ('configure.php');
mysql_connect ("$dbhost", "$dbuser", "$dbpass")or die("Could not connect: ".mysql_error());
mysql_select_db("$dbname") or die(mysql_error());

include 'register.html';   
$email = $_POST['email'];    
$username = $_POST['username'];
$password = md5($_POST['password']);

$username = mysql_escape_string($username);
$email = mysql_escape_string($email);

// lets check to see if the username already exists

$checkuser = mysql_query("SELECT username FROM users WHERE username='$username'"); 

$username_exist = mysql_num_rows($checkuser);

if($username_exist == 1) {
    echo "I'm sorry but the username you specified has already been taken.  Please pick another 

one.";
    unset($username);
    include 'register.html';
    exit();
} 

// lets check to see if the username already exists

$checkemail = mysql_query("SELECT email FROM users WHERE email='$email'"); 

$email_exist = mysql_num_rows($checkemail);

if($email == 1) {
    echo "I'm sorry but the email you specified has already been taken.  Please pick another 

one.";
    unset($email);
    include 'register.html';
    exit();
} 

// lf no errors present with the username
// use a query to insert the data into the database.

mysql_query("INSERT INTO users (id, email, username, password)
VALUES('$id', '$email', '$username', '$password')");


//random id
$num=rand(1, 20);
$result = mysql_query("SELECT * FROM initialstats WHERE id='$num'");
$initial_stats = mysql_fetch_assoc($result); 

while($info = mysql_fetch_assoc( $result )) 
{  

$query = "INSERT INTO user_resources (user_id, restype, resamount)
VALUES('$id', '{$info['restype']}', '{$info['resamount']}')";

mysql_query($query)or die(mysql_error());
} 



//defines variables
$building_id = $initial_stats['building_id'];
$restype = $initial_stats['restype'];
$resamount = $initial_stats['resamount'];

//fetches user_id
$query = mysql_query("SELECT id FROM users WHERE username = '".$username."'");
$users = mysql_fetch_assoc($query);
$id = $users['id'];


//insertion to user_buildings
mysql_query("INSERT INTO user_buildings (user_id, building_id, amount)
VALUES('$id', '$building_id', '1')");

mysql_close ();

echo "You have successfully Registered";
?>

[clearstatcache]

bwt dis 1....

 

$num=rand(1, 20);

$result = mysql_query("SELECT * FROM initialstats WHERE id='$num'");

$initial_stats = mysql_fetch_assoc($result);

 

while($info = mysql_fetch_assoc( $result ))

{

$type = $info['restype'];

$amount = $info['resamount'];

mysql_query(" INSERT INTO user_resources (user_id, restype, resamount)

                    VALUES('$id', '$type', '$amount')

                  ");

}

[JayBachatero]

I recommend that you use something like this instead of doing all those queries you can do what you want with just one query.

 

<?php
$insert = '';
while ($info = mysql_fetch_assoc($result))
{
$type = $info['restype'];
$amount = $info['resamount'];
$insert .= "
	('$id', '$tpe', '$amount'),";
}

mysql_query("
INSERT INTO user_resources
	(user_id, restype, resamount)
        VALUES" . substr($insert, 0, -1));
?>

[Custer]

clearstat, I just tried your code, and it acts like it's completely working, but alas, it's not inserting anything into user_resources...

 

Jayb...what would I put in the $insert label? Because I'm having several things going on in this code...

[clearstatcache]

how about checking ur database table structure.....

[clearstatcache]

try to insert the user_id, restype, and resamount into user_resources the stuff u print if it will work .....to make sure that there's nothing wrong with ur table...

[Custer]

I know the table's correct, because I can print out the array just fine...it's just inserting it, it just won't work.

[Custer]

Fixed it guys! Thanks for all of your help!