Solved

PHP: insert Array data into MySQL table

Posted on 2013-11-24
8
1,070 Views
Last Modified: 2013-11-24
Hi All,

I have the following PHP array and I want to write the "Asks" and "Bids" into a MySQL table.

<?php
array(2) {
  ["result"]=>
  string(7) "success"
  ["data"]=>
  array(3) {
    ["now"]=>
    string(16) "1385294257814224"
    ["asks"]=>
    array(5) {
      [0]=>
      array(5) {
        ["price"]=>
        int(790)
        ["amount"]=>
        float(2.82171788)
        ["price_int"]=>
        string(8) "79000000"
        ["amount_int"]=>
        string(9) "282171788"
        ["stamp"]=>
        string(16) "1385294249352611"
      }
      [1]=>
      array(5) {
        ["price"]=>
        float(798.995)
        ["amount"]=>
        float(0.0522271)
        ["price_int"]=>
        string(8) "79899500"
        ["amount_int"]=>
        string(7) "5222710"
        ["stamp"]=>
        string(16) "1385294236948061"
      }
      [2]=>
      array(5) {
        ["price"]=>
        int(799)
        ["amount"]=>
        float(4.31550163)
        ["price_int"]=>
        string(8) "79900000"
        ["amount_int"]=>
        string(9) "431550163"
        ["stamp"]=>
        string(16) "1385294232274450"
      }
      [3]=>
      array(5) {
        ["price"]=>
        float(799.09271)
        ["amount"]=>
        float(0.124425)
        ["price_int"]=>
        string(8) "79909271"
        ["amount_int"]=>
        string(8) "12442500"
        ["stamp"]=>
        string(16) "1385294160378923"
      }
      [4]=>
      array(5) {
        ["price"]=>
        float(799.10271)
        ["amount"]=>
        float(0.23844422)
        ["price_int"]=>
        string(8) "79910271"
        ["amount_int"]=>
        string(8) "23844422"
        ["stamp"]=>
        string(16) "1385294113088922"
      }
    }
    ["bids"]=>
    array(9688) {
      [0]=>
      array(5) {
        ["price"]=>
        float(1.0E-5)
        ["amount"]=>
        float(12331162.01)
        ["price_int"]=>
        string(1) "1"
        ["amount_int"]=>
        string(16) "1233116200999996"
        ["stamp"]=>
        string(16) "1384815678725592"
      }
      [1]=>
      array(5) {
        ["price"]=>
        float(2.0E-5)
        ["amount"]=>
        float(0.02)
        ["price_int"]=>
        string(1) "2"
        ["amount_int"]=>
        string(7) "2000000"
        ["stamp"]=>
        string(16) "1323840868378228"
      }
      [2]=>
      array(5) {
        ["price"]=>
        float(3.0E-5)
        ["amount"]=>
        float(475.64999999)
        ["price_int"]=>
        string(1) "3"
        ["amount_int"]=>
        string(11) "47564999999"
        ["stamp"]=>
        string(16) "1373190354971103"
      }
      [3]=>
      array(5) {
        ["price"]=>
        float(4.0E-5)
        ["amount"]=>
        float(0.02)
        ["price_int"]=>
        string(1) "4"
        ["amount_int"]=>
        string(7) "2000000"
        ["stamp"]=>
        string(16) "1323843650506685"
      }
      [4]=>
      array(5) {
        ["price"]=>
        float(5.0E-5)
        ["amount"]=>
        float(0.02)
        ["price_int"]=>
        string(1) "5"
        ["amount_int"]=>
        string(7) "2000000"
        ["stamp"]=>
        string(16) "1373800332769955"
      }
    }
  }
}
?>

Open in new window


Can anyone help me with the code?


Here's my table

INSERT INTO `bitcoin`.`market_depth`
            (`id`,
             `type`,
             `price`,
             `amount`,
             `price_int`,
             `amount_int`,
             `stamp`)
VALUES ('id',
        'type',
        'price',
        'amount',
        'price_int',
        'amount_int',
        'stamp');

Open in new window


Type is a 0 for an Ask and a 1 for a bid
0
Comment
Question by:detox1978
  • 4
  • 3
8 Comments
 
LVL 43

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 39672821
Sure. To insert the data 'as is' is very straight forward using PDO. You have an ID field in your table, but can't see from your code where this value comes from - I'm guessing it's an auto ID so doesn't need to be part of the INSERT statement.

Here's a quick and dirty example - it's up to you if you need to sanitize or validate any of the data before INSERTing into your DB. Substitute your own username and password. It assumes your array is called $data

<?php
try {
	//connect to your database
	$dbh = new PDO('mysql:host=localhost;dbname=bitcoin', 'username', 'password');
	$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

	//prepare the INSERT statement with named parameters
	$addRecord = $dbh->prepare("INSERT INTO market_depth (type, price, amount, price_int, amount_int, stamp) VALUES (:type, :price, :amount, :price_int, :amount_int, :stamp)");

	//loop through the 'asks'
	foreach ($data['data']['asks'] as $record):
		//set the 'type'
		$record['type'] = 0;
		//insert the record
		$addRecord->execute($record);
	endforeach;
	
	//loop through the 'bids'
	foreach ($data['data']['bids'] as $record):
		//set the 'type'
		$record['type'] = 1;
		//insert the record
		$addRecord->execute($record);
	endforeach;

} catch(PDOException $e) {
	echo $e->getMessage();
}?>

Open in new window

0
 
LVL 2

Author Comment

by:detox1978
ID: 39673041
I dont have PDO setup and have struggled converting it.

Any chance you could convert it?
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39673054
Convert it to what??

PDO is a standard way of interacting with mySQL databases in PHP, and should be enabled by default - if it's not, then speak to your hosting provider.

The other alterntive is to use the mysqli library. The code is very simliar to what I've posted
0
 
LVL 2

Author Comment

by:detox1978
ID: 39673086
I've got it installed on the windows pc at home.

Normally I just use mysql_connect()
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39673101
the mysql library is deprecated and is due to be dropped from PHP altogether, so you will have to use either PDO or mySQLi whether you want to or not. Using mysql_connect() is no longer an option!

If you try and run the code I've posted, does it work or do you get errors?

Add this to the top of your PHP script:

error_reporting(E_ALL);
ini_set('display_errors', 1);

Open in new window

You might also want to run phpinfo() and check that PDO is enabled (if you're using a recent version of PHP then it should be). If it's not enabled. then edit your php.ini file and look for the following line:

extension=php_pdo_mysql.dll

Make sure it's not commented (i.e. it doesn't start with a semi-colon). If you uncomment it, save the file and restart Apache. If you're running this on a Windows PC, then using something like WampServer makes it a lot easier - you simply select PDO from a list and it's done..

If you would rather do this using mySQLi instead of PDO then let me know. It's not as slick because mySQLi doesn't support named parameters, but it's still doable
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39673115
This article will help you make a logical mapping of the obsolete MySQL functions to the MySQLi and the PDO equivalents.
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

You may need to install the MySQL drivers for PDO.  Only SQLite come standard.
http://php.net/manual/en/pdo.installation.php
http://php.net/manual/en/ref.pdo-mysql.php
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39673136
Here's the mySQLi version if you need it:

//connect to the database
$dbh = new mysqli("localhost", "username", "password", "bitcoin");

//prepare the INSERT query - mySQLi doesn't do Named Parameters :(
$addRecord = $dbh->prepare("INSERT INTO market_depth (type, price, amount, price_int, amount_int, stamp) VALUES (?, ?, ?, ?, ?, ?)");

//bind the parameter to soem variables
$addRecord->bind_param("ssssss", $type, $price, $amount, $price_int, $amount_int, $stamp);

//loop through the 'asks'
foreach ($data['data']['asks'] as $record):
	//prep the data
	$type = 0;
	$price = $record['price'];
	$amount = $record['amount'];
	$price_int = $record['price_int'];
	$amount_int = $record['amount_int'];
	$stamp = $record['stamp'];

	//insert the record
	$addRecord->execute();
endforeach;

//loop through the 'bids'
foreach ($data['data']['bids'] as $record):
	//prep the data
	$type = 1;
	$price = $record['price'];
	$amount = $record['amount'];
	$price_int = $record['price_int'];
	$amount_int = $record['amount_int'];
	$stamp = $record['stamp'];

	//insert the record
	$addRecord->execute();
endforeach;

Open in new window

0
 
LVL 2

Author Closing Comment

by:detox1978
ID: 39673214
Oddly it worked second time.  I must have made a type
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

895 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

13 Experts available now in Live!

Get 1:1 Help Now