PHP - Insert Into Existing Record

I have 2 tables, access_level and pages. In pages table i will have all the details of page like page_id, code, herf, pagename.. and i will select few from this table and store it in access_level table depending on the department and position. 

Now while editing, i will display all the pages which is stored in access_level pages with a particular page code along with those pages from pages table. if the page_id exists in access_level table, its has to get updated, if its not present then it should get inserted into access_level table. 

These things am doing with checkbox and <li>. 

my access_level table    access_level.JPG   18.19KB   0 downloads   and my pages table    pages.JPG   37.86KB   0 downloads   here is my code

$s1 = mysql_query("SELECT pages.page_id as pid, pages.code, pages.page, 
            pages.href, access_level.aid, access_level.page_id as pgid, 
            access_level.department, access_level.position, 
            access_level.active 
        FROM pages
        LEFT JOIN access_level ON (pages.page_id=access_level.page_id 
            AND access_level.department=".$department." 
            AND access_level.position=".$position.") WHERE pages.code='snor die(mysql_error());
				while($s2 = mysql_fetch_array($s1)) {  ?>
				<tr><td><li><?php echo $s2['page']; ?>  </td><td><input type="checkbox" name="sn[]" value="<?php echo $s2['pid']; ?>" <?php if($s2['pgid'] === $s2['pid']) echo 'checked="checked"';?> />
                <input type="hidden" value="<?php echo $s2['pid']; ?>" name="page_id[<?php echo $s2['pgid']; ?>]">

while submittings i am not getting the logic, how can be done. Please somebody suggest me

Not sure if this is right.  I can't get it to insert my record.  Can someone please tell me if I'm doing it right?   cus_functions.php

function dbRowInsert($table_name, $form_data)
{
global $conn;
   
    $fields = array_keys($form_data);

    $sql = "INSERT INTO ".$table_name."
    (`".implode('`,`', $fields)."`)
    VALUES('".implode("','", $form_data)."')";

       return mysqli_query($sql);
}

process.php

 include("new_db.php");
 include("cus_functions.php");
 
 $do=$_GET['do'];
 
 if($do=='addpro'){
 
if(isset($_POST['submit'])){

$input = $_POST['title'];
$comp = '0';

$form_data = array('title' => $input, 'completed' => $comp);
 dbRowInsert('projects', $form_data);

}}

Greetings mates,

I have not been here for years. I have been thrust into an app written by someone else.

The requirement is to amend, not edit, an existing record. In other words, rather than edit an existing record, management would like an empty new text box to enter amended data and save to another table so that when queried, would display existing record and the newly amended record.

Below is an ID of an existing record:

	$sql = "SELECT * FROM DBO.existingTable where year_created = 2019 Order by Id";
    echo '<table id="results-table">
	<thead>
		<tr>
			<th>ID</th>
			<th>Edit</th>
			<th>View PDF</th>
			<th>Task Lead</th>
			<th>Task Title</th>
			<th>Division</th>
          		</tr>
	</thead>';

	while ( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_BOTH  ) ) {
		$div = ($row['division'] == "1" ? "Divname" : ($row['division'] == "2" ? "Highway" : "NULL"));
		$taskType = ($row['task_type'] == 0 ? "Annual" : ($row['task_type'] == 1 ? "Carryover" : ($row['task_type'] == 2 ? "New" : "NULL")));
		if($row['year_created'] == 2019){
			echo '<tr id="taskId">
			<td>'.$row['Id'].'</td>
			<td><a href="project_edit.html?id='.$row['Id'].'" target="_blank">Edit</a></td>
			<td><input type="button" onclick="getReport('.$row['Id'].')" value="View PDF"></td>
			<td>'.$row['task_lead'].'</td>
			<td>'.$row['task_title'].'</td>
			<td>'.$div.'</td>
    		</tr>
		}

	}
	echo '</table>'

The above code isa snippet of code in edit.php.

What I would like to do is grab the ID which is '.$row['Id'].' from this edit page and use it when inserting the amended code which is in insert,php file:

//The ID from edit.php code should go here
$task_lead = (empty($_POST['task_lead'])) ? "''" : "'" . str_ireplace( "'", "", $_POST[ 'task_lead' ] ) . "'";
$task_title = (empty($_POST['task_title'])) ? "''" : "'" . str_ireplace( "'", "''", $_POST[ 'task_title' ] ). "'";
$division = (empty($_POST['division'])) ? "''" : "'" . str_ireplace( "'", "", $_POST[ 'division' ] ) . "'";

//Then my insert statement should be something like this:
	$sql = "INSERT INTO DBO.Amended_Records ({id from table with existing record}, amended_taskActivities, amended_taskProducts,amendscope_dev_fhwa, amendconsultant_procurement, amendcontract_negotiations, amendconsultant_notice, amendtotal_duration, year_created)
	VALUES ({$rowId - that I need to get from edit.php}, $amendproposed_activities, $amendanticipated_products,$amendscope_dev_fhwa,$amendconsultant_procurement,$amendcontract_negotiations,$amendconsultant_notice,$amendtotal_duration, 2021)";

When I insert this record into the DB, the id from edit.php is getting inserted as null. 

Can you please advise how I can resolve this?

I have a form that submits a record and saves it to the database, I've got that working already, I'm trying to figure out if there is a cleaner way to delete a record with a button that gets a value from the unique key, in this case 'id'
here is how the delete button looks like:

   // get info from table
$query  = "SELECT id, Title, Message FROM table_name";
$result = mysql_query($query);

//displaying all data
while($row = mysql_fetch_assoc($result))
{
echo"
<div class='status'><h3>{$row['Title']} <br></h3>" . "
         <h5>{$row['Message']} <br><br></h5>"; 
 $id = $row['id']; //trying to get unique key from database
//delete button
 echo "</div>
 <form action='delete.php' method='post' />
 <input type='hidden' name='delete' value='yes' /> 
 <input type='hidden' name='id' value='$id' />
 <input type='submit' value='remove' /></form>";
}

As you can see, i'm trying to give $id a unique value but for some reason i'm not getting it.
The delete.php code looks like this:

if (isset($_POST['delete']))// check if delete was clicked
        {
         $query = "DELETE FROM table_name WHERE id='$id'";
echo "$id Deleted successfully";
}
elseif(!mysql_query($query, $db_server))
{
echo "DELETE failed: $query<br />" .
myql_error() . "<br /><br />";
         }
mysql_close();

I'm new to php and still learning so, if you think there are other ways to do this, please let me know, the button won't do anything to the data.

Thanks in advance

I am trying to get this query correct.  I want to insert a record into the database upon form submission but only if the record does not already exist.  If the record exists, then I want it to be updated in the database.    What is happening:  Upon form submit, a new record is entered into the database every time.  Note:  The contact_id column is both primary key and unique in my database.  Here is my code:

if($_POST['submit']){
  $con=mysqli_connect("localhost","username","password","database_name");
  // Check connection
  if (mysqli_connect_errno())
    {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
	
	$org = mysql_real_escape_string($_POST['organization']);
  	$namefirst = mysql_real_escape_string($_POST['firstName']);
        $namelast = mysql_real_escape_string($_POST['lastName']);
	$emailaddy = mysql_real_escape_string($_POST['email']);
	$phonenum = mysql_real_escape_string($_POST['phone']);
	$appquestion = mysql_real_escape_string($_POST['appquestion']);
	$banner = mysql_real_escape_string($_POST['banner']);
	$bulletin = mysql_real_escape_string($_POST['bulletin']);
	$giveaway = mysql_real_escape_string($_POST['giveaway']);
	$app = mysql_real_escape_string($_POST['app']);
	$tshirt = mysql_real_escape_string($_POST['tshirt']);
	$tshirtp = mysql_real_escape_string($_POST['tshirtp']);
	$print = mysql_real_escape_string($_POST['print']);
	$party = mysql_real_escape_string($_POST['party']);
	$orgnotes = mysql_real_escape_string($_POST['notes']);
	
 
$sql="INSERT INTO database_name (contact_id, first_name, last_name, email_address, phone_number, org, appquestion, banner, bulletin, giveaway, app, tshirt, promised_tee, print, party, org_notes)
      VALUES
      ('','$namefirst','$namelast','$emailaddy','$phonenum','$churchorg','$appquestion','$banner','$bulletin','$giveaway','$app','$tshirt','$tshirtp','$print','$party','$orgnotes')	
	    
	  ON DUPLICATE KEY UPDATE first_name = '$namefirst', last_name = '$namelast', email_address = '$emailaddy', phone_number = '$phonenum', org = '$org', appquestion = '$appquestion', banner = '$banner', bulletin = '$bulletin', giveaway = '$giveaway', app = '$app', tshirt = '$tshirt', promised_tee = '$tshirtp', print = '$print', party = '$party', org_notes = '$orgnotes'" ;

						

    if (!mysqli_query($con,$sql))
    {
      die('Error: ' . mysqli_error($con));
    }
      echo "1 record added";



  mysqli_close($con);
}

From everything I have read, I need to use ON DUPLICATE KEY UPDATE to replace the old information with new information in the database upon form submission.  While the insert part of my code is working, the portion with ON DUPLICATE KEY UPDATE is not working.   Why might this portion of the code not be working?  Is there a better way to insert else update the information?   Thank you for any help or guidance you can give me!  I've been working on this concept for three days and have read a ton of information about it, but am still not able to get it to work.

I'm missing something here. I have a form, and when the submit is pressed, the relevant post data inserts into table one, then I want the last insert id to insert along with other form data into a second table. The first table's still inserting fine, but I can't get that second one to do anything. It leapfrogs over the query and doesn't give an error. EDIT: I forgot to add an error: I get: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'usage, why VALUES ('14', '', '123', '','1234', '', '')' at line 1

query:INSERT INTO tbl_donar (donar_fname, donar_name, donar_address, donar_address2, donar_city, donar_state, donar_zip, donar_email, donar_phone, donar_fax, donar_company) VALUES ('test 14', 'asdfa', 'asdf', 'adf','asdf', '', '', '', '123', '', '')


Code: [Select]
if (empty($errors)) {

require_once ('dbconnectionfile.php');

$query = "INSERT INTO tbl_donar (donar_fname, donar_name, donar_address, donar_address2, donar_city, donar_state, donar_zip, donar_email, donar_phone, donar_fax, donar_company)
 VALUES ('$description12', '$sn', '$description4', '$cne','$description5', '$description6', '$description7', '$description8',
'$description9', '$description10', '$description11')";

$result = @mysql_query ($query);


if ($result) {

$who_donated=mysql_insert_id();

$query2 = "INSERT INTO tbl_donation (donor_id, donor_expyear, donor_cvv, donor_cardtype, donor_authorization, amount, usage, why)
 VALUES ('$who_donated', '$donate2', '$donate3', '$donate4','$donate5', '$donate6', '$donate7')";
$result2 = @mysql_query ($query2);

if ($result2) {echo "Info was added to both tables! yay!";}

echo "table one filled. Table two was not.";
echo $who_donated;

//header ("Location: http://www.twigzy.com/add_plant.php?var1=$plant_id");

exit();
} else {
echo 'system error. No donation added';

Can anyone tell me why this is not INSERTing? My array data is coming out just fine.. I've tried everything I can think of and cannot get anything to insert.. Ahhhh!

<?php
  $query = "SELECT RegionID, City FROM geo_cities WHERE RegionID='135'";
  $results = mysqli_query($cxn, $query);
  $row_cnt = mysqli_num_rows($results);
  echo $row_cnt . " Total Records in Query.<br /><br />";
  if (mysqli_num_rows($results)) {
      while ($row = mysqli_fetch_array($results)) {
          $insert_city_query = "INSERT INTO all_illinois SET state_id=$row[RegionID], city_name=$row[City] WHERE id = null" or mysqli_error();
          $insert = mysqli_query($cxn, $insert_city_query);
          if (!$insert) {
              echo "INSERT is NOT working!";
              exit();
          }
          
          echo $row['City'] . "<br />";
          
          echo "<pre>";
          echo print_r($row);
          echo "</pre>";
      }
      //while ($rows = mysqli_fetch_array($results))
      
      } //if (mysqli_num_rows($results))
      
      else {
          echo "No results to get!";
      }
?>

Here is my all_illinois INSERT table structu

CREATE TABLE IF NOT EXISTS `all_illinois` (
  `state_id` varchar(255) NOT NULL,
  `city_name` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Here is my source table geo_cities structu

CREATE TABLE IF NOT EXISTS `1` (
  `CityId` varchar(255) NOT NULL,
  `CountryID` varchar(255) NOT NULL,
  `RegionID` varchar(255) NOT NULL,
  `City` varchar(255) NOT NULL,
  `Latitude` varchar(255) NOT NULL,
  `Longitude` varchar(255) NOT NULL,
  `TimeZone` varchar(255) NOT NULL,
  `DmaId` varchar(255) NOT NULL,
  `Code` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Can anyone help me please, I am trying to place a PHP calendar into an existing DIV tag that used to hold a flash slideshow application, but we want to replace that with a calendar.

Here is my code:

Code: [Select]
echo '<div class="grid_9">
<div class="box">
<h2>'.$titleone.'</h2>
<div class="block">';
if (!defined('BLOCK_FILE'))
{
  Header('Location: ../index.php');
    die();
}

$modName = 'GCalendar';             

$blockConfig =
   array('maxTitle'     => 21,               
         'eventPrefix'  => '&bull;&nbsp;',   
         'maxEvents'    => 10,               
         'lookahead'    => 2,               
         'twoColumn'    => false,           
                                           
         'excludeCats'  => array(),
         'force_center' => false,           
        );


require_once 'modules/' . $modName . '/language.php';
gcalGetLang($modName);
require_once 'modules/' . $modName . '/gcal.inc.php';
require_once 'modules/' . $modName . '/common.inc.php';
require_once 'modules/' . $modName . '/getMonthlyEvents.php';
require_once 'modules/' . $modName . '/gcalBlock.php';

$config = getConfig();

list($year, $month, $today) = explode(',', date('Y,n,j'));
$block = new GCalBlock($year, $month, $today, $config, $blockConfig);

$content = $block->calendar();
$content .= '<br />';
$content .= $block->upcomingEvents();

echo '</div>
</div>
</div>';

When I try this above, I just get an 'Too many page loads' error, so I'm not sure what I've done wrong.

Any help appreciated.

Regards,
Tree

Is there a way to instantiate a PDO object using an already existing database connection? Or does PDO detect an already opened connection and use it? The existing connection is a persistant connection.

Hi

I spent some time "Googling" today but all I found was how to create PDFs on the fly.
What I wonder is if it is possible to print text on an existing PDF. And if so, could I get some pointer where to obtain this knowledge?

Simply put. I have a PDF form that is not yet filled out. I would like to be able to look up an address and other information in a database, and use that information to print on the existing PDF file.

Any help is much appreciated.

I would like to support UTF-8 on my website but am unsure - and quite fearful - whether it will break my existing code or not?!

I looked at http://us3.php.net/mbstring(), but worry that I'm going to miss something.

Here is some sample code where I think things could easily break...

// Trim all Form data.
$trimmed = array_map('trim', $_POST);


// ************************
// Validate Form Data. *
// ************************

// Validate First Name.
if (empty($trimmed['firstName'])){
// No First Name.
$errors['firstName'] = 'Enter your First Name.';
}else{
// First Name Exists.
if (preg_match('#^[A-Z \'.-]{2,30}$#i', $trimmed['firstName'])){
// Valid First Name.
$firstName = $trimmed['firstName'];
}else{
// Invalid First Name.
$errors['firstName'] = 'First Name must be 2-30 characters (A-Z \' . -)';
}
}//End of VALIDATE FIRST NAME


// Validate Username.
if (empty($trimmed['username'])){
// No Username.
$errors['username'] = 'Enter your Username.';
}else{
// Username Exists.
if (preg_match('~(?x) # Comments Mode
^ # Beginning of String Anchor
(?=.{8,30}$) # Ensure Length is 8-30 Characters
.* # Match Anything
$ # End of String Anchor
~i', $trimmed['username'])){
// Valid Username.


// ******************************
// Check Username Availability. *
// ******************************

// Build query.
$q1 = 'SELECT id
FROM member
WHERE username=?';

// Prepare statement.
$stmt1 = mysqli_prepare($dbc, $q1);

// Bind variable to query.
mysqli_stmt_bind_param($stmt1, 's', $trimmed['username']);

// Execute query.
mysqli_stmt_execute($stmt1);

// Store results.
mysqli_stmt_store_result($stmt1);

// Check # of Records Returned.
if (mysqli_stmt_num_rows($stmt1)>0){
// Duplicate Username.
$errors['username'] = 'This Username is taken.  Try again.';
}else{
// Unique Username.
$username = $trimmed['username'];
}
}else{
// Invalid Username.
$errors['username'] = 'Username must be 8-30 characters.';
}
}//End of VALIDATE USERNAME



Three possible areas where I could run into trouble are with...

1.) array_map

2.) preg_match

3.) Prepared Statements


For #2, I see there is mb_ereg_match but I am not sure if I just replace my current Regex function with that one, or if there is more involved.

I'm not sure if any problems would arise with #1 or #3 or elsewhere, and would really appreciate a second set of eyes on this code!!

requinix said switching is a good idea, but I'm pretty freaked out that I'm going to break things and create a big security hole?!   

Any help would be appreciated!!

Thanks,


Debbie

Hi guys,

using the code below within an admin panel to create a drop down allowing the user to select the profiles they wish to assign to the record they're creating, problem we have is that once a record is created, if they need to edit it for what ever reason the selected profile option isn't sticking. I've played around with lots of variants of if existing_record to try and get it add selected="selected" into the code but failed at every attempt, any advice gratefully received.

Code: [Select]
<?php

// List only breeder profiles in the database

  echo '<select name="profile" class="textinput noborder">';
  echo '<option value="any">Any</option>';

        $qryGetDistinctProfile = "SELECT * FROM profiles ORDER BY title ASC";
        $resGetDistinctProfile = mysql_query($qryGetDistinctProfile,$connection) or die(mysql_error());

        if(mysql_num_rows($resGetDistinctProfile) > 0){

$id       =  mysql_result($resProfile, 0, "id");
          
         while ($row = mysql_fetch_assoc($resGetDistinctProfile)){
           
          echo  '<option value="'.$row['id'].'" >'.$row['title'].'</option>';

         }

        }

  echo '</select>';
?>

Okay so I have a time in the format yyyy-mm-dd that is pulled from a MySQL array row and I need to add one month to it then echo that out. I assume it has something to do with the strtotime() and date() functions but I have tried every combination and cannot figure it out. Can I get some assistance please and thank you? Very very very new to php.

Here is what I have lol;
Code: [Select]
$row=mysql_fetch_array($result);
$ddaterow=$row['donation_date'];
Like I said the date will always be in the format yyyy-mm-dd

index.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" >
<head>
<meta http-equiv="Content-type" content="text/html; charset=utf-8" />
<meta http-equiv="Content-language" content="en" />
<link type="text/css" rel="stylesheet" href="css/reset.css"  />
<link type="text/css" rel="stylesheet" href="css/960.css"  />
<link type="text/css" rel="stylesheet" href="css/custom.css"  />
        <title>BAY AREA REMOTE CONTROL SOCIETY</title>
</head>
    <body>
        <div id="wrapper" class="container_12">
            <div id="header" class="grid_12">
                <div id="left-header" class="grid_5 alpha"></div>
                <div id="newsflash" class="grid_7 omega"><?php include('content/newsflash.php'); ?></div>
            </div>
            <div id="leftmenu" class="grid_3"><?php include('content/menu.php'); ?></div>
            <div id="maincontent" class="grid_9"><?php include('content/maincontent.php'); ?></div>
            <div id ="footer" class="grid_12"><?php include('content/footer.php'); ?></div>
        </div><!-- end wrapper -->
    </body>
</html>


menu.php

<?php

?>
<div class="content">
<ul id="menu">
    <li><a href="index.php?page=testpage"><span>Home</span></a></li> 
    <li><a href="index.php?page=about"><span>About us</span></a></li>
    <li><a href="index.php?page=schedule"><span>Schedule</span></a></li>
    <li><a href="index.php?page=media"><span>Media</span></a></li>
    <li><a href="index.php?page=products"><span>Products</span></a></li>
    <li><a href="index.php?page=misc"><span>Miscellaneous</span></a></li>
</ul>
</div>


When I click HOME on the Menu program it works fine but it does not clear the content of maincontent.php. How do I do that?