Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2015-01-28
6
Medium Priority
?
330 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
[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
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 111

Accepted Solution

by:
Ray Paseur earned 2000 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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 111

Expert Comment

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

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

715 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