PHP - How To Insert An Array Into A Mysql Table?

Hello, I have been at this for awhile now and cant seem to figure it out.  I have 2 separate arrays like shown below and I am trying to insert each value into my database using one update query. I have tried a for loop inside each array, but only one column populates.  I have tried breaking the values outside their for loop, etc, etc.

Does anyone know a way this can be done?

Code: [Select]

//Cars
$cars = array('ford', 'toyota', 'bmw');

//Places
$places =arrray('Orlando','California','Texas');

 foreach ($cars as $car){
          foreach ($places as $place){
          $place .=$place;
         }
 mysql_query("INSERT INTO vehicles  (cars, places) VALUES ('" . $car. "', '" . $place . "'')");
}

I am trying to insert records from an foreach array, it only inserts the first record and I cannot work out how to get it to loop through all of the records.

Thank you


foreach($_SESSION['post_data_array'] AS $seat) {
     $rowId = substr($seat, 0, 1);
     $columnId = substr($seat, 1);
      echo $rowId . $columnId . ", "; 
}

$sql125 = "INSERT INTO booked_seats(booking_id, row_id, column_id) values ('$book_id', '$rowId', '$columnId')";

$result125 = mysql_query($sql125);

if ($result125)

{

echo "worked";

}

else

{

echo "didnt work";

}

Im trying to insert some values automatically into a table once the form loads, but Im getting an error. Here is the code

Code: [Select]
<?php
$aid = $_GET['aid'];
$sd = $_GET['sd'];


?>


<style>
#message {margin:20px; padding:20px; display:block; background:#cccccc; color:#cc0000;}
</style>

<div id="message">Your notification has been submitted.</div>

<div style="text-align:center ">
<?php
$connection = mysql_connect("localhost",
        "username",
        "password");

    mysql_select_db("articles", $connection); 

$query="INSERT INTO broken_links (articleid, article) VALUES ('$aid', '$sp')";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Submitted";
mysql_close($con)
?>

<table border="0" cellpadding="3" cellspacing="3" style="margin:0 auto;" >
 
<input type="submit" id="Login" value="&nbsp;&nbsp;Thank you. Please press to close&nbsp;&nbsp;" onclick="tb_remove()"></td>
</tr>
</table>
</div>

Any help will be appreciated

Hi,   The following code was written by someone else. It allows me to upload images to a directory while saving image name in the mysql table.   I also want the code to allow me save other data (surname, first name) along with the image name into the table, but my try is not working, only the images get uploaded.   What am I missing here?

if(isset($_POST['upload']))
{

$path=$path.$_FILES['file_upload']['name'];

if(move_uploaded_file($_FILES['file_upload']['tmp_name'],$path))
{
echo " ".basename($_FILES['file_upload']['name'])." has been uploaded<br/>";
echo '<img src="gallery/'.$_FILES['file_upload']['name'].'" width="48" height="48"/>';
$img=$_FILES['file_upload']['name'];
$query="insert into imgtables (fname,imgurl,date) values('$fname',STR_TO_DATE('$dateofbirth','%d-%m-%y'),'$img',now())";
if($sp->query($query)){
echo "<br/>Inserted to DB also"; 
}else{
echo "Error <br/>".$sp->error; 
}
}
else
{
echo "There is an error,please retry or check path";
}
}

?>

joseph

An affiliate marketer refers some products which are stored in a simple array.
My website creates a JSON variable of all products sold.

$all_items_sold = '{"7777":{"item":"hammer","price":"4.99"},"8888":{"item":"nail","price":"1.99"},"9999":{"item":"apple","price":"2.00"}}';
$referred_by_Affiliate = array('1234','8888','7777');

So, out of all the 3 items that sold, only 2 of them were referred by the affiliate marketer.
DESIRED EFFECT: insert this product 8888: $1.99 and insert this product 7777: $4.99 into mysql.

Currently I do this:
1.) foreach loop,
2.) use strpos to see if it's in the raw JSON variable.
3.) If there, I use preg_match to find the price.
4.) Do a mySQL insert while still in the foreach loop.

Is there a "best practices" way to accomplish this?
I'm guessing there is a "one liner" so I don't have to do a foreach loop and using strpos.
And I'm guessing there is a way to do multiple mySQL inserts all at once with just one line of code, instead of from inside a foreach loop.
This probably can all be accomplished with just one line of code total, error trapping included 😁

Thank you.
 

Edited July 28 by ChenXiu

Good evening,   I am working on a program that takes images, and casts them into an associated array, I then want to display thumbnails of those images into a 3 column by 4 row table...   [image][image][image] [image][image][image] [image][image][image] [image][image][image]   (For my visual friends)   here is my current, pardon for the mess, but right now, functionality is more important.

<!DOCTYPE html>
<html>
<head>
	<title>Zodiac Gallery</title>
</head>
<body>
	
<h2 style="text-align:center">Zodiac Gallery</h2>
<p>Click a thumbnail image to see enlarged view.</p>
<?php

$ZodiacArray = array (
	"Images/rat.jpg" => "Rat",
	"Images/ox.jpg" => "Ox",
	"Images/tiger.jpg" => "Tiger",
	"Images/rabbit.jpg" => "Rabbit",
	"Images/dragon.jpg" => "Dragon",
	"Images/snake.jpg" => "Snake",
	"Images/horse.jpg" => "Horse",
	"Images/goat.jpg" => "Goat",
	"Images/monkey.jpg" => "Monkey",
	"Images/rooster.jpg" => "Rooster",
	"Images/dog.jpg" => "Dog",
	"Images/pig.jpg" => "Pig");

foreach ($ZodiacArray as $image => $zodiac)
	{
		echo "<table>
			<tr>
				<td><img src="$image"  alt="$zodiac" style="width:68px;height:65px"></td
				<td><img src="$image" alt="$zodiac" style="width:68px;height:65px"></td>
				<td><img src="$image" alt="$zodiac" style="width:68px;height:65px"></td>
			</tr>
			<tr>
				<td><img src="$image" alt="$zodiac" style="width:68px;height:65px"></td>
				<td><img src="$image" alt="$zodiac" style="width:68px;height:65px"></td>
				<td><img src="$image" alt="$zodiac" style="width:68px;height:65px"></td>
			</tr>
			<tr>
				<td><img src="$image" alt="$zodiac" style="width:68px;height:65px"></td>
				<td><img src="$image" alt="$zodiac" style="width:68px;height:65px"></td>
				<td><img src="$image" alt="$zodiac" style="width:68px;height:65px"></td>
			</tr>
			<tr>
				<td><img src="$image" alt="$zodiac" style="width:68px;height:65px"></td>
				<td><img src="$image" alt="$zodiac" style="width:68px;height:65px"></td>
				<td><img src="$image" alt="$zodiac" style="width:68px;height:65px"></td>
			</tr>
		</table>";

?>

Help is welcome, thank you.

Hi guys   A mobile application send data in Json format, so I want to insert the data into Mysql table, when I try to insert I have gotten the error "PHP Warning: mysqli::prepare() expects exactly 1 parameter, 3 given", my table only has 2 columns, "id" and "name" column.   The array has this data

(
[0] => ('id', '2')
[1] => ('name', 'Jhon')
)

The function to parse and insert data ...

function redeem() {
    // Check for required parameters
        $json = file_get_contents('php://input');
        $obj = json_decode($json,true);
        // Assumes $obj == array(0 => $assocArray, 1 => $assocArray)
        foreach($obj as $index => $assocArray) {
            // Assumes $assocArray == array(0 => array('id' => '2'), 1 => array('name' => 'John'))
            $stmt = $this->db->prepare('INSERT INTO prueba (id,nombre) VALUES (%d,%s)',$assocArray[0]['id'],$assocArray[1]['name']) or die(mysqli_error($this->db));
            $stmt->execute();
            
        }    
        
    }

Edited by Maq, 06 June 2014 - 03:54 PM.

Hello,

I'm working on a form that once submitted, it inserts the result into my SQL table, but I'm trying to get it so it will update an imploded array.

EG: current SQL table is "red,blue,green". Then user submits 'white', the SQL table should then update to: "red,blue,green,white" (notice how its added to the end). This is what I have so far:

Code: [Select]
$attack_out = $info['attack_out'];
$attack_out_split = explode(",", $attack_out);

if(isset($_POST["submit_attack"])){
   $attack_user = $_GET["attack"]; // user - sent from form
   //$troops_send = $_POST["troop_send"]; // ammount - sent from form

   $attack_user_array = array();
   $i = 0;
   $max = (count($attack_out_split) + 1);
   while($i < $max){
      $reverse = ($max - 1) - $i;
      $attack_user_array[$i] = $attack_out_split[$reverse];
      $attack_user_array[$max - 1] = $attack_user;
      $i++;
   }
   $attack_user_glue = implode(",",$attack_user_array);
   $result = mysql_query("UPDATE users SET attack_out='$attack_user_glue' WHERE username='$username'");
}

I can't figure out the algorithm in my while loop. Would appreciate any help, thanks

Hi to All,

I am performing calculation which i echo it in the input form to insert it to DB table
The calculation works fine when i submit the form but the it does not correct calculation to the DB.
it seems that the isert to database is done before the calculation and I can't figure a way around it.

Because i'm submitting to the same page, the calculation populate in the input form correctly but it insert Zero to the database table instead of correct calculation populated in the input field to DB table

You could see from the line 114 to 116 that, i'm performing some calculations and echo it at line 128 and line 128 in the input form value field.

Please any help on how to do this...All the function in the code is in another file and it works fine...so the only program is that the calculated value is not inserted as expected

Here is the code

<?php
include 'core/initForMainLogPage.php';


if(isset($_GET['empId']) && !empty($_GET['empId'])){

//delete employee here
$empId=$_GET['empId'];
grabEmpId($empId);
}
?>

<?php
if(logged_in()){
$data=user_dataManager('username');
$usernameData=$data['username'];
}else{
header('Location: index.php');
}
?>
<?php include 'includes/adminHeadAll.php';?>

<header>
<?php include 'includes/managerMenu.php';?>  
</header>
<div class="container">
<br/>
<h3>Pay Employee</h3>
<?php
$error=array();
$errorAll='';
$leave="";
if(isset($_POST['empId']) && isset($_POST['name']) && isset($_POST['date']) && isset($_POST['basicSalary'])
&& isset($_POST['leave']) && isset($_POST['salaryPerDay']) && isset($_POST['leaveDeduct']) 
&& isset($_POST['netSalary'])){
$empId=htmlentities(mysql_real_escape_string($_POST['empId']));
$name=htmlentities(mysql_real_escape_string($_POST['name']));
$date=htmlentities(mysql_real_escape_string($_POST['date']));
$basicSalary=htmlentities(mysql_real_escape_string($_POST['basicSalary']));
$leave=htmlentities(mysql_real_escape_string($_POST['leave']));
$salaryPerDay=htmlentities(mysql_real_escape_string($_POST['salaryPerDay']));
$leaveDeduct=htmlentities(mysql_real_escape_string($_POST['leaveDeduct']));
$netSalary=htmlentities(mysql_real_escape_string($_POST['netSalary']));



//checking for the validity of data entered
if(empty($leave) || empty($date)){
$error[]='Pleave leave or date field is empty.';
}else{

if(preg_match('/[0-9]/',$leave)==false){
$error[]='Leave should only contain numbers'; 
}

if(empId($empId)===false){
$error[]="This employee is not recoganize by the system and can not be paid,he may need to register first.";
}

}

if(!empty($error)){
$errorAll= '<div class="error"><ul><li>'.implode('</li><li>',$error).'</li></ul></div>';


}else{
//this funciton insert into database
payrollData($name,$empId,$date,$basicSalary,$leave,$salaryPerDay,$leaveDeduct,$netSalary);
echo '<p class="pa">Payment made successfully.&nbsp;&nbsp;&nbsp;<a href="employees-salary-report.php">See Payment Records</a></p>';
}


}//end isset


?>

<div class="tableWrap">
<form action="" method="post" >
<div class="styletable"><table cellpadding="" cellspacing="" border="0">
<?php
$query=mysql_query("SELECT 
			  empId,name,level,company.compId,company.levelOne,company.levelTwo,
			  company.levelThree,company.levelFour,company.levelFive
			   FROM employee JOIN company ON company.compId=1 WHERE empId='$empId' LIMIT 1");


while($row=mysql_fetch_array($query)){
  $empId=$row['empId'];
  $name=$row['name'];
  $levelEmp=$row['level'];
  $levelOne=$row['levelOne'];
  $levelTwo=$row['levelTwo'];
  $levelThree=$row['levelThree'];
  $levelFour=$row['levelFour'];
  $levelFive=$row['levelFive'];
  
  if($levelEmp==1){
	  $levelPay=$levelOne;
  }elseif($levelEmp==2){
	  $levelPay=$levelTwo;
  }elseif($levelEmp==3){
	  $levelPay=$levelThree;
  }elseif($levelEmp==4){
	  $levelPay=$levelFour;
  }elseif($levelEmp==5){
	  $levelPay=$levelFive;
  }
  


//making calculations here

$basicSalary=$levelPay * 30;
$leaveDeduct=$leave * $levelPay;
$netSalary=$basicSalary - $leaveDeduct;
}


?>
<tr><td>Employee ID: </td><td><input type="text" name="empId" readonly="readonly" value="<?php if(isset($empId)){echo $empId;}?>"></td></tr>
<tr><td>Employee: </td><td><input type="text" name="name" readonly="readonly" value="<?php if(isset($name)){ echo $name;}?>"></td></tr>
<tr><td>Date: </td><td><input type="text" id="Date" class="picker" name="date"></td></tr>

<tr><td> Basic Salary: </td><td><input type="text" name="basicSalary" readonly="readonly" value="<?php echo $basicSalary;?>"></td></tr>
<tr><td> No. Of Absent: </td><td><input type="text" name="leave" class="input" value=""></td></tr>
<tr><td> Salary Per Day:</td><td><input type="text" name="salaryPerDay" readonly="readonly" value="<?php echo $levelPay;?>"></td></tr>
<tr><td> Deduction For Absentee:</td><td><input type="text" name="leaveDeduct" readonly="readonly" value="<?php echo $leaveDeduct;?>"></td></tr>
<tr><td> Net Salary:</td><td><input type="text" name="netSalary" readonly="readonly" value="<?php echo $netSalary;?>"></td></tr>
<tr><td> </td><td><input type="submit" value="Submit Pay" class="submit" name="pay"></td></tr>
</table></div>

</form>
<?php



?>
</div>
<br />
<?php echo $errorAll; ?>
<p>Manage the monthly salary details of your employee along with the allowances, deductions, etc. by just entering their leave</p>
</div> 
<?php include 'includes/footerAll.php';?>  

<script type="text/javascript" src="js/jquery.js"></script>
<script type="text/javascript" src="js/jquery-ui.js"></script>
<script type="text/javascript" src="js/ui.js"></script>
</body>
</html>

What gets put in for the values is my problem.  I have tried every way I can think of but I get errors in the code or I don't get the row inserted.

Can anyone give me a code sample using $ variables, preferably a multi-line list?  I would have to fix up my code to show you what I am trying as it is in between tries now.

I have to be doing something wrong and may have to put together a simple test script but a working example would be better.  Everything I look at on the web shows "xxxxx xxxx" examples not $variables.

Thanks in advance.

Hey yall!
I'm working on a new site idea and I've run across a problem that I know is simple enough but I'm stumped. It's in the signup form
What I want to do is insert the new user into the 'Login' table and get the user id that was just created and use that to create a table with the user id in the name.

Here is what I have:
 // now we insert user into 'Login' table

mysql_real_escape_string($insert = "INSERT INTO `Login` (`UID`, `pass`, `HR`, `mail`, `FullName`) VALUES ('{$_POST['username']}', '{$_POST['pass']}', '{$_POST['pass2']}', '{$_POST['e-mail']}', '{$_POST['FullName']}')");

mysql_query($insert) or die( 'Query string: ' . $insert . '<br />Produced an error: ' . mysql_error() . '<br />' );

 $error="Thank you, you have been registered.";

 setcookie('Errors', $error, time()+20);
 
 // Get user ID

mysql_real_escape_string($checkID = "SELECT * FROM Login WHERE `mail` = '{$_POST['e-mail']}'");

while ($checkIDdata = mysql_fetch_assoc($checkID)) { 

$userID = $checkIDdata;


 // now we create table 'Transactions" for the user
mysql_real_escape_string($create = "CREATE TABLE  `financewatsonn`.`Transactions_{$userID}` (
`ID` INT( 20 ) NOT NULL AUTO_INCREMENT COMMENT  'Transaction ID',
 `name` VARCHAR( 50 ) NOT NULL COMMENT  'Name/Location',
 `amount` VARCHAR( 50 ) NOT NULL COMMENT  'Amount',
 `date` VARCHAR( 50 ) NOT NULL COMMENT  'Date',
 `category` VARCHAR( 50 ) DEFAULT NULL COMMENT  'Category',
 `delete` INT( 1 ) NOT NULL DEFAULT  '0',
UNIQUE KEY  `ID` (  `ID` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8 COMMENT =  'User ID {$userID}'");

mysql_query($create) or die( 'Query string: ' . $create . '<br />Produced an error: ' . mysql_error() . '<br />' );
}

I know in  'Get user ID' that I need to get the ID but I'm not sure how to get that information.

i get the error Quote

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

at 166 which is the while line under Get user ID

I am simply trying to insert a value generated from an array in a while loop, but it seems the value is not global and I can't pass it as I like. I did some research on this, but could not find an answer that solved my issue...

Here is my select box code which is working perfect:

<select name="city">
<?php
$sql = "SELECT id, city_name FROM cities ".
"ORDER BY city_name";
$results_set = (mysqli_query($cxn, $sql)) or die("Was not able to produce the result set!");
while($row = mysqli_fetch_array($results_set)) {
echo "<option value=$row[id]>$row[city_name]</option>";
}
?>
</select>

Are any variables defined in a while loop global to the while loop only?

Here is my SQL which you can see my $row[id] being passed thru field city_id... The value is being generated as supposed to based on value like: value="1", value="2" etc.. for the select options for each city name. So the values are there... But I CANNOT get that numerical id to pass to the database when submitting my form.

Any ideas for a workaround to get this value passing as normal?

if (isset($_POST['addPosting'])) {
      $query = "INSERT INTO Postings (id, city_id, title, description) VALUES
('','$row[id]','$_POST[title]','$_POST[description]')";

hello

i have a db with name which populates a form dropdown list
there are various variables like this in the form.
the user selects their choices and then submits the form to a new db table


at the moment it does not capture the dropdown data to the insert sql

help please

here is the code:

===============================

<?php
include ('connect.php');

// Insert any new image into database
if(isset($_POST['xsubmit']) && $_FILES['imagefile']['name'] != "") {
  $fileName = $_FILES['imagefile']['name'];
  $fileSize = $_FILES['imagefile']['size'];
  $fileType = $_FILES['imagefile']['type'];
  $content  = addslashes (file_get_contents($_FILES['imagefile']['tmp_name']));
  $jeweltype = $_POST['jeweltype'];
  $jewelsize = $_POST['jewelsize_in'];
  $jewelcolour = $_POST['jewelcolour_in'];
  $jewelmaterial = $_POST['jewelmaterial_in'];
  $jewelgender = $_POST['jewelgender_in'];

  if(!get_magic_quotes_gpc()) {
    $fileName = addslashes($fileName);
  }

  // Checking file size
  if ($fileSize < 150000) {
    mysql_query ("INSERT into jewel_images (name,size,type,content,jeweltype,jewelsize,jewelcolour,jewelmaterial,jewelgender) "
   . "values ('$fileName','$fileSize','$fileType','$content','$jeweltype','$jewelsize','$jewelcolour','$jewelmaterial','$jewelgender')");
  } else {
    $err = "The Image file to too large!";
  }
}

// Find out about latest image

$gotten = mysql_query("select * from jewel_images order by row_id desc");
$row    = mysql_fetch_assoc($gotten);
$bytes  = $row['content'];

// If this is the image request, send out the image

 if ($_REQUEST['pic'] == 1) {
   header("Content-type: $row[type];");
 print $bytes; 
 }
?>
<html>
<head>
<title>Upload an image to a database</title>
</head>
<body>
  <font color="#FF3333"><?php echo $err ?></font>
  <table>
    <form name="Upload" enctype="multipart/form-data" method="post">
      <tr>
        <td>Upload <input type="file" name="imagefile"><br />
            Jewelery Type:
               <select>
               <?php
               $sql="SELECT jeweltype FROM jeweltypes";
               $result =mysql_query($sql);
               while ($data=mysql_fetch_assoc($result)){
               ?>
               <option value ="jeweltype" ><?php echo $data['jeweltype'] ?></option>
               <?php } ?>
               </select>
               <br />
            Jewelery Size:
               <select>
               <?php
               $sql="SELECT jewelsize FROM jewelsizes";
               $result =mysql_query($sql);
               while ($data=mysql_fetch_assoc($result)){
               ?>
               <option value ="jewelsize" ><?php echo $data['jewelsize'] ?></option>
               <?php } ?>
               </select>
               <br />
            Jewelery Colour:
               <select>
               <?php
               $sql="SELECT jewelcolour FROM jewelcolours";
               $result =mysql_query($sql);
               while ($data=mysql_fetch_assoc($result)){
               ?>
               <option value ="jewelcolour_in" ><?php echo $data['jewelcolour'] ?></option>
               <?php } ?>
               </select>
               <br />
            Jewelery Material:
               <select>
               <?php
               $sql="SELECT jewelmaterial FROM jewelmaterials";
               $result =mysql_query($sql);
               while ($data=mysql_fetch_assoc($result)){
               ?>
               <option value ="jewelmaterial_in" ><?php echo $data['jewelmaterial'] ?></option>
               <?php } ?>
               </select>
               <br />
            Jewelery Gender:
               <select>
               <?php
               $sql="SELECT jewelgender FROM jewelgenders";
               $result =mysql_query($sql);
               while ($data=mysql_fetch_assoc($result)){
               ?>
               <option value ="jewelgender_in" ><?php echo $data['jewelgender'] ?></option>
               <?php } ?>
               </select>
               <br />
        <input type="submit" name="xsubmit" value="Upload">
        </td>
      </tr>
      <tr>
       <td>Latest Image</td>
      </tr>
      <tr>
        <td><img src="?pic=1"></td>
      </tr>
    </form>
 </table>
</body>
</html>

How can i use single quote for values?

$qry='insert into tablename values('a','b');';

Hello guys, im using this code atm and it's working, but the new rows created are put in the end of the table. I want them to stack up from the beginning pushing older rows down. What should i do to make that work?


<?php
$con = mysql_connect("localhost","*****","*******");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("ananaz_se", $con);

$sql="INSERT INTO stuff (adult, namn, url)
VALUES
('$_POST[adult]','$_POST[namn]','$_POST[url]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 row added";

mysql_close($con)
?>