Beginner with weird results to an insert query

sal

Hello everyone,

I want to use the following code to insert multiple values to a table. The code nearly works in that for every row containing the value I enter in the form, there is another row with nothing in the required column.

ie. id category
1

2
3 blue
4 red

Can anyone help? Here is the code

$count=2;
for ($i = 0; $i < $count; $i++) {
$sql = "INSERT INTO $mysql_cat (id, user, category) VALUES ('', '$user', '$category[$i]')";
$result = mysql_query($sql) or die("ERROR: cannot INSERT data into database");
?>
<form method=post>
<input type="text" name="category[]">
<input type="hidden" name="count" value="<?php echo $count ?>">
<?
}
?>
<input type="submit">
</form>

barand

You have one field on the form called category[] so a value entered in it goes into category[0]. But you always insert 2 records, with category[0] (with a value) and category[1] which is always empty.

sal

ok. Thanks.

But what do I do to prevent it.

When I put

$count=1

I only get one

category[] field in the form.

sal

ok. Thanks.

But what do I do to prevent it.

When I put

$count=1

I only get one

category[] field in the form.

sal

ok. Thanks.

But what do I do to prevent it.

When I put

$count=1

I only get one

category[] field in the form.

barand

Put <form> outside loop.

<form method=post>      /* form action ??? */
<?
$count=2; 
for ($i = 0; $i < $count; $i++) { 
$sql = "INSERT INTO $mysql_cat (id, user, category) VALUES ('', '$user', '$category[$i]')"; 
$result = mysql_query($sql) or die("ERROR: cannot INSERT data into database"); 
?> 
<input type="text" name="category[]"> 

<? 
} 
?> 
<input type="hidden" name="count" value="<?php echo $count ?>"> 
<input type="submit"> 
</form>

What exactly should the order of events be? Your structure is a bit cloudy.

Are you entering the values in the form and then inserting
or
are you inserting, building a form of the inserted values to do something else with then ?

sal

I have 2 tables: members, categories

The form is a registering form. I want to insert members info into table.member. PLUS categories info to table.categories.

However, I want to give members the option of registering more than one categories.

ie. A teacher might teach math, physics and chemistry. Those 3 items would go in the categories.table

barand

It's better to separate the input from the update.

Is there a maximum number of categories that anyone can enter?
Is there a predefined set of categories that the user could choose from?

If the max number is 5, say, then

<form action="update.php" method="post">
Enter your name <input type="text name="name"> <br>
Enter Categories<br>

<? for ($i=0; $i<5; $i++) { ?>
<?=$i+1?> <input type="text" name="category[]" ><br>
<? } ?>

Or, iff there is a predefined set of categories you may want to consider a list that the user can select from.

Select categories
<select name="category[]" size="10" multiple>
<option value="biology"> biology</option>
<option value="physics"> physics</option>
<option value="chemistry"> chemistry</option>
<option value="maths"> maths</option>
<option value="english"> english</option>
<option value="french"> french</option>
<option value="latin"> latin</option>
/ etc /
</select>
<input type="submit" value="Submit">
</form>

In update.php you can process each posted category with a [man]foreach[/man] loop

<?
$name = $_POST["name"];
mysql_query ("INSERT INTO member (name) VALUES ('$name')";
$id = mysql_insert_id();

foreach ($_POST["category"] as $cat) {
	$sql = "INSERT INTO category (id,category) VALUES ($id,' $cat')";
	myql_query("$sql);
}

header("location: another.php");    /* redirect to another page */
?>

hth

sal

I am beginning to understand the logic.

Is there something missing in the following:

<? for ($i=0; $i<5; $i++) { ?>
<?=$i+1?> <input type="text" name="category[]" ><br>
<? } ?>

Thank you very much for your help by the way.

barand

Is there something missing in the following:

<? for ($i=0; $i<5; $i++) { ?>
<?=$i+1?> <input type="text" name="category[]" ><br>
<? } ?>

I don't think so. It just gives a list of empty text fields numbered 1 to 5.

sal

I have got it!

I had not separated the form!

Works beautifully. I am going to try your method of inserting a value according to the options presented.

You have now idea how many days I have strugging with this!

Thank you again!

barand

I almost forgot

If you use the list of text fields rather than the pre-defined options you will get blank categories in unused fields. So you need to test for them before inserting a category

foreach ($_POST["category"] as $cat) {
if ($cat !="")   {  

    $sql = "INSERT INTO category (id,category) VALUES ($id,' $cat')";
    myql_query("$sql);
  }
}

sal

Tried all manners of forms.

Used the extra code for unused felds.

Works really well.

Again thank you.