asked on

PHP: concatenate query

Dear Experts

My question is about concatenation. As you can see, I decided to break up a query but I can't figure out what is wrong .. I get an error that there is an unexpected "," Is the problem the double quotes?

$RunnerID = $_POST['RunnerID'];
	$EventID = $_POST['EventID'];
	$Date =  $_POST['Date'];
	$FinishTime = $_POST['FinishTime'];
	$Position = $_POST['Position'];
	$CategoryID = $_POST['CategoryID'];
	$AgeGrade = $_POST['AgeGrade'];
	$PB = $_POST['PB'];
	
$sql_start = "INSERT INTO RESULTS (RunnerID, EventID, Date, FinishTime, Position, CategoryID, AgeGrade, PB) VALUES(";
$sql_end = ");";
$sql = $sql_start .	"'$RunnerID'", "'$EventID'" , "'$Date'" , "'$FinishTime'" , "'$Position'" , "'$CategoryID'" , "'$AgeGrade'" , "'$PB'" . $sql_end;

Open in new window

SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Massimo Scola

ASKER

Ray, your code is awesome. Just one more followup question.

I have created a function for the DB connection; it is in the file connection.php.

I include this file and then execute the query. However, when I run the code, I get the following error message:

Parse error: syntax error, unexpected '$result' (T_VARIABLE) in E:\xampp\htdocs\insert_runner.php on line 83

It somehow doesn't like the $result variable.

Creating a custom function and having it saved in an external file (for later usage) is a good practise , isn't it?
Is there anything wrong with the $result variable or the way I'm adding the data?

input file:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?php // demo/temp_mscola.php
/**
 * https://www.experts-exchange.com/questions/28993826/PHP-concatenate-query.html
 */

error_reporting(E_ALL);

	

// SIMULATED STUB FOR ESCAPE/SANITIZE FUNCTION
function my_escape_function($e) { return $e; }


// COLUMNS IN TABLE
$elements = array
( 'RunnerID'
 , 'EventID'
 , 'Date'
 , 'FinishTime'
 , 'Position'
 , 'CategoryID'
 , 'AgeGrade'
 , 'PB'
)
;


// SANITIZE THE POST ARRAY
// my_espace_function is used to protect the site from SQL Injection 
// https://en.wikipedia.org/wiki/SQL_injection
foreach ($elements as $key)
{
    // KEEP ONLY THE ELEMENTS WE WANT
    $safe[$key] = !empty($_POST[$key]) ? my_escape_function($_POST[$key]) : "";
}

// CREATE THE COLUMN NAMES FROM THE LIST OF ELEMENTS
$cols = implode(', ', $elements);

// CREATE THE VALUES FROM THE SAFE COPY OF $_POST AND THE LIST OF ELEMENTS
$vals
= "'"
. implode("', '", $safe)
. "'"
;

// CREATE THE QUERY
$sql
= 'INSERT INTO RESULTS ('
. $cols
. ') VALUES ('
. $vals
. ')'
;


// SHOW THE REQUEST AND THE WORK PRODUCT
echo '<pre>';
print_r($_POST);
var_dump($sql);
//connect to database
	include("connection.php");
	$connection = connect_db("localhost", "root", "xxxxx");
	mysql_query($sql, $connection) 

		$result = mysql_query($sql, $connection);
		if($result)
		{
		echo "Success";

		}
		else
		{
		echo "Error";

		} 
	?>
</body>
</html>

Open in new window

connection.php

<?php
	
	/**
	Function: connect to a database.
	$host = hostname to connect
	$id = username
	$pwd = user password
	Returns the database connection.
	**/
	
	function connect_db($host, $username, $password)
	{
	
	$connection = @mysql_connect($host, $username, $password) 
		or die('connection problem:' .mysql_error());
	return $connection;
		
		if  (!$connection) {
		print ("internal error " . mysql_errno());
	}
		
	}
	

?>

Open in new window

Ray Paseur

a custom function and having it saved in an external file

Yes, that's a standard practice. It allows you to write the function once and use it in several places. Usually we use include() to bring in the external libraries, or use an auto-loader, or similar.

The error message cites line 83, but there is no instance of $result on line 83. I think the error may be occurring because line 72 is missing the trailing semicolon.

Looks like you could use some "getting started" guidance in PHP. This might be able to help you find good resources:
https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html

You're going to need to get off the MySQL extension. There are no current versions of PHP that have support for this extension any more. This article tells the how and why, as well as step-by-step examples showing what you must do to keep your scripts running.
https://www.experts-exchange.com/articles/11177/PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Ray Paseur

Here is how I might code the connect_db() function. Get in the habit to use trigger_error() instead of die() because some day you may want to upgrade your code with custom error handlers, and it will be hard to fix if you have die() statements all over the place.

<?php // connection.php
/**
 * Function: connect to a database.
 *    $host = hostname to connect
 *    $username
 *    $password
 *    Returns the database connection.
 */
error_reporting(E_ALL);

function connect_db($host, $username, $password)
{
    $connection = mysql_connect($host, $username, $password) 
        or trigger_error('connection problem:' . mysql_error(), E_USER_ERROR);
    return $connection;
}

Open in new window

Ray Paseur

Here is how I might code the main script. Important to note that you need to build out the function identified in the comments as a "stub." At a minimum you would use mysql_real_escape_string() in place of this stub function, but there may be more things you want to do to sanitize the information that comes from the POST request.

You also need to think about selecting the database. This is mysql_select_db() and it probably belongs somewhere in the connection script.

<!DOCTYPE html>
<html dir="ltr" lang="en-US">
<head>
<meta charset="utf-8" />
<title>Exercise from E-E Question</title>
</head>

<body>
<?php // demo/temp_mscola.php
/**
 * https://www.experts-exchange.com/questions/28993826/PHP-concatenate-query.html
 */
error_reporting(E_ALL);


// SET UP THE DB CONNECTION
require_once('connection.php');
$connection = connect_db("localhost", "root", "xxxxx");
    

// SIMULATED STUB FOR ESCAPE/SANITIZE FUNCTION <--- THIS NEEDS TO BE BUILT OUT!!
function my_escape_function($e) { return $e; }


// COLUMNS IN TABLE
$elements = array
( 'RunnerID'
 , 'EventID'
 , 'Date'
 , 'FinishTime'
 , 'Position'
 , 'CategoryID'
 , 'AgeGrade'
 , 'PB'
)
;


// SANITIZE THE POST ARRAY
// my_escape_function is used to protect the site from SQL Injection 
// https://en.wikipedia.org/wiki/SQL_injection
foreach ($elements as $key)
{
    // KEEP ONLY THE ELEMENTS WE WANT
    $safe[$key] = !empty($_POST[$key]) ? my_escape_function($_POST[$key]) : "";
}

// CREATE THE COLUMN NAMES FROM THE LIST OF ELEMENTS
$cols = implode(', ', $elements);

// CREATE THE VALUES FROM THE SAFE COPY OF $_POST AND THE LIST OF ELEMENTS
$vals
= "'"
. implode("', '", $safe)
. "'"
;

// CREATE THE QUERY
$sql
= 'INSERT INTO RESULTS ('
. $cols
. ') VALUES ('
. $vals
. ')'
;


// SHOW THE REQUEST AND THE WORK PRODUCT
echo '<pre>';
print_r($_POST);
var_dump($sql);

// RUN THE QUERY
$res = mysql_query($sql);
if (!$res) trigger_error('Query Failure:' . mysql_error(), E_USER_ERROR);

echo "Success!;

?>
</body>
</html>

Open in new window

Note: The code snippets I have posted here are probably correct in principle, but I have not subjected these to any testing at all, so caveat emptor :-)

Massimo Scola

ASKER

Now it finally works - the data is added to the table.
I do have one more issue though: it's an error with the mysql_query() and I believe it has to do with my connection code as I get the following error message:

Warning: mysql_query() expects at most 2 parameters, 3 given in E:\xampp\htdocs\insert_runner.php on line 70

Why is the function returning three values?

connection.php

error_reporting(E_ALL);

function connect_db($host, $username, $password, $db)
{
    $connection = mysql_connect($host, $username, $password) 
        or trigger_error('connection problem:' . mysql_error(), E_USER_ERROR);
    mysql_select_db($db);
	return $connection;
}
?>

Open in new window

this is the main code:

<?php // demo/temp_mscola.php
/**
 * https://www.experts-exchange.com/questions/28993826/PHP-concatenate-query.html
 */

error_reporting(E_ALL);

//set up db connection
//http://php.net/manual/en/function.require-once.php
require_once('connection.php');
$connection = connect_db("localhost", "root", "112011", "tt284");


// COLUMNS IN TABLE
$elements = array
( 'RunnerID'
 , 'EventID'
 , 'Date'
 , 'FinishTime'
 , 'Position'
 , 'CategoryID'
 , 'AgeGrade'
 , 'PB'
)
;


// SANITIZE THE POST ARRAY
// my_espace_function is used to protect the site from SQL Injection 
// https://en.wikipedia.org/wiki/SQL_injection
foreach ($elements as $key)
{
    // KEEP ONLY THE ELEMENTS WE WANT
    $safe[$key] = !empty($_POST[$key]) ? mysql_real_escape_string($_POST[$key]) : "";
}

// CREATE THE COLUMN NAMES FROM THE LIST OF ELEMENTS
$cols = implode(', ', $elements);

// CREATE THE VALUES FROM THE SAFE COPY OF $_POST AND THE LIST OF ELEMENTS
$vals
= "'"
. implode("', '", $safe)
. "'"
;

// CREATE THE QUERY
$sql
= 'INSERT INTO RESULTS ('
. $cols
. ') VALUES ('
. $vals
. ')'
;


// SHOW THE REQUEST AND THE WORK PRODUCT
echo '<pre>';
print_r($_POST);
var_dump($sql);

	mysql_query($sql, $connection, 'tt284');

		// RUN THE QUERY
		$res = mysql_query($sql, $connection);
		if (!$res) trigger_error('Query Failure:' . mysql_error(), E_USER_ERROR);

		echo "Success!";
			

	?>

Open in new window

Ray Paseur

Help us understand the issue a little more, please. What is the 'tt284' thing? Where did that come from and why is it in the arguments for the query function on line 62? Is there a particular reason your script is running the INSERT query twice?

Going forward, it would be helpful if we could see the true and correct error messages and code. In particular, the line numbers in the error messages do not match the line numbers in the code, so we know something is missing, withheld, obscured, or similar. We can offer better help when we are all looking at a true copy the same code. Just a thought, but an important one when you're trying to get help in an online forum like E-E. See also: SSCCE.

Pawan Kumar

Hi mscola,
Have you tried my suggestion?

Thank you in advance.

Massimo Scola

ASKER

Hello Ray

[quote]Help us understand the issue a little more, please. What is the 'tt284' thing? Where did that come from and why is it in the arguments for the query function on line 62? Is there a particular reason your script is running the INSERT query twice?[/quote]

tt284 is the database and yes - the issue was that the query was running twice.
I shouldn't be doing work at midnight ..

Not only does the code work, but I have also learned a number of new techniques from you.

Pawan

I have also then tested your code, and it works fine too. What I needed to change was my connection string.

Julian

Thanks for the information about the concatenation and especially about the HEREDOC style. It is definitely much easier to read the code in such a way; I will start using that with my next code.

Thank you very much for your help and support and patience.

mscola

Julian Hansen

You are welcome.

Soumen Roy

in the $sql variable, join the various strings using "." not ","..

$sql = $sql_start . "'$RunnerID'", "'$EventID'" , "'$Date'" , "'$FinishTime'" , "'$Position'" , "'$CategoryID'" , "'$AgeGrade'" , "'$PB'" . $sql_end;

To concatenate the string to form the final working query, "." operator has to be used.

Hope this helps..