PHP - Mysqli Affected Rows Not Working With Insert

The code below kind of works, I don't get any errors. It has 2 problems; it only inserts the second row of data I get nothing for the first row. It also does not insert the 4id correctly I just get 1 instead of the actually user id number.

I'd appreciate some help, been trying to hack this out for a couple hours.


Code: [Select]
<?php
// Begin the script for this page
$id = $_SESSION['id'];
if (!isset($_POST['submit'])) { // if page is not submitted to itself echo the form

} else {
//Assign each array to a variable
$id = $_SESSION['id'];
$store = $_POST['store'];
$item = $_POST['item'];
$itemprice = $_POST['itemprice'];
$itemnumber = $_POST['itemnumber'];
$couponvalue = $_POST['couponvalue'];

$limit = count($id);

for($i=0;$i<$limit;$i++){
$id[$i] = $id[$i];
$store[$i] = check_input($store[$i]);
$item[$i] = check_input($item[$i]);
$itemprice[$i] = check_input($itemprice[$i]);
$itemnumber[$i] = check_input($itemnumber[$i]);
$couponvalue[$i] = check_input($couponvalue[$i]);
}

$query = "INSERT INTO `item` (user_id, store, item, itemprice, itemnumber, couponvalue) VALUES ('".$id[$i]."','".$store[$i]."', '".$item[$i]."', '".$itemprice[$i]."', '".$itemnumber[$i]."', '".$couponvalue[$i]."')";

if (!mysql_query($query,$link)){
die('Error: ' . mysql_error());
} else {
echo "$row record added";
}
}

?>
<div class="pageContent">
    <div id="main">
<div class="container">
<form action="" method="post">
<table>
<tr>
<input type="text" name="id[]" id="id[]" value="<?php echo $_SESSION['id'];?>" />
<td><DIV CLASS="p2"><center><b>Store</b></center></div>
<input type="text" name="store[]" id="store[]" size="15" maxlength="255"/></td>
<td><DIV CLASS="p2"><center><b>Item</b></center></div>
<input type="text" name="item[]" id="item[]" size="15" maxlength="255"/></td>
<td><DIV CLASS="p2"><center><b>Item Price</b></center></div>
<input type="text" name="itemprice[]" id="itemprice[]" size="15" maxlength="255"/></td>
<td><DIV CLASS="p2"><center><b>Item Number</b></center></div>
<input type="text" name="itemnumber[]" id="itemnumber[]" size="15" maxlength="255"/></td>
<td><DIV CLASS="p2"><center><b>Coupon Value</b></center></div>
<input type="text" name="couponvalue[]" id="couponvalue[]" size="15" maxlength="255"/></td>
</tr>
<tr>
<input type="text" name="id[]" id="id[]" value="<?php echo $_SESSION['id'];?>" />
<td><input type="text" name="store[]" id="store[]" size="15" maxlength="255"/></td>
<td><input type="text" name="item[]" id="item[]" size="15" maxlength="255"/></td>
<td><input type="text" name="itemprice[]" id="itemprice[]" size="15" maxlength="255"/></td>
<td><input type="text" name="itemnumber[]" id="itemnumber[]" size="15" maxlength="255"/></td>
<td><input type="text" name="couponvalue[]" id="couponvalue[]" size="15" maxlength="255"/></td>
</tr>
</table>
<input type="submit" name="submit" value="Submit Item">
</form>
</div>
</div>
</div>

Hi coders,     i have a js to add multiple input and its depend to a user how many input should he want to add. and my problem is, it work insert data in a first line of rows but the rest of rows are not save.   any idea, what should i add to make it work.    js

<script>
$(document).ready(function(){
  $('#add').click(function(){
   var inp = $('#box');
   var i = $('input').size() + 1;
      $('<div id="box' + i +'"><input type="text" id="name" class="name" name="code[]' + i +'" placeholder="Input '+i+'"/><img src="remove.png" width="32" height="32" border="0" align="top" class="add" id="remove" /> </div>').appendTo(inp);
				
i++;
});
$('body').on('click','#remove',function(){
$(this).parent('div').remove();
});
});
</script>

html

<div id="box">
<input name = "code[]" type="text" id="name" class="name" placeholder="Input 1">
<img src="add.png" width="32" height="32" border="0" align="top" class="add" id="add" />
</div>

php

$rf = $_POST['regform'];
$b = $_POST['branch'];
$d = $_POST['date'];
$c = $_POST['code'];
$u = $_POST['user'];
				
for($i = 0; $i<count($c); $i++)
  {
    $a_query = mysql_query("INSERT INTO code_number(ts_number,branch_id,yy,code,username) VALUES('$rf','$b','$d','$c[$i]','$u')");
  }		
  return $a_query;

The information is drawn in from a table into a form that the user will click submit and all the rows will be inputted into the table with their username association.

With this form, its only inputting the last row and not all of them. 

You're help is much appreciated!

Here is the form in the PHP area:

while($row=mysql_fetch_array($get_items_sql)){
   
              $name  =$row['item'];
              $date = ($row['expiration_date']);
              $exp_date = date('M d Y', strtotime($row['expiration_date']));
                    $content .= "";
               $content .="<table width=\"450\" border=\"0\"><tr><td width=\"200\"><span class=\"anotherfont\"><input name=\"item[]\" type=\"hidden\" value=\"$name\" id=\"item[]\">$name</span></td><td width=\"200\"><span class=\"greenfont\"><input name=\"expiration_date[]\" type=\"hidden\" value=\"$date\" id=\"expiration_date[]\">$exp_date</span><input name=\"username[]\" type=\"hidden\" value=\"$username\" id=\"username[]\"/>
</td></tr><br /></table>";
    $content .= "\n";
            }
            $content .= "</ul>\n";


Then below is part of the HTML Form with $content referenced:

<form action="add_select.php?source_id=<?php echo $coupon_source ?>" method="post" enctype="multipart/form-data" name="add_new" id="add_new">
<?php echo $content ?>


<input name="SUBMIT" type="submit" value="submit" /></form><br />


Then here is the section from the "add_select.php" file from the form:

include('connect.php');

foreach($_POST['item'] as $row=>$item)
{
$Item_name=$item;
$expiration_date=$_POST['expiration_date'][$row];
$username=$_POST['username'][$row];
}

foreach($_POST['item'] as $row=>$item)
{
$Item_name=mysql_real_escape_string($item);
$expiration_date=mysql_real_escape_string($_POST['expiration_date'][$row]);
$username=mysql_real_escape_string($_POST['username'][$row]);
}


 
$sql2="INSERT INTO place (item, expiration_date, username) VALUES ('$Item_name', '$expiration_date', '$username')"; 



if (!mysql_query($sql2))

  {

  die('Error: ' . mysql_error());

  }else {
     
     
    header("location:insert_complete.php");
}
                 

i have created my own code of custom shopping cart   i have viewcart.php working great, now when i want to insert the orders from viewcart.php with list of like 5 items, how can i insert 5 names of products into my database 1 row   example   names are 1. jean 2. mond 3. richard 4. gwen   list above is the results of my while loop, now i want to insert those names to my database column[order_productname] so that i can identity what products are paid by my clients.   i tried fetch_array but if i assign variable to fetch array result, it only shows 1 which is "jean"   i wish this is possible   dforth  

Hi all,

I am managing a social network and am creating a script to allow the admin to private message all users on the site. Even setting the max execution time to unlimited within the file does not allow us to insert all 40,000 rows into the 'message' table, it stops after several thousand rows. Our hosts tell us setting this globally is a bad idea/unstable.

Does anyone have some advice on the best to do this? No email notifications need to be sent so it is purely allowing the script to run it's full course without stopping. I did consider a cron job but without emails doing in 'batches' seems a bit unecessary so I'm clearly missing something. ANy and all advice would be most welcome!

Richard

So when I execute this it'll go for about 130k rows then give a 500 error. Any ideas how to avoid this and get it to complete?

Code: [Select]
$i=10000000;
while($i<=99999999)
  {
  $public = $i;
  $value = rand(10000000, 99999999); 
  mysql_query("INSERT INTO yummy_table (public, value) VALUES('$public', '$value' ) ") or die(mysql_error()); 
  $i++;
  }
echo "done";

Hello, is it possible to insert somehow information from textarea to mysql?
For example i have 3 lines in text area:
John Tacker is a bad guy
Tom Tacker has a big nose
Ted Tacker is the same person as John Tacker
and i want to insert them in table's different rows

I am new to MySQLI and tried to test the rollback functionality.

I created a simple database called mysqli and one table called test with and id and name field.

my PHP is as follows:
Code: [Select]
$mysqli = new mysqli('localhost',name,password,'mysqli');

$mysqli->autocommit(FALSE);

$result = $mysqli->query("INSERT INTO test (name) VALUES('bla')");

$mysqli->commit();
$mysqli->rollback();

After hitting the page, it does the insert, but does not roll back the transaction.  So it ends up being inserted into the DB.  Why is the rollback not working?

I'm a beginner here and i am learning the basic in converting from MySQL to MySQLi. I am currently working on this registration page which I would want to convert to MySQLi. Please advise me how to modify this script, I would prefer the procedural style. The MySQLi coding is not working because it would notg insert into the database like the MySQL coding would, would appreciate if your can help me. MYSQL

<?php
error_reporting(1);
$submit = $_POST['submit'];
//form data
$name = mysql_real_escape_string($_POST['name']);
$name2 = mysql_real_escape_string($_POST['name2']);
$email = mysql_real_escape_string($_POST['email']);
$password = mysql_real_escape_string($_POST['password']);
$password2 = mysql_real_escape_string($_POST['password2']);
$email2 = mysql_real_escape_string($_POST['email2']);
$address = mysql_real_escape_string($_POST['address']);
$address2 = mysql_real_escape_string($_POST['address2']);
$address3 = mysql_real_escape_string($_POST['address3']);
$address4 = mysql_real_escape_string($_POST['address4']);
$error = array();
if ($submit) {
    //open database
    $connect = mysql_connect("localhost", "root", "Passw0rd") or die("Connection Error");
    //select database
    mysql_select_db("logindb") or die("Selection Error");
    //namecheck
    $namecheck = mysql_query("SELECT * FROM users WHERE email='{$email}'");
    $count = mysql_num_rows($namecheck);
    if($count==0) {
    }
    else
    {
        if($count==1) {
            $error[] = "<p><b>User ID taken. Try another?</b></p>";
        }
    }
    //check for existance
    if($name&&$name2&&$email&&$password&&$password2&&$email2&&$address&&$address2&&$address3&&$address4) {
        if(strlen($password)<8) {
            $error[] = "<p><b>Password must be least 8 characters</b></p>";
        }
        if(!preg_match("#[A-Z]+#",$password)) {
            $error[] = "<p><b>Password must have at least 1 upper case characters</b></p>";
        }
        if(!preg_match("#[0-9]+#",$password)) {
            $error[] = "<p><b>Password must have at least 1 number</b></p>";
        }
        if(!preg_match("#[\W]+#",$password)) {
            $error[] = "<p><b>Password must have at least 1 symbol</b></p>";
        }
        //encrypt password
        $password = sha1($password);
        $password2 = sha1($password2);
        if($_POST['password'] != $_POST['password2']) {
            $error[] = "<p><b>Password does not match</b></p>";
        }
        //rescue email match check
        if($_POST['email2'] == $_POST['email']) {
            $error[] = "<p><b>Rescue Email must not be the same as User ID</b></p>";
        }
        //generate random code
        $random = rand(11111111,99999999);
        //check for error messages
        if(isset($error)&&!empty($error)) {
            implode($error);
        }
        else
        {
            //Registering to database
            $queryreg = mysql_query("INSERT INTO users VALUES ('','$name','$name2','$email','$password','$password2','$email2','$address','$address2','$address3','$address4','$random','0')");
            $lastid = mysql_insert_id();
            echo "<meta http-equiv='refresh' content='0; url=Activate.php?id=$lastid&code=$random'>";
            die ();
        }
    }
}
?>

MYSQLi (NOT WORKING AFTER CONVERTING)

<?php
error_reporting(1);
$submit = $_POST['submit'];
//form data
$name = mysqli_real_escape_string($connect, $_POST['name']);
$name2 = mysqli_real_escape_string($connect, $_POST['name2']);
$email = mysqli_real_escape_string($connect, $_POST['email']);
$password = mysqli_real_escape_string($connect, $_POST['password']);
$password2 = mysqli_real_escape_string($connect, $_POST['password2']);
$email2 = mysqli_real_escape_string($connect, $_POST['email2']);
$address = mysqli_real_escape_string($connect, $_POST['address']);
$address2 = mysqli_real_escape_string($connect, $_POST['address2']);
$address3 = mysqli_real_escape_string($connect, $_POST['address3']);
$address4 = mysqli_real_escape_string($connect, $_POST['address4']);
$error = array();
if ($submit) {
    //open database
    $connect = mysqli_connect("localhost", "root", "Passw0rd", "logindb") or die("Connection Error");
    //namecheck
    $namecheck = mysqli_query($connect, "SELECT * FROM users WHERE email='{$email}'");
    $count = mysqli_num_rows($namecheck);
    if($count==0) {
    }
    else
    {
        if($count==1) {
            $error[] = "<p><b>User ID taken. Try another?</b></p>";
        }
    }
    //check for existance
    if($name&&$name2&&$email&&$password&&$password2&&$email2&&$address&&$address2&&$address3&&$address4) {
        if(strlen($password)<8) {
            $error[] = "<p><b>Password must be least 8 characters</b></p>";
        }
        if(!preg_match("#[A-Z]+#",$password)) {
            $error[] = "<p><b>Password must have at least 1 upper case characters</b></p>";
        }
        if(!preg_match("#[0-9]+#",$password)) {
            $error[] = "<p><b>Password must have at least 1 number</b></p>";
        }
        if(!preg_match("#[\W]+#",$password)) {
            $error[] = "<p><b>Password must have at least 1 symbol</b></p>";
        }
        //encrypt password
        $password = sha1($password);
        $password2 = sha1($password2);
        if($_POST['password'] != $_POST['password2']) {
            $error[] = "<p><b>Password does not match</b></p>";
        }
        //rescue email match check
        if($_POST['email2'] == $_POST['email']) {
            $error[] = "<p><b>Rescue Email must not be the same as User ID</b></p>";
        }
        //generate random code
        $random = rand(11111111,99999999);
        //check for error messages
        if(isset($error)&&!empty($error)) {
            implode($error);
        }
        else
        {
            //Registering to database
            $queryreg = mysqli_query($connect, "INSERT INTO users VALUES ('','$name','$name2','$email','$password','$password2','$email2','$address','$address2','$address3','$address4','$random','0')");
            $lastid = mysqli_insert_id();
            echo "<meta http-equiv='refresh' content='0; url=Activate.php?id=$lastid&code=$random'>";
            die ();
        }
    }
}
?>

Hi, i have a user update function in my code so I can easily change user fields int he database
        public function updateUser ($username, $value, $what) {
$q = "UPDATE `users` SET ? = ? WHERE username = ?";
if ($stmt = $this->db_connection->prepare($q)) {
$stmt->bind_param("sis", $what, $value, $username);
$stmt->execute();
}
}

The database is connected successfully, but say i run a updateUser('blaine0002', 'blah', 'password'); nothing would get updated and no errors are thrown.
Am i doing something wrong? Thank you!

Hi again all,

Why does the foreach loop im doing, put the array values from collection, into seperate table rows rather than 1 row per whole array?



    <?php
        class registration{                    
            public $fields = array("username", "email", "password");
            public $data = array();
            public $table = "users";
            public $dateTime = "";
            public $datePos = 0;
            public $dateEntryName = "date";
            public $connection;
            
            function timeStamp(){
                return($this->dateTime = date("Y-m-d H:i:s"));
            }
            
            function insertRow($collection){      
                    //HERE
                    foreach($this->fields as $row => $value){
                        mysql_query("INSERT INTO $this->table ($value)
                        VALUES ('$collection[$row]')");
                    }
                    
                    mysql_close($this->connection->connectData);
            }
            
            function validateFields(){            

                $this->connection = new connection();
                $this->connection->connect(); 
                        
                foreach($this->fields as $key => $value){
                    array_push($this->data, $_POST[$this->fields[$key]]);
                }
                $this->dateTime = $this->timeStamp();
                array_unshift($this->data, $this->dateTime);                
                array_unshift($this->fields, $this->dateEntryName);   
                
                foreach($this->data as $value){
                    echo "$value";
                }
                
                $this->insertRow($this->data);
            }
        }
        
        $registration = new registration();
        $registration->validateFields();
    ?>




I end up with 3 rows

row 1: username
row 2: email
row 3 : password

rather than row 1:username email password.

Hello , this is my first post in this forum , i would like to see the following code and tell me your opinion , not about the security issues not about how good or bad it is. Its just a simple demonstration in order to show you the problem.

<?php

include('config/functions.php');

$mysqli = connectionToDb();

$mysqli->autocommit(FALSE);

$stmt = $mysqli->prepare("INSERT INTO users (first_name,last_name) VALUES ('stelios','stelios2')");
$stmt->execute();
$stmt->close();

$stmt = $mysqli->prepare("INSERT INTO users (first_name,last_name) VALUES ('stelios3','stelios4')");
$stmt->execute();
$stmt->close();

var_dump($mysqli->commit());

$mysqli->close();

This is the code and the var_dump($mysqli->commit()); line prints false ! BUT , the inserts are in the database. If i comment out the commit line , then nothing is in the database.    Can you advise ? Thanks !

can anyone see anything wrong here?? it wont update the Ships table..

i should of gone bed hours ago but its bugging me:

<?php
$sql="SELECT * FROM ships ORDER BY auction='Yes' DESC";
$result=mysql_query($sql);

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


<tr>
<td align="center"><strong>Active?</strong></td>
<td align="left"><strong>typeID</strong></td>
<td align="left"><strong>Item Name</strong></td>
<td align="left"><strong>Base Price</strong></td>
<td align="left"><strong>Market Price</strong></td>
<td align="center"><strong>Auction</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<?php
if ($rows['auction'] != "Yes")  { ?>
<td align="center"><img src="../images/site/delete.png" /></td>
<?php }
else { ?>
<td align="center"><img src="../images/site/accept.png" /></td>
<? } ?>
<td align="left"><span class="isk">
<? $typeID[]=$rows[typeID]; ?>
<? echo $rows['typeID']; ?></span></td>
<td align="left"><span class="eveyellow"><? echo $rows['typeName']; ?></span></td>
<td align="left"><span class="normal"><?php echo number_format($rows['basePrice'])?> isk</span></td>
<td align="left"><input name="marketPrice[]" type="text" size="10" id="marketPrice" value="<? echo $rows['marketPrice']; ?>"> <span class="credits">(<?php echo number_format($rows['marketPrice']/1000000, 2)?> m)</span></td>
<td align="center"><input name="auction[]" type="text" size="6" id="auction" value="<? echo $rows['auction']; ?>"></td>
</tr>
<?php
}
?>
<tr>
<td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>
<?php
// Check if button name "Submit" is active, do this
if($Submit){
for($i=0;$i<$count;$i++){
$sql1="UPDATE ships SET marketPrice='$marketPrice[$i]', auction='$auction[$i]' WHERE typeID='$typeID[$i]'";
$result1=mysql_query($sql1);
}
}

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

typeID is my primary key in the table and i only want to update "marketPrice" and "auction" upon submit. its a update multiple rows form by the way.

thank you so much for helping me... again.. would be lost without phpfreaks.

Hey I'm trying to check a row in my database to see if its empty but this isnt working

$usercheck = mysql_query("SELECT pet FROM users where name='$user'");
$returned_rows = mysql_num_rows($usercheck);
if ($returned_rows == 0){

// do stuff if no pet

}else{

    echo 'There was ' . $returned_rows . ' records found.';

}


The problem is its not checking the row pet its just checking to see if anything exists

my database has

id    name        pet
1     joe        <empty>

its just seeing joe and echoing "there was 1 record found" instead of seeing that the row pet is empty.

thanks for the help

Hi guys, I have an interesting situation and I was hoping someone could explain what is going on to me.

I have an enquiry form on a site that sends an e-mail. All the code is on one page with the form submitting back to the same page. I have a variable, msg, which at the top of the page is set to nothing and then an if statement to see if the form was submitted. If the form was submitted then msg will have one of three messages in it by the end (regardless of anything else). However when I check the msg variable after the if statement it is empty.

To test if it was being affected I changed the initial value from nothing to TEST. After the IF statement the variable still held the value TEST, thus confirming it wasn't being affected by the IF statement. Now onto my code (the whole page is over 600 lines long so I'm only posting the relevant code):

Code: [Select]
<?php
session_start();
$msg = '';
$input = array(
24 variables from form here...
);
if(isset($_POST['form-submitted'])) {
$missing_info = FALSE;
$array = array(
variables along with their filter validation/sanitize types...
);
$input = filter_input_array(INPUT_POST, $array);
/* PHP validation code checking appropriate fields were not empty. If they were set $missing_info = TRUE */

if(!$missing_info) {
/* Construct e-mail, this sends successfully. */
if(mail($to, $subject, $content, $headers)) { 
$msg = '<div class="message"><h1>Form Sent</h1><br />Your form has been sent to us. We endevour to respond to all booking requests with 2 working days. If you have not heard from us in that time please contact us on 01462 893620. If you chose to confirm by post please allow 5 working days before contacting us.</div>';
$_SESSION['msg'] = $msg;
} else {
$msg = '<div class="message"><h1>Technical Issues</h1><br />We appologise but the website is currently experiencing technical difficulties. Please try again later or print this form out and post / fax it to us. Alternatively you can call us on 01462 893620.</div>';
$_SESSION['msg'] = $msg;
}
} else {
$msg = '<div class="message"><h1>Incomplete Form<h1><br />Please make sure to complete all fields of the form before submitting your booking request.</div>';
$_SESSION['msg'] = $msg;
}
}
if(isset($_SESSION['msg'])) { 
$msg = $_SESSION['msg'];
unset($_SESSION['msg']);
}
?>

If I don't load the message into session and then back out of session after the if statement then $msg will still hold whatever it was initially set to (in this case an empty string). Why isn't it set in the if statement?