Inserting row in Mysql table : extra row and wrong character encoding

Hi all,

When I try to insert a row in the table TEACHINGS, I have 2 errors so far:
1. Each time I insert a row, there is another row inserted at the same time. This is not the case when I catch the SQL query and insert it directly using phpmyadmin
2. strings like "journée" are stored with strange characters e.g. : "journée"

I am sure it is one more time newbie issues.

You have after the SQL script for the table and the PHP script I use.

Best regards,
Richard

I created the following table :
CREATE TABLE IF NOT EXISTS `TEACHINGS` (
  `teaching_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(60) NOT NULL,
  `location` varchar(50) NOT NULL,
  `teaching_date` date NOT NULL,
  `description` text NOT NULL,
  `opening_hours` varchar(10) NOT NULL,
  UNIQUE KEY `TEACHING_ID` (`teaching_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=24 ;

Open in new window


I use the following code :
<!DOCTYPE html>
<html lang="fr">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=620">
<title>Teaching lesson creation</title>
<link rel="stylesheet" href="">
<script src=""></script>
</head>
<body>
<?php
if (($_GET["action"] === "") or ($_GET["action"] === null)) {
	$action = '';
}
else {
	$action = $_GET["action"];
}

if ($action == "g") { // We get data to create a teaching lesson
?>
<h1>Création d'un cours</h1>
<div id="">
<form action="teaching-create-ee.php?action=s" method="post" class="">
<div>
		<label for="teaching-title" class="">Nom de la formation <span>(obligatoire)</span></label>
		<input type="text" name="teaching-title" id="teaching-title" value="Veuillez saisir un titre pour cette formation" class=""  required aria-required="true"/>
	</div>

<div>
		<label for='teaching-location' class=''>Lieu</label>
		<input type='text' name='teaching-location' id='teaching-location' value='Studio A Lyne' class=""  />
	</div>

<div>
		<label for='teaching-when' class="">Date de la formation</label>
		<input type='date' name='teaching-when' id='teaching-when' value='' class=""  />
	</div>
<div>
		<label for='teaching-opening-hours' class="">Durée/heures de la formation</label>
		<input type='text' name='teaching-opening-hours' id='teaching-opening-hours' value='' class=""  />
	</div>

<div>
		<label for='teaching-description' class='grunion-field-label textarea'>Description de la formation <span>(obligatoire)</span></label>
		<textarea name='teaching-description' id='teaching-description' rows='20'  required aria-required='true'></textarea>
	</div>
	<p class='contact-submit'>
		<input type='submit' value='Créer &#187;' class=''/>
	</p>
</form>
</div>
<?php
} else {
?>
<h1>Création de la formation <?php echo $_POST['teaching-title']; ?></h1>
<?php	
try
{
    $dbh = new PDO("mysql:host=XXXX;dbname=XXXX", "XXXX", "XXXX");
}
catch (Exception $e)
{
    die('Erreur : ' . $e->getMessage());
}

$sql  = "INSERT INTO `pouxdagoalyne`.`TEACHINGS` (`teaching_id`, `title`, `location`, `teaching_date`, `description`, `opening_hours`) VALUES ";
$sql .= "(0, ";
$sql .= "'" . $_POST['teaching-title'] . "', ";
$sql .= "'" . $_POST['teaching-location'] . "', ";
$sql .= "'" . $_POST['teaching-when'] . "', ";
$sql .= "'" . $_POST['teaching-description'] . "', ";
$sql .= "'" . $_POST['teaching-opening-hours'] . "'";
$sql .= ")";

print_r($sql);

/* Delete all rows from the FRUIT table */
$count = $dbh->exec($sql);

/* Return number of inserted rows */
print("$count inserted row.\n");

}
?>
<br>
<p><?php echo $_POST["teaching-location"] ?></p>
</body>
</html>

Open in new window

LVL 1
Richard Coffree-commerce Product ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris StanyonWebDevCommented:
The only thing I can see in your query that raises an alarm is that you're trying to insert the contstant value of 0 into the teaching-id auto-increment column. For an auto-increment column, you don't insert a value, so drop that (and the column name from your query).

You should also really be using a prepared query and binding the values to that - makes for safer queries.
0
Ray PaseurCommented:
The character encoding issue is a collision.  This article shows what's going on and how to fix it.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11880-Unicode-PHP-and-Character-Collisions.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
Maybe you would want to try wrapping the INSERT query call into a try{} catch{} block.  You could emit some kind of message as you run the query (perhaps print the query string) to see where the logic is making the second INSERT.

Just guessing, but it looks like this may be part of the issue:

if ($action == "g") {

Open in new window


If $action is NOT "g" it looks like the script will try to run the INSERT query - no matter what is present in the POST array.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Richard Coffree-commerce Product ManagerAuthor Commented:
That's very strange.
When I use a print_r to display the SQL query and also show the number of rows impacted I got:

INSERT INTO `pouxdagoalyne`.`TEACHINGS` (`title`, `location`, `teaching_date`, `description`, `opening_hours`) VALUES ('Test 22:50', 'Studio A Lyne', '2015-07-03', 'dfgdfgf gdg fd gdgd gdg g d ds', 'am')1 inserted row. 

Open in new window


There is only one row.
0
Richard Coffree-commerce Product ManagerAuthor Commented:
Hi guys,

I splitted my script in 2 parts and I used the prepare() function as follows and I solved the duplication insert issue.

I will focus on the collision tomorrow, it is a little late in France.

<!DOCTYPE html>
<html lang="fr">
<head>
<meta charset=utf-8>
<meta name="viewport" content="width=620">
<title>Teaching lesson creation</title>
<link rel="stylesheet" href="">
<script src=""></script>
</head>
<body>
<h1>Création de la formation <?php echo $_POST['teaching-title']; ?></h1>
<?php	

include("data-connection.inc.php");

$sql  = "INSERT INTO `pouxdagoalyne`.`TEACHINGS` (`title`, `location`, `teaching_date`, `description`, `opening_hours`) VALUES (:title, :location, :teaching_date, :description, :opening_hours)";
try
{
	$sth = $dbh->prepare($sql);
}
catch(PDOException $e)
{
    var_dump($e);
}

try
{
	$sth->execute(array(':title' => $_POST['teaching-title'], ':location' => $_POST['teaching-location'], ':teaching_date' => $_POST['teaching-when'], ':description' => $_POST['teaching-description'], ':opening_hours' => $_POST['teaching-opening-hours']));   
}
catch(PDOException $e)
{
    var_dump($e);
    die();
}

?>
</body>
</html>

Open in new window

0
Chris StanyonWebDevCommented:
Better, but you should just wrap the prepare and execute in the same try block:

$sql  = "INSERT INTO `pouxdagoalyne`.`TEACHINGS` (`title`, `location`, `teaching_date`, `description`, `opening_hours`) VALUES (:title, :location, :teaching_date, :description, :opening_hours)";
try
{
	$sth = $dbh->prepare($sql);
	$sth->execute(array(':title' => $_POST['teaching-title'], ':location' => $_POST['teaching-location'], ':teaching_date' => $_POST['teaching-when'], ':description' => $_POST['teaching-description'], ':opening_hours' => $_POST['teaching-opening-hours']));
}
catch(PDOException $e)
{
	die(e->Message);	
}

Open in new window

0
Richard Coffree-commerce Product ManagerAuthor Commented:
The 2 solutions selected are useful but I can select only one, what a shame.
Chris and Ray gave me solutions for 2 distinct issues, and Ray gave me full insight about the issue.

Best regards,
guys
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.