Link to home
Start Free TrialLog in
Avatar of Massimo Scola
Massimo ScolaFlag for Switzerland

asked on

PHP: Insert Data into MySQL

I had this question after viewing PHP: concatenate query. I have implemented this code in my project and it works beautifully. I do have one more follow up question.
The code to add the data to the database is:

//set up db connection
//http://php.net/manual/en/function.require-once.php
require_once('connection.php');
$connection = connect_db("localhost", "root", "xxxx", "tt284");


// COLUMNS IN TABLE
$elements = array
( 'RunnerID'
 , 'EventID'
 , 'Date'
 , 'FinishTime'
 , 'Position'
 , 'CategoryID'
 , 'AgeGrade'
 , 'PB'
)
;


// SANITIZE THE POST ARRAY
// my_espace_function is used to protect the site from SQL Injection 
// https://en.wikipedia.org/wiki/SQL_injection
foreach ($elements as $key)
{
    // KEEP ONLY THE ELEMENTS WE WANT
    $safe[$key] = !empty($_POST[$key]) ? mysql_real_escape_string($_POST[$key]) : "";
}

// CREATE THE COLUMN NAMES FROM THE LIST OF ELEMENTS
$cols = implode(', ', $elements);

// CREATE THE VALUES FROM THE SAFE COPY OF $_POST AND THE LIST OF ELEMENTS
$vals
= "'"
. implode("', '", $safe)
. "'"
;

// CREATE THE QUERY
$sql
= 'INSERT INTO RESULTS ('
. $cols
. ') VALUES ('
. $vals
. ')'
;


// SHOW THE REQUEST AND THE WORK PRODUCT
echo '<pre>';
print_r($_POST);
var_dump($sql);

	//mysql_query($sql, $connection, 'tt284');

		// RUN THE QUERY
		$res = mysql_query($sql, $connection);
		if (!$res) trigger_error('Query Failure:' . mysql_error(), E_USER_ERROR);

		echo "Success!";
			

	?>

Open in new window


How do I change this code to catch PHP_Posts that are empty? For example:

If the value for 'Position', 'Category ID' or 'AgeGrade' fields is empty when the form is submitted then the value should be submitted as '-1'. Similarly, if no value is given for 'Personal best' then this should be submitted as '0'.

Thanks

mscola
SOLUTION
Avatar of Dan Craciun
Dan Craciun
Flag of Romania image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Massimo Scola

ASKER

Why would you not do that in PHP?

Even if it's not recommend, how would you do it?
>>Why would you not do that in PHP?

Because I'm goal-oriented. And lazy. Which is a huge must-have in IT :)

You'll need 4 ifs in PHP to do the same, and they will run every time you insert the data. Plus you'll have more code to maintain.

Not efficient.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
you are right, the easiest way is it to set up default values in the database.
I'm using an older textbox, that's why I used the old mysql connection string.

thanks for your assistance, esepcially to Ray for the awesome code