Page 1 of 1

create * mysql rows

Posted: Thu Jul 27, 2006 5:14 am
by FE
hello,
i am looking for a sql command to populate a database with a set amount of rows
for example:

on a form a button is pressed and it will execute code to
Create 50 rows in a database containing 1 or 2 fields of constant data:

ID DATA1 DATA2
1 233
2 233
3 233
4 233
5 233
6 233

i'm not sure if it will make a difference but the amount of rows needed to be created will be differnet each time and be from a variable, each created row will have some duplicated data (which will be used to create the initial rows), non-duplicated data will be filled in from another page.

Thanks again for your help

Posted: Thu Jul 27, 2006 5:19 am
by jamiel
A simple loop with a counter in PHP will achieve this for you.

Posted: Thu Jul 27, 2006 5:19 am
by SimonJ621
I'm not sure what scrypting language you're using, but you could just use a for loop.

Posted: Thu Jul 27, 2006 5:24 am
by FE
i'm using PHP
i also need it to check if the database contains a specific number (id number) before populating the database so users can't populate the database with the same data twice

any sample code would definitly help
and cheers for the quick replies!!

Posted: Thu Jul 27, 2006 5:37 am
by SimonJ621
As a start...

Code: Select all

$numRows = $_POST["numRows"];
for ( count = 0; count < $numRows; count++) {

     Connect to DB;
     Connect to Table;
     Insert Data;

}
As far as having a unique ID... just use auto_increment and set that as the Primary key.

Hope that helps,

Jason

Posted: Thu Jul 27, 2006 5:56 am
by SimonJ621
FE wrote: i also need it to check if the database contains a specific number (id number) before populating the database so users can't populate the database with the same data twice
Maybe I didn't quite understand this...

If you want to see if the id number that the user is using is already in the database, you'll need to run a statement like:

$number = $_POST["number"];

SELECT id FROM table WHERE id = $number

and then check to see if that returns rows... if it returns rows, that number is already in the database.

However, if you only wanted to make sure that each input has it's own original number, you can set-up your table to auto_increment the id field. Use this as your primary key and everything will be primo!

Jason

Posted: Thu Jul 27, 2006 6:08 am
by FE
hello yeah cheers i have done that
the current code looks like this:

Code: Select all

<?php
require_once ('../mysql_connect.php');
mysql_select_db("batch");

$sql = "SELECT GRN_NUMBER FROM GRN_ITEM WHERE GRN_NUMBER='$grnnumber'";
$result = mysql_query($sql);
if (mysql_num_rows($result) == 0) {
	
		for ( count = 0; count < $QTY_RECEIVED; count++) { 

		$query2 = "INSERT INTO batch (batchno) VALUES ($batchid)";
		$result = @mysql_query($query2);

		} if ($result) { // If it ran OK.
		
		Echo 'Database successfully populated with new data';
	
		}else { // If it did not run OK.
			$errors[] = 'The database was not populated due to an error with accessing the database'; // Public message.
			$errors[] = mysql_error() . '<br /><br />Query: ' . $query; // Debugging message.
				}
				
		} else { // database already populated already
		$errors[] = 'ERROR! You have already populated the database';
}
		

mysql_close(); 


?>
Currently i am getting an error of:

Parse error: parse error, unexpected '=', expecting ';' in d:\Inetpub\wwwroot\popbatch.php on line 19

line 19 = "for ( count = 0; count < $QTY_RECEIVED; count++) { "

cheers

Posted: Thu Jul 27, 2006 6:19 am
by onion2k

Code: Select all

create function insertFiftyThings(myvalue int) returns VARCHAR(30)
begin

	declare fiftyLoop int default 0;

	while fiftyLoop < 50 do

		set fiftyLoop := fiftyLoop + 1;
		insert into table VALUES (myvalue);

	end while;
	
	return concat(’Inserted ’,myvalue,’ ’,fiftyLoop,’ items’);

end
I think that would work.. not got MySQL 5 handy to try it out.

Posted: Thu Jul 27, 2006 6:28 am
by SimonJ621
FE wrote: Currently i am getting an error of:

Parse error: parse error, unexpected '=', expecting ';' in d:\Inetpub\wwwroot\popbatch.php on line 19

line 19 = "for ( count = 0; count < $QTY_RECEIVED; count++) { "

cheers
You forgot the $ in front of count. It should look like:

"for ($count = 0; count < $QTY_RECEIVED; count++) { "

Hope that solves it :)

Jason

Posted: Thu Jul 27, 2006 6:28 am
by FE
onion2k wrote:

Code: Select all

create function insertFiftyThings(myvalue int) returns VARCHAR(30)
begin

	declare fiftyLoop int default 0;

	while fiftyLoop < 50 do

		set fiftyLoop := fiftyLoop + 1;
		insert into table VALUES (myvalue);

	end while;
	
	return concat(’Inserted ’,myvalue,’ ’,fiftyLoop,’ items’);

end
I think that would work.. not got MySQL 5 handy to try it out.
sorry i should of posted this in PHP forum as i am looking for PHP code
Could a mod move this to the PHP forum please?

Posted: Thu Jul 27, 2006 6:52 am
by FE
SimonJ621 wrote:
FE wrote: Currently i am getting an error of:

Parse error: parse error, unexpected '=', expecting ';' in d:\Inetpub\wwwroot\popbatch.php on line 19

line 19 = "for ( count = 0; count < $QTY_RECEIVED; count++) { "

cheers
You forgot the $ in front of count. It should look like:

"for ($count = 0; count < $QTY_RECEIVED; count++) { "

Hope that solves it :)

Jason
Alright jason, yeah my mistake now i get another error

Parse error: parse error, unexpected T_INC, expecting ')' in d:\Inetpub\wwwroot\popbatch.php on line 19
The Code is and again line 19 is: "for ( $count = 0; count < $n; count++ ) { "

Code: Select all

$sql = "SELECT QTY_RECEIVED FROM GRN_ITEM WHERE GRN_NUMBER='$grnnumber'";
$result = mysql_query($sql);
if (mysql_num_rows($result) == 0) {

		$n = $row['QTY_RECEIVED'];
		for ( $count = 0; count < $n; count++ ) { 

		$query2 = "INSERT INTO batch (batchno) VALUES ($batchid)";
		$result1 = @mysql_query($query2);

		} if ($result1) { // If it ran OK.
		
		Echo 'Database successfully populated with new data';
	
		}else { // If it did not run OK.
			$errors[] = 'The database was not populated due to an error with accessing the database'; // Public message.
			$errors[] = mysql_error() . '<br /><br />Query: ' . $query; // Debugging message.
				
				
		} else { // error if database already populated before
		$errors[] = 'ERROR! You have already populated the database';
		}
}

Posted: Thu Jul 27, 2006 7:23 am
by SimonJ621
Parse errors are pretty much always human error. T_INC has to do with the ++ part of the scrypt. Knowing this, you can see that you forgot to add the $ before count the second and third time. I hope that fixes your problem.

now:
"for ( $count = 0; count < $n; count++ ) { "

should:
"for ( $count = 0; $count < $n; $count++ ) { "

For more info on parse errors, and a good reference, check out this site:

http://us2.php.net/manual/en/tokens.php#tokens

Jason