?
Solved

PHP & MYSQL GET DATA FROM ARRAY AND INSERT INTO TABLE.

Posted on 2011-04-25
6
Medium Priority
?
569 Views
Last Modified: 2013-12-12
Hi there. I have to get the data from array.

PHP CODE:

 
<?php
error_reporting(E_ALL);

date_default_timezone_set('Europe/London');

// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // ON THE LOCAL COMPUTER USUALLY LOCALHOST
$db_name = "";          // DATABASE NAME
$db_user = "";        // DATABASE LOGIN NAME
$db_word = "";        // DATABASE PASSWORD

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
                                                                                                                                                     
// SEE IF THE TABLE IS LOADED
// MAN PAGE: http://php.net/mysql_fetch_array
$sql = "SELECT COUNT(*) FROM config";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];
$fmt = number_format($num);
echo "<br/>THERE ARE $fmt ROWS IN THE TABLE";


// DETERMINE THE NEWEST ROW IN THE TABLE
$sql = "SELECT period FROM config ORDER BY period DESC LIMIT 1";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH ASSOCIATIVE KEYS
$row = mysql_fetch_assoc($res);
$new = $row["period"];
echo "<br/>THE NEWEST DATE IN THE TABLE IS $new";

// SET A LOOP INDICATOR
$loop = TRUE;
while ($loop)
{
    $old = date('Y-m-d', strtotime($new . ' - 6 days'));
    $sql = "SELECT * FROM config WHERE period BETWEEN '$old' AND '$new' ";
    $res = mysql_query($sql);
    if (!$res)
    {
        $errmsg = mysql_errno() . ' ' . mysql_error();
        echo "<br/>QUERY FAIL: ";
        echo "<br/>$sql <br/>";
        die($errmsg);
    }
    $num = mysql_num_rows($res);
    echo "<br/>FROM $old TO $new";
    if (!$num)
    {
        $loop = FALSE;
        echo "<br/>NO DATA";
    }
    else
    {         
        echo "<br/>$num DATA ROWS";
    }
    while ($row = mysql_fetch_assoc($res))
    {
        echo "<br/>";
        var_dump($row);
            }
    $new = date('Y-m-d', strtotime($old . ' - 1 days'));
}

Open in new window


OUTPUT:

OUTPUT IMAGE
SQL:

 
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE TABLE `config` (
  `id` int(10) NOT NULL auto_increment,
  `pcid` varchar(32) NOT NULL,
  `period` date NOT NULL,
  `downloaded` varchar(64) NOT NULL,
  `uploaded` varchar(64) NOT NULL,
  `totals` varchar(128) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=19 ;

INSERT INTO `config` VALUES (1, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-19', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (2, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-18', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (3, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-17', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (4, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-16', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (5, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-15', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (6, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-14', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (7, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-13', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (8, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-12', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (9, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-11', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (10, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-10', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (11, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-09', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (12, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-08', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (13, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-07', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (14, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-06', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (15, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-05', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (16, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-04', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (17, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-03', '1073741824', '1073741824', '2147483648');
INSERT INTO `config` VALUES (18, '4db58eb70efe9157cb2e1bea48b57919', '2011-04-02', '1073741824', '1073741824', '2147483648');

Open in new window



What I need:

At the end before "NO DATA" it still prints a row "FROM - TO" that needs to be removed.
Also I need to insert that data into HTML table... How to do that?
0
Comment
Question by:altimofejevs
  • 3
  • 2
6 Comments
 
LVL 5

Expert Comment

by:Ronak Patel
ID: 35462574
try below code:

 
$num = mysql_num_rows($res);

if(!$num)
      echo "<br/><span style='display:none;'> FROM $old TO $new</span>";
else
        echo "<br/>FROM $old TO $new";

if (!$num)
{
    $loop = FALSE;
    echo "<br/>NO DATA";
}
else
{
    echo "<br/>FROM $old TO $new";
    echo "<br/>$num DATA ROWS";
}

Open in new window

0
 

Author Comment

by:altimofejevs
ID: 35462807
not really, Ronak_Patel. To insert this into the table we need to get it out from the array first.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35462852
Try moving this echo statement from line 84 to line 92

    echo "<br/>FROM $old TO $new";
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:altimofejevs
ID: 35462986
No it doesn't work and can't.
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 35463387
No it doesn't work and can't.
What does that mean?

When you run a query, you get a resource identifier. You use the resource identifier with one of the functions like mysql_fetch_assoc() to get an array.  The array is an associative array with the keys equal to the column names and the values equal to the data values from each row.

You can use var_dump() to print these arrays, and that is what you have got in the script above.  Var_dump() is useful because it shows you what the data looks like, for example, it shows you an array of a certain number of elements, it shows you the keys and the values.

You don't get data "out from" any array - computers do not work like that.  The data is there, just waiting to be used.  These statements are equivalent, but one of them wastes instructions copying information from one place to another.  No extra credit for that.

// EXAMPLE 1
echo $row["period"];

// EXAMPLE 2
$p = $row["period"];
echo $p;

Tabular information in HTML and data base results sets seem to be well suited for each other.  You can generate the rows of HTML tables with statements that look something like this (see code snippet).  Spew out a table tag before and a close-table tag after and you have your tabular information.  Add a little arithmetic and CSS and you can have nicely formatted and meaningful content.  It's not rocket science, but it takes time and effort to write the code.  If you are not sure how to write the code, please consider hiring a professional developer to write the code for you.  It won't cost much and it will get you professional results quickly.

Best of luck with your project, ~Ray

while ($row = mysql_fetch_assoc($res))
{
    echo "<tr>";
    echo "<td>";
    echo $row["id"];
    echo "</td>";
    echo "<td>";
    echo "$row["pcid"];
    echo "</td>";
    echo "</tr>";
}

Open in new window

0
 

Author Comment

by:altimofejevs
ID: 35463569
ok this is really helpful. Thanks.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question