westonian Posted November 23, 2006 Share Posted November 23, 2006 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.phpit 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! Quote Link to comment Share on other sites More sharing options...
ataria Posted November 23, 2006 Share Posted November 23, 2006 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] Quote Link to comment Share on other sites More sharing options...
westonian Posted November 23, 2006 Author Share Posted November 23, 2006 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. Quote Link to comment Share on other sites More sharing options...
ataria Posted November 23, 2006 Share Posted November 23, 2006 yeah, definitly do that.[then the while will work] Quote Link to comment Share on other sites More sharing options...
westonian Posted November 23, 2006 Author Share Posted November 23, 2006 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 columnsI 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 Quote Link to comment Share on other sites More sharing options...
ataria Posted November 23, 2006 Share Posted November 23, 2006 You can use ' include("/motion.php"); ' Quote Link to comment Share on other sites More sharing options...
westonian Posted November 23, 2006 Author Share Posted November 23, 2006 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" showany advice? Quote Link to comment Share on other sites More sharing options...
ataria Posted November 23, 2006 Share Posted November 23, 2006 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] Quote Link to comment Share on other sites More sharing options...
westonian Posted November 23, 2006 Author Share Posted November 23, 2006 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 Quote Link to comment Share on other sites More sharing options...
marcus Posted November 23, 2006 Share Posted November 23, 2006 [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] Quote Link to comment Share on other sites More sharing options...
westonian Posted November 23, 2006 Author Share Posted November 23, 2006 lolso 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] Quote Link to comment Share on other sites More sharing options...
marcus Posted November 23, 2006 Share Posted November 23, 2006 I edited my code, just copy and paste is again. It should work. Quote Link to comment Share on other sites More sharing options...
westonian Posted November 23, 2006 Author Share Posted November 23, 2006 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 10Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/eeskinet/public_html/motion4a.php on line 31Week Date Start Notes Date End[/code]but hey, at least the table titles are appearing! Quote Link to comment Share on other sites More sharing options...
marcus Posted November 23, 2006 Share Posted November 23, 2006 Connect to your database. Should work then.You're basically selecting from infinite. Quote Link to comment Share on other sites More sharing options...
westonian Posted November 23, 2006 Author Share Posted November 23, 2006 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] Quote Link to comment Share on other sites More sharing options...
marcus Posted November 23, 2006 Share Posted November 23, 2006 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 Quote Link to comment Share on other sites More sharing options...
westonian Posted November 23, 2006 Author Share Posted November 23, 2006 awesome!Just AWESOME!thankyou so much for your help! Quote Link to comment Share on other sites More sharing options...
marcus Posted November 23, 2006 Share Posted November 23, 2006 Yep, no problem. Quote Link to comment Share on other sites More sharing options...
westonian Posted November 23, 2006 Author Share Posted November 23, 2006 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] Quote Link to comment Share on other sites More sharing options...
westonian Posted November 23, 2006 Author Share Posted November 23, 2006 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! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.