Insert data into mysql

[ludo1960]

I changed the item column to be UNIQUE, and now 23 records are inserted. Don't thinks it is a proper fix, would love to know why the code causes a double insert. I don't see how the code is being run twice, surely a web page just refreshes itself once?

[Barand]

I guess you don't have any form of error reporting otherwise the second attempt to insert the records should now fail with duplicate key errors.

[ludo1960]

Gonna read up on error reporting, and try and find out why the double entry is haappening, many thanks, for all the help!

[Barand]

My pdo connection code looks like this. The ERRMODE option tells it to throw an exception error message when an error occurs

    $dsn = "mysql:dbname=".DBNAME."; host=".HOST."; charset=utf8";

    $db = new pdo($dsn, USERNAME, PASSWORD, 
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]);

If you use that you should get an error message from the 24th insert telling you where it is happening.

[ludo1960]

Already got that in my connection.php, and no errors are shown?

function pdoConnect() 
{
    $dsn = "mysql:dbname=".DBNAME."; host=".HOST."; charset=utf8";

    $db = new pdo($dsn, USERNAME, PASSWORD, 
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]);
    return $db;
}

$db = pdoConnect();

 

[Barand]

To summarise -

• the record counts show 23 records are added.
• No errors are reported from attempts to add duplicate keys.
• You report that 46 records are in the table

What is the query you are running that shows you 46 records?

[ludo1960]

Reading https://www.php.net/manual/en/language.types.array.php

the $newarr array at the moment is  [article] => somevalue, should be ['article'] => somevalue so,

foreach ($myarray as $k => $v) {
        $newarr[$k] = $v['href']; 
}

// how to cast $newarr key to string ??

 

[ludo1960]

From the mysql shell, i'm runnig:

SELECT COUNT(*) FROM apidata;

 

[ludo1960]

foreach ($myarray as $k => $v) {
        $newarr[strval($k)] = $v['href']; 
}

Does not work??

[Barand]

17 minutes ago, ludo1960 said:

foreach ($myarray as $k => $v) { $newarr[$k] = $v['href']; } // how to cast $newarr key to string ??

They are stings. print_r doesn't show the quotes.

$arr = [  "apple"  => "red",                          // array with string keys
          "orange" => "orange",
          "banana" => "yellow"
       ];
       
echo '<pre>', print_r($arr, 1), '</pre>';

/* OUTPUT FROM print_r()

        Array
        (
            [apple] => red
            [orange] => orange
            [banana] => yellow
        )

*/

echo '<pre>', var_dump($arr), '</pre>';

/* OUTPUT FROM var_dump()

        array(3) {
          ["apple"]=>
          string(3) "red"
          ["orange"]=>
          string(6) "orange"
          ["banana"]=>
          string(6) "yellow"
        }

*/

 

[ludo1960]

And here was me thinking I found the error, back to the drawing board!

Oh, and I added

error_reporting(E_ALL);

to the page, but still no errors show?

Edited May 14, 2019 by ludo1960

[Barand]

19 minutes ago, ludo1960 said:

From the mysql shell, i'm runnig:

SELECT COUNT(*) FROM apidata;

 

And is the "apidata" table you are querying from the mysql shell in the same database that you are connecting to in your script?

[ludo1960]

Yes,

USE mydb;

---->then 

SELECT COUNT(*) FROM apidata;

 

[ludo1960]

Try this yourself and you will see double entries:

<?php   

include_once 'connection.php' ;

//$url = "http://admin.contenta.com/api";

$url = 'https://live-contentacms.pantheonsite.io/api';

$jsondata = file_get_contents($url);

$response = json_decode($jsondata, true);

$myarray = $response['links'] ;

foreach ($myarray as $k => $v) {
        $newarr[strval($k)] = $v['href']; 
}

    $res = $db->prepare("INSERT INTO apidata (item, value) VALUES (?, ?)") ;
    
    foreach ($newarr as $key => $value) {
        $res->execute( [ $key, $value ] );
    }

    echo "<pre>";  print_r( $newarr ); echo "</pre>";

 

[Barand]

First of all I ran just the first portion of your code to look at the data

$url = 'https://live-contentacms.pantheonsite.io/api';

$jsondata = file_get_contents($url);

$response = json_decode($jsondata, true);

$myarray = $response['links'] ;

echo '<pre>', print_r($myarray, 1), '</pre>';

which gave me

Array
(
    [self] => https://live-contentacms.pantheonsite.io/api
    [blocks] => https://live-contentacms.pantheonsite.io/api/blocks
    [comments] => https://live-contentacms.pantheonsite.io/api/comments
    [reviews] => https://live-contentacms.pantheonsite.io/api/reviews
    [commentTypes] => https://live-contentacms.pantheonsite.io/api/commentTypes
    [consumer--consumer] => https://live-contentacms.pantheonsite.io/api/consumer/consumer
    [files] => https://live-contentacms.pantheonsite.io/api/files
    [graphql_query_map--graphql_query_map] => https://live-contentacms.pantheonsite.io/api/graphql_query_map/graphql_query_map
    [imageStyles] => https://live-contentacms.pantheonsite.io/api/imageStyles
    [mediaBundles] => https://live-contentacms.pantheonsite.io/api/mediaBundles
    [images] => https://live-contentacms.pantheonsite.io/api/images
    [articles] => https://live-contentacms.pantheonsite.io/api/articles
    [pages] => https://live-contentacms.pantheonsite.io/api/pages
    [recipes] => https://live-contentacms.pantheonsite.io/api/recipes
    [node--tutorial] => https://live-contentacms.pantheonsite.io/api/node/tutorial
    [contentTypes] => https://live-contentacms.pantheonsite.io/api/contentTypes
    [menus] => https://live-contentacms.pantheonsite.io/api/menus
    [vocabularies] => https://live-contentacms.pantheonsite.io/api/vocabularies
    [categories] => https://live-contentacms.pantheonsite.io/api/categories
    [tags] => https://live-contentacms.pantheonsite.io/api/tags
    [roles] => https://live-contentacms.pantheonsite.io/api/roles
    [users] => https://live-contentacms.pantheonsite.io/api/users
    [menuLinks] => https://live-contentacms.pantheonsite.io/api/menuLinks
)

Strange!. This the format you wanted without the next bit of code (which actually then fails as there are no subarrays with a "href" key).

So omiting the next bit and loading the data

$url = 'https://live-contentacms.pantheonsite.io/api';

$jsondata = file_get_contents($url);

$response = json_decode($jsondata, true);

$myarray = $response['links'] ;


    $res = $db->prepare("INSERT INTO apidata (item, value) VALUES (?, ?)") ;
    
    foreach ($myarray as $key => $value) {              // using myarray and not newarr
        $res->execute( [ $key, $value ] );
    }

then looking at the apidata with mysql workbench - 23 records

[ludo1960]

Stranger and stranger, if I run the code with the original code above: i get:

Array
(
    [self] => h
    [blocks] => h
    [comments] => h
    [reviews] => h
    [commentTypes] => h
    [consumer--consumer] => h
    [files] => h
    [graphql_query_map--graphql_query_map] => h
    [imageStyles] => h
    [mediaBundles] => h
    [images] => h
    [articles] => h
    [pages] => h
    [recipes] => h
    [node--tutorial] => h
    [contentTypes] => h
    [menus] => h
    [vocabularies] => h
    [categories] => h
    [tags] => h
    [roles] => h
    [users] => h
    [menuLinks] => h
)

And the database is like so:

Then changing to your new version of the code gives me:

Array
(
    [self] => https://live-contentacms.pantheonsite.io/api
    [blocks] => https://live-contentacms.pantheonsite.io/api/blocks
    [comments] => https://live-contentacms.pantheonsite.io/api/comments
    [reviews] => https://live-contentacms.pantheonsite.io/api/reviews
    [commentTypes] => https://live-contentacms.pantheonsite.io/api/commentTypes
    [consumer--consumer] => https://live-contentacms.pantheonsite.io/api/consumer/consumer
    [files] => https://live-contentacms.pantheonsite.io/api/files
    [graphql_query_map--graphql_query_map] => https://live-contentacms.pantheonsite.io/api/graphql_query_map/graphql_query_map
    [imageStyles] => https://live-contentacms.pantheonsite.io/api/imageStyles
    [mediaBundles] => https://live-contentacms.pantheonsite.io/api/mediaBundles
    [images] => https://live-contentacms.pantheonsite.io/api/images
    [articles] => https://live-contentacms.pantheonsite.io/api/articles
    [pages] => https://live-contentacms.pantheonsite.io/api/pages
    [recipes] => https://live-contentacms.pantheonsite.io/api/recipes
    [node--tutorial] => https://live-contentacms.pantheonsite.io/api/node/tutorial
    [contentTypes] => https://live-contentacms.pantheonsite.io/api/contentTypes
    [menus] => https://live-contentacms.pantheonsite.io/api/menus
    [vocabularies] => https://live-contentacms.pantheonsite.io/api/vocabularies
    [categories] => https://live-contentacms.pantheonsite.io/api/categories
    [tags] => https://live-contentacms.pantheonsite.io/api/tags
    [roles] => https://live-contentacms.pantheonsite.io/api/roles
    [users] => https://live-contentacms.pantheonsite.io/api/users
    [menuLinks] => https://live-contentacms.pantheonsite.io/api/menuLinks
)

And my database looks like:

My database must be goosed! How lucky is that?

[ginerjm]

You add one line for error reporting but did you add this one as well:

    ini_set('display_errors', '1');
 

Your line selects the type of errors to show, but this line turns on the display to your screen.  The manual would have helped you here.

[ludo1960]

Just tried it:

error_reporting(E_ALL);

ini_set('display_errors', '1');

Still no errors reported, double entries persist. I think a re-install of my dev box is on the cards.

[ginerjm]

Could we see where you did this?

[ludo1960]

sure, here you go:

<?php   

error_reporting(E_ALL);
ini_set('display_errors', '1');

include_once 'connection.php' ;

$url = "http://admin.contenta.com/api";

//$url = 'https://live-contentacms.pantheonsite.io/api';

$jsondata = file_get_contents($url);

$response = json_decode($jsondata, true);

$myarray = $response['links'] ;

foreach ($myarray as $k => $v) {
       $newarr[strval($k)] = $v['href']; 
}

    $res = $db->prepare("INSERT INTO apidata (item, value) VALUES (?, ?)") ;
    
    foreach ($newarr as $key => $value) {
        $res->execute( [ $key, $value ] );
    }

    echo "<pre>";  print_r( $newarr ); echo "</pre>";

 

[Barand]

The best place for those settings is in your php.ini file. This has 2 distinct advantages

1. You don't have to keep repeating that code on every page
2. start-up errors, such as syntax errors, will also be reported instead of just a blank page

[ludo1960]

Added it to /etc/php/7.2/cli/php.ini  :

error_reporting = E_ALL

error_reporting(E_ALL);
ini_set('display_errors', '1');

Restarted Nginx, sudo service php7.2-fpm reload, truncated the database table, tried page again, still double entry and no errors.

[gw1500se]

I suggest your turn on DB logging and see if that tells you anything.

[ludo1960]

ok,

I added  !include /etc/mysql/mariadb.cnf to my /etc/mysql/my.cnf

and added 

log_error=/var/log/mysql/mariadb.err 

to /etc/mysql/mariadb.cnf

restarted mysql, truncated and tried again, still double entry and no mariadb.err where it should be??

[gw1500se]

Not error log, general log. It should tell you how many inserts are being done.