Solved

PHP: insert Array data into MySQL table

Posted on 2013-11-24
8
1,103 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 
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 110

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article discusses four methods for overlaying images in a container on a web page
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…

751 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