Another Mysql insert fiasco!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
cap2cap10
Forum Contributor
Posts: 158
Joined: Mon Apr 14, 2008 11:06 pm

Another Mysql insert fiasco!

Post by cap2cap10 »

Hello again, members of the PHP Technorati! Ok, previously, I had a problem with inserting apostrophes/single quotes into mysql. This was solved using mysql real escape string. So, I tried to implement this into another part of my code to insert form data into the DB. I am not getting an error, but nothing is being added to the DB?!!! :banghead: Here is my code:

Code: Select all

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

    $userID = $_POST['userID'];
    $object = $_POST['object'];
    $skills = $_POST['skills'];
    $boss_1 = $_POST['boss_1'];
    $job_title_1 = $_POST['job_title_1'];
    $start_mo_1 = $_POST['start_mo_1'];
    $start_yr_1 = $_POST['start_yr_1'];
    $end_mo_1 = $_POST['end_mo_1'];
    $end_yr_1 = $_POST['end_yr_1'];
    $job_duty_1 = $_POST['job_duty_1'];
    $boss_2 = $_POST['boss_2'];
    $job_title_2 = $_POST['job_title_2'];
    $start_mo_2 = $_POST['start_mo_2'];
    $start_yr_2 = $_POST['start_yr_2'];
    $end_mo_2 = $_POST['end_mo_2'];
    $end_yr_2 = $_POST['end_yr_2'];
    $job_duty_2 = $_POST['job_duty_2'];
    $boss_3 = $_POST['boss_3'];
    $job_title_3 = $_POST['job_title_3'];
    $start_mo_3 = $_POST['start_mo_3'];
    $start_yr_3 = $_POST['start_yr_3'];
    $end_mo_3 = $_POST['end_mo_3'];
    $end_yr_3 = $_POST['end_yr_3'];
    $job_duty_3 = $_POST['job_duty_3'];
    $boss_4 = $_POST['boss_4'];
    $job_title_4 = $_POST['job_title_4'];
    $start_mo_4 = $_POST['start_mo_4'];
    $start_yr_4 = $_POST['start_yr_4'];
    $end_mo_4 = $_POST['end_mo_4'];
    $end_yr_4 = $_POST['end_yr_4'];
    $job_duty_4 = $_POST['job_duty_4'];
    $boss_5 = $_POST['boss_5'];
    $job_title_5 = $_POST['job_title_5'];
    $start_mo_5 = $_POST['start_mo_5'];
    $start_yr_5 = $_POST['start_yr_5'];
    $end_mo_5 = $_POST['end_mo_5'];
    $end_yr_5 = $_POST['end_yr_5'];
    $job_duty_5 = $_POST['job_duty_5'];
	$boss_6 = $_POST['boss_6'];
    $job_title_6 = $_POST['job_title_6'];
    $start_mo_6 = $_POST['start_mo_6'];
    $start_yr_6 = $_POST['start_yr_6'];
    $end_mo_6 = $_POST['end_mo_6'];
    $end_yr_6 = $_POST['end_yr_6'];
    $job_duty_6 = $_POST['job_duty_6'];
	$boss_7 = $_POST['boss_7'];
    $job_title_7 = $_POST['job_title_7'];
    $start_mo_7 = $_POST['start_mo_7'];
    $start_yr_7 = $_POST['start_yr_7'];
    $end_mo_7 = $_POST['end_mo_7'];
    $end_yr_7 = $_POST['end_yr_7'];
    $job_duty_7 = $_POST['job_duty_7'];
	$boss_8 = $_POST['boss_8'];
    $job_title_8 = $_POST['job_title_8'];
    $start_mo_8 = $_POST['start_mo_8'];
    $start_yr_8 = $_POST['start_yr_8'];
    $end_mo_8 = $_POST['end_mo_8'];
    $end_yr_8 = $_POST['end_yr_8'];
    $job_duty_8 = $_POST['job_duty_8'];
	$boss_9 = $_POST['boss_9'];
    $job_title_9 = $_POST['job_title_9'];
    $start_mo_9 = $_POST['start_mo_9'];
    $start_yr_9 = $_POST['start_yr_9'];
    $end_mo_9 = $_POST['end_mo_9'];
    $end_yr_9 = $_POST['end_yr_9'];
    $job_duty_9 = $_POST['job_duty_9'];
	$boss_10 = $_POST['boss_10'];
    $job_title_10 = $_POST['job_title_10'];
    $start_mo_10 = $_POST['start_mo_10'];
    $start_yr_10 = $_POST['start_yr_10'];
    $end_mo_10 = $_POST['end_mo_10'];
    $end_yr_10 = $_POST['end_yr_10'];
    $job_duty_10 = $_POST['job_duty_10'];
	$school_1 = $_POST['school_1'];
    $major_1= $_POST['major_1'];
    $degree_1 = $_POST['degree_1'];
    $year_1 = $_POST['year_1'];
    $school_2 = $_POST['school_2'];
    $major_2 = $_POST['major_2'];
    $degree_2 = $_POST['degree_2'];
    $year_2 = $_POST['year_2'];
    $school_3 = $_POST['school_3'];
    $major_3 = $_POST['major_3'];
    $degree_3 = $_POST['degree_3'];
    $year_3 = $_POST['year_3'];
    $school_4 = $_POST['school_4'];
    $major_4 = $_POST['major_4'];
    $degree_4 = $_POST['degree_4'];
    $year_4 = $_POST['year_4'];
    $school_5 = $_POST['school_5'];
    $major_5 = $_POST['major_5'];
    $degree_5 = $_POST['degree_5'];
    $year_5 = $_POST['year_5'];
    $certificate = $_POST['certificate'];
    $association = $_POST['association'];
    $refer= $_POST['refer'];
}

// Connect to server and select databse.
require 'open_db.php';

	mysql_query("UPDATE `js_resume` SET object ='". mysql_real_escape_string($object) . "', skills ='". mysql_real_escape_string($skills) . "', boss_1 ='". mysql_real_escape_string($boss_1) . "', job_title_1 = '". mysql_real_escape_string($job_title_1) . "',
    start_mo_1 = '$start_mo_1', start_yr_1 = '$start_yr_1', end_mo_1 = '$end_mo_1', end_yr_1 = '$end_yr_1', job_duty_1 = '". mysql_real_escape_string($job_duty_1) . "',
    boss_2 = '". mysql_real_escape_string($boss_2) . "', job_title_2 = '". mysql_real_escape_string($job_title_2) . "', start_mo_2 = '$start_mo_2', start_yr_2 = '$start_yr_2', end_mo_2 = '$end_mo_2',
    end_yr_2 = '$end_yr_2', job_duty_2 = '". mysql_real_escape_string($job_duty_2) . "', boss_3 = '". mysql_real_escape_string($boss_3) . "', job_title_3 = '". mysql_real_escape_string($job_title_3) . "', start_mo_3 = '$start_mo_3',
    start_yr_3 = '$start_yr_3', end_mo_3 = '$end_mo_3', end_yr_3 = '$end_yr_3', job_duty_3 = '". mysql_real_escape_string($job_duty_3) . "', boss_4 = '". mysql_real_escape_string($boss_4) . "',
    job_title_4 = '". mysql_real_escape_string($job_title_4) . "', start_mo_4 = '$start_mo_4', start_yr_4 = '$start_yr_4', end_mo_4 = '$end_mo_4', end_yr_4 = '$end_yr_4',
    job_duty_4 = '". mysql_real_escape_string($job_duty_4) . "', boss_5 = '". mysql_real_escape_string($boss_5) . "', job_title_5 = '". mysql_real_escape_string($job_title_5) . "', start_mo_5 = '$start_mo_5', start_yr_5 = '$start_yr_5',
    end_mo_5 = '$end_mo_5', end_yr_5 = '$end_yr_5', job_duty_5 = '". mysql_real_escape_string($job_duty_5) . "', boss_6 = '". mysql_real_escape_string($boss_6) . "', job_title_6 = '". mysql_real_escape_string($job_title_6) . "',
	start_mo_6 = '$start_mo_6', start_yr_6 = '$start_yr_6', end_mo_6 = '$end_mo_6',
    end_yr_6 = '$end_yr_6', job_duty_6 = '". mysql_real_escape_string($job_duty_6) . "', boss_7 = '". mysql_real_escape_string($boss_7) . "', job_title_7 = '". mysql_real_escape_string($job_title_7) . "', start_mo_7 = '$start_mo_7',
	start_yr_7 = '$start_yr_7', end_mo_7 = '$end_mo_7', end_yr_7 = '$end_yr_7', job_duty_7 = '". mysql_real_escape_string($job_duty_7) . "', boss_8 = '". mysql_real_escape_string($boss_8) . "',
	job_title_8 = '". mysql_real_escape_string($job_title_8) . "', start_mo_8 = '$start_mo_8', start_yr_8 = '$start_yr_8', end_mo_8 = '$end_mo_8',
    end_yr_8 = '$end_yr_8', job_duty_8 = '". mysql_real_escape_string($job_duty_8) . "', boss_9 = '". mysql_real_escape_string($boss_9) . "', job_title_9 = '". mysql_real_escape_string($job_title_9) . "', start_mo_9 = '$start_mo_9',
	start_yr_9 = '$start_yr_9', end_mo_9 = '$end_mo_9', end_yr_9 = '$end_yr_9', job_duty_9 = '". mysql_real_escape_string($job_duty_9) . "', boss_10 = '". mysql_real_escape_string($boss_10) . "',
	job_title_10 = '". mysql_real_escape_string($job_title_10) . "', start_mo_10 = '$start_mo_10', start_yr_10 = '$start_yr_10', end_mo_10 = '$end_mo_10',
    end_yr_10 = '$end_yr_10', job_duty_10 = '". mysql_real_escape_string($job_duty_10) . "',school_1 = '$school_1', major_1 = '$major_1',
    degree_1 = '$degree_1', year_1 = '$year_1', school_2 = '$school_2', major_2 = '$major_2', degree_2 = '$degree_2',
    year_2 = '$year_2', school_3 = '$school_3', major_3 = '$major_3', degree_3 = '$degree_3', year_3 = '$year_3',
    school_4 = '$school_4', major_4 = '$major_4', degree_4 = '$degree_4', year_4 = '$year_4', school_5 = '$school_5',
    major_5 = '$major_5', degree_5 = '$degree_5', year_5 = '$year_5', certificate = '". mysql_real_escape_string($certificate) . "', association = '". mysql_real_escape_string($association) . "',
    refer = '". mysql_real_escape_string($refer) . "' WHERE userID = '$userID'") or die(mysql_error());

    mysql_query("UPDATE js_resume SET res_date = CURDATE() WHERE userID = '$userID'")or die('Query failed: ' . mysql_error());
mysql_close();
So, in the absence of the mysql real escape string this code works; without adding apostrophes. With the mysql real escape string, nothing is added to the database. Please enlighten me on the error of my ways!

Thanks in advance,

Batoe
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Another Mysql insert fiasco!

Post by mikosiko »

I don't see any INSERT in that code.... only UPDATES
User avatar
cap2cap10
Forum Contributor
Posts: 158
Joined: Mon Apr 14, 2008 11:06 pm

Re: Another Mysql insert fiasco!

Post by cap2cap10 »

Yes, It is an update script. The original fields were already created. This script allows the user to change their information.

Batoe
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Another Mysql insert fiasco!

Post by mikosiko »

so the post should be titled "Another Mysql UPDATE fiasco" :mrgreen:
"tried to implement this into another part of my code to insert form data into the DB. I am not getting an error, but nothing is being added to the DB?"
... don't follow... you want to insert or update?

anyways... just debug your code to see if the query that you are sending to the DB is what you'r expecting ... simple way:

Code: Select all

$query = "<move here your complete UPDATE sentence>"; 
// also with the proper adjustments you could move all the mysql_real_escape out of the query for a cleaner code

echo $query;   // check if your query string is what it should be.

//mysql_query($query) or die(mysql_error());  // comment this line while you test the $query
and BTW... and UPDATE syntactically correct but with the wrong WHERE conditions is going to be executed silently with no errors returned.
User avatar
cap2cap10
Forum Contributor
Posts: 158
Joined: Mon Apr 14, 2008 11:06 pm

Re: Another Mysql insert fiasco!

Post by cap2cap10 »

Thanks! I know semantics counts in coding I will try it out or reprogram using For each statement.

Batoe
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Another Mysql insert fiasco!

Post by McInfo »

Sequential field names like {boss_1, boss_2, ...} are a symptom of poor database design. Tables should expand by adding records, not fields. If you find that the table is growing wider instead of taller, consider how you can redistribute the data to a new table. In this case, the "job" and "school" entities belong in their own tables.
Post Reply