multiple inserts into mysql

gvtlinux

MySQL multiple insert
Posted by Nameless Nobody (gvt) on Friday
Hello All: I have MySql up and working and have created a table within a database that is just serial numbers. I want to prefill the table with 1,000,000 serial
numbers. I can enter one serial number at a time just fine with the command:

mysql> INSERT INTO serial VALUES();

The serial number is AUTO_INCREMENT PRIMARY KEY NOT NULL

Is there an easier way within mysql to repeat this command 1,000,000 times? or better how would I code that in PHP. I am not sure what to look for in answering this question.

Thanks
gvt

Anon

uhh... a million?

you could do

for ($i = 0; $i < 1000000; $i++) {
//insert here
}

you may need to set_time_limit() if it takes awhile..

gvtlinux

Thank you Nola for the for loop suggestion. I am obviously failing to grasp the usage of the loop process here because it is giving erratic results. Adding two serial numbers on th first execution and only one there after. My book recommends an if statement to keep the memory and cpu from being saturated. So far that has not occured. Any suggestions while I continue to work this one out?

Thank you

gvt

[deleted]

Why would you want to prefill a table if the numbers are auto_increment? what's the point?

gvtlinux

We are building 6 different imbeded products. CIO wants a unique serial number for each product. So Each purduct has a product name and an associated product id within it own table. Customers receiving these products are referenced within their own tables with the product id and serial number. If I build a table for each product and assign a serial number there each table would begin with serial number one. I also have test results tables that reference serial numbers and product id's. Then there are cvs tables for the software that is incorporated within the individual products and referenced by theor own product id's. Having a table with serial numbers prebuilt makes it easy to pointto that serial number for a query and its associated records. If you have a better idea I will listen. I could use some pointers on database design.

Thanks gvt

[deleted]

"So Each purduct has a product name and an associated product id within it own table."

Why does it have it's own table?

You may want to search the net for some totrials on 'database normalization'.

If you have only 6 products, you'd put those 6 in one table;
productname | productnumber

All other information about these products references that table through the product's number.

"Then there are cvs tables for the software that is incorporated within the individual products and referenced by theor own product id's."

These would also be in their own table, and the link between the product and the software would be stored in a 'link' table that holds one productid and one softwareid per row.

A sidenote: if you make six tables of 1M rows, and each row is 500 bytes, you will have 6 datafiles of 500MB each. In short: no performance whatsoever.

gvtlinux

I must of made an error in explaining hwo my product table is configured. It is in fact as you described.
Product_ name | productid
product_A | 1

product_B | 2

and so on.

Another table picks up the productid, serial number, shipping carrier, status and testing_results from those tables. Adds in construction date shipping address tracking number to make a complete record.

Now on top of attempting to create the 1,000,000 serial numbers I now am not able to view an individual record through the browser. I can from the mysql command line issue
select * from project where serial = 1
and recieve the record of serial number one. But from the browser I enter the command syntax in a submit box and it returns nothing. In the sample below is the php script for table project in datase ourdb. Pulls up all records just fine bu incapable of refining the search to just one entry.

<?php

if(!isset($query) || empty($query))
{$query = "select * from project";}

$query=stripslashes($query);

    $usr = "me";
    $pwd = "secret";
    $db = "ourdb";
    $host = "guess";

# connect to database
    $cid = mysql_connect($host,$usr,$pwd);
    if (!$cid) { echo("ERROR: " . mysql_error() . "\n");    }

mysql_select_db("apiqs") or
die ("Can not Select project database");
$result = mysql_query($query)
or die ( mysql_error() );

$number_cols = mysql_num_fields($result);

echo "<b>query: $query</b>";

// table header
echo "<table border = 1>\n";
echo "<tr align=center>\n";

for ($i=0; $i<$number_cols; $i++)
{
echo "<th>" . mysql_field_name($result, $i). "</th>\n";
}
echo "</tr>\n";

//end table header

//tablebody construction
while ($row = mysql_fetch_row($result))
{
echo"<tr aligh=left>\n";
for ($i=0; $i<$number_cols; $i++)
{
echo "<td>";
if (!isset($row[$i])) //test for null value
{echo "NULL";}
else
{echo $row[$i];}
echo "</td>\n";
}
echo "</tr>";
}
// end tablebody construction
echo "</table>";

?>
// begin new submition
<?php
if (isset($submit) && $submit=="yes")
{
echo "thank you for submitting your form.";
} else {
?>
<form action="<? echo $PHP_SELF?> method="get">
<input tupe="text" name="query" size="80"><br>
<input type="submit">

</form>
<?php
// end new submission
}
?>

[deleted]

"I can from the mysql command line issue
select * from project where serial = 1"

Are you sure the query is passed to the script properly, and that you are in fact using the correct database on the correct server?

Anon

If the serial number is that important to you, I would suggest not using the auto_increment feature of mysql.

Write some code that would generate the ID and then insert it.

[deleted]

The only 'safe' serial is the mysql-generated one, all the others can only be trusted if they have been pre-generated, which is not a good idea to do.

gvtlinux

Well no, I am not sure the query is passed up the script properly.

I am sure the database selected in this code is correct. This page when viewed retrieves the entire table from the database. That small bit of php code at the end, I was led to believe through the documentation of MySQL/PHP Database Applications by Jay Greenspan and Brad Bulger passed the new submit information to a new query via the $query as outlined in their publication. At the moment that is the model with which I am attempting to run successfully. What I am looking for is guidance in how to properly pass the new query issued by a new submition dialog at the end of this code that will retrieve a single entry based on a where condition. Unless that is of course there is another way to achieve the same results.

Thank you

gvt

gvtlinux

Thank you Vincent I have resolved the query problem.

The part that caused this code to fail was in the stripslashes function. It seems that if magic quotes is turned off it can by itself prevent the query from taking place. The otherside of that is if Magic Quotes is turned on stripslashes can remove the escaping performed by magic quotes.

I reference Kevin Yank on an article titled Give me back my MySQL Command Line. In this article Kevin makes it quite clear the problem undocumented anywhere else with stripslashes and how to overcome it.

Thanks
gvt

gvtlinux

When you say generate the id. I take it you are not refering to an id field within the serial number table itself .

In which case how would I point that id to the serial number table with the pregenerated serial numbers or are you suggesting that the id by itself generate the serial numbers? Not sure where you are going here.

gvt