Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Displaying data from mysql 1

Status
Not open for further replies.
Evil8

Evil8

MIS
Mar 3, 2006
313
US
Okay, I'm a newby and I've only been working on this for two days. I have:

$query = 'SELECT * FROM `meeting` '
. ' INNER JOIN `place` ON meeting.name = place.name '
. ' INNER JOIN `information` ON meeting.info_id = information.id '
. ' ORDER BY `plan`,`state`,`city` ';
$result=mysql_query($query);

echo "<table border='0'>
<tr>
<th>Date</th>
<th>Time</th>
<th>Place</th>
<th>Address</th>
<th>City</th>
<th>State</th>
<th>Zip</th>
<th>Information</th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['date'] . "</td>";
echo "<td>" . $row['time'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['address1'] . ", " . $row['place_address2'] . "</td>";
echo "<td>" . $row['city'] . "</td>";
echo "<td>" . $row['state'] . "</td>";
echo "<td>" . $row['zip'] . "</td>";
echo "<td>" . $row['plan'] . "</td>";
echo "</tr>";
}
echo "</table>";

Of course this way the data is displayed in rows like a spreadsheet. What I for the output to look like would be

for the data to be grouped under header rows by city, state

underneith the header row would be:

[name]
[date] . " - " . [time] //date format Thursday, October 28, 2010 and time format 11:00 am
[address1] . " - " . [address2] // if address2 is null then just address1

As always your help is greatly appreciated.
 
Sort by date Sort by votes
Okay, I'm making some progress.

I've figured I've changed:

Code: 
{  
echo "<tr>";
echo "<td>" . $row['date'] . "</td>";
echo "<td>" . $row['time'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['address1'] . ", " . $row['place_address2'] . "</td>";
echo "<td>" . $row['city'] . "</td>";
echo "<td>" . $row['state'] . "</td>";
echo "<td>" . $row['zip'] . "</td>";
echo "<td>" . $row['plan'] . "</td>";
echo "</tr>";  
}

to:

Code: 
{
echo "<tr>";
echo "<td>" . $row['city'] . ", " . $row['state'] .  "</br>";
echo "<br>" . date("l F d, Y",$row[`date`]) . " - " . date("g:i a",$row['time'] .  "</br>";
echo "<br>" . $row['name'] . "</br>";
echo "<br>" . $row['address1'] . ", " . $row['address2'] . "</br>";
echo "<br>" . $row['plan'] . "</td>";			
echo "</tr>";
}
 
Upvote 0 Downvote
Hit the damned wrong button... grrrr

I still have a crappy web page format. What's the proper syntax in my case to add in font size, color and bolding into my code?

Also I still need to figure out how to group this data under the proper headers and columnize it.
 
Upvote 0 Downvote
I found some typos above and fixed those, but I noticed that all my dates are returning as:

Wednesday December 31, 1969

and all my times are returning as:
7:00 pm

What gives?

I'm really confused about where to put things, in what order and proper syntax. Can anyone help or point me to a good book?
 
Upvote 0 Downvote
okay I fixed that too.

Code: 
echo "<br>" . date("F j, Y ", strtotime($row['date'])). " - " . date("g:i a ", strtotime($row['time'])) .  "</br>";

I'm still looking...
 
Upvote 0 Downvote
it's difficult to follow this thread.

what is the current problem and what is the current code that you are using that exposes this problem?
 
Upvote 0 Downvote
Sorry... I'm learning my way through this and I founds some solutions on my own such as the correct use of the date() function. What I want to do is have the mysql meeting schedual data format simliar to the static page I have up at So far I have the data in one column and it looks like crap. Here is my code so far is:

Code: 
<?php 

  $username="me";
  $password="******";
  $database="me-meetingSchedual";

  mysql_connect("me-meetingSchedual.mysql.me.com:3306",$username,$password);
  @mysql_select_db($database) or die("Unable to select database");
		
    $query = 'SELECT * FROM `meeting` '
    . ' INNER JOIN `place` ON meeting.name = place.name '
    . ' INNER JOIN `information` ON meeting.info_id = information.id '
    . ' ORDER BY `plan`,`state`,`city` '; 
	$result=mysql_query($query);
		
	echo "<table border='1'>
	  <tr>
	  </tr>";
		
	  while($row = mysql_fetch_array($result))

		{
		echo "<tr>";
		echo "<td>" . $row['city'] . ", " . $row['state'] .  "</br>";
		echo "<br>" . date("F j, Y ", strtotime($row['date'])). " - " . date("g:i a ", strtotime($row['time'])) .  "</br>";
		echo "<br>" . $row['name'] . "</br>";
		echo "<br>" . $row['address1'] . ", " . $row['address2'] . "</br>";
		echo "<br>" . $row['plan'] . "</td>";			
		echo "</tr>";
		}
	echo "</table>";

?>

Thank you for all your help. I don't really anyone to do the work for me, I just need help figuring where to put code, in what order code items go and proper syntax. How to break this into multiple columns with proper headings and apply html formatting codes.

Future endeavors in html/mysql/php will be to develop users sorts of this data, a user rsvp page of some kind, a web page interface to input new data and as you can guess this needs to hide the login and password to the server.

Again thank you.
 
Upvote 0 Downvote
you are seperating your data using line breaks <br>
try using <td> instead.
setup your headings in the table before the while loop

check the source code of the sample site & then compare it to that generated by your script

I have found it is well worth adding newlich characters into echo statements ("\n")

echo "<td>First column</td>\n";
echo "<td>Second Column</td>\n";

They have no effect on the browser rendering but make the generated HTML much more readable.


I do not Have A.D.D. im just easily, Hey look a Squirrel!
 
Upvote 0 Downvote
IPGuru, Thanks...

you are seperating your data using line breaks <br>try using <td> instead.

echo "<td>" . $row['name'] . "</td>";

works for all the data lines, but puts them in table rows...

echo "<td> . $row['name'] . </td>\n";

gives me a blank page - I'm uploading to a remote server so I don't get error messages.

echo "<td>" . $row['name'] . "</td>\n";

also gives me a blank page.

setup your headings in the table before the while loop

I assume I'm going to have to come up with something sql here to grab the correct city and state for the corresponding data. I have 11 different cities in two states. On the static page listed above I have 13 groups of some 30 meetings in 11 cities in two states with 3 different plans.

check the source code of the sample site & then compare it to that generated by your script

I can't find any output samples on any site that does what I need yet, but I'm still looking.
 
Upvote 0 Downvote
actually I would say that it is semantically wrong to use tables to display non-tabular data.

try this instead (not tested and needs a proper doctype etc). this will stack different plans across the page but different dates for the same plan will go down the page
Code: 
<?php

$username="me";
$password="******";
$database="me-meetingSchedual";

  mysql_connect("me-meetingSchedual.mysql.me.com:3306",$username,$password);
  @mysql_select_db($database) or die("Unable to select database");
        
    $query = 'SELECT * FROM `meeting` '
    . ' INNER JOIN `place` ON meeting.name = place.name '
    . ' INNER JOIN `information` ON meeting.info_id = information.id '
    . ' ORDER BY `plan`,`state`,`city` '; 
    $result=mysql_query($query);
	$plan = $city = $state = '';
	$first = true;
?>
<style type="text/css">
	body,div {padding:0; margin:0; line-height:1; font-family:Verdana, Geneva, Arial, Helvetica, sans-serif; font-size:70%;}
	#container{margin:0 auto; width:80%; background-color:#f1f1f1;overflow-y:hidden;}
	.citystateplan{width: 25%; min-width: 250px; margin-right:20px; float:left;}
	.name{margin-top: 36px; font-weight: bold;}
	.city{color:#990000; font-size:18px;}
	.plan{color:#000000; font-size:14px}
</style>
<?php while ($row = mysql_fetch_object($result)): ?>
<div class="event"
<?php if ($plan !== $row['plan'] || $state !==$row['state'] || $city !== $row['city']):
	$plan = $row['plan'];
	$city = $row['city'];
	$plan = $row['plan'];
	if (!$first) echo '</div>'; else $first = false;
?>
	<div class="citystateplan">
		<div class="city"><?php echo $row['city'];?>, <?php echo $row['state'];?> </div>
		<div class="plan"><?php echo $plan;?></div>
		
<?php endif;?>
		<div class="name"><?php echo $row['name'];?></div>
		<div class="date"><?php echo date("F j, Y ", strtotime($row['date'])). " - " . date("g:i a ", strtotime($row['time']));?></div>
		<div class="address"><?php echo $row['address1'];?>, <?php echo $row['address2'];?></div>
		
</div>
<?php endwhile;?>
</div>
 
Upvote 0 Downvote
Again thank you, this is helpfull. I'm trying to figure this out, in it's current form I an getting 3 columns of October 29, 2010 - 12:00 am in very small type. I will continue to work on this though.
 
Upvote 0 Downvote
jpadie,

In your code sample:
Code: 
<?php while ($row = mysql_fetch_object($result)): ?>
<div class="event"
<?php if ($plan !== $row['plan'] || $state !==$row['state'] || $city !== $row['city']):
    $plan = $row['plan'];
    $city = $row['city'];
    $plan = $row['plan'];
    if (!$first) echo '</div>'; else $first = false;
?>

When I change the mysql_fetch_object to mysql_fetch_array and put the > after the <div class="event I get data.

I'm still working on other parts as the display isn't correct yet. But again thanks a lot, I'm futher along than I've been.
 
Upvote 0 Downvote
You are correct. I should have fetched an associative array mysql_fetch_assoc()
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

R
  • Question
How Can I Use MySQL To Send Data To An Email
Replies
1
Views
241
Chris Miller
Chris Miller
SashaBuilder3
  • Locked
  • Question
import CSV file into MySql table in PhpMyAdmin
Replies
0
Views
366
SashaBuilder3
SashaBuilder3
lexer
  • Locked
  • Question
Join two mysql tables for populate datatable
Replies
3
Views
264
spamjim
spamjim
Brianfree
  • Locked
  • Question
Dropdown list data generator
Replies
3
Views
267
Brianfree
Brianfree
P
  • Locked
  • Question
JPgraph - How to stop a line graph from dropping to zero?
Replies
2
Views
362
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top