PHP - Select,if Exists,update,insert Problem

i dont know if i remember this right.... but months ago i found an article saying that UPDATE is nice since it can do INSERT if there is nothing to update... is this true? or is there other functions that can do this?

if(isset($_POST['lqlabour']))
{
//insert quoted labour
foreach ($_POST['lqlabour'] as $k => $lqlabour) {
    $ldescription = $_POST['ldescription'][$k];
	$llabour = $_POST['llabour'][$k];
	$lhelper = $_POST['lhelper'][$k];
	$lmarkup = $_POST['lmarkup'][$k];
	$ltotal = $_POST['ltotal'][$k];
	
	
	
	if ($ltotal == '0.00'){
	 $lstatus = "2";
	 } else {
	 $lstatus = "1";
	 }
	
	 $sql = "INSERT INTO quotedlabour VALUES (
	 NULL, 
	 $quoteid,
	 $k,
	 '" . mysql_real_escape_string($lqlabour) ."',
	 '" . mysql_real_escape_string($ldescription) ."',
	 '" . mysql_real_escape_string($llabour) ."',
	 '" . mysql_real_escape_string($lhelper) ."',
	 '" . mysql_real_escape_string($lmarkup) ."',
	 '" . mysql_real_escape_string($ltotal) ."',
	 '" . mysql_real_escape_string($lstatus) ."'
	 )";
	 mysql_query($sql) or die('Error 33 adding.  Check you fields and try again.');	 
}
}
//insert quoted labour




//update existing labour status
foreach ($_POST['lid'] as $s => $lid) {
$lid = $_POST['lid'][$s];
$ltotal = $_POST['ltotal'][$s];


if ($ltotal == '0.00'){
	 $lstatus = "2";
	 } else {
	 $lstatus = "1";
	 }

$sql1 = "UPDATE quotedlabour SET status = '" . mysql_real_escape_string($lstatus) ."', total = '" . mysql_real_escape_string($ltotal) ."' WHERE id = '$lid'"; 
  
$result3 = mysql_query($sql1) or die('Error, updating 222 failed.  Check you fields and try again.');

}
//update existing labour status




$lii=200;		
$result = mysql_query("SELECT * FROM quotedlabour WHERE quoteid = $quoteid ORDER BY linenumber");
while($row = mysql_fetch_array($result))
{

  $id=$row['id'];
  $qlabour=$row['qlabour'];
  $description=$row['description'];
  $labour=$row['labour'];
  $helper=$row['helper'];
  $markup=$row['markup'];
  $total=$row['total'];
  $status=$row['status'];
  
  if($status == 1) {     
    $active = "checked";
}
  if($status == 2) {     
    $active = "unchecked";
	}
 
$lqty="lqlabour-$lii";
$lqhelper="lhelper-$lii";
$lqlabour="llabour-$lii";
$lqtotal="ltotal-$lii";
$lqbox="lbox-$lii";
  

  echo "<tr>";
  echo "<input type='hidden' name='lid[]' value='" . $id . "'>";
  echo "<td align='center'><input type='text' name='lqlabour[]' id='" . $lqty . "' size='10' disabled='disabled' value='" . $qlabour . "'></td>";
  echo "<td align='center'>" . $description . "</td>";
  echo "<td align='center'><input type='text' name='llabour[]' id='" . $lqlabour . "' size='10' disabled='disabled' value='" . $labour . "'></td>";
  echo "<td align='center'><input type='text' name='lhelper[]' id='" . $lqhelper . "' size='10' disabled='disabled' value='" . $helper . "'></td>";
  echo "<td align='center'><input type='text' name='ltotal[]' id='" . $lqtotal . "' size='10' readonly value='" . $total . "'></td>";
  echo "<td align='center'><input type='checkbox' name='lbox[]' id='" . $lqbox . "' value='0' $active onblur='filllabour(this.id)'></td>";
  echo "</tr>";
  
  $lii=$lii+1;
  }
		

Hi All, I have the following code which is a form that pulls records from a database to show and it also allows you to dynamically add new rows to the database and im stumped.  when I submit a form the record that previously existed is updated perfectly but any time I add a new record it is adding it but one field called total is getting its value from the first row of the database instead of using the data from the form.  any help is much appreciated.    

Hi,

I have about 40 select boxes and I've allocated each of them an individual id because the amount of select boxes will never change.  I've put each value in a JS array and then receive it in PHP through AJAX.

The problem I am having is to do with inserting/updating data in the db. The problem is currently I have the following logic set when the user presses the save button:

Code: [Select]
[center]
                        $sql = "SELECT * FROM tbl_supervision WHERE workload_id = '".$_SESSION['active_workload']."' AND semester = '1';";       
$result = mysql_query($sql) or die ("Error in query: $sql. " . mysql_error());
$rows = mysql_num_rows($result);
$supervision_data = array();
 
while($row = mysql_fetch_array($result))
{
$supervision_data[] = $row;
}

if(!empty($supervision_data))
{
for($i = 0; $i < count($supervision_data); $i++)
{
$sql4 = "UPDATE tbl_supervision SET workload_id = '".$_SESSION['active_workload']."', degree = '".$degree1array[$i]."', student_name = '".$student1array[$i]."', student_eft = '".$eft1array[$i]."', supervision_role = '".$role1array[$i]."' WHERE supervision_id = '".$supervision_data[$i]['supervision_id']."' AND semester = '1';";
$result4 = mysql_query($sql4) or die ("Error in query: $sql4. " . mysql_error());

if (!$result4)
{
die('Error: ' . mysql_error());
}
else
{
$success = 1;
}
}
}
else
{
for($i = 0; $i < count($degree1array); $i++)
{
if($degree1array[$i] != "None" || $student1array[$i] != "" || !empty($eft1array[$i]) || $role1array[$i] != "")
{
if($degree1array[$i] != "None")
{
$sql7 = "INSERT into tbl_supervision (workload_id, degree, student_name, student_eft, supervision_role, semester) VALUES ('".$_SESSION['active_workload']."', '".$degree1array[$i]."', '".$student1array[$i]."', '".$eft1array[$i]."', '".$role1array[$i]."', '1');";
$result7= mysql_query($sql7) or die ("Error in query: $sql7. " . mysql_error());

if (!$result7)
{
die('Error: ' . mysql_error());
}
else
{
$success = 1;
}
}
}
else
{
$success = 0;
}
}
}[/center]


What I want to achieve is:

1. If there are the same number of inputs in the db compared to the number of inputs received from the select boxes, the update query will simply kick in.
2. If there there are less number of inputs in the db compared to the number of inputs received from the select boxes, the insert query will only insert those values that aren't already in the db.

Currently, with the code I have posted above,  I can only insert values from scratch and only update those values that are already there in the db but I can't add in new values after the initial data has been inserted.

I've tried using count() and sizeof() functions but I can't seem to work out how could I manipulate the code to achieve what should be straight forward functionality. Could anybody please help me out?

The degree1array, student1array, eft1array & role1array are the arrays that contain the select box values..

Code: [Select]
var degree1 = new Array (document.getElementById('degreetype1').value, document.getElementById('degreetype2').value, document.getElementById('degreetype3').value,
document.getElementById('degreetype4').value, document.getElementById('degreetype5').value, document.getElementById('degreetype6').value,
document.getElementById('degreetype7').value, document.getElementById('degreetype8').value, document.getElementById('degreetype9').value,
document.getElementById('degreetype10').value);

   var student1 = new Array (escape(document.getElementById('studentname1').value), escape(document.getElementById('studentname2').value), escape(document.getElementById('studentname3').value),
escape(document.getElementById('studentname4').value), escape(document.getElementById('studentname5').value), escape(document.getElementById('studentname6').value),
escape(document.getElementById('studentname7').value), escape(document.getElementById('studentname8').value), escape(document.getElementById('studentname9').value),
escape(document.getElementById('studentname10').value));

   var eft1 = new Array (document.getElementById('eft1').value, document.getElementById('eft2').value, document.getElementById('eft3').value,
document.getElementById('eft4').value, document.getElementById('eft5').value, document.getElementById('eft6').value,
document.getElementById('eft7').value, document.getElementById('eft8').value, document.getElementById('eft9').value,
document.getElementById('eft10').value);

   var role1 = new Array (document.getElementById('suprole1').value, document.getElementById('suprole2').value, document.getElementById('suprole3').value,
document.getElementById('suprole4').value, document.getElementById('suprole5').value, document.getElementById('suprole6').value,
document.getElementById('suprole7').value, document.getElementById('suprole8').value, document.getElementById('suprole9').value,
document.getElementById('suprole10').value);

Thanks

I am logging occasions someone's profile is viewed.

I will use a Cron to report the amount of views.

Do I?

(1) INSERT a record every time and count up at end. Remember, I'm using a Cron to report amount of views so won't be counting up on every page request!!

(2) UPDATE a table (but won't I need to SELECT previous value first)? Unless, "UPDATE field1 and add 1 to it's value" is possible.

Which is best?

I have a form right now that Updates a table on my database.

I was wondering if I could possibly still have that working but also Insert the same information into another table on the database when the submit button is pressed?

Hi i need to count the number items in an album now on the album i have create a field called count which counts the number items in that album

but now i dnt knw how to update those as soon insert a item in the table item

so i thought woul be possible to create a script that when insert data into item tables also update album table row count by adding one like update count with A now A equal row count ++1  any help on start of it if possible???/

Hello:

Can someone give me some insight on how to be able to not just add (INSERT) a photo and photo description with the following code, but also how to add the ability to UPDATE (if the user wants to replace a photo or adjust the photo description):

Code: [Select]
<?php
include("config.inc.php");

// initialization
$result_final = "";
$counter = 0;

// List of our known photo types
$known_photo_types = array( 
'image/pjpeg' => 'jpg',
'image/jpeg' => 'jpg',
'image/gif' => 'gif',
'image/bmp' => 'bmp',
'image/x-png' => 'png'
);

// GD Function List
$gd_function_suffix = array( 
'image/pjpeg' => 'JPEG',
'image/jpeg' => 'JPEG',
'image/gif' => 'GIF',
'image/bmp' => 'WBMP',
'image/x-png' => 'PNG'
);

// Fetch the photo array sent by preupload.php
$photos_uploaded = $_FILES['photo_filename'];

// Fetch the photo caption array
$photo_caption = $_POST['photo_caption'];

while( $counter <= count($photos_uploaded) )
{
if($photos_uploaded['size'][$counter] > 0)
{
if(!array_key_exists($photos_uploaded['type'][$counter], $known_photo_types))
{
$result_final .= "File ".($counter+1)." is not a photo<br />";
}
else
{
mysql_query( "INSERT INTO gallery_photos(`photo_filename`, `photo_caption`, `photo_category`) VALUES('0', '".addslashes($photo_caption[$counter])."', '".addslashes($_POST['category'])."')" );
$new_id = mysql_insert_id();
$filetype = $photos_uploaded['type'][$counter];
$extention = $known_photo_types[$filetype];
$filename = $new_id.".".$extention;

mysql_query( "UPDATE gallery_photos SET photo_filename='".addslashes($filename)."' WHERE photo_id='".addslashes($new_id)."'" );

// Store the orignal file
copy($photos_uploaded['tmp_name'][$counter], $images_dir."/".$filename);

// Let's get the Thumbnail size
$size = GetImageSize( $images_dir."/".$filename );
if($size[0] > $size[1])
{
$thumbnail_width = 100;
$thumbnail_height = (int)(100 * $size[1] / $size[0]);
}
else
{
$thumbnail_width = (int)(100 * $size[0] / $size[1]);
$thumbnail_height = 100;
}

// Build Thumbnail with GD 1.x.x, you can use the other described methods too
$function_suffix = $gd_function_suffix[$filetype];
$function_to_read = "ImageCreateFrom".$function_suffix;
$function_to_write = "Image".$function_suffix;

// Read the source file
$source_handle = $function_to_read ( $images_dir."/".$filename ); 

if($source_handle)
{
// Let's create an blank image for the thumbnail
      $destination_handle = ImageCreate ( $thumbnail_width, $thumbnail_height );

// Now we resize it
       ImageCopyResized( $destination_handle, $source_handle, 0, 0, 0, 0, $thumbnail_width, $thumbnail_height, $size[0], $size[1] );
}

// Let's save the thumbnail
$function_to_write( $destination_handle, $images_dir."/tb_".$filename );
ImageDestroy($destination_handle );
//

$result_final .= "<img src='".$images_dir. "/tb_".$filename."' /> File ".($counter+1)." Added<br />";
}
}
$counter++;
}

// Print Result
echo <<<__HTML_END

<html>
<head>
<title>Photos uploaded</title>
</head>
<body>
$result_final
</body>
</html>

__HTML_END;
?>

The tutorial says to edit a photo, simply add:
Code: [Select]
function edit_photo( $photo_id, $new_caption, $new_category )        <br>
{        <br>
  mysql_query( "UPDATE gallery_photo SET photo_caption='".addslashes( $new_caption )."', photo_category='".addslashes( $new_category )."' WHERE photo_id='".addslashes( $photo_id )."'"  );        <br>
}


But I can not figure out how to call that function.

Anyone help me out?

Thanks.

I am attempting something similar to the following:

Code: [Select]
mysql_query("UPDATE table SET name='$name' WHERE id=$id");
if (mysql_affected_rows()==0) {
mysql_query("INSERT INTO table (id, name) VALUES ('$name',$id);
}

If the $id row does not exists in the table, mysql_affected_rows() returns 0 and a new $id row gets inserted
but if the $id row already exists and UPDATE changes nothing, mysql_affected_rows() still returns 0
and gives an 'duplicate id' error as expected.

I know I could use a SELECT to test for the existance of the $id row.
In Perl there is an '0E0', 'zero but true', condition to handle this. Is there an equivilant in PHP?
 

I am trying to create a script that takes information from a form and puts in a database.
In the action page, I decided to post a URL that shows the user there story that they posted. This is where I ran into the problem. . I realized that for the optional fields I could not just use a seperate insert statement, because this creates a new row. So I desided to use update statments, but this STILL does not work, they values or simply not getting inserted.  Here is the code:
Code: [Select]
<?php

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

die('SPAM BOT!');
}
if (
!isset($_POST['title']) 
&& !isset($_POST['summary'])
&& !isset($_POST['story'])
&& !isset($_POST['rating'])
&& !isset($_POST['cat'])
)
{
die("<div id='impor'>You forgot to enter one(or more) of the following fields <br /> 
1. Title <br />
2. Summary <br />
3. Story<br />
 </div>
 ");

}


mysqlConnect();


//take data from form an\ put them in variable
$title_form = bb(mysql_real_escape_string($_POST['title'])); //required 
$summ_form = bb(mysql_real_escape_string($_POST['summary']));// required
$story_form = bb(mysql_real_escape_string($_POST['story'])); 
$cat_form = $_POST['cat'];
$rating_form = $_POST['rating'];
$username = $_SESSION['user'];
// Make the other var into a list of links




mysql_query("
INSERT INTO story_info (title, sum, story, user, cat, rating)
VALUES('$title_form','$summ_form', '$story_form,', '$username', '$cat_form','$rating_form')
");
if(isset($_POST['notes']))
{
$notes_form = mysql_real_escape_string($_POST['notes']);
$notes_final = bb($notes_form);
mysql_query("
UPDATE story_info
SET notes = '$notes_final'
WHERE story = '$story_form' AND user = '$username' AND sum = '$summ_form' AND title = '$title_form'
");
}
//put other in array. Use while loop to put link code. Then but it back into one non array variable
if(isset($_POST['u_id']))
{
$uid = mysql_real_escape_string($_POST['u_id']);
$uid_db = str_replace(' ','_', $uid);
$blerg = "
UPDATE story_info
    SET series_id = '$uid_db'
WHERE story = '$story_form' AND user = '$username' AND sum = '$summ_form' AND title = '$title_form'
";
mysql_query($blerg);

}
echo "<h1> Your Story Has Been Posted! Thanks for posting $username .   </h1>";
echo "Please review the post below <br />";
echo "<h2> $title_form </h2>";
echo "<strong> <h2> Summary: </h2> </strong> $summ_form";
echo "<h4> Story: </h4>";
echo "$story_form";
if(isset($notes))
{
echo "<h4> Author's Notes: </h4> ";
echo "$notes_final";


}
if (isset($uid_db))
{
echo '<h3> Unique Series ID </h3>';
echo '<p> Make sure to write down this! <br />' .$uid_db .'</p> ';
}

$db = mysql_query("
SELECT story_id
FROM story_info
WHERE story='$story_form' AND user='$username'
")or die(mysql_error());
$rows = mysql_fetch_assoc($db);

$id = $rows['story_id'];





echo "Catagory: $cat_form <br />
Rating: $rating_form  <br />
";
echo "<a href='?p=page&id=$id'> Click here to view your story! </a>'";
?>


Please help!

quick question  i have located in my code

$r = mysql_query("insert into points values ('${v[0]}', ${v[1]}, '$At') on duplicate key update Points = Points + ${v[1]}");

-it is updateing the player points but i need it to do a new entry  how would i go about that i tried just taking out the on duplicate and it gives me a tstring insert error on next line. if i leave it in i get no error

I need to insert data into a table if it doesn't exist or update if it does.

Can someone give me an example of this code.

im using mySQL 5.1.42
im using phpMyAdmin 3.1.o

how can i make this code to insert a single, multiple and update rows in the database. The code only insert new rows in the database.

Code: [Select]

if (($handle = fopen('inventorylist.csv', "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) {
        $num = count($data);
     

                  $sql="INSERT into inventory(itemNumber,itemDesc,quantityHand,category,Whse) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";

                    mysql_query($sql) or die(mysql_error());

       
    }
    fclose($handle);

        }

I can't find the error, someone help me please.                   $conn = mysql_connect("localhost","root","Pass");
                $err_db = mysql_select_db('bd_amics');
                $sql = ("INSERT INTO `'".$_SESSION["use"][14]."'` (ID,Amic,PubID) VALUES ('".$_SESSION["person"][14]."', "1", '".$_SESSION["person"][15]."')");
                mysql_query("SET NAMES utf8");
                mysql_query($sql, $conn);
                mysql_close();                   $conn3 = mysql_connect("localhost","root","Pass");
                $err_db3 = mysql_select_db('bd_amics');
                $sql3 = ("UPDATE `'".$_SESSION["person"][14]."'` SET Amic="2" WHERE ID='".$_SESSION["use"][14]);
                mysql_query("SET NAMES utf8");
                mysql_query($sql3, $conn3);
                mysql_close();

Is there any way to tell my php ajax file to run the update query if the data already exist and if not, then create the row in the database?  I have both the update and the insert functions created, but was just wondering if I could tell php which one to use without passing through a parameter.

Hi I'm currently experiencing a problem with my query. I've used the `INSERT IGNORE` option in my query and it works pretty well not to add duplicates, but the problem is that if my unique field match it doesn't update any other info in the row, is there maybe another option to update a row if an existing field exists but insert new row if it doesn't exists?   my current query is as follow  

$query = mysqli_query($con,"INSERT IGNORE INTO mxit (ip,time,user_agent,contact,userid,id,login,nick,location,profile) 
VALUES ('$ip','$post_time','$mxitua','$mxitcont','$mxituid','$mxitid','$mxitlogin','$mxitnick','$mxitloc','$mxitprof')") or die(mysqli_error($con));