displaying delimited data from mySQL in a HTML table

[westonian]

Hi. I've got data on a mySQL table. Its bascially a list of dates, however all the data is displayed in ONE column of the mySQL table.

Using PHP, i have been able to show the data on a PHP page, but all the data is unformated (delimited).
You can see what I mean here:

http://www.eeski.net/dates2.php

it comes out like this:
[quote]Week1|2006-12-31|12:00:00|-1|Week1|1|1|-1|-1|New Years Eve|2007-01-07|10:00:00 Week2|2007-01-07|12:00:00|-1|Week2|1|1|-1|-1||2007-01-14|10:00:00 Week3|2007-01-14|12:00:00|-1|Week3|1|1|-1|-1||2007-01-21|10:00:00 Week4|2007-01-21|12:00:00|-1|Week4|1|1|-1|-1|Australia Day|2007-01-28|10:00:00 Week5|2007-01-28|12:00:00|-1|Week5|1|1|-1|-1|Australia Day|2007-02-04|10:00:00[/quote]

Thats exactly how its stored in the cell of the mySQL table. The other cells have other information that I don't want to use.

Now that file able (dates2.php) shows the data in delimited form.
My question is this: can I write a script that formats the un-formated data from dates2.php into a basic HTML table, adding spaces and placing the data into cells.

I don't neccissarily have to use dates2.php either. If I can create a PHP script that sources the data from the mySQL table and formats its instantly then thats fine too!

[ataria]

Use 'while();'

[code]
$select = mysql_query("SELECT * FROM `table_name`");

while ($info = mysql_fetch_array($select)) {
$week = $info['week'] // or whatever the column name is.
// do ^ that till you have all the columns listed..

<tr>
<td>
echo "$week";
</td>
<td>
// next variable...
</td>
</tr>
}

[/code]

[westonian]

hmm, didn't work.

This is a screenshot from myPHP admin:
[img]http://img85.imageshack.us/img85/2775/mysql1lw8.jpg[/img]
as you can see, all the information is in the one cell.

dates2.php displays the data in its raw form:

[quote]Week1|2006-12-31|12:00:00|-1|Week1|1|1|-1|-1|New Years Eve|2007-01-07|10:00:00 Week2|2007-01-07|12:00:00|-1|Week2|1|1|-1|-1||2007-01-14|10:00:00 Week3|2007-01-14|12:00:00|-1|Week3|1|1|-1|-1||2007-01-21|10:00:00 Week4|2007-01-21|12:00:00|-1|Week4|1|1|-1|-1|Australia Day|2007-01-28|10:00:00 Week5|2007-01-28|12:00:00|-1|Week5|1|1|-1|-1|Australia Day|2007-02-04|10:00:00[/quote]

what I want to be able to do it display that data on a NEW page (using HTML) in a nicely formatted table.

I was thinking that perhaps I should create a PHP script that uses the data from the original mySQL table to populate a NEW mySQL table, adding colums and rows and all. Perhaps that would make it easier.

[ataria]

yeah, definitly do that.
[then the while will work]

[westonian]

Ok, good news.

As shown above, I created [url=http://www.eeski.org/dates2.php]http://www.eeski.org/dates2.php[/url] to show the raw unformatted data from the cell of the initial table. It produces the following:

[quote]Week1|2006-12-31|12:00:00|-1|Week1|1|1|-1|-1|New Years Eve|2007-01-07|10:00:00 Week2|2007-01-07|12:00:00|-1|Week2|1|1|-1|-1||2007-01-14|10:00:00 Week3|2007-01-14|12:00:00|-1|Week3|1|1|-1|-1|Tets Week|2007-01-21|10:00:00 Week4|2007-01-21|12:00:00|-1|Week4|1|1|-1|-1|Australia Day|2007-01-28|10:00:00 Week5|2007-01-28|12:00:00|-1|Week5|1|1|-1|-1||2007-02-04|10:00:00 Week6|2007-02-04|12:00:00|-1|Week6|1|1|-1|-1||2007-02-11|10:00:00[/quote]

I then created a new table called "dates_table" and set it up to include the approrpriate number of columns

I then created a new php script called [url=http://www.eeski.org/motion.php]http://www.eeski.org/motion.php[/url]

This scrip took the information generated by dates2.php and inserted it into the "dates_table" table. Therefore, every time the initial table was altered, all I would have to do it run "motion.php" and it would (in turn) take the information generated by "dates2.php" and insert it into the new mySQL table "dates_table".

That part works well.

The next part draws the information in the "dates_table" table and displays it as a formatted HTML table. I called this file "motion2.php" and it works well. All it does is draws the information from the mySQL table and displays it as a HTML table. That works great!

My problem lies in the updating of MySQL table.
Bascially activating "motion.php" updates the mySQL table, but activating "motion2.php" generates the end result (a nicely formated HTML table populated with the MySQL table data).

Is there any code that I can place at the beging of "motion2.php" to run "motion.php", so that by the time the computer gets onto computing the rest of the script in "motion2.php" its already activated "motion.php", hence making the information that is finally generated completely up-to-date?

Boy i hope you can understand that

[ataria]

You can use  ' include("/motion.php"); '

[westonian]

ok!

awesome!

got it to work now.
check out [url=http://www.eeski.net/motion3a.php]http://www.eeski.net/motion3a.php[/url]

That displays all the info in the table 'dates_table'.

Now all I have to do is display certain coloums of that data on a HTML page.

For example, only make the rows "week", "date_start", "notes" and "date_end" show

any advice?

[ataria]

Yhup.

You can use the while for this..

[code]
<?php
// connect to database.

$select = mysql_query("SELECT * FROM `table_name`");
echo "<table><tr><td>Week</td><td>Date Start</td><td>Notes</td><td>Date End</td></tr>";
while ($info = mysql_fetch_array($select)) {
$week = $info['week'];
$date_start = $info['date_start']; 
$notes = $info['notes'];
$date_end = $info['data_end'];

<tr>
<td>
echo "$week";
</td>
<td>
echo "$date_start";
</td>
<td>
echo "$notes";
</td>
<td>
echo "$date_end ";
</td></tr>
}
echo "</table>";
?>

that should do it.
[/code]

[westonian]

hmmm, keeps coming up with [code]Parse error: syntax error, unexpected '<' in /home/eeskinet/public_html/motion4.php on line 16[/code]

that line corresponds with the first <tr>

*edit: by the first <tr> i mean the <tr> that exists at the second half of the code, not the first part near the $select = mysql stuff

[marcus]

[code]
<?php
// connect to database.

$select = mysql_query("SELECT * FROM `table_name`");
echo "<table><tr><td>Week</td><td>Date Start</td><td>Notes</td><td>Date End</td></tr>";
while ($info = mysql_fetch_assoc($select)) {
$week = $info['week'];
$date_start = $info['date_start']; 
$notes = $info['notes'];
$date_end = $info['data_end'];

echo "<tr>
<td>
$week
</td>
<td>
$date_start
</td>
<td>
$notes
</td>
<td>
$date_end
</td></tr>";
}

mysql_free_result($select);
echo "</table>";
?>
[/code]

[westonian]

lol

so close!

now i'm getting [quote]Parse error: syntax error, unexpected '/' in /home/eeskinet/public_html/motion4a.php on line 32[/quote]

that line corresponds to [quote]echo "</table>";[/quote]

[marcus]

I edited my code, just copy and paste is again. It should work.

[westonian]

yeah, just saw that.
Ok, I edited the code and it came back with this:

[code]Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/eeskinet/public_html/motion4a.php on line 10

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/eeskinet/public_html/motion4a.php on line 31
Week Date Start Notes Date End[/code]

but hey, at least the table titles are appearing!

[marcus]

Connect to your database. Should work then.

You're basically selecting from infinite.

[westonian]

I dont think thats the problem.

This is my full code:

[code]<?php
$username = "***";
$password = "***";
$hostname = "***";
$dbh = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
     
$select = mysql_query("SELECT * FROM `dates_table`");
echo "<table><tr><td>Week</td><td>Date Start</td><td>Notes</td><td>Date End</td></tr>";
while ($info = mysql_fetch_assoc($select)) {
$week = $info['week'];
$date_start = $info['date_start']; 
$notes = $info['notes'];
$date_end = $info['data_end'];

echo "<tr>
<td>
$week
</td>
<td>
$date_start
</td>
<td>
$notes
</td>
<td>
$date_end
</td></tr>";
}

mysql_free_result($select);
echo "</table>";
?>[/code]

[marcus]

You aren't connecting correctly.

[code]
<?php
$username = "username";
$password = "password";
$hostname = "localhost";
$database = "database";
$dbh = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
$db = mysql_select_db($database,$dbh) or die("Unable to connect to datase");
     
$select = mysql_query("SELECT * FROM `dates_table`");
echo "<table><tr><td>Week</td><td>Date Start</td><td>Notes</td><td>Date End</td></tr>";
while ($info = mysql_fetch_assoc($select)) {
$week = $info['week'];
$date_start = $info['date_start']; 
$notes = $info['notes'];
$date_end = $info['data_end'];

echo "<tr>
<td>
$week
</td>
<td>
$date_start
</td>
<td>
$notes
</td>
<td>
$date_end
</td></tr>";
}

mysql_free_result($select);
echo "</table>";
?>
[/code]

you're going to have to select a database to connect to

[westonian]

awesome!
Just AWESOME!

thankyou so much for your help!

[marcus]

Yep, no problem.

[westonian]

ok, everything is working really well, except for one part.

the following code worked before with the other versions I had, but now it doesn't seem to work
[code] $sql = "TRUNCATE TABLE `dates_table`";  [/code]

It should wipe the data from the table "dates_table" so that the rest of the script wont produce values that get added to data that is already in the table. In all my other attemps it worked well, but with this new code it doesn't seem to work.

get me?

This is my code:
[code]<?php
$username = "***";
$password = "***";
$hostname = "***";
$database = "***";
$dbh = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");



$db = mysql_select_db($database,$dbh) or die("Unable to connect to datase");

$sql = "TRUNCATE TABLE `dates_table`";   

 
  $fcontents = file ('http://www.eeski.net/dates2.php');
for($i=0; $i<sizeof($fcontents); $i++) {
      $line = trim($fcontents[$i]);
      $arr = explode("|", $line);
      #if your data is comma separated
      # instead of tab separated,
      # change the '\t' above to ','
   
      $sql = "insert into dates_table values ('".
                  implode("','", $arr) ."')";
      mysql_query($sql);
     
     
}
 
$select = mysql_query("SELECT * FROM `dates_table`");
echo "<table><tr><td>Week</td><td>Date Start</td><td>Notes</td><td>Date End</td></tr>";
while ($info = mysql_fetch_assoc($select)) {
$week = $info['week'];
$date_start = $info['date_start']; 
$notes = $info['notes'];
$date_end = $info['date_end'];

echo "<tr>
<td>
$week
</td>
<td>
$date_start
</td>
<td>
$notes
</td>
<td>
$date_end
</td></tr>";
}

mysql_free_result($select);
echo "</table>";
?>[/code]

[westonian]

what.. i got it!

god i'm dumb!

oh well... i only started to work with mySQL about 18 hours ago so i'm pretty pleased that i figured out what was wrong!

thanks for all your help!