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

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?
altimofejevsAsked:
Who is Participating?
 
Ray PaseurConnect With a Mentor Commented:
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
 
Ronak PatelCommented:
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
 
altimofejevsAuthor Commented:
not really, Ronak_Patel. To insert this into the table we need to get it out from the array first.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Ray PaseurCommented:
Try moving this echo statement from line 84 to line 92

    echo "<br/>FROM $old TO $new";
0
 
altimofejevsAuthor Commented:
No it doesn't work and can't.
0
 
altimofejevsAuthor Commented:
ok this is really helpful. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.