Solved

PHP, mysql query inserting triple the amount of records when using foreach loop.

Posted on 2015-01-28
6
303 Views
Last Modified: 2015-01-29
Interesting and weird problem with my code.  I have a form in which a person selects a single student or all students in one select input.  

If they select all students, the information gets passed like this: $student=5,3,7,4,1,2

The problem I am seeing is that when I try to input the students into the system it's tripling the records.  There should only be 6 records, but I'm getting 18.

Here's the code:
$userid = $_GET['userid'];
$student = $_GET['student'];
$date = $_GET['date'];
$type = $_GET['type'];
$subtype = $_GET['subtype'];
$notes = mysql_real_escape_string($_GET['notes']);
if (strpos($student,',') !== false) {
	$students = explode(",", $student);
	foreach($students as &$value) {
		mysql_query("INSERT INTO b2b_a_events (studentId, eventType, eventSubType, notes, dateOfEvent, userid) VALUES ($value, $type, $subtype, '$notes', '$date', $userid)") or die(mysql_error());
	}
} else {
	mysql_query("INSERT INTO b2b_a_events (studentId, eventType, eventSubType, notes, dateOfEvent, userid) VALUES ($student, $type, $subtype, '$notes', '$date', $userid)") or die(mysql_error());	
}

Open in new window


The same happens for the single student being selected.  Everything being entered into the system is being entered in 3 times...
0
Comment
Question by:Andrew Derse
6 Comments
 
LVL 9

Expert Comment

by:Brian Tao
ID: 40576978
Have you tried to echo $student and see what's in there?
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 40576982
Hi!

Change the line 8
$students = explode(",", $student);
to
$students = explode(',', $student);

You can use var_dump($students) in your code (after line 8 ) to see the content of the array for debug purposes.

Regards,
    Tomas Helgi
0
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 40577169
Three dangerous things and one thing that may help you debug this.  

First dangerous thing: The script appears to be updating a data base on the basis of a GET request.  GET requests must be idempotent.  You must use a POST or PUT request if you're going to update the data base.  The consequences of doing it wrong include the risk that any GET access, even a search engine spider, can damage your data model.

Second dangerous thing: PHP is doing away with MySQL support.  To understand why and to find a database extension that will be supported in the future, please see this article.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Third dangerous thing: You must always escape external data.  It is never safe to assume that external data can be added to a query string without filtering it.

Hopefully helpful thing.  Amend the code so you can get a printout of the queries before you run them.  By creating the query string in a separate variable, instead of overloading the function call, we can see the queries that are about to be run.  Here is an example.

$userid  = $_GET['userid'];
$student = $_GET['student'];
$date    = $_GET['date'];
$type    = $_GET['type'];
$subtype = $_GET['subtype'];
$notes   = mysql_real_escape_string($_GET['notes']);

// IF THERE ARE COMMAS IN THE $student VARIABLE MAKE SEVERAL QUERIES
if (strpos($student,',') !== false) {
	$students = explode(",", $student);
	foreach($students as $value) {
		$query = "INSERT INTO b2b_a_events (studentId, eventType, eventSubType, notes, dateOfEvent, userid) VALUES ($value, $type, $subtype, '$notes', '$date', $userid)";
		echo PHP_EOL . $query;
		mysql_query($query) or die(mysql_error());
	}
} 

// IF THERE ARE NO COMMAS IN THE $student VARIABLE MAKE ONLY ONE QUERY
else {
	$query = "INSERT INTO b2b_a_events (studentId, eventType, eventSubType, notes, dateOfEvent, userid) VALUES ($student, $type, $subtype, '$notes', '$date', $userid)";
	echo PHP_EOL . $query;
	mysql_query($query) or die(mysql_error());	
}

Open in new window

If you're new to PHP and want some good learning resources, this article may be able to help you find those, and more importantly, steer yourself away from the many obsolete and risky examples that litter the internet.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Andrew Derse
ID: 40578067
Excellent, thank you all for your advice.  I will check out each solution now and get back to you.
0
 

Author Closing Comment

by:Andrew Derse
ID: 40578133
Great answer!  I have not been keeping up to date with the current PHP/MySQL standards and have to admit I was rushing this project.  Thanks for helping me to slow down and do it correctly.  Your solution worked perfectly.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 40578561
Thanks for the points and best of luck with your project, ~Ray
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
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 count occurrences of each item in an array.

808 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