• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 495
  • Last Modified:

insert range between two numbers into database

I have two text fields on a page. One is "start" and the other is "end". If I input 504242 into "start" and 504282 into "end", is there a way for php to determine all the numbers in between those numbers? So, I basically want it to insert each number into a new row in the database. once I have entered those two numbers and clicked "submit" I would want the database to look like this:

504242
504243
504244
504245
504246
all the way to 504282

Is this possible?
0
jonofat
Asked:
jonofat
  • 6
  • 3
  • 3
  • +2
1 Solution
 
teebonCommented:
Hi jonofat,

Yes this is possible. Basically you need a for loop / while loop, something like this:

For i = textfield_one ; i <= textfield_two; i ++
{
   insert into table(id) values( i );
}
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
either you create a php loop to do this, or some mysql query to generate the sequence.
there is nothing "build-in" to create the array or so with "0 code".
0
 
gavsmithCommented:
<?php
  for ($i = (int)$_POST[fromfield]; $i <= (int)$_POST[tofield];$i++)
  {
      'insert comand using value $i
  }
..

Open in new window

0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Julian HansenCommented:
Here is the php code you need to implement
<?php
$start = empty($_POST['start'])? 0 : $_POST['start'];
$end = empty($_POST['end'])? 0 : $_POST['end'];
if ($start > 0 && $start < $end) {
  $conn =mysqli_connect("localhost", "USER", "PASSWORD", "DATABASE");
  for ($i = $start; $i <= $end; $i++) {
    $query = "INSERT INTO tablename (fieldname) VALUE($i)";
    mysqli_query($query);
  }
}
?>

Open in new window

0
 
jonofatAuthor Commented:
JulianH, that is awesome! I don't know if you would be willing to help me with one last thing, I just wanted to add another field but can't get it to work. It was working stand alone but once I added to it, it stopped working :(

$start = empty($_POST['start'])? 0 : $_POST['start'];
$end = empty($_POST['end'])? 0 : $_POST['end'];
if ($start > 0 && $start < $end) {
   mysql_select_db($database_test, $test);
  for ($i = $start; $i <= $end; $i++) {
    $query = ("INSERT INTO test (tankno, jobno) VALUES($i, $s)",
	GetSQLValueString($_POST['jobno'], "text"));

Open in new window

0
 
jonofatAuthor Commented:
Sorry, the error is:

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 1

Notice: Undefined variable: s in untitled.php on line 38

 $query = sprintf("INSERT INTO test (tankno, jobno) VALUES($i, $s)",
      GetSQLValueString($_POST['jobno'], "text"));
0
 
Julian HansenCommented:
Your assignment of the GetSQLValueString call is incorrect - you either have to concatenate it into the string or call the sprintf function
Concatenate
$start = empty($_POST['start'])? 0 : $_POST['start'];
$end = empty($_POST['end'])? 0 : $_POST['end'];
if ($start > 0 && $start < $end) {
   mysql_select_db($database_test, $test);
  for ($i = $start; $i <= $end; $i++) {
    $query = "INSERT INTO test (tankno, jobno) VALUES($i,'" .  GetSQLValueString($_POST['jobno'], "text") . "')";
                                            

Open in new window

sprintf
$start = empty($_POST['start'])? 0 : $_POST['start'];
$end = empty($_POST['end'])? 0 : $_POST['end'];
if ($start > 0 && $start < $end) {
   mysql_select_db($database_test, $test);
  for ($i = $start; $i <= $end; $i++) {
   // Problem is your param in the sprintf string was $s instead of '%s'
    $query = sprintf("INSERT INTO test (tankno, jobno) VALUES($i, '%s')",
	GetSQLValueString($_POST['jobno'], "text"));
                                            

Open in new window

Also it looks like you are embedding a string in the query value list so you need to enclose the value in quotes as shown above
0
 
jonofatAuthor Commented:
Not sure if it makes a difference but the jobno field isn't like the start and end fields. The jobno field must be the same ie:

jobno    start

1234    504242
1234    504243
1234    504244
0
 
jonofatAuthor Commented:
Error I get:

INSERT INTO test (tankno, jobno) VALUES(504242, '8384')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 '55' at line 1
0
 
gavsmithCommented:
it doesn't make any difference

regards
Gav
0
 
gavsmithCommented:
Looks like you are trying to store text into a number field can you verify that jobno is a text field?
0
 
jonofatAuthor Commented:
OKay, so I changed printf to sprintf and it works! :)
0
 
jonofatAuthor Commented:
Legend!
0
 
Julian HansenCommented:
Looks like you are trying to store text into a number field can you verify that jobno is a text field?

It does not matter SQL will interpret the string as an int.

The GetSQLValueString call was using the parameter "text" to return data so seemed it was a text field. To be safe enclose in quotes

printf to sprintf - yes the former is for outputting to the display so that wouldn't work.

Glad you got sorted - thanks for the points
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 6
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now