mySQL seperate data stored in table

Question

currently I have two pieces of data being stored in a sql db and then pulled into my website. What I want though is the two pieces of data being stored to be separated instead of totaled together.

So i setup my DB like so:

DROP TABLE IF EXISTS totals;
    CREATE TABLE totals (
    id int(11) NOT NULL AUTO_INCREMENT,
    total float NOT NULL,
    PRIMARY KEY (id)
) ;

INSERT INTO totals VALUES (1, 0);

And the PHP I'm using:

$api = array();
$api[] = 'http://api.jo.je/justgiving/data/myuserpage';
$api[] = 'http://api.jo.je/justgiving/data/myuserpage2';

$total = 0;

foreach($api as $data) {

    $open = file_get_contents($data);

    $feed = json_decode($open);

    if(is_object($feed)) {

        $total = $total + $feed->donations_total;

    }

}

// database connection
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);   // new data
$id = 1;
// query
$sql = "SELECT total 
        from totals
        WHERE id=?";

$q = $conn->prepare($sql);
$q->execute(array($id));

$data = $q->fetch();


$total = $data['total'];

Being a noobie at this, I just need some help storing two seperate pieces of data instead of one.

what are the two piece of data you are talking about? are they related? example? — Steve, Nov 21 '14 at 18:55
They are values of total donations from the Just Giving API from two seperate pages, currently they are being added together — Gerico, Nov 21 '14 at 18:57
Let me update the PHP example to show the API call, I think that might be helpful? — Gerico, Nov 21 '14 at 19:10

score 0 · Answer 1 · edited May 23 '17 at 10:33

I think you simply want two separate columns in the table:

CREATE TABLE totals (
    id int(11) NOT NULL AUTO_INCREMENT,
    total1 float NOT NULL,
    total2 float NOT NULL,
    PRIMARY KEY (id)
) ;


$api = array(
    'total1' => 'http://api.jo.je/justgiving/data/myuserpage',
    'total2' => 'http://api.jo.je/justgiving/data/myuserpage2',
);

// The saving part is missing from your code, but it should be something like
$sql   = "UPDATE totals SET {$source}=? WHERE id=?";$q = $conn->prepare($sql);
$query = $conn->prepare($sql);
// Note: the above assumes that the "id" already exists. Otherwise
// you need an "UPSERT" (UPdate or inSERT) that will insert a new value or update
// it if it already exists. Find more @ this answer:
// https://stackoverflow.com/questions/15383852/sql-if-exists-update-else-insert-into

/*
* Instead of adding up the two API calls' results, we store them separately
*
* Of course the value of "id" here must be the same as in the second page, or
* what you will retrieve will NOT be what you have stored!
*/
foreach($api as $column => $source) {    
    $data = file_get_contents($source);
    $feed = json_decode($data);
    if (is_object($feed)) {
        $value = $feed->donations_total;
        $query->execute(array($value, $id));
    }
}

Now in the second page

// query
$sql = "SELECT total1, total2 from totals WHERE id=?";
$q = $conn->prepare($sql);
$q->execute(array($id));

$data = $q->fetch();

$total1 = $data['total1'];
$total2 = $data['total2'];

(This is the link to the answer referred above).

mySQL seperate data stored in table

1 Answers1