Jump to content

larry29936

Members
 View Profile  See their activity

  • Posts

    128

  • Joined

  • Last visited

 Content Type 

Posts posted by larry29936

    convert unsigned int ip_address from db to string

    in PHP Coding Help

    Posted · Edited by larry29936

    @Barand, I fixed the code so it doesn't error, but the ip's are wrong, many displaying as 0.0.123.678, which I know are wrong. The new code is: 

    <?php
    $query = $con->query('SELECT inet_ntoa(IP_ADDRESS)as address, FILENAME, country, area, city FROM download WHERE FILENAME is not null  ORDER BY country,area,city');

    while ($row = $query->fetch()) 
    {
        echo "<tr>";
        echo "<td>" . $row['address'] ."</td>";
        echo "<td>" . $row['FILENAME'] ."</td>";
        echo "<td>" . $row['country'] . "</td>";
        echo "<td>" . $row['area'] . "</td>";
        echo "<td>" . $row['city'] . "</td>";
        echo "</tr>";
    }
?>

    The sql file that the data was imported from had the ip's as strings that went into a column that is int(50) unsigned. I checked the import file and none of the imported ip's start with 0.0.

    convert unsigned int ip_address from db to string

    in PHP Coding Help

    Posted

    I have ip_addresses stored in the database as unsigned integers. I want to display them in standard xxx.xxx.xxx.xxx format in a table. In my code, I tried the following: 

    <?php
while ($row = $query->fetch()) 
{
    $address = inet_ntoa($row['IP_ADDRESS']);  //Throws error here
    echo "<tr>";
//    echo "<td>" . $row['IP_ADDRESS'] ."</td>";
    echo "<td>" . $row['$address'] ."</td>";
    echo "<td>" . $row['FILENAME'] ."</td>";
    echo "<td>" . $row['country'] . "</td>";
    echo "<td>" . $row['area'] . "</td>";
    echo "<td>" . $row['city'] . "</td>";
    echo "</tr>";
}
?>

    It throws the following error: 

    Parse error: syntax error, unexpected 'inet_ntoa' (T_STRING) in /home/larry/web/test/public_html/report1.php on line 205

    How do I do the conversion so I can display the ip_address?

    Thanks in advance,

    Larry

     

    PHP uncaught error in script that runs fine in phpmyadmin

    in PHP Coding Help

    Posted

    I have the following php code that errors as indicated: 

    $query = $con->query('SELECT FILENAME, country, area, city FROM download WHERE FILENAME is not null');

Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'country' in 'field list' in /home/larry/web/test/public_html/report1.php:47 Stack trace: #0 /home/larry/web/test/public_html/report1.php(47): PDO->query('SELECT FILENAME...') #1 {main} thrown in /home/larry/web/test/public_html/report1.php on line 47

    The Select statement doesn't error when run in mysql shell or phpmyadmin. Here's the result of show create table download: 

    
localhost/test/download/        http://localhost/phpmyadmin/tbl_sql.php?db=test&table=download&token=5739c407033be3e118287bc7a9041c2c
 Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available. 

Your SQL query has been executed successfully.

show create table download

download    CREATE TABLE `download` (
  `ID` int(5) NOT NULL AUTO_INCREMENT,
  `LOG_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `IP_ADDRESS` int(64) unsigned NOT NULL,
  `FILENAME` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  `country` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  `area` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  `city` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1266 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci    

    Does anyone have an idea why this is happening?

    Need help creating mysql trigger

    in MySQL Help

    Posted

    @Barand, here's what you asked for: 

    
download	CREATE TABLE `download` (
  `ID` int(5) NOT NULL AUTO_INCREMENT,
  `LOG_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `IP_ADDRESS` int(64) unsigned NOT NULL,
  `FILENAME` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  `country` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  `area` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  `city` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1266 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci

      

    
ip_lookup	CREATE TABLE `ip_lookup` (
  `id` int(25) unsigned NOT NULL AUTO_INCREMENT,
  `start_ip` int(64) unsigned NOT NULL,
  `end_ip` int(64) unsigned NOT NULL,
  `continent` varchar(2) COLLATE utf8_general_mysql500_ci NOT NULL,
  `country` varchar(50) COLLATE utf8_general_mysql500_ci NOT NULL,
  `area` varchar(50) COLLATE utf8_general_mysql500_ci NOT NULL,
  `city` varchar(50) COLLATE utf8_general_mysql500_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `start_ip` (`start_ip`)
) ENGINE=InnoDB AUTO_INCREMENT=5277086 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci

     

    Need help creating mysql trigger

    in MySQL Help

    Posted · Edited by larry29936

    @Barand, ran the following  in mysql shell and it took 7 minutes to run: 

    SELECT download.FILENAME , ip_lookup.country , ip_lookup.area , ip_lookup.city 
FROM download 
INNER JOIN ip_lookup ON download.IP_ADDRESS BETWEEN ip_lookup.start_ip and ip_lookup.end_ip 
WHERE download.FILENAME is not null;

    There are 5 million + records in the ip_lookup table so that's probably the hang up. That was one reason why I wanted all the information in the download table.
     

    Need help creating mysql trigger

    in MySQL Help

    Posted · Edited by larry29936

    @Barand, This query gives an error of "Operand should contain 1 column(s)" in mysql shell:

    SELECT (download.FILENAME, ip_lookup.country, ip_lookup.area, ip_lookup.city)
    FROM download, ip_lookup
    WHERE download.IP_ADDRESS BETWEEN ip_lookup.start_ip and ip_lookup.end_ip;

     

    That's why I wanted all data in one table.

    Need help creating mysql trigger

    in MySQL Help

    Posted · Edited by larry29936

    I'm new to using triggers and really not sure how to proceed. I have an access table that a row is inserted whenever someone opens the website or downloads a file with the following structure: 

      `ID` int(5) NOT NULL autoincrement,
  `LOG_TIME` datetime NOT NULL DEFAULT current_timestamp(),
  `IP_ADDRESS` int(64) unsigned COLLATE utf8_general_mysql500_ci NOT NULL,
  `FILENAME` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
    `country` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
    `area` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
    `city` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci;

    When the site is accessed, a row is inserted with only the first 3 fields are filled. When a file is downloaded another row is inserted with the first 4 fields filled. I want to create a trigger such that when a download row is inserted, the IP_ADDRESS is compared to another table to update the country, area, and city fields. I can currently do that for the whole table in mysql shell using the following code: 

    UPDATE access t2, ip_lookup t1 SET t2.country = t1.country, t2.area = t1.area, t2.city = t1.city 
WHERE ((t2.IP_ADDRESS) BETWEEN (t1.start_ip) AND (t1.end_ip)) AND  t2.FILENAME is not null and t2.country is null;

    How would I write an "after insert" trigger to update the last 3 fields based on the ip of the row that was inserted because of a download?

    Thanks in advance,

    Larry

    Insert data in html table or form from php sql retrieve

    in PHP Coding Help

    Posted

    @gw1500se, I've decided to try to go about it in a different way but I'm having a problem extracting the ip_address from an array, as follows:

      

      $stmt = $con->query("SELECT DISTINCT(IP_ADDRESS) FROM download WHERE FILENAME is not NULL");
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $temp = $row ;
        echo var_dump($temp);  // shows as "array(1) { ["IP_ADDRESS"]=> string(64) "92.240.60.16" }"
        $country = $con->query("SELECT (country) FROM ip_lookup WHERE '$temp' between start_ip and end_ip");
        $area = $con->query("SELECT (area) FROM ip_lookup WHERE '$temp' between start_ip and end_ip");
        $city = $con->query("SELECT (city) FROM ip_lookup WHERE '$temp' between start_ip and end_ip");
        $test = $con->query("UPDATE TABLE download SET country = '$country', area = '$area', city= '$city' WHERE IP_ADDRESS = '$temp' and FILENAME is not NULL") ;
    }

    All ip_addresses are stored as binary(64) and the errors I'm getting follow: 

    Notice: Array to string conversion in /home/larry/web/test/public_html/report.php on line 26

Notice: Array to string conversion in /home/larry/web/test/public_html/report.php on line 27

Notice: Array to string conversion in /home/larry/web/test/public_html/report.php on line 28

Recoverable fatal error: Object of class PDOStatement could not be converted to string in /home/larry/web/test/public_html/report.php on line 29

    I'd appreciate some guidance.

    Larry

    Insert data in html table or form from php sql retrieve

    in PHP Coding Help

    Posted

    I already tried that but I'm getting a "Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't2.country' in 'field list' " from  

     $sql1 = 'Update  download t2, ip_lookup t1 set t2.country = t1.country, t2.area = t1.area, t2.city = t1.city where ((t2.IP_ADDRESS) = (t1.start_ip) OR (t2.IP_ADDRESS) > (t1.start_ip)) AND ((t2.IP_ADDRESS) = (t1.end_ip) OR (t2.IP_ADDRESS) < (t1.end_ip)) AND (t2.FILENAME is not null and t2.country is null)';

    I'm going to try making a trigger for the download table so that on insert it takes care of having to call that later.

    Insert data in html table or form from php sql retrieve

    in PHP Coding Help

    Posted

    6 minutes ago, gw1500se said:

    This is a bit confusing but the first query seems unnecessary. You don't need a query just to find the count since later you query for the data. The last query does not want to be in a loop. After the execute do a fetch which gives you an array. The size of that array is the count for your table size.

    So, get rid of the first query and get rid of the loop on the last query. What will the fetch give me, a count of the rows or the actual data to be inserted in the table?

    Insert data in html table or form from php sql retrieve

    in PHP Coding Help

    Posted

    I'm creating a report page and can't figure out how to retrieve multiple rows from a table and display them in html. There are 5 data elements in each row and I'm thinking that using a form in the html might be the best way as I'm totally ignorant about tables in html. I'm a newbie to php and can't figure out how to accomplish this. Here's the code that I have so far: 

    <?php
$filename = NULL;
session_start();
// start of script every time.

//  setup a path for all of your canned php scripts
$php_scripts = '../php/'; // a folder above the web accessible tree
//  load the pdo connection module  
require $php_scripts . 'PDO_Connection_Select.php';

//*******************************
//   Begin the script here
// Connect to the database

if (!$con = PDOConnect("foxclone")):
{
    echo "Failed to connect to database" ;
    exit;
}
else:
{
    $sql = 'SELECT COUNT(IP_ADDRESS) FROM download WHERE FILENAME IS NOT NULL'; 

    $sql1 = 'Update  download t2, ip_lookup t1 set t2.country = t1.country, t2.area = t1.area, t2.city = t1.city where ((t2.IP_ADDRESS) = (t1.start_ip) OR (t2.IP_ADDRESS) > (t1.start_ip)) AND ((t2.IP_ADDRESS) = (t1.end_ip) OR (t2.IP_ADDRESS) < (t1.end_ip)) AND (t2.FILENAME is not null and t2.country is null)';

    $sql2 = 'SELECT (IP_ADDRESS, FILENAME, country, area, city) from download where FILENAME is not null';

// Update the table
    $stmt = $con->prepare($sql1);
    $stmt->execute();  

// Get count of rows to be displayed in table
    $stmt = $con->prepare($sql);
    $stmt->execute() ;
    $cnt = $stmt->fetch(PDO::FETCH_NUM);

// retrieve one row at a time
    $i = 1;
    while($i <= $cnt){
      $stmt = $con->prepare($sql2);
      $row->execute(array(''));  // Do I need an array here?
// from here on, I'm lost

    $i++;

    I'd appreciate any guidance you can provide or understandable tutorials you can point me to.

    Larry     

    SQL insert from CLI

    in MySQL Help

    Posted · Edited by larry29936

    Got it working but having a problem getting the data into the fields in the proper format. Here's the sql I used to get it working: 

    LOAD DATA LOCAL INFILE  '/home/larry/web/test/public_html/dbip-city-lite-2020-04.csv'
INTO TABLE ip_lookup
FIELDS TERMINATED BY ',' ;

     

    Here's the structure of the table: 

    CREATE TABLE `foxclone`.`ip_lookup` ( `id` INT(10) NOT NULL AUTO_INCREMENT , `start_ip` VARCHAR(20) NOT NULL , `end_ip` VARCHAR(20) NOT NULL , `continent` VARCHAR(2) NOT NULL , `country` VARCHAR(2) NOT NULL , `area` VARCHAR(50) NOT NULL , `city` VARCHAR(50) NOT NULL , `latitude` DECIMAL NOT NULL , `longitude` DECIMAL NOT NULL , PRIMARY KEY (`id`(50))) ENGINE = InnoDB;

    Here's sample data from the csv: 

    105.155.190.0,105.155.191.67,AF,MA,Rabat-Salé-Kénitra,Rabat,34.015,-6.83272
105.155.191.68,105.155.191.255,AF,MA,Marrakesh-Safi,Marrakesh,31.6342,-7.99994
105.155.192.0,105.155.192.255,AF,MA,Fès-Meknès,Meknes,33.873,-5.54073
105.155.193.0,105.155.193.255,AF,MA,Oriental,Berkane,34.92,-2.32
105.155.194.0,105.155.194.255,AF,MA,Rabat-Salé-Kénitra,Rabat,34.015,-6.83272
105.155.195.0,105.155.195.255,AF,MA,Oriental,Oujda,34.6814,-1.90858

    The id column isn't autoincrementing, the start_ip  is showing the wrong format (2a00:10:a9ff:ffff:ff) and the rest of the fields are in the wrong columns. 

     

    SQL insert from CLI

    in MySQL Help

    Posted

    I've been trying to load a table from a 450MB .csv file but keep getting syntax errors. The insert sql looks like this: 

    LOAD DATA LOCAL INFILE '/home/larry/web/test/public_html/dbip-city-lite-2020-04.csv' INTO TABLE IP_LOOKUP FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

    The code was copied from [https://www.mysqltutorial.org/import-csv-file-mysql-table/]

    Does anyone see where I've gone wrong?

    Additional question: How to accomplish this import on my web host?

    Thanks in advance,
    Larry

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