The database does not update with my coinlist in SQL

[Johnruth]

I cannot get my code to update my table lcw-token in my SQL database.
When I run the PHP file, I do not get any errors, it shows that it is working for 2 seconds, then it is done, but there is no data in the table.

These are my columns in my database ID my two columns (CODE and NAME)

1st file

<?php

    session_start();
    include "../../include/connection.php";


  // Update LifeCoinWatch Database

    if(($_POST['db'] == 'LifeCoinWatchDB')){

        //$sql = mysqli_query($pdo,"SELECT * FROM api_currency ");

        $row = mysqli_fetch_array($sql);
			$data = json_encode(array('currency' => 'USD', 'sort' => 'rank', 'order' => 'ascending', 'offset' => 0,'meta' => false));
			$context_options = array (
			'http' => array (
			'method' => 'POST',
			'header' => "Content-type: application/json\r\n"
			. "x-api-key: $LIVECOINWATCH_API" . "\r\n",
			'content' => $data
			)
			);
			
$context = stream_context_create($context_options);
$fp = fopen('https://api.livecoinwatch.com/coins/list', 'r', false, $context);


       foreach($decoded_json_LifeCoinWatch['data'] as $LifeCoinWatch) {


            $LIFECOINWATCH_CODE = $LifeCoinWatch['code'];
            $LIFECOINWATCH_NAME = $LifeCoinWatch['name'];
           
            $LIFECOINWATCH_CODE = htmlentities($LIFECOINWATCH_CODE, ENT_QUOTES, "UTF-8");


            $checkSql = mysqli_query($pdo,"SELECT * FROM lcw_token WHERE CODE = '$CODE' ") or die(mysqli_error($pdo));
            $checkSqlCount = mysqli_num_rows($checkSql);

            if($checkSqlCount > 0){
            }
            else{

                $insertSql = mysqli_query($pdo,"INSERT INTO lcw_token (CODE, NAME) 
                VALUES('$LIFECOINWATCH_CODE', '$LIFECOINWATCH_NAME')") or die(mysqli_error($pdo));
            }
    

        }


    }


?>

second file

<p>
    <button class="btn btn-success btn-lg" id="LifeCoinWatchDB">LifeCoinWatch DB</button>&nbsp;
    <span id="LifeCoinWatchUpdate"></span>  
    <span id="loaders2" style="display:none;">
    <img alt="" src="../assets/img/loader/loada.gif">                   
    </span>
</p>

<script>

    $(document).ready(function()
	{
		$('#LifeCoinWatchDB').on('click', function() 
		{
			$.ajax(
			{
                type:'POST',
                url:'updateDatabase/db.php',
                data:{db:"LifeCoinWatchDB"},
                beforeSend: function()
				{
                    $("#loaders2").show();
                },
                    complete: function()
					{
						$("#loaders2").hide();
                    },
							success:function(data) 
							{
								$("#LifeCoinWatchUpdate").html("Databasis Opgedateer");
                            
							},
								error: function (error) 
								{
									$("#LifeCoinWatchUpdate").html(error);
								}
            });
		});
	});
	</script>

<p>
    <button class="btn btn-success btn-lg" id="LifeCoinWatchDB">LifeCoinWatch DB</button>&nbsp;
    <span id="LifeCoinWatchUpdate"></span>  
    <span id="loaders2" style="display:none;">
    <img alt="" src="../assets/img/loader/loada.gif">                   
    </span>
</p

 

Edited August 15, 2022 by Johnruth

[mac_gyver]

since you are using ajax to make the request to the server-side code, you won't see any output from the web page unless you look in the browser's developer tools network tab. i recommend that you get your code working fully without using an ajax request, then add the ajax code.

next, your connection variable is named $pdo, but you are using mysqli statements. you cannot mix database extension calls. you should be using the PDO extension anyways and using a prepared query when supplying external, unknown, dynamic values to a query when it gets executed.

also, you should NOT attempt to run a SELECT query to find if data already exists to decide if you are going to INSERT (or UPDATE) data. just  define an appropriate unique index in the database table and attempt to insert the data. if you don't care if the data already exists, use the IGNORE keyword in the sql query to prevent a duplicate index error. if you actually want to insert new data or update existing data, look into an INSERT ... ON DUPLICATE KEY UPDATE ... query.

Edited August 15, 2022 by mac_gyver

[Johnruth]

Thank you, I will look at all the mistakes, and try to fix them.

19 minutes ago, mac_gyver said:

since you are using ajax to make the request to the server-side code, you won't see any output from the web page unless you look in the browser's developer tools network tab. i recommend that you get your code working fully without using an ajax request, then add the ajax code.

next, your connection variable is named $pdo, but you are using mysqli statements. you cannot mix database extension calls. you should be using the PDO extension anyways and using a prepared query when supplying external, unknown, dynamic values to a query when it gets executed.

also, you should NOT attempt to run a SELECT query to find if data already exists to decide if you are going to INSERT (or UPDATE) data. just  define an appropriate unique index in the database table and attempt to insert the data. if you don't care if the data already exists, use the IGNORE keyword in the sql query to prevent a duplicate index error. if you actually want to insert new data or update existing data, look into an INSERT ... ON DUPLICATE KEY UPDATE ... query.

 

[Johnruth]

I have tried to get it to work, but still truckle to figure this out.

The code I have now( my new code)  and then the code that I am working from.

Most code have remarks to show what it is for.

<?php
  // Update LifeCoinWatch Database

   if(($_POST['db'] == 'LifeCoinWatchDB'))
	{

        $sql = mysqli_query($pdo,"SELECT * FROM api_currency ");

        $row = mysqli_fetch_array($pdo);
		
		 $LIVECOINWATCH_API = $row['LIVECOINWATCH_API'];
		
		  $url = 'https://api.livecoinwatch.com/coins/list';


        $parameters = [
			'currency' => 'USD',
            'start' => '1',
            'sort' => 'ascending',
			'offset' => '0',
			'meta' => 'false'
		];
		
		$headers =[
		"Accepts: application/json",
		"x-api-key: $LIVECOINWATCH_API\r\n"
		];
			
		
		$qs = http_build_query($parameters); // query string encode the parameters
        $request = "{$url}?{$qs}"; // create the request URL

       $curl = curl_init(); // Get Curl resource
        // Set cURL options
        curl_setopt_array($curl, array(
        CURLOPT_URL => $request,            // set the request URL
        CURLOPT_HTTPHEADER => $headers,     // set the headers 
        CURLOPT_RETURNTRANSFER => 1         // ask for raw response instead of bool
        ));
		
		$response = curl_exec($curl); // Send the request, save the response

        curl_close($curl); // Close request

        $decoded_json_lifecoinwatch = json_decode($response, true);// in index.php
		
					
		foreach($decoded_json_livecoinwatch['data'] as $livecoinwatch)// in index.html
		{


            $LIFECOINWATCH_code = $livecoinwatch['CODE'];
            $LIFECOINWATCH_name = $livecoinwatch['NAME'];
          
            $LIFECOINWATCH_name = htmlentities($LIFECOINWATCH_name, ENT_QUOTES, "UTF-8");		
					

			$checkSql = mysqli_query($pdo,"SELECT * FROM lcw_munt_lys WHERE CODE = '$LIFECOINWATCH_code' ") or die(mysqli_error($pdo));
            $checkSqlCount = mysqli_num_rows($checkSql);

            if($checkSqlCount > 0){
              
            }
            else{

				$insertSql = mysqli_query($pdo,"INSERT INTO lcw_munt_lys (CODE, NAME) VALUES('$LIFECOINWATCH_code', '$LIFECOINWATCH_name')") or die(mysqli_error($pdo));
				
				
								
			}
		}
?>

<?php
$data = json_encode(array('currency' => 'USD', 'sort' => 'rank', 'order' => 'ascending', 'offset' => 0, 'limit' => 2,'meta' => false));
$context_options = array (
    'http' => array (
        'method' => 'POST',
        'header' => "Content-type: application/json\r\n"
            . "x-api-key: <YOUR_API_KEY>" . "\r\n",
        'content' => $data
    )
);
$context = stream_context_create($context_options);
$fp = fopen('https://api.livecoinwatch.com/coins/list', 'r', false, $context);
print_r(stream_get_contents($fp));
?>

 

[mac_gyver]

have you determined if you are getting data from the api and if so, what exactly does $response look like?

[Johnruth]

1 hour ago, mac_gyver said:

have you determined if you are getting data from the api and if so, what exactly does $response look like?

I am trying to use echo or print, just to show me if it works, but not showing any info, I may put it at the wrong place.

[Johnruth]

I have done some changes, just to try to see what the result is using PHP 7.4.29, but getting an error ob line 12

<?php
$url = "https://api.livecoinwatch.com/coins/list";

$data = json_encode(array('currency' => 'USD', 'sort' => 'rank', 'order' => 'ascending', 'offset' => 0, 'limit' => 2,'meta' => false));

$headers = array(
   "content-type: application/json",
   "x-api-key: LIVECOINWATCH_API",
);

 $qs = http_build_query($data); // query string encode the parameters
 $request = "{$url}?{$qs}"; // create the request URL

 $curl = curl_init(); // Get Curl resource
 
curl_setopt_array($curl, array(
        CURLOPT_URL => $request,            // set the request URL
        CURLOPT_HTTPHEADER => $headers,     // set the headers 
        CURLOPT_RETURNTRANSFER => 1         // ask for raw response instead of bool
        )); 

       $response = curl_exec($curl); // Send the request, save the response

       curl_close($curl); // Close request   
?>

error  that I do not understand

Warning: http_build_query() expects parameter 1 to be array, string given in /tmp/c5j1hvdl6w41cdm/tester.php on line 12 Fatal error:

$qs = http_build_query($data); // query string encode the parameters

 

[Barand]

What is there not to understand? Your $data variable contains a string and not an array (as required)

[gizmola]

This is the php function you are trying to use http_build_query.

So as Barand stated, clearly you should not be json encoding the array.   Instead:

 

$data = array('currency' => 'USD', 'sort' => 'rank', 'order' => 'ascending', 'offset' => 0, 'limit' => 2,'meta' => false);

 

I can't say for sure if that is going to work with your API, but at least you'll get past your error.  Take a look at the php manual for a function whenever you have issues with one.