Page 1 of 1

Another Mysql insert fiasco!

Posted: Fri May 06, 2011 12:14 am
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

Re: Another Mysql insert fiasco!

Posted: Fri May 06, 2011 6:23 am
by mikosiko
I don't see any INSERT in that code.... only UPDATES

Re: Another Mysql insert fiasco!

Posted: Fri May 06, 2011 10:25 am
by cap2cap10
Yes, It is an update script. The original fields were already created. This script allows the user to change their information.

Batoe

Re: Another Mysql insert fiasco!

Posted: Fri May 06, 2011 11:16 am
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.

Re: Another Mysql insert fiasco!

Posted: Fri May 06, 2011 1:34 pm
by cap2cap10
Thanks! I know semantics counts in coding I will try it out or reprogram using For each statement.

Batoe

Re: Another Mysql insert fiasco!

Posted: Fri May 06, 2011 1:57 pm
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.