Jump to content

How insert multiple rows in mysql table with php array?

chackaz

By chackaz
in PHP Coding Help

 Share

Recommended Posts

chackaz Newbie

chackaz

Posted
Posted

I have an order form with will contain about 15 rows of same type of data on my exemple, I included only 4 rows. I want to use php to insert all rows in 1 table at once. All rows on the form are available to be filled up but not all should be filled. Problem I am getting is: If the first row only is filled the query will insert 1 row in the table. If all rows are filled, query will insert 4 rows in the table. Problem came when a row is skipped. Example Row 1 is filled row2 is empty row3 is filled row4 is filled. all fields on the first row will be inserted in the table but it will insert only first field ('name') of row3 and row4 event if the remaining field are not empty. Also, if the first row is empty, only first fields on the filled row be saved.

here are my codes

Entry form (custompc_form2.php)

<?php 
    include '../subs/pcpartsdrop.php';
    connect(); 

?>

<!DOCTYPE html>
<html>
<head></head>
<body>

<form action="../subs/custompcorder2.php/" method="post" id="form">

<div id="orderwrap">
    <div id="orderheather">
    <select id="platform" name="platform">
        <option selected="selected" disabled="disabled">Select the platform</option>
        <option value="Intel">Intel</option>
        <option value="AMD">AMD</option>
    </select> 
    </div> 

    <div id="orderbody">

        <p>name<select id="name" name="name[]">
                    <option selected="selected" disabled="disabled">Choose hard drive</option>
            <?php  query() ?> 
            </select>
                    quantity1: <input type="text" id="quantity" name="quantity[]"/> price1: <input id="name-data" type="text" name="price[]"/></p> 

                    <p>name<select id="name" name="name[]">
                    <option selected="selected" disabled="disabled">Choose memory</option>
            <?php  query2() ?> 
            </select>
                    quantity: <input type="text" id="quantity" name="quantity[]"/> price1: <input id="name-data" type="text" name="price[]"/></p>

                    <p>name<select id="name" name="name[]">
                    <option selected="selected" disabled="disabled">Choose monitor</option>
            <?php  query3() ?> 
            </select>
                    quantity: <input type="text" id="quantity" name="quantity[]"/> price1: <input id="name-data" type="text" name="price[]"/></p>

        <p>name<select id="name" name="name[]">
                    <option selected="selected" disabled="disabled">Choose mouse</option>
            <?php  query4() ?> 
            </select>
                    quantity: <input type="text" id="quantity" name="quantity[]"/> price1: <input id="name-data" type="text" name="price[]"/></p>

        <input id="submit" type="submit" value="Submit Order" name="submission"/>
    </div>
</div>  
</form>

Insertion form (custompcorder2.php)

I want this to go thru all rows and pick data wen the rows has data if no move to the nex ... end of the rows.

<?php
include '../db/connect.php';

$row_data = array();
foreach($_POST['name'] as $row=>$name) { 
        $name=mysqli_real_escape_string($con,$name);
        $quty=mysqli_real_escape_string($con,($_POST['quantity'][$row]));
        $price=mysqli_real_escape_string($con,($_POST['price'][$row]));



    $row_data[] = "('$name', '$quty', '$price')";
}
if (!empty($row_data)) {
    $sql = 'INSERT INTO oz2ts_custompc_details(part_id, quantity, price) VALUES '.implode(',', $row_data);
    $result = mysqli_query($con, $sql ) or die(mysqli_error($con));

    if ($result)
    echo 'Successful inserts: ' . mysqli_affected_rows($con);
    else
    echo 'query failed' ;
} 

?>

Functions feeding the drop-down list on the entry form (pcpartsdrop.php)

<?php
require_once '../db/connect1.php';


function connect(){
    mysql_connect(DB_HOST,DB_USER,DB_PASS) or die('Could not connect to database ' .mysql_error());
            mysql_select_db(DB_NAME);
}

function close(){
    mysql_close();
    }

function query(){
$myData=mysql_query("SELECT * FROM oz2ts_mijoshop_product");
while($record=mysql_fetch_array($myData)){
    echo'<option value="'.$record['product_id'].'">'.$record['model'].'</option>';
    }
}

function query2(){
$myData=mysql_query("SELECT * FROM oz2ts_mijoshop_product");
while($record=mysql_fetch_array($myData)){
    echo'<option value="'.$record['product_id'].'">'.$record['model'].'</option>';
    }

   }

function query3(){
$myData=mysql_query("SELECT * FROM oz2ts_mijoshop_product");
while($record=mysql_fetch_array($myData)){
    echo'<option value="'.$record['product_id'].'">'.$record['model'].'</option>';
    }
}

function query4(){
$myData=mysql_query("SELECT * FROM oz2ts_mijoshop_product");
while($record=mysql_fetch_array($myData)){
    echo'<option value="'.$record['product_id'].'">'.$record['model'].'</option>';
    }
}



?>
Link to comment
Share on other sites
CrimpJiggler Member

CrimpJiggler

Posted
Posted

Well the first thing I'd do is figure out what data is being submitted by the form, so instead of inserting into the DB after submitting the form, just display the data like this:

echo '<pre>' . $_POST . '</pre>';

that'll display everything that got posted in an easy to read format. If fields from row 3 are still missing, then you know its a problem with the PHP/HTML, not the SQL. As for the problem with inserting empty rows, why not just clean the array before inserting into database? What I'd do is get all the post data into a new array like this $newArray = $_POST, then clean it up so that the array only contains what I want to insert into the DB. CakePHP has built in functions for cleaning arrays, but I didn't know that when I first started using it so I wrote my old one, hold on I'll dig it up for you.

 

BTW are you able to use javascript for this project? If so, I reckon that will make life a lot easier.

Link to comment
Share on other sites
chackaz Newbie

chackaz

Posted
  • Author
Posted

Thank you CrimpJiggler.  I am new in PHP and I really appreciate your help. I use javasript to autofill the other fields I didn't put it here. I will appreciate if you can copy my code and show me ecxacly where to put your suggestions it will save me some more head each.

 

.

Link to comment
Share on other sites
CrimpJiggler Member

CrimpJiggler

Posted
Posted (edited)

Alright, gimme a few mins. Firstly though, heres a script I made a while ago for looking at whats inside global variables like $_POST, I recommend taking a look:

<?php
setcookie('testcookie1','CONTENTS_OF_COOKIE_1',time()+3600*24);
?>
<div width="100%">
<hr width="100%">
<h3>SERVER</h3>
<pre>
<?php print_r($_SERVER); ?>
<?php
?>
</pre>
</div>


<div width="100%">
<hr width="100%">
<h3>GET</h3>
<pre>
<?php print_r($_GET); ?>
</pre>
</div>


<div width="100%">
<hr width="100%">
<h3>POST</h3>
<pre>
<?php print_r($_POST); ?>
</pre>
</div>


<div width="100%">
<hr width="100%">
<h3>REQUEST</h3>
<pre>
<?php print_r($_REQUEST); ?>
</pre>
</div>


<div width="100%">
<hr width="100%">
<h3>COOKIE</h3>
<pre>
<?php print_r($_COOKIE); ?>
</pre>
</div>

<hr width="100%">
<form name="TErsts" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>?urlVar1=UV1&urlVar2=UV2">
<input name="post_field1" type="text">
<input name="hidden_post_field" type="hidden" value="afbafg">
<input name="bton" type="submit">

</form>

if you're new to PHP, it should give you some insight. I'll see if I can get your code in there, but you'll need to give me the static HTML code for your form cuz I don't have access to the database you're using to populate the dropdown menus. Otherwise I can show you how I do things. I used to make multiple database queries like that, but I've learned its easier to just make one or more database queries at the top of the page, load all the results into a multivariable array, then use that array to add items to the dropdown menus. 

Edited by CrimpJiggler
Link to comment
Share on other sites
chackaz Newbie

chackaz

Posted
  • Author
Posted (edited)

Hi Crimp  here is the entry form simplified with no drop down you can add ant value to try.

<!DOCTYPE html>
<html>
<head></head>
<body>
    
<form action="../subs/custompcorder2.php/" method="post" id="form">

            
         <p>part_id: <input id="name" name="name[]"/> quantity: <input type="text" id="quantity" name="quantity[]"/> price1: <input id="name-data" type="text" name="price[]"/></p>
         <p>part_id: <input id="name" name="name[]"/> quantity: <input type="text" id="quantity" name="quantity[]"/> price1: <input id="name-data" type="text" name="price[]"/></p>
        <p>part_id: <input id="name" name="name[]"/> quantity: <input type="text" id="quantity" name="quantity[]"/> price1: <input id="name-data" type="text" name="price[]"/></p>
        <p>part_id: <input id="name" name="name[]"/> quantity: <input type="text" id="quantity" name="quantity[]"/> price1: <input id="name-data" type="text" name="price[]"/></p>   
                           
            
        <input id="submit" type="submit" value="Submit Order" name="submission"/>
    
        
</form>
        
            
</body>

</html>


<?php
	include '../db/connect.php';

	$row_data = array();
	foreach($_POST['name'] as $row=>$name) { 
    		$name=mysqli_real_escape_string($con,$name);
    		$quty=mysqli_real_escape_string($con,($_POST['quantity'][$row]));
    		$price=mysqli_real_escape_string($con,($_POST['price'][$row]));



    	$row_data[] = "('$name', '$quty', '$price')";
	}
	if (!empty($row_data)) {
    	$sql = 'INSERT INTO oz2ts_custompc_details(part_id, quantity, price) VALUES '.implode

(',', $row_data);
    	$result = mysqli_query($con, $sql );
    
    	if ($result)
        echo 'Successful inserts: ' . mysqli_affected_rows($con);
    	else
        echo 'query failed' ;
	} 
  
?>
Edited by chackaz
Link to comment
Share on other sites
chackaz Newbie

chackaz

Posted
  • Author
Posted

here is my entry form cleaned out:

<!DOCTYPE html>
<html>
<head></head>
<body>
<form action="../subs/custompcorder2.php/" method="post" id="form">
 
<p><input id="name" name="name[]"/> 
      <input type="text" id="quantity" name="quantity[]"/>
      <input id="name-data" type="text" name="price[]"/></p>

<p><input id="name" name="name[]"/> 
      <input type="text" id="quantity" name="quantity[]"/>
      <input id="name-data" type="text" name="price[]"/></p>

<p><input id="name" name="name[]"/> 
      <input type="text" id="quantity" name="quantity[]"/>
      <input id="name-data" type="text" name="price[]"/></p>

<p><input id="name" name="name[]"/> 
      <input type="text" id="quantity" name="quantity[]"/>
      <input id="name-data" type="text" name="price[]"/></p>

<input id="submit" type="submit" value="Submit Order" name="submission"/>

</form>
</body>
 
</html>
Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.