asked on

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

SOLUTION

Chris Stanyon

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Ray Paseur

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.

Richard Coffre

ASKER

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.

Richard Coffre

ASKER

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

Chris Stanyon

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

Richard Coffre

ASKER

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