Solved

PHP MySQL Insert help

Posted on 2013-01-05
18
254 Views
Last Modified: 2013-01-09
I have a program that students will complete homework assignments online.

I have three different tables, one that holds main information on the homework, one that holds the questions, and one that holds the input possibilities and values.

What is happening is the values for the assignment are not being passed to the submithomework.php page, but everything else is.

Can somebody point me in the right direction?

Thanks!

<?php
$today = date("Y-m-d");
$homeworkid = $_GET['homeworkid'];

$sql4 = "SELECT assignmentdate, directions, video, videolink, numquestions from homework where homeworkid = '".$homeworkid."'";
$res4 = mysql_query($sql4) or die(mysql_error());

$date = date("Y-m-d");
	
echo "<form action = 'submithomework.php' method = 'post'>";
echo "<input type='hidden' name='uname' value='".$_SESSION['username'] . "'>";

$row4 = mysql_fetch_row($res4);
$assignmentdate = $row4[0];
$directions = $row4[1];
$video = $row4[2];
$videolink = $row4[3];
$numquestions = $row4[4];

	echo "<input type='hidden' name='date' value='".$date."'>";
		
	echo "<span class='categorytitle'><strong>Directions:</strong> " . nl2br(stripslashes($directions)) . '</span><br /><br />';
	echo "<input type='hidden' name='homeworkid' value='".$homeworkid."'>";
	echo "<input type='hidden' name='numquestions' value= '".$numquestions."'>";
	
	if ($video == '1')
		{
		echo '<div align="center"><iframe width="560" height="315" src="'.$videolink.'" frameborder="0" allowfullscreen></iframe></div><br /><br />';
		}
		
	echo "<table width='900px' cellspacing='10' cellpadding='10' border='0'>";

	
$sql5 = "SELECT questionid, homeworkid, qid, questionname, numinputs
		 FROM homeworkquestions
		 WHERE homeworkid = '".$homeworkid."'";
		 	 
$res5 = mysql_query($sql5) or die(mysql_error());
$num5 = mysql_num_rows($res5);

for($e = 0; $e < $num5; ++$e)
	{
	$fullqid = mysql_result($res5, $e, 0);
	$questnum = mysql_result($res5, $e, 2);
	$question = mysql_result($res5, $e, 3);
	$numinputs = mysql_result($res5, $e, 4);
	
	echo '<input type="hidden" name="numinputs[]" value = "'.$numinputs.'">';
	echo '<input type="hidden" name="questionid[]" value = "'.$fullqid.'">';
	
	$sql6 = "SELECT inputid, inputtype, questionid, inputval FROM homeworkinputs WHERE questionid = '".$fullqid."'";
	$res6 = mysql_query($sql6) or die(mysql_error());
	$num6 = mysql_num_rows($res6);
	
	echo '<tr><td width="100px" align="right">' . $questnum . '.</td><td colspan="2"> ' . $question . '</tr>';
	
	 for($f = 0; $f <= $num6; ++$f)
		 {
		 $inputid = mysql_result($res6, $f, 0);
		 $inputtype = mysql_result($res6, $f, 1);
		 $questionid = mysql_result($res6, $f, 2);
		 $inputval = mysql_result($res6, $f, 3);

		 $sql7 = "SELECT name, type FROM inputtypes WHERE id = '".$inputtype."'";
		 $res7 = mysql_query($sql7) or die(mysql_error());
		 $row7 = mysql_fetch_row($res7);
		 
		 $inputtype = $row7[1];
		 
		 if($inputtype == 'radio')
			{
			$inputtype = '<input type="radio" name="'.$inputid.'[]" value = "'.$inputval.'">';
			}
			
		if($inputtype == 'text')
			{
			$inputtype = '<input type="text" name="'.$inputid.'[]" class="tbpurple" value="'.$inputval.'">';
			}		 
			
		 if($inputtype == 'checkbox')
			{
			$inputtype = '<input type="checkbox" name="'.$inputid.'[]" value="'.$inputval.'">';
			}
		 
		echo '
		   <tr>
			 <td>&nbsp; </td>
			 <td width="150px" align="right">' . $inputval . '</td><td>' . $inputtype . '</td>
		   </tr>';

		   
		}
	
	}

Open in new window


<?php

session_start();

$_SESSION["username"];

ERROR_REPORTING(0);

include("includes/sqlconnect.php");

$today = date("Y-m-d");

$homeworkid = $_POST['homeworkid'];
$numquestions = $_POST['numquestions'];
$inputid = $_POST['inputid'];
$numinputs = $_POST['numinputs'];
$questionid = $_POST['questionid'];

echo var_dump($_POST) . '<br /><br />';

echo 'Homeworkid: ' . $homeworkid . ' - Number of questions: ' . $numquestions . '<br /><br />';

for($a = 0; $a < $numquestions; ++$a)
	{
	echo 'Question Id: ' . htmlentities($_POST["questionid"][$a]) . ' - Num Inputs: ' . htmlentities($_POST["numinputs"][$a]) . '<br />';

	for($b = 0; $b < htmlentities($_POST["numinputs"][$a]); ++$b)
		{
		echo 'Input Value: ' . htmlentities($_POST["inputid"][$b]) . '<br /><br />';

		}
	}

Open in new window

0
Comment
Question by:t3chguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 2
18 Comments
 
LVL 1

Author Comment

by:t3chguy
ID: 38748085
What the homework page looks like.
0
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 38748207
Perhaps I0m missing something, but in your form I don't see the input submit. In addition, can you say which lines hold the values that are not passed to submithomework.php?
Thanks
0
 
LVL 1

Author Comment

by:t3chguy
ID: 38748209
I forgot to add the submit button in the code I posted.

echo '<input type="submit" name="submit" value="Submit Homework" class="button" />';

Open in new window


			 if($inputtype == 'radio')
			{
			$inputtype = '<input type="radio" name="'.$inputid.'[]" value = "'.$inputval.'">';
			}
			
		if($inputtype == 'text')
			{
			$inputtype = '<input type="text" name="'.$inputid.'[]" class="tbpurple" value="'.$inputval.'">';
			}		 
			
		 if($inputtype == 'checkbox')
			{
			$inputtype = '<input type="checkbox" name="'.$inputid.'[]" value="'.$inputval.'">';
			}

Open in new window


The code above is what is holding the data that is not being passed.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 1

Author Comment

by:t3chguy
ID: 38748220
var_dump produces the following:

array(13) { ["uname"]
 string(5) "danny" ["date"]
 string(10) "2013-01-06" ["homeworkid"]
 string(1) "2" ["numquestions"]
 string(1) "4" ["numinputs"]
 array(4) { [0]
 string(1) "5" [1]
 string(1) "1" [2]
 string(1) "2" [3]
 string(1) "2" } ["questionid"]
 array(4) { [0]
 string(1) "4" [1]
 string(1) "5" [2]
 string(1) "6" [3]
 string(1) "7" } ["inputid"]
 string(0) "" ["input5"]
 string(3) "top" ["input8"]
 string(3) "ten" ["input9"]
 string(3) "tan" ["input11"]
 string(3) "Yes" ["input14"]
 string(2) "No" ["submit"]
 string(15) "Submit Homework" } 

Open in new window


The above looks great, but essentially what I need to do is figure out how to structure my query to take the following values out of the array.

 string(3) "top" ["input8"]
 string(3) "ten" ["input9"]
 string(3) "tan" ["input11"]
 string(3) "Yes" ["input14"]
 string(2) "No" ["submit"]

Open in new window

0
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 38748277
I still don't understand, but in another way, now: are you saying you want access directly tha last five values? Or you want to delete them from the $_POST array?
And another question: I believed your posted code produced the form you show us in your secon post, but there is a textarea which is missing in the code where you only have text input. This way I can't really understand what should be the expected $_POST array and what is the difference with the actual one. But mayb I need some more coffee :-)
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 38748348
This book: http://www.sitepoint.com/books/phpmysql5/ is very readable, has great examples and its own downloadable code library that you can copy and modify for your own purposes.  Now in its fifth printing, it's been a permanent part of my professional library since Edition One.  It will not make you a pro, but it will help you get some kind of foundation in how PHP and SQL work together.

There is so much wrong with these scripts I almost do not know where to start.  You might benefit from the PHP Introductory Tutorial on Dealing with Forms.

Let's look at this segment of code from the second code block in the initial question.  I have added comments.
<?php
// THIS STARTS THE SESSION, BUT THAT IS NOT NECESSARY 
// BECAUSE THE $_SESSION VARIABLE IS NOT USE BY EITHER SCRIPT
session_start();

// THIS STATEMENT DOES NOTHING AT ALL
$_SESSION["username"];

// THIS STATEMENT MAKES IT IMPOSSIBLE TO SEE ANY OF THE
// PHP ERRORS, WARNINGS, OR NOTICES, MAKING DEBUGGING
// INTO A TOTAL GUESSING GAME
ERROR_REPORTING(0);

// THIS STATEMENT MISUSES VAR_DUMP().  THE CORRECT WAY
// IS TO OMIT THE "ECHO"
echo var_dump($_POST) . '<br /><br />';

Open in new window

When you begin working with HTML forms in PHP, the first thing you would do (assuming a POST-method request) is write an "action" script that says this:
<?php
error_reporting(E_ALL);
echo '<pre>';
var_dump($_POST);

Open in new window

Then using that action script you would try several different submit actions, to see what comes through in the post-method request.  What you will find is that inputs of type = text or textarea are always defined, however they may be empty.  Inputs without any name= attribute are not defined.  And inputs of type = checkbox or radio will be present only if they have been selected by the client.  Otherwise they are undefined.

HTH, and I really hope you will take some time to get some structured learning about HTML and PHP.  It will make your work more efficient and effective if you understand every line of code, and you can only get that understanding through formal study.  The SitePoint book will be a good study guide, I promise!

Best of luck with your project, ~Ray
0
 
LVL 1

Author Comment

by:t3chguy
ID: 38748524
marqus,

That is exactly what I'm trying to do. Those last five variables, I need to somehow be able to insert those into the database table, called homework answers, where each one of those five will be stored in a new row.  Also, you are correct on the text area field, I never really made an 'if statement' for if the teacher selects a text area, but I figured it'd be easy enough once I figured out how to code the insert query.

Ray,

As always, your wisdom is greatly appreciated.  I'm the programmer for an international company, but was completely self-taught on programming and only been doing this for a few short years now and never really taught myself a lot of the basics that you have pointed out.  While we have very functional and working programs at my company, I'm positive that the code I've written is very faulty and can be improved upon.  I'm also really bad with arrays and foreach loops.  I'll certainly give that book a read.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 38748548
You'll love the SitePoint book; it will make your code better almost instantly.  Another good one (more of a cookbook style) is by Eli White.
http://www.amazon.com/dp/0672328887

If you can find a PHP user group near you, you might want to join and participate.  Here in Washington, DC we meet twice monthly in the evenings.  One meeting is with an agenda and a speaker, the other is just to drink beer.  Both give us great opportunities to share ideas, network, and keep in touch with like-minded developers.
0
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 38748588
Well, get the last five elemnts is quite simple using array_slice function:

$lastFive = array_slice($_POST, count($_POST) - 5, 5);

Now the array $lastFive holds:

0 => "top"
1 => "ten"
2 => "tan"
3 => "Yes"
5 => "No"

Now we go to insert them in the database:

foreach ($lastFive as $item) {
  $sql = "INSERT INTO homework_answers VALUES ($item)";
  mysql_query($sql) or die(mysql_error());
}

Open in new window


Let me say that this code is really gross. In addition I don't know how many columns has your table and how is structured so you can't use this code copying and pasting it because the query must be more precise. In addition keep in mind PHP manual encourages to use mysqli or PDO: the old mysql functions are now deprecated as of php 5.5.0 and it will be removed.
A last word to confirm the Ray suggestion: I didn't read White's book but Sitepoint one is very good and it brought me in the php world quickly and easily.
0
 
LVL 1

Author Comment

by:t3chguy
ID: 38748639
Very close to what I need.  

  ["questionid"]=>
  array(4) {
    [0]=>
    string(1) "4"
    [1]=>
    string(1) "5"
    [2]=>
    string(1) "6"
    [3]=>
    string(1) "7"
  }
  ["inputid"]=>
  string(0) ""
  ["input5"]=>
  string(3) "top"
  ["input8"]=>
  string(3) "ten"
  ["input9"]=>
  string(3) "tan"
  ["input11"]=>
  string(3) "Yes"
  ["input14"]=>
  string(2) "No"
  ["submit"]=>
  string(15) "Submit Homework"

I also need to get the question id associated with each input as well as the inputid.

The question id for top, ten tan is #4 and the associated inputs are 5, 8, 9.

The question id for the 'Yes' is 6 and the input id is 11.

The question id for the 'No' is 7 and the input id is 14.

I do not need the submit button to be included in the query.

My query currently looks like this:


INSERT INTO homework_answers(`homeworkid`, `questionid`, `inputid`, `value`) VALUES (2, Array, , ten)

INSERT INTO homework_answers(`homeworkid`, `questionid`, `inputid`, `value`) VALUES (2, Array, , tan)

INSERT INTO homework_answers(`homeworkid`, `questionid`, `inputid`, `value`) VALUES (2, Array, , Yes)

INSERT INTO homework_answers(`homeworkid`, `questionid`, `inputid`, `value`) VALUES (2, Array, , No)

INSERT INTO homework_answers(`homeworkid`, `questionid`, `inputid`, `value`) VALUES (2, Array, , Submit Homework)
0
 
LVL 31

Accepted Solution

by:
Marco Gasi earned 500 total points
ID: 38748719
First I recommend you to replace numbers with significant names: or you have an extraordinary brain or you pass some time to look at some table to understand what each number means.
That said, let me ask: do you need to put input 'names' in the array: if you used $questionid directly as input names you would get an associative array where to each question id corresponds an answer.
But if you really need input id, you can add it to the value:

			 if($inputtype == 'radio')
			{
                            $inputtype = '<input type="radio" name="'.$inputid.'[]" value="' . $questionid . ',' . $inputval . '">';
			}
			
		if($inputtype == 'text')
			{
			$inputtype = '<input type="text" name="'.$inputid.'[]" class="tbpurple" value="'. $questionid . ',' . $inputval.'">';
			}		 
			
		 if($inputtype == 'checkbox')
			{
			$inputtype = '<input type="checkbox" name="'.$inputid.'[]" value="'. $questionid . ',' . $inputval.'">';
			}
                                            

Open in new window


Then we get lastFive values without submit element

$lastFive = array_slice($_POST, count($_POST) - 5, 4);

This way we have last four useful values in this form:

[inputid]=>questionid,value = [5] => 8, No (I use random values)

Finally we insert in the database

foreach ($lastFive as $k=>$v) {
  $inputid = $k;
  // WE GET THE VALUE AND WE SEPARATE IT IN TWO ELEMENTS, INPUTID AND VALUE
  $qa = explode(',' $v); 
  $questionid = $qa[0];
  $value = $qa[1];
  $sql = "INSERT INTO homework_answers(`homeworkid`, `questionid`, `inputid`, `value`) VALUES (2, $questionid, $inputid, $value) ";
  mysql_query($sql) or die(mysql_error());
}

Open in new window


Hope this is what you're looking for.

Cheers
0
 
LVL 1

Author Comment

by:t3chguy
ID: 38748761
The page does not load, I can't seem to find the error with it?

<?php

error_reporting(E_ALL);

echo '<pre>';

var_dump($_POST);

echo '</pre>';

include("includes/sqlconnect.php");

$today = date("Y-m-d");

$homeworkid = $_POST['homeworkid'];
$numquestions = $_POST['numquestions'];
$inputid = $_POST['inputid'];
$numinputs = $_POST['numinputs'];
$questionid = $_POST['questionid'];

$lastFive = array_slice($_POST, count($_POST) - 5, 5);

foreach($lastFive as $k=>$v) 
	{
	$inputid = $k;
	$qa = explode(',' $v); 
  	$questionid = $qa[0];
  	$value = $qa[1];
	$sql = "INSERT INTO homework_answers(`homeworkid`, `questionid`, `inputid`, `value`) VALUES ('2', '".$questionid."', '".$inputid."', '".$value."')";

	echo $sql . '<br /><br />';
	}

Open in new window

0
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 38748796
I don't see evident errors, but you have to replace the last 5 number in array_slice with a 4

$lastFive = array_slice($_POST, count($_POST) - 5, 4);

In these cases I try to comment a block of code: if the page load, I try to reduce teh commented block by 1 or 2 lines and retry until I don't finde the statement which causes the problem. If the page doesn't load, I uncomment the commented block and I comment another one. If I suspect the problem could be in the calling script, I comment all code in the called script and I live only something echo "hello"; And so on...
0
 
LVL 1

Author Comment

by:t3chguy
ID: 38748811
Ah, figured it out...there wasn't a comma in the explode area, but the query still isn't quite tweaked right.

INSERT INTO homework_answers(`homeworkid`, `questionid`, `inputid`, `value`) VALUES ('2', 'Array', '0', '')

INSERT INTO homework_answers(`homeworkid`, `questionid`, `inputid`, `value`) VALUES ('2', 'Array', '1', '')

INSERT INTO homework_answers(`homeworkid`, `questionid`, `inputid`, `value`) VALUES ('2', 'Array', '2', '')

INSERT INTO homework_answers(`homeworkid`, `questionid`, `inputid`, `value`) VALUES ('2', 'Array', '3', '')

$homeworkid = $_POST['homeworkid'];
$numquestions = $_POST['numquestions'];
$inputid = $_POST['inputid'];
$numinputs = $_POST['numinputs'];
$questionid = $_POST['questionid'];

$lastFive = array_slice($_POST, count($_POST) - 5, 4);

foreach($lastFive as $k=>$v)
	{
	$inputid = $k;
	$qa = explode(',', $v); 
  	$questionid = $qa[0];
  	$value = $qa[1];
	$sql = "INSERT INTO homework_answers(`homeworkid`, `questionid`, `inputid`, `value`) VALUES ('2', '".$questionid."', '".$inputid."', '".$value."')";

	echo $sql . '<br /><br />';
	}

Open in new window

0
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 38748832
That means your $questionid is an array. Since in your code $questionid is defined as

mysql_result($res6, $f, 2);

which is a custom function, evidently this function return an array instead an integer value as I thought. Am I correct? and if so, how can you change this?
0
 
LVL 1

Author Comment

by:t3chguy
ID: 38748838
The questionid actually appears in var_dump "4,top" "4,ten", "4,tan" "6,yes" "7,no"

  ["inputid"]=>
  string(0) ""
  [5]=>
  array(1) {
    [0]=>
    string(5) "4,top"
  }
  [8]=>
  array(1) {
    [0]=>
    string(5) "4,ten"
  }
  [9]=>
  array(1) {
    [0]=>
    string(5) "4,tan"
  }
  [11]=>
  array(1) {
    [0]=>
    string(5) "6,Yes"
  }
  [14]=>
  array(1) {
    [0]=>
    string(4) "7,No"

I'll try changing the array on the form.
0
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 38758928
Then it's all right? Thanks for points and best luck with your project.
0
 
LVL 1

Author Comment

by:t3chguy
ID: 38758945
I actually had to change it up quite a bit, but your help and commentary definitely helped point me in the right direction to solve it!

Thanks again!
0

Featured Post

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question