Solved

PHP: insert Array data into MySQL table

Posted on 2013-11-24
8
1,068 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 42

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 42

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 42

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 42

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to dynamically set the form action using jQuery.
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…

743 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

14 Experts available now in Live!

Get 1:1 Help Now