php callback url & storing info in mysql

[ezkim0x]

I'm not PHP savvy at all.. I'm able to edit code around.. but that's about it.

I have a program that loads a callback url when it's finished to store it's completed data (I think it's called callback url?)

ie

site.com/stats.php?&data1=value1&data2=value2

right now I'm using a php script that I edited that will just mail me the 2 data values.. but I'd much prefer to make it store this info in a mysql database.. and even display it on stats.php or another page when i visit it.

here's what i'd like to see on the page when I load it.. basically everything would be stored under value1.. and then it would also save value2 and the date so I can call that back on the stats page.


something like that.. however like I said I'm not very php savvy.

If anyone could help me out with this it would be appreciated.. if this is too much to ask for maybe just some links to relevant info that may help me accomplish this would be appreciated.. I've tried searching on google but not having much luck as i'm not 100% what to search for.

 

[Ken g6]

I think it's called a "web service", but maybe I'm not understanding you.

Are you stuck with PHP? Somehow I get the feeling you'd be happier with Ruby on Rails.

 

[ezkim0x]

I think it's called a "web service", but maybe I'm not understanding you.

Are you stuck with PHP? Somehow I get the feeling you'd be happier with Ruby on Rails.

basically what's happening: the program is launching a url upon completion to save data in a database.

I'm not stuck to PHP it can be anything as long as it can use variables in the url I guess.

for instance #data1# and #data2# are the 2 variables that the program has.. and if using php I would do it like so:

website.com/statsorwhateverpageiwant.php?&mydata=#data1#&mydata2=#data2#

and the program replaces those 2 #data# variables with the actual data.. like say #data1# = 45 and #data2# 3

it would launch the url website.com/statsorwhateverpageiwant.php?&mydata=45&mydata2=3

so as long as I could put that into a url that RoR could identify the variables from then it would work.. but I've never messed with RoR at all.. not that my knowledge of PHP is that much superior to it or anything lol.

 

[Ken g6]

Well, what you want to do is pretty simple, so just about any web-oriented language should do it easily. Here's a page Google turned up that's not a bad guide example. Generally ignore everything after "create one master array of the records". And remember to sanitize your database inputs!

 

[CuriousMike]

Basic code to take the values and stuff them in your db.

<?php
//verify there are values passed on URL
if (isset ($_GET['data1']) && isset ($_GET['data2']) )
{
	//get those values.  you might want to validate them depending on what they are.
	$data1 = $_GET['data1'];
	$data2 = $_GET['data2'];
	
	//open db
	$dbc = mysql_connect ('localhost', 'root', '');
	$dbSelected = mysql_select_db("yourdb", $dbc);

	//insert values into db; the values are inserted, along with the time right now.
	$query = "INSERT INTO mytable (data1Field,data2Field,date)".
					"VALUES ('$data1','$data2',NOW())";
	$result = mysql_query ($query) or die ('Error inserting into db.');
	
	//we're done.  
	mysql_close ($dbc);
	
}
else
{
	die ('Missing paramaeter(s).');
}
?>

And the basic code to print out all the values... ie. www.yousite.com/displayAll.php

<?php
$query = 'SELECT * from mytable';
$result = mysql_query ($query);
if (!$result) 
{
	die('Invalid query: ' . mysql_error());
}
else
{
	while ($row = mysql_fetch_array ($result))
	{
		echo ('data1 = '. $row['data1'].' data2 = '.$row['data2'].' date = '.$row['date'].'<br>');
	}
}
?>

 

[beginner99]

Basic code to take the values and stuff them in your db.

<?php
//verify there are values passed on URL
if (isset ($_GET['data1']) && isset ($_GET['data2']) )
{
	//get those values.  you might want to validate them depending on what they are.
	$data1 = $_GET['data1'];
	$data2 = $_GET['data2'];
	
	//open db
	$dbc = mysql_connect ('localhost', 'root', '');
	$dbSelected = mysql_select_db("yourdb", $dbc);

	//insert values into db; the values are inserted, along with the time right now.
	$query = "INSERT INTO mytable (data1Field,data2Field,date)".
					"VALUES ('$data1','$data2',NOW())";
	$result = mysql_query ($query) or die ('Error inserting into db.');
	
	//we're done.  
	mysql_close ($dbc);
	
}
else
{
	die ('Missing paramaeter(s).');
}
?>

This is very bad advice. Prone to very basic sql injection. NEVER directly use User Input to create an SQL command by string concatenation.

Use mysqli and prepared statements instead!!!

Create mysqli object:

function getDatabaseConnection(){

    /*
     * Database Server Name
     * set it to localhost in case database is on same server as the php files
     */
    $dbHost="localhost";

    // Database Name
    $dbName="myDatabaseName";

    // Database User Name. DO NOT USE ROOT
    $dbUser="mySqlUserName";

    // Password for Database User
    $dbPass="******";
	
    $connection = new mysqli( $dbHost, $dbUser, $dbPass, $dbName );
    $connection->set_charset("utf8");
    return $connection;
}

The code for inserting data usign mysqli:

// see http://php.net/manual/de/book.mysqli.php
// for info on mysqli

if (isset ($_GET['data1']) && isset ($_GET['data2'])) {

	// validate values if possible
	// validation IS NOT MEANT TO PREVENT SQL INJECTION
	// it is more to check for the right data type (string, integer,date)	
	$data1 = $_GET['data1'];
	$data2 = $_GET['data2'];
	
	$con = getDatabaseConnection();
	if (mysqli_connect_errno() != 0) {	
		// do something more meaningful here
		die("Database Connection failed");
	}
	// Note: Better use database trigger to insert date
	$sql = "INSERT INTO mytable (data1Field,data2Field)".
				"VALUES (?,?)";

	$preparedStatement = $con->prepare($sql);
	
	// this assumes $data1 = integer and $data2 = String
	// hence 'is". If both are strings use 'ss' instead
	// http://php.net/manual/de/mysqli-stmt.bind-param.php
	$preparedStatement->bind_param('is', $data1, $data2);
	$preparedStatement->execute();
	
	// will return the value of the AUTO_INCREMENT column of the table
	// see http://php.net/manual/de/mysqli.insert-id.php for more info
	$databaseId = $preparedStatement->insert_id;
}

Note that above code is missing proper error handling for simplicity.

 

[ezkim0x]

thanks guys..

I'm currently busy with some other work.. but I'll be taking a look over the last few posts hopefully later tonight and get it setup.

but just wanted to say thanks again.

 

[Crusty]

Basic code to take the values and stuff them in your db.

<?php
//verify there are values passed on URL
if (isset ($_GET['data1']) && isset ($_GET['data2']) )
{
	//get those values.  you might want to validate them depending on what they are.
	$data1 = $_GET['data1'];
	$data2 = $_GET['data2'];
	
	//open db
	$dbc = mysql_connect ('localhost', 'root', '');
	$dbSelected = mysql_select_db("yourdb", $dbc);

	//insert values into db; the values are inserted, along with the time right now.
	$query = "INSERT INTO mytable (data1Field,data2Field,date)".
					"VALUES ('$data1','$data2',NOW())";
	$result = mysql_query ($query) or die ('Error inserting into db.');
	
	//we're done.  
	mysql_close ($dbc);
	
}
else
{
	die ('Missing paramaeter(s).');
}
?>

And the basic code to print out all the values... ie. www.yousite.com/displayAll.php

<?php
$query = 'SELECT * from mytable';
$result = mysql_query ($query);
if (!$result) 
{
	die('Invalid query: ' . mysql_error());
}
else
{
	while ($row = mysql_fetch_array ($result))
	{
		echo ('data1 = '. $row['data1'].' data2 = '.$row['data2'].' date = '.$row['date'].'<br>');
	}
}
?>

Please do not use this code. This is the perfect example of what not to do when writing a web script. There is NO input validation whatsoever, one query to your server and I can wipe your entire database.

 

[sourceninja]

i like pdo for my database access in php, the bindParm method allows you to validate the data is of an accepted type as well as prevents injection. (mysqli probably does that as well)

 

[Zargon]

I think it's called a "web service", but maybe I'm not understanding you.

Are you stuck with PHP? Somehow I get the feeling you'd be happier with Ruby on Rails.

PHP is generally much faster tho.

and yes please sanitize data.

 

[ezkim0x]

I finally got around to having time to work on this for a little bit.. but having some probs.

I'm using beginner99s code

Fatal error: Call to a member function bind_param() on a non-object

what I did to the file was add in my mysql database info.. create the table mytable with 2 fields for now (try to figure out date after I get this part working). data1field and data2field

then changed "VALUES (?,?) to "VALUES ($data1,$data2)

data1 will be text, and data2 will be a number (but text I guess?)

then I tried changing is to si to ss at the bind_param part but didn't help same error.

----------------------

I think the problem lies in when I added the 2 fields to my table.. not sure what i'm suppose to select for the TYPE in phpmyadmin.

here's what I have:

         Field 	Type	Collation	Attributes	Null	Default	Extra	Action
	 data1Field	text	latin1_german2_ci	 	No 	 	 	 	 				 
	 data2Field 	text	latin1_german2_ci	 	No

 

[beginner99]

then changed "VALUES (?,?) to "VALUES ($data1,$data2)

That is causing the error. The ? are correct. they stand for a parameter which you then bind to the statement with

$preparedStatement->bind_param('is', $data1, $data2);

This statement tells that the first parameter (=the first ?) is an integer (the i in the string 'is') and should have the value of $data1 and that the second parameter is a string with the value of $data2.

Also see:
What is a prepared statement?
http://en.wikipedia.org/wiki/Prepared_statement

mysqli specific instructions on how to bind parameters:
http://www.php.net/manual/en/mysqli-stmt.bind-param.php

 

[ezkim0x]

yep that fixed it.

thanks for the code with detailed comments+links