Insert data in html table or form from php sql retrieve

[act78723]

I’m creating a report page and can’t figure out how to retrieve multiple rows from a database 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.

EDIT: Just tried running the report and get a fatal error on the sql1 update. That same sql runs fine in a mysql shell.

[frankbeen]

Why so difficult with those vague variable names and counters? There are tons of examples on the internet. Use associative arrays and not numerical arrays as names describes the subject better.

$stmt = $con->query("SELECT COUNT(IP_ADDRESS) FROM download WHERE FILENAME IS NOT NULL");

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) 
{

    // show what we have got
    echo '<pre>' . print_r($row, true) . '</pre>';
    
    // then for real start to use the columnnames
    echo $row['columnname'] . '<br>';
}

[act78723]

echo ‘

’ . print_r($row, true) . ‘

’;

I’ve revised the code as follows:

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

if (!$con = PDOConnect("test")):
{
	echo "Failed to connect to database" ;
    exit;
}
else:
{
    $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);
        $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") ;
    }

    $stmt = $con->query("SELECT COUNT(DISTINCT `IP_ADDRESS`) FROM download");
    $test = $stmt->fetch(PDO::FETCH_NUM); 
    $distinct = $test[0];

    $stmt = $con->query("SELECT COUNT(IP_ADDRESS) FROM download");
    $test = $stmt->fetch(PDO::FETCH_NUM);
    $total= $test[0];
 
    $stmt = $con->query("SELECT COUNT(IP_ADDRESS) FROM download WHERE FILENAME LIKE '%.iso'");
    $test = $stmt->fetch(PDO::FETCH_NUM);   
    $iso = $test[0];
 
    $stmt = $con->query("SELECT COUNT(IP_ADDRESS) FROM download WHERE FILENAME LIKE '%.deb'");
    $test = $stmt->fetch(PDO::FETCH_NUM);
    $deb = $test[0];
  
    $stmt = $con->query("SELECT COUNT(IP_ADDRESS) FROM download WHERE FILENAME LIKE '%.tar.gz'");
    $test = $stmt->fetch(PDO::FETCH_NUM);
    $src = $test[0];

}


endif;
//exit; 

?>

Now I’m having a problem extracting an ip_address from an array. The ip is stored in the table as a binary(64) and I’m comparing it to other ip’s also stored as binary(64). Can you provide any guidance?

Thanks

[frankbeen]

Well, PDO::query() returns a PDOStatement object and from that object you must fetch your data again.

while($row = $country->fetch(PDO::FETCH_ASSOC)) {

See it like this. The query function will pick the rows for you from the table and set them ready for delivery but not more then that. The fetch() function will deliver the rows one by one for you.

But you have more problems:

• You insert variables directly into your query which is very dangerous. You risk SQL injection what could screw up or leak out your whole database. You should use prepared statements instead.
• You have multiple SELECT queries in a while loop which could be a serious performance issue, It will be very slow and you make maybe hundreds of requests to your database server. The trick is to QUERY all the data you need (and not more) in one time and then walk through the data with a loop,
• You should examine your database layout. It looks like you could add some relation between the download table and ip_lookup table. Google on Mysql JOINs.
• You are still unnecessarily copying variables ($temp = $row)
• realize that $row is still an array. you should use the syntax $row[‘columnname’] to use a single value

[act78723]

@frankbeen How would I join the following: