Query add 1 x times

mwarren

I want to have a form that says
My name ___________
number of entries __

If I chose 20 entries I want to have a script query the last numerical entry and add 20 entries into mysql
If I chose 40 then add 40 and so forth.

Can someone help me with this?

Matt

jheitz

Could you expand the question a little bit? I can't really understand what you mean

mwarren

If I have a db with the following fields: id (primary,auto-increment) , username, phonenumber and listing number. id, username,phonenumber will all be passed variables to a form. The form will then ask "how many listings do you want". I need to do a query that says, select * from database where 'phonenumber' = $http_post_var "phonenumber" sort DESC . Grab the last largest number in the query. say 1010 , "how many listings do you want" if I entered 20 listings I want the databese to enter $username , $phonenumber , $listingnumber 20 times until I now have 1011-1021 added . I think this would have something to start off like :

Query "like above"
$num=$lastnumberinquery + 20
$nextnum=$lastnumberinquery +1

if $num = $lastnumberinquery stop
else add 1 more row.

I quess though this would be hard if the $lastnumberinquery would change. I also suck at if statements and so forth.

jheitz

If you only want to show 20 records it is not wise to do a select of all records and then just show 20... it won't be very efficient on both the database and the data passed between the database and the PHP engine.

My suggestion is that you call a STORED PROCEDURE passing as an argument the number of elements and maybe the initial record's ID and have this SP return the specified number of rows starting at the specified ID.

There are different ways of doing this depending on the RDBMS. In SQL Server, for example, this can be done by running a query that looks like this:

SELECT TOP 20 *
FROM <TABLE>
WHERE <ID_FIELD> >= <SPECIFIED_NUMBER>
ORDER BY <ID_FIELD> DESC

The syntax is most probably incorrect, but do you get the idea? Would this do what you want?

mwarren

the number 20 is a ficticous number the number will change for every form entered. It is a form variable. the query I want is to find the last number entered then the form variable showing how many they want to add into the database. it may be 1 it may be 100 . I am not sure how to loop this. I can do this in CLI and Berkeley but not php/mysql. I need it in mysql for relational and website functions.

jheitz

If you want the MAX number why not do?

select max(id) from database where 'phonenumber' = $http_post_var "phonenumber"

To loop then you could use WHILE or FOR I would suggest you search on the web for

"PHP WHILE LOOP" or "PHP FOR LOOP"

to get a better picture

mwarren

I don't know how is why I am asking.. LOL ... I figure I need to set the variable on the form side for $lastnumber otherwise it will continuously change I can do that part just not the add to the database "x" number of times using that.

Kudose

The basic of basics.

<?php
  $TABLE = 'mytable';
  $COLUMN = 'name';

  if(isset($_POST)){
    $name = trim($_POST['name']);
    $X = intval(trim($_POST['X']));
    $SQL = "INSERT INTO $TABLE ($COLUMN) VALUES ('$name')";
    for($i=0;$i<$X;$i++)
      mysql_query($SQL) or die(mysql_error());
    echo 'Done.';
  } else {
    ?>
      <form method="POST" action="<?php echo $_SERVER['PHP_SELF']; ?>">
        My name is:<input type="text" name="name" /><br />
        Insert <input type="text" name="X" /> rows.<br />
        <input type="submit" value="submit" name="submit" />
      </form>
    <?php
  }
?>

You could just auto_increment the IDs and use LAST_INSERT_ID to get ... you know.

Or you could do it like this

$nextID = mysql_result(mysql_query("SELECT MAX(id) FROM table"), 0) + 1;

Roger_Ramjet

Kudose wrote:
The basic of basics.

Or you could do it like this
$nextID = mysql_result(mysql_query("SELECT MAX(id) FROM table"), 0) + 1;

No you can't Kudose, you should know better than that.

Autoinc columns issue the next id BEFORE they do an insert. If the insert fails, or a record is later deleted, that id number is never re-issued.

So there is no guarantee that:
a) the id numbers will be an unbroken sequence - infact it is almost guaranteed that there will be gaps.
b) the next id to be issued will be the last id +1

Additionally, in a multi-user enviropnment like the web, someone else can do an insert between you looking up the last one and then doing your own insert - so the number you calculated will be taken already.

The ONLY way to work with autoincs is to do the insert and then query for the id that was actually issued afterwards.

laserlight

If the insert fails, or a record is later deleted, that id number is never re-issued.

That may be dependent on the database system. I believe on SQLite the id may be re-issued, but only after all the other available numbers are used up and assuming the column specification did not explicitly disallow the re-use.

Roger_Ramjet

As to your original problem Matt;

How to insert X records, or rather new record stubs, into your db.

Running a loop X times to insert 1 record each time is one way - but very inefficient once it becomes 10 or 20 loops. You would really have to query for last insert id each time as well to account for simultaineous (can anyone spell that word 😕 ) users whose multiple insert queries would interleave the ids issued.

You could run a loop to build an insert query that inserted the X records in one go.

$base = "INSERT INTO table(username, phonenumber, listingnumber) 
     VALUES ($user,$phone,$list)";
$vals = "";
for($i=1;$i<$X;$i++) {
   $vals .= ",($user, $phone, $list)";
}
$query = $base . $vals;

That will insert your X records in one go.

Kudose

Roger Ramjet wrote:
No you can't Kudose, you should know better than that.

I guess I should have specified that there are two ways to go about getting the last ID inserted dependent upon how you choose to increment your column. By auto_increment or by your own increment method.

Oh well -- it was late and you all have come to the rescue.