Jump to content

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?

Link to post
Share on other sites
  • Replies 56
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Posted Images

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.

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

 

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?

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

*/

 

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?

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

 

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

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?

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.

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

 

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

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

Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

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.