Solved

PHP MySQL Insert help

Posted on 2013-01-05
18
249 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
  • 9
  • 7
  • 2
18 Comments
 
LVL 1

Author Comment

by:t3chguy
Comment Utility
What the homework page looks like.
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:t3chguy
Comment Utility
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 30

Expert Comment

by:Marco Gasi
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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 30

Expert Comment

by:Marco Gasi
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:t3chguy
Comment Utility
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 30

Accepted Solution

by:
Marco Gasi earned 500 total points
Comment Utility
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
Comment Utility
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 30

Expert Comment

by:Marco Gasi
Comment Utility
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
Comment Utility
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 30

Expert Comment

by:Marco Gasi
Comment Utility
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
Comment Utility
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 30

Expert Comment

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

Author Comment

by:t3chguy
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now