create * mysql rows

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
FE
Forum Commoner
Posts: 35
Joined: Fri Jul 14, 2006 5:21 am

create * mysql rows

Post 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
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

A simple loop with a counter in PHP will achieve this for you.
Last edited by jamiel on Thu Jul 27, 2006 5:20 am, edited 1 time in total.
User avatar
SimonJ621
Forum Commoner
Posts: 36
Joined: Thu Jul 27, 2006 5:07 am
Location: Ohio

Post by SimonJ621 »

I'm not sure what scrypting language you're using, but you could just use a for loop.
FE
Forum Commoner
Posts: 35
Joined: Fri Jul 14, 2006 5:21 am

Post 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!!
User avatar
SimonJ621
Forum Commoner
Posts: 36
Joined: Thu Jul 27, 2006 5:07 am
Location: Ohio

Post 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
User avatar
SimonJ621
Forum Commoner
Posts: 36
Joined: Thu Jul 27, 2006 5:07 am
Location: Ohio

Post 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
FE
Forum Commoner
Posts: 35
Joined: Fri Jul 14, 2006 5:21 am

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
User avatar
SimonJ621
Forum Commoner
Posts: 36
Joined: Thu Jul 27, 2006 5:07 am
Location: Ohio

Post 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
FE
Forum Commoner
Posts: 35
Joined: Fri Jul 14, 2006 5:21 am

Post 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?
FE
Forum Commoner
Posts: 35
Joined: Fri Jul 14, 2006 5:21 am

Post 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';
		}
}
User avatar
SimonJ621
Forum Commoner
Posts: 36
Joined: Thu Jul 27, 2006 5:07 am
Location: Ohio

Post 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
Post Reply