Solved

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

Posted on 2015-01-28
6
300 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 108

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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 108

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
TCPDF HTML Table of Contents how can I create leading dots 7 35
join tables 4 50
php call to a non-object 3 31
Help Extract Specific in SQL 8 15
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
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.

930 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

10 Experts available now in Live!

Get 1:1 Help Now