Jump to content
ludo1960

Insert data into mysql

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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();

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

From the mysql shell, i'm runnig:

SELECT COUNT(*) FROM apidata;

 

Share this post


Link to post
Share on other sites
foreach ($myarray as $k => $v) {
        $newarr[strval($k)] = $v['href']; 
}

Does not work??

Share this post


Link to post
Share on other sites
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"
        }

*/

 

Share this post


Link to post
Share on other sites
Posted (edited)

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 by ludo1960

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

Yes,

USE mydb;

---->then 

SELECT COUNT(*) FROM apidata;

 

Share this post


Link to post
Share on other sites

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>";

 

Share this post


Link to post
Share on other sites

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

image.png.9cb5f78d5b688f125e3603208fb3472f.png

Share this post


Link to post
Share on other sites

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:

image.thumb.png.3e46f1eb36244d1cb544012459214168.png

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:

image.thumb.png.d0a31bc6d4e4818a0e9aea7c17e1b5e8.png

My database must be goosed! How lucky is that?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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>";

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.