Insert data in html table or form from php sql retrieve

[larry29936]

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     

[gw1500se]

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.

[larry29936]

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?

[larry29936]

OK, first query is gone and last query looks like this:

   $stmt = $con->prepare($sql2);
   $stmt->execute();  
   $result = $stmt->fetchAll();

Does the loop come next? Currently, it would give me 70+ rows of data.

[gw1500se]

Yes. $result is the data returned from the query where each element is a row from the query. The size of that array determines the size of your table. Although when you build the table you really don't need to know that. Jut add rows to the table until you run out of data.

You can see what the array looks like with this:

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

 

[larry29936]

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.

[gw1500se]

That is because there is a problem with your query. You may need take that problem to the MySQL forum but essential t2.country does not exist in that column.

[larry29936]

that query runs fine in a mysql shell. I don't understand why it fails in the php. Will have to run it by folks in the mysql forums.

[gw1500se]

Did you echo $sql1 then copy/paste that into the MySQL shell.

[larry29936]

No, I haven't. Thanks for that. I'll do it to confirm nothing get's changed from the original.

 

EDIT: Took the sql from the echo and it runs fine in mysql shell. I'm going to try taking the shorthand out of the sql and put in the actual table names to see what happens.

Edited May 1, 2020 by larry29936
Result of test

[larry29936]

Replacing t1 & t2 with actual table names didn't help in the report. same fatal error, " Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'download.country' in 'field list'". It runs both with t1 & t2 and with table names in mysql shell.

[StevenOliver]

Aside from the "column not found" error, would it be worth trying wrapping the string values in quotes in your SQL query? Or, for example, converting the IP addresses to integers before using comparison operators (using inet-ntop function)?

Edited May 2, 2020 by StevenOliver

[larry29936]

@StevenOliver, thanks for the response. I think that unless the "column not found" is resolved it doesn't make any sense to worry about the rest. BTW, the IP's are stored as binaries because I kept getting size errors trying to store them as unsigned integers. Still looking into why, but I think it may be because of IPv6 IP's. 

[gw1500se]

That is a MySQL error being returned by PDO. Since you claim the echo'ed string works in a MySQL shell you need to do some testing. First try to run it without setting that column. Perhaps the error will move or it may go away. Depending on which, try setting just that column in a separate query.

[larry29936]

@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

[gw1500se]

Part of that problem may be the way you are storing it in you database. You should store it as unsigned int and use the MySQL functions INET_ATON and INET_NTOA in your queries.

[larry29936]

I used binary(64) because for IPv6 ip's the db wanted the unsigned int to have a width of 128 vs binary worked at width of 64. I could recreate the tables and copy the data over but that seems like a lot of work. What do you suggest?

[larry29936]

I went ahead and re-created the tables and set all ip's as int(64) unsigned and imported the data again. This time the data imported OK.