• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1206
  • Last Modified:

PHP: insert Array data into MySQL table

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
detox1978
Asked:
detox1978
  • 4
  • 3
1 Solution
 
Chris StanyonCommented:
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
 
detox1978Author Commented:
I dont have PDO setup and have struggled converting it.

Any chance you could convert it?
0
 
Chris StanyonCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
detox1978Author Commented:
I've got it installed on the windows pc at home.

Normally I just use mysql_connect()
0
 
Chris StanyonCommented:
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
 
Ray PaseurCommented:
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
 
Chris StanyonCommented:
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
 
detox1978Author Commented:
Oddly it worked second time.  I must have made a type
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now