This is my first post to DaniWeb, and I've read the Member Rules, so I think this is the correct forum.

I have created a page in PHP that collects seven items of data then displays them lower on the same page. The data columns are: name, alignment, origin, archetype, primary, secondary, and notes. Name and notes are text field values, the rest are selected values from drop down menus.

For some reason, when I do an INSERT query using all seven values the query fails. However, if I remove the selected values it works. Here is the code:

<?php

if(isset($_POST['submitted'])) {
	$errors = array();
	
	$name = $_POST['name'];	
	$alignment = $_POST['alignment'];
	$origin = $_POST['origin'];
	$archetype = $_POST['archetype'];
	$primary = $_POST['primary'];
	$secondary = $_POST['secondary'];
	$notes = $_POST['notes'];
	
	if(empty($errors)) {
		
		include('includes/passwordsetc.inc'); //not the real extention
		$con = mysqli_connect(HOST, USER, PASS, DB) OR die('Could not connect to MySQL: '.mysqli_connect_error());
		
		$query = "SELECT origin FROM names WHERE name = '$name'";
		$result = mysqli_query($con, $query);
		if(mysqli_num_rows($result) == 0) {

			$query = "INSERT INTO names (name, alignment, origin, archetype, primary, secondary, notes)
				VALUES ('$name', '$alignment', '$origin', '$archetype', '$primary', '$secondary', '$notes')";
			if($result = mysqli_query($con, $query)) {
				$success = "Successful!";
			} else {
				$failure = "Unable to INSERT into DB.";
			}
		} else {
			$failure = "The name '".$name."' is already used.";
			$name = "";
			$notes = "";
		}
	} else {
		$failure = "Please fix the following errors.";
	}
}
		
$kwords = '';
$title = 'CoH/CoV Names';
$page = 'names';
include('includes/header.inc');
include('includes/dropdown.js');
?>

</head>
<?php
include('includes/body.inc');
?>
		<div id="content" class="column">
			<h2>Names</h2>
			<div class="toppara" id="cntr">
			<?php echo $failure;
			echo $success;?>
			<table>
			<tr><form name="addname" method="post" action="index.php">
			<td><input type="text" name="name" value="<?php if(isset($_POST['name'])) echo $name;?>"/></td>
			<td><select name="alignment" size="1" onchange="setAlign(document.addname.alignment.options[document.addname.alignment.selectedIndex].value);">
				<option value="select"></option>
				<option value="hero">Hero</option>
				<option value="villain">Villain</option>
			</select></td>
			<td><?php include('includes/origin.inc');?></td>
			
			<td><select name="archetype" size="1" onchange="setPrimary(document.addname.archetype.options[document.addname.archetype.selectedIndex].value);">
				<option value=""></option>
			</select></td>
			
			<td><select name="primary" size="1">
				<option value=""></option>
			</select></td>
			
			<td><select name="secondary" size="1">
				<option value=""></option>
			</select></td>
			<td><input type="text" name="notes" value="<?php if(isset($_POST['notes'])) echo $notes;?>" /></td>
			<td><input class="button" type="submit" value="Add" />
			<input type="hidden" name="submitted" value="true" /></td>
			</form></tr>
			
			<tr><td><label for="names"><span class="sort"><a href="index.php?sort=name">NAME</a></span></label></td>
			<td><span class="sort"><a href="index.php?sort=alignment">ALIGNMENT</a></span></td>
			<td><span class="sort"><a href="index.php?sort=origin">ORIGIN</a></span></td>
			<td><span class="sort"><a href="index.php?sort=archetype">ARCHETYPE</a></span></td>
			<td><span class="sort"><a href="index.php?sort=origin">PRIMARY</a></span></td>
			<td><span class="sort"><a href="index.php?sort=origin">SECONDARY</a></span></td>
			<td>NOTES</td>
			</tr>
			<?php
			include('includes/threeolives.inc');
			$con = @mysqli_connect(HOST, USER, PASS, DB) OR die('Could not connect to MySQL: '.mysqli_connect_error());
			$query = "SELECT * FROM names ORDER BY $sort";
			$result = @mysqli_query($con, $query);
			if(mysqli_num_rows($result) == 0) {
				echo "<tr><td>There are no names to view.</td></tr>";
			} else {
				while($row = mysqli_fetch_assoc($result)) {
					$name = $row['name'];
					$alignment = $row['alignment']; 
					$origin = $row['origin'];
					$archetype = $row['archetype'];
					$primary = $row['primary'];
					$secondary = $row['secondary'];
					$notes = $row['notes'];
					
					echo "<tr><td><label for='first'>$name</label></td>
					<td>$alignment</td>
					<td>$origin</td>
					<td>$archetype</td>
					<td>$primary</td>
					<td>$secondary</td>
					<td>$notes</td>
					<td></td>
					</tr>\n";
				}
			}
			?>
			</table>
			</div>
		</div>
		<div class="clr"></div>
	</div>
</div>
<?php
include('includes/footer.inc');
?>

I didn't include the included Javascript code or PHP code - it's not relevant to the problem (at least, I don't think so). Thanks for any help on this.

Try the following, it will give you the error message, so you can see what is wrong. Perhaps one of your fields contains invalid data.

$query = "INSERT INTO names (name, alignment, origin, archetype, primary, secondary, notes)
    VALUES ('$name', '$alignment', '$origin', '$archetype', '$primary', '$secondary', '$notes')";
  if($result = mysqli_query($con, $query)) {
    $success = "Successful!";
  } 
  else {
    $failure = "Unable to INSERT into DB: " . mysqli_error($con);
  }

I belive that the single quotes around the variables prevents PHP from parsing them as variables. This would insert literal value '$name' into the database IF they were all text. However, MySql will error out due to type misimatch for the non-text types.

I belive that the single quotes around the variables prevents PHP from parsing them as variables. This would insert literal value '$name' into the database IF they were all text. However, MySql will error out due to type misimatch for the non-text types.

The query itself is in double quotes, so the variables will be parsed. Only if the outer quotes are single, the string is parsed as literal. The single quotes around the variables is SQL per definition.

The query itself is in double quotes, so the variables will be parsed. Only if the outer quotes are single, the string is parsed as literal. The single quotes around the variables is SQL per definition.

You're right, pritaeas. The quotes aren't the problem. And thanks for the tip about .mysqli_erro($con). It gave me the following error message:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'primary, secondary, notes) VALUES ('test name', 'hero', 'science', 'Blaster'' at line 1

So, I'm researching this. It's odd because I'm using the same code elsewhere.

After some good old fashioned trial-and-error, I discovered that only the primary column/value causes the error. With that removed, all other six values are entered. That column, along with the alignment and secondary columns are all js generated. Here's the code:

<script type="text/javascript">
function setAlign(chosen) {
	var archbox = document.addname.archetype;
	var primebox = document.addname.primary;
	var secondbox = document.addname.secondary;
	
	archbox.options.length = 0;
	primebox.options.length = 0;
	secondbox.options.length = 0;
	if(chosen == "") {
		archbox.options[archbox.options.length] = new Option('SELECT','');
	}
	if(chosen == "hero") {
		<?php include('includes/arch-hero.inc');?>
	}
	if(chosen == "villain") {
		<?php include('includes/arch-villain.inc');?>
	}
}
function setPrimary(chosen) {
	var primebox = document.addname.primary;
	var secondbox = document.addname.secondary;
	
	primebox.options.length = 0;
	secondbox.options.length = 0;
	if(chosen == "") {
		primebox.options[primebox.options.length] = new Option('','');
		secondbox.options[secondbox.options.length] = new Option('','');
	}
	if(chosen == "Blaster") {
		<?php include('includes/blaster-prime.inc');?>
		<?php include('includes/blaster-second.inc');?>
	}
	if(chosen == "Controller") {
		<?php include('includes/controller-prime.inc');?>
		<?php include('includes/controller-second.inc');?>
	}
	if(chosen == "Defender") {
		<?php include('includes/defender-prime.inc');?>
		<?php include('includes/defender-second.inc');?>
	}
	if(chosen == "Scrapper") {
		<?php include('includes/scrapper-prime.inc');?>
		<?php include('includes/scrapper-second.inc');?>
	}
	if(chosen == "Tanker") {
		<?php include('includes/tanker-prime.inc');?>
		<?php include('includes/tanker-second.inc');?>
	}
	if(chosen == "Peacebringer") {
		<?php include('includes/pb-prime.inc');?>
		<?php include('includes/pb-second.inc');?>
	}
	if(chosen == "Warshade") {
		<?php include('includes/ws-prime.inc');?>
		<?php include('includes/ws-second.inc');?>
	}
	if(chosen == "Brute") {
		<?php include('includes/brute-prime.inc');?>
		<?php include('includes/brute-second.inc');?>
	}
	if(chosen == "Corruptor") {
		<?php include('includes/corruptor-prime.inc');?>
		<?php include('includes/corruptor-second.inc');?>
	}
	if(chosen == "Dominator") {
		<?php include('includes/dominator-prime.inc');?>
		<?php include('includes/dominator-second.inc');?>
	}
	if(chosen == "Mastermind") {
		<?php include('includes/mastermind-prime.inc');?>
		<?php include('includes/mastermind-second.inc');?>
	}
	if(chosen == "Stalker") {
		<?php include('includes/stalker-prime.inc');?>
		<?php include('includes/stalker-second.inc');?>
	}
	if(chosen == "Arachnos Soldier") {
		<?php include('includes/as-prime.inc');?>
		<?php include('includes/as-second.inc');?>
	}
	if(chosen == "Arachnos Widow") {
		<?php include('includes/aw-prime.inc');?>
		<?php include('includes/aw-second.inc');?>
	}
}	
</script>

Here's a sample of the php included prime.inc code:

primebox.options[primebox.options.length] = new Option('','');
primebox.options[primebox.options.length] = new Option('Archery','Archery');
primebox.options[primebox.options.length] = new Option('Assault Rifle','Assault Rifle');
primebox.options[primebox.options.length] = new Option('Electrical Blast','Electrical Blast');
primebox.options[primebox.options.length] = new Option('Energy Blast','Energy Blast');
primebox.options[primebox.options.length] = new Option('Fire Blast','Fire Blast');
primebox.options[primebox.options.length] = new Option('Psychic Blast','Psychic Blast');
primebox.options[primebox.options.length] = new Option('Sonic Attack','Sonic Attack');

But I'm still clueless as to why this column/value pair won't validate.

Have you tried to echo the $primary variable to see if it contains a value?

Have you tried to echo the $primary variable to see if it contains a value?

Yes, I have and it worked exactly as I suspected. In fact, that was the last thing I checked before posting here. I'm pretty spent by this mystery - now the dang JavaScript isn't even working. :) This page was just supposed to be a stupid place to keep track of CoX names before I resume my subscription. Sad.

Oh my god, I solved it! It turns out that 'primary' is not allowed as a column name. I changed it to 'prime' and all is good. I was fumbling around at MySql.com reading about Indexing columns when I saw something labeling a column as Unique or Primary. Well, there you go!

Thanks for the help above, gang.

Ah nice job, that makes sense. I ran into a similar problem when trying to use "desc" as a column name. MySQL interprets it as "descending".

Oh my god, I solved it! It turns out that 'primary' is not allowed as a column name. I changed it to 'prime' and all is good. I was fumbling around at MySql.com reading about Indexing columns when I saw something labeling a column as Unique or Primary. Well, there you go!

Thanks for the help above, gang.

Didn't notice this, but it is still possible to use primary as a column name. To do this put backticks around it when using it in a query, like this:

SELECT `primary` FROM your_table
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.