[RESOLVED] Multiple Insert help

MystedVeil

I have two tables with the following structure

Table 1: (geography_zone)
zone_id (primary)
zone
zone_level
zone_descript
zone_notes
zone_map (this will be an image)

Table 2: (geography_map)
legend_id (primary)
zone_id (relates to table 1)
legend_key
legend

Here is my form for adding to Table 1. This works just fine!

add_zone.php

if(isset($_POST['submit'])){// handle the form
// Connect to the database.
require_once ('../incl/mysql_connect.php');
        // Query the database.    


// The insert condition.
$query = "INSERT INTO geography_zone (zone_id, zone, zone_level, zone_descript, zone_notes, zone_map) VALUES
  ('',  '$zone', '$zone_level', '$zone_descript', '$zone_notes', '$zone_map') ";  


        $result = @mysql_query ($query); // Run the query.

        if ($result) { // If it ran OK.

            // Send an email, if desired.
            echo '<table width=95% align=center class=tableOutline>
<tr><td><p>Thank you for adding this zone!</p><p><a href=/forms/add_zone.php target=_self>Back</a></p></td></tr></table>'; 
                exit();                

        } else { // If it did not run OK.
            // Send a message to the error log, if desired.
            echo '<table width=95% align=center class=tableOutline>
<tr><td><p>Your information could not be registered due to a system error. We apologize for any inconvenience.</p></td></tr></table>'; 
            }        

    mysql_close(); // Close the database connection.
	}

add_maplegend.php ( One zone could have any where from 5 to 40 related legend keys and I would like to be able to add them via a multiple insert but I am more than a tad bit confused on how to do this. Yes I have done a search using "multiple inserts" but all I have managed to do is confuse myself.)

if(isset($_POST['submit'])){// handle the form
// Connect to the database.
require_once ('../incl/mysql_connect.php');
        // Query the database.    


// The insert condition.
$query = "INSERT INTO geography_map (legend_id, zone_id, legend_key, legend) VALUES
  ('',  '$zone_id', '$legend_key', '$legend') ";  


        $result = @mysql_query ($query); // Run the query.

        if ($result) { // If it ran OK.

            // Send an email, if desired.
            echo '<table width=95% align=center class=tableOutline>
<tr><td><p>Thank you for adding this zone!</p><p><a href=/forms/add_zone.php target=_self>Back</a></p></td></tr></table>'; 
                exit();                

        } else { // If it did not run OK.
            // Send a message to the error log, if desired.
            echo '<table width=95% align=center class=tableOutline>
<tr><td><p>Your information could not be registered due to a system error. We apologize for any inconvenience.</p></td></tr></table>'; 
            }        

    mysql_close(); // Close the database connection.
	}

<table width="95%" border="0" align=center>
<tr><td valign="top" colspan=4 align=center class=header>Add Map Information</td></tr>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

 require_once ('../incl/mysql_connect.php');
                   $q = "select zone_id, zone from geography_zone order by zone";
                    $result = mysql_query($q) or die (mysql_error());        

                while ($row = mysql_fetch_array($result))
                {
                    extract($row);
                    echo "<option value=\"$zone_id\">$zone</option>";
                }

</select></td>

<td valign="top">Legend Key: <input class="TextInput" type="text" name="legend_key" size="5" maxlength="10" value="" /></td>

<td valign="top">Legend: <input class="TextInput" type="text" name="legend" size="50" maxlength="255" value="" /></td>
</tr>

</form>
</table>

bpat1434

Hmm... code is confusing. So your first insert works fine. Great.

I do see a couple things with the second:
1.) You only specify a text-field as the Legend-Key input area, not a multiple select box (or checkboxes). How will you differentiate?
2.) You're using lots of register_globals=on dependant code. As of PHP 4.x, it's off, so I'd update your code.
3.) You are opening yourself up to mySQL injection because your code is insecure. Get that tied down too.

I understand the question, but without knowing what your end goal is, or how your database is set up (I get the fields, but what type? Integer, Text, Varchar, Blob?)? Normally multiple queries are done in a loop, but I see nothing to loop in your code.

As an example of what secured code would look like, here's your first script secured:

if(isset($_POST['submit'])){// handle the form
// Connect to the database.
require_once ('../incl/mysql_connect.php');
        // Query the database.    

$zone = mysql_real_escape_string($_POST['zone']);
$zone_level = mysql_real_escape_string($_POST['zone_level']);
$zone_descript = mysql_real_escape_string($_POST['zone_descript']);
$zone_notes = mysql_real_escape_string($_POST['zone_notes']);
$zone_map = $_POST['zone_map'];

// The insert condition.
$query = "INSERT INTO geography_zone (zone_id, zone, zone_level, zone_descript, zone_notes, zone_map) VALUES
  ('',  '$zone', '$zone_level', '$zone_descript', '$zone_notes', '$zone_map') ";  


        $result = @mysql_query ($query); // Run the query.

        if ($result) { // If it ran OK.

            // Send an email, if desired.
            echo '<table width=95% align=center class=tableOutline>
<tr><td><p>Thank you for adding this zone!</p><p><a href=/forms/add_zone.php target=_self>Back</a></p></td></tr></table>';
                exit();                

        } else { // If it did not run OK.
            // Send a message to the error log, if desired.
            echo '<table width=95% align=center class=tableOutline>
<tr><td><p>Your information could not be registered due to a system error. We apologize for any inconvenience.</p></td></tr></table>';
            }        

    mysql_close(); // Close the database connection.
    }

~Brett

MystedVeil

Sorry I forgot to add that.
legend_id (primary, integar)
zone_id (relates to table 1, integar)
legend_key (integar) (all areas on the maps are already marked with numbers)
legend (text)

I have a details page set up to show the geography information from both tables. I am going to use the add_maplegend.php to add the map legend and its keys quickly as the end goal is to have that information show up beneath the map on my details page for the related zone (zone_id).

I will be showing the map image from table one with the information from table two beneath it. (using random names here to just show these in no way relate to the table.)

Zone Information

Map Image (will be marked with the Integars that coincide with the legend_key)

Key1 (legend_key) = Memphis (legend)
Key2 (legend_key) = Nashville (legend)
Key3 (legend_key) = Chattanooga (legend)
Key4 (legend_key) = Knoxville (legend)

Also, should I also use this structure on my update forms as well? Will this affect inputs with apostrophe's?

$zone = mysql_real_escape_string($_POST['zone']); 
$zone_level = mysql_real_escape_string($_POST['zone_level']); 
$zone_descript = mysql_real_escape_string($_POST['zone_descript']); 
$zone_notes = mysql_real_escape_string($_POST['zone_notes']); 
$zone_map = $_POST['zone_map'];

bpat1434

geography_zone					geography_map
--------------					-------------
zone_id-----------+			legend_id
zone							+---->zone_id
zone_level							legend_key
zone_descript						legend
zone_notes
zone_map

Is that the relationship of your databases? Not that it really matters, but just for reference.

Well, I'm still not seeing multiple inserts since it only seems to be a single insert. If you're speaking of multiple (as in multiple values in same column) then just run the query and put in the values. The only one that can't be the same is the primary/auto-incremented value. OTherwise, it's free to be repeated.

Take the numbers from the form, and just insert them. You should be fine. If it's getting them back you have a problem with, jsut say so.

~Brett

MystedVeil

I am not quite sure on how to do a multiple insert.

You are right on the relationship between the two tables and I know how to retrieve the data once it is inputted.

The geography_map input would look something like this.
zone_id legend_key legend
1 1 Memphis
1 2 Knoxvile
1 3 Chattanooga
1 4 Nashville

<table width="95%" border="0" align=center>
<tr><td valign="top" colspan=4 align=center class=header>Add Map Information</td></tr>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

// The insert condition. (will I repeat this 15-50 times? surely there is an easier way I am just not grasping?)

require_once ('../incl/mysql_connect.php'); 
                   $q = "select zone_id, zone from geography_zone order by zone"; 
                    $result = mysql_query($q) or die (mysql_error());         

                while ($row = mysql_fetch_array($result)) 
                { 
                    extract($row); 
                    echo "<option value=\"$zone_id\">$zone</option>"; 
                }

</select></td>

<td valign="top">Legend Key: <input class="TextInput" type="text" name="legend_key" size="5" maxlength="10" value="" /></td>

<td valign="top">Legend: <input class="TextInput" type="text" name="legend" size="50" maxlength="255" value="" /></td>
</tr>

</form>
</table>

MystedVeil

Trying to grasp this so I can add all geography_map keys in one form. Just curious if I am going towards teh right path?

if(isset($_POST['submit'])){// handle the form
// Connect to the database.
require_once ('../incl/mysql_connect.php');
        // Query the database.    

$zone_id = mysql_real_escape_string($_POST['zone_id']); 
$legend_key = mysql_real_escape_string($_POST['legend_key']); 
$legend = mysql_real_escape_string($_POST['legend']); 

$query = "INSERT INTO geography_map 
  (legend_id, zone_id, legend_key, legend) 
VALUES 
  ('$legend_id', '$level', '$legend_key', '$legend'), 
  ('$legend_id', '$level', '$legend_key', '$legend'), 
  ('$legend_id', '$level', '$legend_key', '$legend'), 
  ('$legend_id', '$level', '$legend_key', '$legend'), 
  ('$legend_id', '$level', '$legend_key', '$legend'), 
  ('$legend_id', '$level', '$legend_key', '$legend'), 
  ('$legend_id', '$level', '$legend_key', '$legend'), 
  ('$legend_id', '$level', '$legend_key', '$legend'), 
  ('$legend_id', '$level', '$legend_key', '$legend'), 
  ('$legend_id', '$level', '$legend_key', '$legend') 
";

        $result = @mysql_query ($query); // Run the query.

        if ($result) { // If it ran OK.

            // Send an email, if desired.
            echo '<table width=95% align=center class=tableOutline>
<tr><td><p>Thank you for adding this zone!</p><p><a href=/forms/add_zone.php target=_self>Back</a></p></td></tr></table>'; 
                exit();                

        } else { // If it did not run OK.
            // Send a message to the error log, if desired.
            echo '<table width=95% align=center class=tableOutline>
<tr><td><p>Your information could not be registered due to a system error. We apologize for any inconvenience.</p></td></tr></table>'; 
            }        

    mysql_close(); // Close the database connection.
	}

And if this is along the right path in my form will I repeat this 10 times?

require_once ('../incl/mysql_connect.php'); 
                   $q = "select zone_id, zone from geography_zone order by zone"; 
                    $result = mysql_query($q) or die (mysql_error());          

                while ($row = mysql_fetch_array($result)) 
                { 
                    extract($row); 
                    echo "<option value=\"$zone_id\">$zone</option>"; 
                }

</select></td>

<td valign="top">Legend Key: <input class="TextInput" type="text" name="legend_key" size="5" maxlength="10" value="" /></td>

<td valign="top">Legend: <input class="TextInput" type="text" name="legend" size="50" maxlength="255" value="" /></td>
</tr>

bpat1434

$query = "INSERT INTO geography_map
  (legend_id, zone_id, legend_key, legend)
VALUES
  ('$legend_id', '$level', '$legend_key', '$legend'),
  ('$legend_id', '$level', '$legend_key', '$legend'),
  ('$legend_id', '$level', '$legend_key', '$legend'),
  ('$legend_id', '$level', '$legend_key', '$legend'),
  ('$legend_id', '$level', '$legend_key', '$legend'),
  ('$legend_id', '$level', '$legend_key', '$legend'),
  ('$legend_id', '$level', '$legend_key', '$legend'),
  ('$legend_id', '$level', '$legend_key', '$legend'),
  ('$legend_id', '$level', '$legend_key', '$legend'),
  ('$legend_id', '$level', '$legend_key', '$legend')
";

THat's correct, that's how you'd do it.

As far as the repeating thing, why not just use a checkbox?

<?php

while($row = mysql_fetch_array($result))
{
  echo '<input type="checkbox" name="zones[]" value="'.$row['zone_id'].'">'.$row['zone'].'<br>';
}
?>

Then, the posted array "zones" will have all the zones that that area is linked to.

~Brett

MystedVeil

Okay I now have that added to my form and the zone checkbox, my next question is on the legend_key and legend parts of the form.

I want to repeat this 10-50 times, as my form stands at the moment I have the

VALUES
('$legend_id', '$zone_id, '$legend_key', '$legend'),

listed 10 times so I will go with that for now.

1 zone can have anywhere from 10-50 legend_key and legend. Would I just repeat

<td valign="top">Legend: <input class="TextInput" type="text" name="legend" size="40" maxlength="255" value="" /></td>

this part of the form 10 times?

bpat1434

you could... although you need to define unique names for each input.

~Brett

MystedVeil

I have done a bit of adjusting and am to this point. I have a table now called geography_relation which holds the areas and their related zones.

geography_relation
geography_id int( 11 ) NOT NULL AUTO_INCREMENT
area_id (11) NOT NULL
zone_id int(11) NOT NULL

now it is inserting for however many zones I pick through the checkboxes but it isn't inserting the zone_id. (EDIT: Okay i take that back, somewhere in trying to figure this out I apparently screwed up that working too, lol.)

The insert statement:

if(isset($_POST['submit'])){// handle the form
// Connect to the database.
require_once ('../incl/mysql_connect.php');
        // Query the database.    

   foreach ( array($_POST['zones']) as $zone_id )
   {
$query = "INSERT INTO  geography_relation (area_id, zone_id) VALUES
  ('$area_id', '$zone_id')   ";  

            $result = @mysql_query ($query); // Run the query.
  }
             if ($result) { // If it ran OK.

            // Send an email, if desired.
            echo '<table width=95% align=center class=tableOutline>
<tr><td><p>Thank you for adding these starting stats!</p><p><a href=http://wow.designofimagination.com/forms/add_georelation.php target=_self>Back</a></p></td></tr></table>'; 
 				include ('../incl/footer.html'); // Include the HTML footer.
                exit();                

        } else { // If it did not run OK.
            // Send a message to the error log, if desired.
            echo '<table width=95% align=center class=tableOutline>
<tr><td><p>Your information could not be registered due to a system error. We apologize for any inconvenience.</p></td></tr></table>'; 
            }        

        mysql_close(); // Close the database connection.
		}

the checkbox choices:

require_once ('../incl/mysql_connect.php'); 
                   $q = "select zone_id, zone from geography_zone order by zone"; 
                    $result = mysql_query($q) or die (mysql_error());           

 while($row = mysql_fetch_array($result)) 
{ 
  echo '<input type="checkbox" name="zones[]" value="'.$row['zone_id'].'">'.$row['zone'].'<br>'; 
}

MystedVeil

Okay I actually got that problem fixed by using this changed submit , but now I want to know how to validate in case zone_id[1] is picked but zone_id[2] isnt.

if(isset($_POST['submit'])){// handle the form
// Connect to the database.
require_once ('../incl/mysql_connect.php');
        // Query the database.    

$array = $_POST['zone_id'];
$query = "INSERT INTO  geography_relation (area_id, zone_id) VALUES
  ('$area_id', '$zone_id[1]'),('$area_id', '$zone_id[2]')   ";  

        $result = @mysql_query ($query); // Run the query.

         if ($result) { // If it ran OK.

            // Send an email, if desired.
            echo '<table width=95% align=center class=tableOutline>
<tr><td><p>Thank you for adding these areas and their relations!</p><p><a href=http://wow.designofimagination.com/forms/add_georelation.php target=_self>Back</a></p></td></tr></table>'; 
 				include ('../incl/footer.html'); // Include the HTML footer.
                exit();                

        } else { // If it did not run OK.
            // Send a message to the error log, if desired.
            echo '<table width=95% align=center class=tableOutline>
<tr><td><p>Your information could not be registered due to a system error. We apologize for any inconvenience.</p></td></tr></table>'; 
            }        

        mysql_close(); // Close the database connection.
		}

bpat1434

if it's [man]empty/man... it's not checked... if [man]isset/man returns false.... it's not checked...

MystedVeil

ok I changed the submit a bit and now it is telling me the code processed (I am getting the thank you for adding) but nothing is going into the database table now.

if(isset($_POST['submit'])){// handle the form
// Connect to the database.
require_once ('../incl/mysql_connect.php');
        // Query the database.    

if (isset($_POST['zone_id'])) {
foreach($_POST['zone_id'] AS $value) {
$query = "INSERT INTO
    geography_relation
    (area_id, zone_id)        VALUES
                ('$area_id','$value')";
$result = mysql_query($query) or die(mysql_error());
} 
} 

         if ($result) { // If it ran OK.

            // Send an email, if desired.
            echo '<table width=95% align=center class=tableOutline>
<tr><td><p>Thank you for adding these areas and their relations!</p><p><a href=/forms/add_georelation.php target=_self>Back</a></p></td></tr></table>'; 
 				include ('../incl/footer.html'); // Include the HTML footer.
                exit();                

        } else { // If it did not run OK.
            // Send a message to the error log, if desired.
            echo '<table width=95% align=center class=tableOutline>
<tr><td><p>Your information could not be registered due to a system error. We apologize for any inconvenience.</p></td></tr></table>'; 
            }        

        mysql_close(); // Close the database connection.
		}

Here is the actual form in case that helps:

<table width="95%" border="0" align=center>
<tr><td valign="top" colspan=2 align=center class=header>Add Area to Zone Relations</td></tr>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

///Here is where the area is selected.
<tr><td valign="top" colspan=2><strong>Area</strong>:<select class="SelectInput" name="area_id">
<option value=""></option>

                   $q = "select area_id, area from geography_area order by area";
                    $result = mysql_query($q) or die (mysql_error());                           

                    while ($row = mysql_fetch_array($result))
                    {
                        extract($row);
                        echo "<option value=\"$area_id\">$area</option>";
                    }

</select></td></tr>

$cols = 2; // how many cols you want
$result2 = mysql_query("select zone_id, zone from geography_zone order by zone");
echo mysql_error();  

if (mysql_num_rows($result2)) 
for($i=1;$row = mysql_fetch_array($result2);$i++)
{
    if($i==1)
    {
        echo "<TR>";
    }
     echo '<td width=50% valign=top><input type=checkbox name=\"zone_id[]\" value=\"$zone_id\">'.$row['zone'].'</td>';
    if($i==$cols)
    {
        echo "</TR>";
        $i=0;
    }
}
if($i!=1)
{
    echo "</TR>";
}

</form>

bpat1434

And where do you define $area_id? I dont' see it extracted anywhere before you use it.... it should be throwing an error...

MystedVeil

BTW, when using the above code it processes and gives me the "Thank you for adding these areas and their relations!" as if the query ran properly, which we know it isnt as nothing is being sent to the database no matter how many boxes I check.

are you meaning I should place

if (isset($POST['zone_id']) || isset($POST['area_id'])) {
foreach($_POST['zone_id'] AS $value) {
$query = "INSERT INTO
geography_relation
(area_id, zone_id) VALUES
('$area_id','$value')";
$result = mysql_query($query) or die(mysql_error());
}
}

it like that.. sorry more than a bit confused as I have scanned these forums as well as several other tutorials trying to figure this out.

bpat1434

Think through your code for a second:
If either zone_id or area_id are posted from a form do the following:
[indent] For each zone_id (referenced as $value) create and run a query. This query references two variables: $value and $area_id. $value is already declared (in the foreach loop); however $area_id isn't.[/indent]

See what I'm getting at? $area_id is empty because you dont' declare it... so the query should fail. Why it's not tripping an error, I'm not sure.

MystedVeil

Not usre why it isn't tripping an error either as I have included the error call I do believe.

$area_id= ($_POST['area_id']);

is that what you mean and if so, do I put in the foreach loop or before it?

I have tried it like this but still no success:

$area_id = $_POST['area_id'];
if (isset($_POST['zone_id'])) {
 foreach($_POST['zone_id'] AS $value) {

$query = "INSERT INTO
geography_relation
(area_id, zone_id) VALUES
('$area_id','$value')";
$result = mysql_query($query) or die(mysql_error());
}
}

bpat1434

What you have there should work....

Let me ask, do you have a primary key to your table? If so, what field is it on? area_id, zone_id? If it's one of those two, it might not be updating since it finds duplicates....

MystedVeil

Well that isn't working, it is still saying "Thank you for..." but not posting anything.

The primary key for the geography_relations table is geography_id.

bpat1434

do a quick mysql_affected_rows() call to see if you're actually inserting anything....