a simpler way to add multiple rows?

[__greg]

Hello,
I was wondering if there is a loop or something simpler to add multiple rows to a table in my database..

Here is my current code:

if ($_POST[venue] == NULL && $_POST[venue2] == NULL && $_POST[venue3] == NULL && $_POST[venue4] == NULL && $_POST[venue5] == NULL && $_POST[venue6] == NULL && $_POST[venue7] == NULL && $_POST[venue8] == NULL && $_POST[venue9] == NULL && $_POST[venue10] == NULL) {
		echo "You have to enter at least one date!<br><br>";
		}
	if ($_POST[venue2] == NULL && $_POST[venue3] == NULL && $_POST[venue4] == NULL && $_POST[venue5] == NULL && $_POST[venue6] == NULL && $_POST[venue7] == NULL && $_POST[venue8] == NULL && $_POST[venue9] == NULL && $_POST[venue10] == NULL) {
		$sql = "INSERT INTO evr_tours VALUES ('', '$_POST[band_id]', '$_POST[date]', '$_POST[band]', '$_POST[venue]', '$_POST[location]', '$_POST[time]', '$_POST[price]', '$_POST[misc]')";
		}




if ($_POST[venue3] == NULL && $_POST[venue4] == NULL && $_POST[venue5] == NULL && $_POST[venue6] == NULL && $_POST[venue7] == NULL && $_POST[venue8] == NULL && $_POST[venue9] == NULL && $_POST[venue10] == NULL) {
	$sql = "INSERT INTO evr_tours VALUES ('', '$_POST[band_id]', '$_POST[date]', '$_POST[band]', '$_POST[venue]', '$_POST[location]', '$_POST[time]', '$_POST[price]', '$_POST[misc]'), 
			('', '$_POST[band_id]', '$_POST[date2]', '$_POST[band]', '$_POST[venue2]', '$_POST[location2]', '$_POST[time2]', '$_POST[price2]', '$_POST[misc2]')";
	}

... and on and on and on.

If there some way to have it just loop through each form field... if there is data, insert it... if not... skip it?

Thank you!

[LordShryku]

What a mouthful....

So, my suggestion would be to just make all of the text fields named "venue[]". That will just create the an array of venues you could count against.

Otherwise...I'd change the name of "venue" to "venue1", and do this:

$j=0;
for($i=1;$i<11;$i++) {
    if(isset($_POST['venue'.$i])) {
       $sql[$j] = //your sql
       $j++;
    }
}

[tbach2]

I think this cries for a better data structure.

There are some variables that are common to all the inserts: $band_id and $band.

Everything else is related (I think). Therefore you would have these variables (using $i as an index):
$data[$i]['date']
$data[$i]['venue']
$data[$i]['location']
$data[$i]['time']
$data[$i]['price']
$data[$i]['misc']

So in your html you would use for example

name="data[2]['date']" instead of name="date2"

Then all you have to do is iterate through $data and do queries based on which of the array elements have values (almost exactly what lord said)

$data = $_POST['data'];

for($i=0;$i<sizeof($data);$i++) {
    if($data[$i]['venue']) {
        $query = sprintf("INSERT INTO evr_tours VALUES ('', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
                         $_POST['band_id'],
                         $data[$i]['date'],
                         $_POST['band'],
                         $data[$i]['venue'],  

                         $data[$i]['location'],
                         $data[$i]['time'],
                         $data[$i]['price'],
                         $data[$i]['misc']
                         );
        // do query
    }
}

Note that I am starting counting with zero, not one.

[__greg]

alright thanks guys for helping, but i still can't seem to get it to work..

here's my HTML:

<table border=0 cellspacing=0 cellpadding=2>
<tr>
<td align=right><b>Date:</b></td>
<td valign=top><input name=date[1]['date'] type=text size=15 maxlength=10 class=box value=\"yyyy-mm-dd\" onfocus=\"if (this.value=='yyyy-mm-dd') this.value='';\" onblur=\"if(this.value=='') this.value='yyyy-mm-dd';\"></td>		
</tr>
<tr>
<td align=right><b>Venue:</b></td>
<td colspan=3><input name=venue[1]['venue'] type=text size=50 class=box></td>
</tr>
<tr>
<td align=right><b>City:</b></td>
<td colspan=3><input name=city[1]['city'] type=text size=50 class=box></td>
</tr>
<tr>
<td align=right><b>State:</b></td>
<td colspan=3><input name=state[1]['state'] type=text size=10 class=box></td>
</tr>
<tr>
<td align=right><b>Time:</b></td>
<td colspan=3><input name=time[1]['time'] type=text size=10 class=box> (ex: 7:00pm)</td>
</tr>
<tr>
<td align=right><b>Price:</b></td>
<td colspan=3><input name=price[1]['price'] type=text size=10 class=box value=\"$\"> (ex: $10)</td>
</tr>
<tr>
<td colspan=2 align=right>[<a href=javascript:insertLink()>URL</a>]</td>
</tr>
<tr>
<td align=right valign=top><b>Misc:</b></td>
<td colspan=3><textarea name=misc[1]['misc'] cols=49 rows=5 class=box></textarea></td>
</tr>
</table>

... and here is the php to execute the insertion:

$data = $_POST['data']; 

	for($i=0;$i<sizeof($data);$i++) { 
		if($data[$i]['venue']) { 
			$query = sprintf("INSERT INTO evr_tours VALUES ('', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')", 
							 $_POST['band_id'], 
							 $data[$i]['date'], 
							 $_POST['band'], 
							 $data[$i]['venue'],   
							 $data[$i]['city'], 
							 $data[$i]['state'], 
							 $data[$i]['time'], 
							 $data[$i]['price'], 
							 $data[$i]['misc'] 
							 ); 
			$insertquery = mysql_query($query) or die(mysql_error());
		} 
	}

i'm not so good at this, but i'm trying to understand everything. thank you guys for helping me out..

[tbach2]

Originally posted by tbach2

So in your html you would use for example

name="data[2]['date']" instead of name="date2"

ie, where you have

<input name=date[1]['date'] type=text size=15 maxlength=10 class=box value=\"yyyy-mm-dd\" onfocus=\"if (this.value=='yyyy-mm-dd') this.value='';\" onblur=\"if(this.value=='') this.value='yyyy-mm-dd';\">
...
<input name=venue[1]['venue'] type=text size=50 class=box>
...

it should be

<input name=[B]data[/B][1]['date'] type=text size=15 maxlength=10 class=box value=\"yyyy-mm-dd\" onfocus=\"if (this.value=='yyyy-mm-dd') this.value='';\" onblur=\"if(this.value=='') this.value='yyyy-mm-dd';\">
...
<input name=[B]data[/B][1]['venue'] type=text size=50 class=box>
...

[__greg]

oh yeah, that would make sense....

but i tried that, and it still isn't inserting anything into the database..

I think it might have to do with the '%s' in the insert query, because I've never seen that before.. but as i said.. i'm still pretty new to this..

Here's my current code that still doesn't seem to work:

HTML snippit (so you get the idea):

<tr>
<td align=right><b>Venue:</b></td>
<td colspan=3><input name=data[1]['venue'] type=text size=50 class=box></td>
</tr>

PHP (once submit button is clicked):

$data = $_POST['data']; 

	for($i=0;$i<sizeof($data);$i++) { 
		if($data[$i]['venue']) { 

			$query = sprintf("INSERT INTO evr_tours VALUES ('', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')", 
							 $_POST['band_id'], 
							 $data[$i]['date'], 
							 $_POST['band'], 
							 $data[$i]['venue'],   
							 $data[$i]['city'], 
							 $data[$i]['state'], 
							 $data[$i]['time'], 
							 $data[$i]['price'], 
							 $data[$i]['misc'] 
							 ); 

			mysql_query($query) or die(mysql_error());
		} 
	}

[tbach2]

have you done your debugging? Make sure the variables are getting passed and that you are accessing them correctly:

        for($i=0;$i<sizeof($data);$i++) { 
print "loop $i:<br>";

        if($data[$i]['venue']) { 
            $query = sprintf("INSERT INTO evr_tours VALUES ('', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')", 
                             $_POST['band_id'], 
                             $data[$i]['date'], 
                             $_POST['band'], 
                             $data[$i]['venue'],   
                             $data[$i]['city'], 
                             $data[$i]['state'], 
                             $data[$i]['time'], 
                             $data[$i]['price'], 
                             $data[$i]['misc'] 
                             ); 
print "query = $query";
                $insertquery = mysql_query($query) or die(mysql_error());
            } 
        }

I think it might have to do with the '%s' in the insert query, because I've never seen that before..

Look up sprintf in the manual; it is a very handy way to format and, to some extent, filter data.

This example doesn't filter the data at all, but once you get used to the look of the command,
it is much easier to read later and to debug.

The "%s" merely means to substitute a string for the %s, while a %d would only insert a numerical digit.

That really comes in handy if you want to ensure that you only pass a number in as an argument to a query...

[__greg]

yeah it seems like sprintf would definitely come in handy..

so i tried the code you presented to see what would happen and still the same thing... no inserts. but this was the only message it gave me:

loop 0:

I'm taking that to mean that the data isn't being sent correctly since the for statement isn't picking up on it?

[tbach2]

Oh, I see what's going on--

I was under the impression that there would be more than one set of date/city/venue/etc. The html that you have provided only has one set, with an index of 1. The for loop is starting at 0. You can prove that by

$data = $_POST['data']; 
print "<pre>";
print_r($data);
print "</pre>";

if you are going to start with $data[1]['whatever'], then you need to adjust your for loop to

for($i=1; $i<=sizeof($data); $i++) {

[__greg]

yeah you were actually correct, there will be more then one set of date/venue/etc, but for testing purposes or whatever i was just using one... because sometimes, the user might only fill out one set and leave the rest blank..

anyways i thought that would have been a problem, so i tried it both starting at 0 and starting at 1 and it still didn't seem to do anything differently..

now i am testing with two sets of date/venue/etc. the first is:

$data[0]['venue'], etc., etc.

and the second is:

$data[1]['venue'], etc., etc.

and my for loop is:

for($i=0; $i<sizeof($data); $i++) { 

with $data printed (like you put in the last post), this is now what is printed:

Array
(
    [0] => Array
        (
            ['date'] => 2003-10-31
            ['venue'] => ads
            ['city'] => asdads
            ['state'] => ny
            ['time'] => 7:30pm
            ['price'] => $asd
            ['misc'] => 
        )

[1] => Array
    (
        ['date'] => yyyy-mm-dd
        ['venue'] => asdasd
        ['city'] => asdasda
        ['state'] => ye
        ['time'] => 701
        ['price'] => $12
        ['misc'] => 
    )

)

loop 0:
loop 1:

all that 'asdadcd' stuff is whatever i entered to test it out.

but still, no inserts.

[tbach2]

Well, you've got me scratching my head...

obviously, the problem is with the 'if' statement: for some reason, the if condidtional is not being followed. Perhaps it is mistyped?

according to the print_r() output,

Array
(
    [0] => Array
        (
            ['date'] => 2003-10-31
            ['venue'] => ads
            ['city'] => asdads
            ['state'] => ny
            ['time'] => 7:30pm
            ['price'] => $asd
            ['misc'] => 
        )

[1] => Array
    (
        ['date'] => yyyy-mm-dd
        ['venue'] => asdasd
        ['city'] => asdasda
        ['state'] => ye
        ['time'] => 701
        ['price'] => $12
        ['misc'] => 
    )

)

$data[0]['venue'] = ads
and
$data[1]['venue'] = asdasd

therefore, both are true and the statement

if($data[$i]['venue']) {

should evaluate to 'true' and be followed.

Most likely you have misspelled the variable name and thus it evaluates to false.

If it is misspelled, then this is one instance where letting php output all warnings (while developing the page) would be very helpful in debugging: it would spit out "Warning: undeclared variable in line xxx".

You might also try printing out the values for $data[$i]['venue'], just to see what they are... (be sure to include some text in the print statement so that you can see the print command was executed even if there is no value for the variable...)

BTW, be careful allowing dollar signs in the database-- sometimes they are evaluated as variables if you're not careful.

[__greg]

alright well, i guess ill just keep tweaking it and try to find out what the problem is...

thank you for all your help though... you definitely have pointed me in the right direction..

[__greg]

hmm,
it is definitely with the if statement...

if i try:

if($data[$i]) { 
$display_block .= "something";
}

it will dispay the "something" i wanted it to. but if i do this:

if($data[$i]['venue']) { 
$display_block .= "something";
}

it does not.

from adding the ['venue'] ,it makes the if statement return false, therefore not executing the code inside the brackets..

anyone know why this is happening or how to fix it?