I'm creating a page for a class and I have lots of different while loops with lots of different INSERT statements. I didn't think there would be a problem with that until I discovered that they weren't being inputed right. They were all inputed but what happened was that they were all inputed in separate rows as opposed to being one entry across their individual columns. I need it so that all of the INSERT statements are combined into one while still checking to make sure that the full name isn't yet in the database. Please help me rewrite my code so that it can do just that.
Here is the code that needs to be rewritten so that it just has one INSERT statement:
<?php
$con = mysql_connect("localhost","a7068104_user2","wiseguy1345");
if(!$con) {
die("could not connect to localhost:" .mysql_error());
}
header("refresh:1.5; url=NamesAction.php");
mysql_select_db("a7068104_world") or die("Cannot connect to database");
$name = mysql_real_escape_string($_POST['firstname']);
$query = "SELECT * FROM names_1 WHERE firstname='$name'";
$result = mysql_query($query);
if(mysql_num_rows($result) > 0 ){
echo "Your first name is already in the database and will not be added again!";
}
else {
$query = "INSERT INTO names_1 (firstname) VALUES('$name')";
$result = mysql_query($query);
if($result) {
echo "Your first name was successfully added to the database!";
}
else{
echo "Your first name couldn't be added to the database!";
}
}
$name = mysql_real_escape_string($_POST['lastname']);
$query = "SELECT * FROM names_1 WHERE lastname='$name'";
$result = mysql_query($query);
if(mysql_num_rows($result) > 0 ){
echo "Your last name is already in the database and will not be added again!";
}
else {
$query = "INSERT INTO names_1 (lastname) VALUES('$name')";
$result = mysql_query($query);
if($result) {
echo "Your first name was successfully added to the database!";
}
else{
echo "Your first name couldn't be added to the database!";
}
}
$name = mysql_real_escape_string($_POST['firstname'] . " " . $_POST['lastname']);
$query = "SELECT * FROM names_1 WHERE fullname='$name'";
$result = mysql_query($query);
if(mysql_num_rows($result) > 0 ){
echo "Your full name is already in the database and will not be added again!";
}
else {
$query = "INSERT INTO names_1 (fullname) VALUES('$name')";
$result = mysql_query($query);
}
$age = mysql_real_escape_string($_POST['age']);
$query = "INSERT INTO names_1 (age) VALUES('$age')";
$result = mysql_query($query);
if($result) {
echo "Your name was successfully added to the database!";
}
else {
echo "Your name couldn't be added to the database!";
}
mysql_close($con);
?>
<html>
<head>
<link rel="stylesheet" href="Site.css">
<?php include("Header.php"); ?>
</div>
</head>
<body>
<div id="main">
<h1>Names</h1>
<p>You will be redirected back to the <b>Names</b> page in a moment.</p>
<?php include("Footer.php");?>
</div>
</body>
</html>
When inserting a row with multiple columns you can just combine the two groups of keys and values like so:
INSERT INTO names_1 (firstname, lastname) VALUES ('John', 'Smith')
The other alternative is in your database mark both the Firstname and Lastname as Unique columns. Then you can do the following.
INSERT INTO names_1 (firstname, lastname) VALUES ('John', 'Smith') ON DUPLICATE KEY UPDATE names_1 SET x="blah" WHERE blah
http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html
This should accomplish what you are setting out to do.
<?php
$con = mysql_connect("localhost","a7068104_user2","wiseguy1345");
if(!$con) {
die("could not connect to localhost:" .mysql_error());
}
header("refresh:1.5; url=NamesAction.php");
mysql_select_db("a7068104_world") or die("Cannot connect to database");
$fullname = mysql_real_escape_string($_POST['firstname'] . " " . $_POST['lastname']);
$query = "SELECT * FROM names_1 WHERE fullname='" . $fullname . "'";
$result = mysql_query($query);
if(mysql_num_rows($result) > 0 ){
echo "Your full name is already in the database and will not be added again!";
}
else {
$query = "INSERT INTO names_1 (firstname, lastname, fullname, age) VALUES('" . $_POST['firstname'] . "','" . $_POST['lastname'] . "','" . $fullname . "'," . $_POST['age'] . ")";
$result = mysql_query($query);
}
mysql_close($con);
?>
<html>
<head>
<link rel="stylesheet" href="Site.css"/>
<?php include("Header.php"); ?>
</head>
<body>
<div id="main">
<h1>Names</h1>
<p>You will be redirected back to the <b>Names</b> page in a moment.</p>
<?php include("Footer.php");?>
</div>
</body>
</html>