5 replies to this topic

[Hooch]

Is it possible to do a search from 1 table, then have the results of the find entered into another table?

I attempted this by making a form that is in a "while" statement.
All the data shows up, but how would I submit over 200 entries with the click of a button?
Or can this be done?

FYI here is my code so far..

<?php
include 'db.php';
$today0 = date('D F d Y'); // Wed May 07 2003 
?>
<form name="form1" method="post" action="daily-record_chk.php">
<table width="252" border="0" align="center" cellpadding="0" cellspacing="0">
  <td colspan="4"><div align="left">
  <input type="submit" name="Submit" value="Submit" />
  </div></td>
  <tr>
  <td width="72">
  <input name="id" type="hidden" id="id" value="<? echo $row['id']; ?>" />	
  <input name="date" type="hidden" size="35" value="<? echo $today0; ?>" />  </td>
  <td colspan="3"><div align="right"> </div></td>
  </tr>
	<? 
	$result = mysql_query("SELECT id, area, carid, commodity, inout FROM railcar WHERE inout = 1 ORDER by area;")or die(mysql_error());
	while($row=mysql_fetch_array($result)) 
	{ 
	?>
  <tr>   
  <td>
  <input name="area" type="text" id="area" value="<? echo $row['area']; ?>" size="12">  </td>
  <td colspan="2">
  <input name="carid" type="text" id="carid" value="<? echo $row['carid']; ?>" size="18" />  </td>
  <td width="72">
  <input name="commodity" type="text" id="commodity" value="<? echo $row['commodity']; ?>" size="12" />  </td>
  </tr>
  <?php
  }
  mysql_close();
  ?>
 
</table>
</form>

This is to show all the railcars onsite each day.
The database would have around 100 cars in it, but maybe only 200 cars onsite.
So it would be very handy to have a 1 button entry if possible.

Thanks for any help,
Hooch

[Hooch]

Ohh, I'm getting close!

I actually have some success here.
I found a tutorial on updating multiple rows. So I have modified it a bit.
The database is actually updated, but it doubles the amount that should be there.

Here's my updated code.
(I hope this is a workable solution)

<?php
include 'db.php';
$today0 = date('F d Y'); //May 07 2003
$sql="SELECT id, area, carid, commodity, inout FROM railcar WHERE inout = 1 ORDER by area;";
$result=mysql_query($sql);
// Count table rows 
$count=mysql_num_rows($result);
?>
<table width="700" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="daily-record222.php">
<tr> 
<td>
<table width="698" border="0" cellspacing="1" cellpadding="0">


<tr>
<td width="34" align="center"><strong>Id</strong></td>
<td width="94" align="center"><strong>Date</strong></td>
<td width="183" align="center"><strong>Area</strong></td>
<td width="144" align="center"><strong>Car ID </strong></td>
<td width="237" align="center"><strong>Commodity</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><? $id[]=$rows['id']; ?><? echo $rows['id']; ?></td>
<td align="center"><input name="date[]" type="text" id="date" value="<? echo $today0; ?>"></td>
<td align="center"><input name="area[]" type="text" id="area" value="<? echo $rows['area']; ?>"></td>
<td align="center"><input name="carid[]" type="text" id="carid" value="<? echo $rows['carid']; ?>"></td>
<td align="center"><input name="commodity[]" type="text" id="commodity" value="<? echo $rows['commodity']; ?>"></td>
</tr>
<?php
}
?>
<tr>
<td colspan="5" align="center"><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>
<?php
include 'db.php';
// convert posted info to easy to use variables
$date = $_POST['date'];//get date from form
$area = $_POST['area'];//get area from form
$carid = $_POST['carid'];//get carid from form
$commodity = $_POST['commodity'];//get commodity from form

// Check if button name "Submit" is active, do this 
if($Submit){
for($i=0;$i<$count;$i++){
$sql1="INSERT INTO `daily-record` ( `id`, `date`, `area`, `carid`, `commodity`) VALUES ('', '$date', '$area', '$carid', '$commodity');";

$result1=mysql_query($sql1);
}
}

if($result1){
header("location:daily-record222.php");
}
mysql_close();
?>

The result this query brings up is 26 railcars (for my test here), but 52 rows are entered in the DB.
Any ideas??

TY,
Hooch

[Mr. Matt]

if 28 rows are being pulled out from the db then you need to check the query. Run it through phpmyadmin (if you run it) and see how many are pulled out. Also in your query you might need to remove the ; from the query:

$sql="SELECT id, area, carid, commodity, inout FROM railcar WHERE inout = 1 ORDER by area>>>>;<<<<";

not sure if it would make a difference or not, I would try remove it and see what happens.

Edited by deadly, 01 July 2006 - 04:37 AM.

[Hooch]

28 cars is what should be pulled from the DB.
It's the 56 being entered that's wrong.
It's this part that must be wrong somehow..

// Check if button name "Submit" is active, do this 
if($Submit){
for($i=0;$i<$count;$i++){
$sql1="INSERT INTO `daily-record` ( `id`, `date`, `area`, `carid`, `commodity`) VALUES ('', '$date', '$area', '$carid', '$commodity');";

$result1=mysql_query($sql1);
}
}

if($result1){
header("location:daily-record222.php");
}
mysql_close();
?>

Plus there is another problem.
It enters the word ARRAY instead of the values from the database.

???

[Mr. Matt]

I dont have a clue, the only thing I can think of is you shouldnt be useing the for() statement for this, maybe look into foreach().

The only idea for this I have for this is to make a function and select all of the ID's that fit the query and put them into an array. Then use foreach to select all of the values for the ID and to then insert them into the table.

But that will be a bit complex and not needed I think. Maybe .Matt, rc69 or Chaos will have an idea.

[Hooch]

Got it.
Here's my final code.

<?php
include 'db.php';
$today0 = date('F d Y'); //May 07 2003
$sql="SELECT id, area, carid, commodity, inout FROM railcar WHERE inout = 1 ORDER BY area ASC, commodity ASC, carid";
$result=mysql_query($sql);

// Count table rows 
$count=mysql_num_rows($result);
?>
<table width="700" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="">
<tr> 
<td>
<table width="698" border="0" cellspacing="1" cellpadding="0">


<tr>
<td width="34" align="center"><strong>Id</strong></td>
<td width="94" align="center"><strong>Date</strong></td>
<td width="183" align="center"><strong>Area</strong></td>
<td width="144" align="center"><strong>Car ID </strong></td>
<td width="237" align="center"><strong>Commodity</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><? $id=$rows['id']; ?><? echo $rows['id']; ?></td>
<td align="center"><input name="date" type="text" id="date" value="<? echo $today0; ?>"></td>
<td align="center"><input name="area" type="text" id="area" value="<? echo $rows['area']; ?>"></td>
<td align="center"><input name="carid" type="text" id="carid" value="<? echo $rows['carid']; ?>"></td>
<td align="center"><input name="commodity" type="text" id="commodity" value="<? echo $rows['commodity']; ?>"></td>
</tr>
<?php
}
?>
<tr>
<td colspan="5" align="center"><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>

<?php
// convert posted info to easy to use variables
$date = $_POST['date'];//get date from form
$area = $_POST['area'];//get area from form
$carid = $_POST['carid'];//get carid from form
$commodity = $_POST['commodity'];//get commodity from form

// Below is taken from a tutorial from http://www.phpeasystep.com
// Check if button name "Submit" is active, do this 
if($Submit){
for($i=0;$i<$count;$i++){
$sql1 = "INSERT INTO `daily-record` ( `id`, `date`, `area`, `carid`, `commodity` ) VALUES ( '', '$date[$i]', '$area[$i]', '$carid[$i]', '$commodity[$i]')";

$result1=mysql_query($sql1);
}
}

if($result1){
header("location:THISPAGE.php");
}
mysql_close();
?>

Wee Haa!!