Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PHP: insert Array data into MySQL table

Posted on 2013-11-24
8
Medium Priority
?
1,147 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 44

Accepted Solution

by:
Chris Stanyon earned 2000 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 44

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 44

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 111

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 44

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

704 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