Ad:

MySQL Discussion Thread
Marked Solved
Views: 8925

Page 1
2
3
4
Next

You are currently viewing page 1 of this multi-page discussion thread

Feb 28th, 2009

php/mysql display data in row and column

Expand Post »

Hi
I am using the fallowing code to give some information back to the user after running some queries. The information will be displayed in row only.

MySQL Syntax (Toggle Plain Text)
$result1 = mysql_query("SELECT * FROM bookings WHERE bookingID='$last_insert_booking_id'");
 
echo "<table border='1'>
<tr>
<th>ID</th>
<th>Firstname</th>
<th>Lastname</th>
<th>address1</th>
<th>address2</th>
<th>town</th>
<th>postcode</th>
<th>telephone</th>
<th>email</th>
<th>Arrival</th>
<th>Departure</th>
<th>Adults</th>
<th>children</th>
<th>Room Type</th>
<th>Requirements</th>
 
</tr>";
 
while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['clientID'] . "</td>";
  echo "<td>" . $row['firstname'] . "</td>";
  echo "<td>" . $row['surname'] . "</td>";
 echo "<td>" .$row['address1'] . "</td>";
echo "<td>" .$row['address2'] . "</td>";
echo "<td>" .$row['town'] . "</td>";
echo "<td>" .$row['postcode'] . "</td>";
echo "<td>" .$row['telephone'] . "</td>";
echo "<td>" .$row['email'] . "</td>";
  } 
 
  while($row = mysql_fetch_array($result1)) {
echo "<td>" .$row['startdate'] . "</td>";
echo "<td>" .$row['enddate'] . "</td>";
echo "<td>" .$row['adults'] . "</td>";
echo "<td>" .$row['children'] . "</td>";
echo "<td>" .$row['roomtype'] . "</td>";
echo "<td>" .$row['requirements'] . "</td>";
 
  echo "</tr>";
  }
echo "</table>";
 
 
 
echo "Booking is complete";
 
print "Thank you for booking with us and your booking ID number is  ".$last_insert_booking_id;
mysql_close($con);
?>
$result1 = mysql_query("SELECT * FROM bookings WHERE bookingID='$last_insert_booking_id'");

echo "<table border='1'>
<tr>
<th>ID</th>
<th>Firstname</th>
<th>Lastname</th>
<th>address1</th>
<th>address2</th>
<th>town</th>
<th>postcode</th>
<th>telephone</th>
<th>email</th>
<th>Arrival</th>
<th>Departure</th>
<th>Adults</th>
<th>children</th>
<th>Room Type</th>
<th>Requirements</th>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['clientID'] . "</td>";
  echo "<td>" . $row['firstname'] . "</td>";
  echo "<td>" . $row['surname'] . "</td>";
 echo "<td>" .$row['address1'] . "</td>";
echo "<td>" .$row['address2'] . "</td>";
echo "<td>" .$row['town'] . "</td>";
echo "<td>" .$row['postcode'] . "</td>";
echo "<td>" .$row['telephone'] . "</td>";
echo "<td>" .$row['email'] . "</td>";
  } 
 
  while($row = mysql_fetch_array($result1)) {
echo "<td>" .$row['startdate'] . "</td>";
echo "<td>" .$row['enddate'] . "</td>";
echo "<td>" .$row['adults'] . "</td>";
echo "<td>" .$row['children'] . "</td>";
echo "<td>" .$row['roomtype'] . "</td>";
echo "<td>" .$row['requirements'] . "</td>";

  echo "</tr>";
  }
echo "</table>";


  
echo "Booking is complete";

print "Thank you for booking with us and your booking ID number is  ".$last_insert_booking_id;
mysql_close($con);
?>

I would like to display the information in this way i.e:

-----------------------------------------
First name | Smith
------------------------------------
last Nmae | Alan
------------------------------------
Arrival date| 28/02/2009
------------------------------------
and so on ......

Any suggestion as I am new to PHP. I have attached the image to show how the information is displayed.
Regards
HB25

Attached Images

display.bmp (275.7 KB, 110 views)

Similar Threads

Need Help...Please in MySQL
please help in MySQL
a little help in PHP
Insert Selected data In MySQL in PHP
Displaying images from mysql database in PHP

HB25

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

74 posts
since Dec 2007

Permalink

Feb 28th, 2009

Re: php/mysql display data in row and column

try this:

MySQL Syntax (Toggle Plain Text)
<?php
 
//DO your query
$result = mysql_query("SELECT * FROM bookings WHERE bookingID='".$last_insert_booking_id."'");
 
//because your query uses a PRIMARY KEY, we only have one result AND so don't have to use the while function. instead, save all the query results into an array.
$result_array = mysql_fetch_array($result);
 
//for each piece of information saved in the array, display it to the user.
foreach($result_array as $key => $value)
{
	if( !is_int($key) )
	{
		echo $key.' | '.$value.'<br />';
	}
}
 
?>
 
<?php

//do your query
$result = mysql_query("SELECT * FROM bookings WHERE bookingID='".$last_insert_booking_id."'");

//because your query uses a primary key, we only have one result and so don't have to use the while function. instead, save all the query results into an array.
$result_array = mysql_fetch_array($result);

//for each piece of information saved in the array, display it to the user.
foreach($result_array as $key => $value)
{
	if( !is_int($key) )
	{
		echo $key.' | '.$value.'<br />';
	}
}

?>

That will write all the results nicely to the page with titles for the user. You can organise it nicely into tables by changing the code echo'd in the foreach section if you want.

Max

Last edited by MaxMumford; Feb 28th, 2009 at 6:08 pm.

MaxMumford

Reputation Points: 32

Solved Threads: 3

Posting Whiz in Training

Offline

228 posts
since Oct 2006

Permalink

Feb 28th, 2009

Re: php/mysql display data in row and column

Hi
Thank you for replying to my thread bu t i don’t understand what do you mean buy using this code:

MySQL Syntax (Toggle Plain Text)

 
foreach($result_array as $key => $value)

For your information, i do have two queryes one return data from clients table and the other from bookings table.

can you be a bit clearer as I am new to PHP.
Kind Regards
HB25

Last edited by HB25; Feb 28th, 2009 at 6:22 pm.

HB25

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

74 posts
since Dec 2007

Permalink

Feb 28th, 2009

Re: php/mysql display data in row and column

yeah sure. assuming you understand arrays, the foreach function cycles through each array segment and runs a specific piece of code for that segment. The segment's title is saved in the $key variable, and it's contents is saved in the $value variable.

So from your mysql query, the $result_array would be filled with each columns value. The $key will be the column name, and the value of it will be the value of that column in the query.

So doing this:

echo $result_array['bookingID'];

would write that user's booking id.

doing this:

echo $result_array['first_name'];

would write that users first name (providing that information was saved in the database of course...)

Hope that helps. Max

MaxMumford

Reputation Points: 32

Solved Threads: 3

Posting Whiz in Training

Offline

228 posts
since Oct 2006

Permalink

Feb 28th, 2009

Re: php/mysql display data in row and column

Hi
This code work but not solve the program completely as i want to display the information in table, this is what I get:
booking has been changedbookingID | 33
roomID | 0
clientID | 33
startdate | 2009-03-01
enddate | 2009-03-02
adults | 1
children | 0
roomtype | Double
requirements | PC
2009-03-012009-03-0210DoublePC

there is two problem:
1. It does not display the infor mation in table
2. It does bring back everything from the table

Below is the code which i have used:

MySQL Syntax (Toggle Plain Text)
foreach($result_array as $key => $value)
{
	if( !is_int($key) )
	{
		echo $key.' | '.$value.'<br />';
	}
}
 
 
echo $result_array['startdate'];
echo $result_array['enddate'];
echo $result_array['adults'];
echo $result_array['children'];
echo $result_array['roomtype'] ;
echo $result_array['requirements'] ;
foreach($result_array as $key => $value)
{
	if( !is_int($key) )
	{
		echo $key.' | '.$value.'<br />';
	}
}


echo $result_array['startdate'];
echo $result_array['enddate'];
echo $result_array['adults'];
echo $result_array['children'];
echo $result_array['roomtype'] ;
echo $result_array['requirements'] ;

HB25

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

74 posts
since Dec 2007

Permalink

Feb 28th, 2009

Re: php/mysql display data in row and column

What do you mean by:
2) It does bring back everything from the table

As for bringing it back in a table, use this:

MySQL Syntax (Toggle Plain Text)
<?php
 
//DO your query
$result = mysql_query("SELECT * FROM bookings WHERE bookingID='".$last_insert_booking_id."'");
 
//because your query uses a PRIMARY KEY, we only have one result AND so don't have to use the while function. instead, save all the query results into an array.
$result_array = mysql_fetch_array($result);
 
//setup your table
echo '<table>';
 
//for each piece of information saved in the array, display it to the user.
foreach($result_array as $key => $value)
{
	if( !is_int($key) )
	{
		echo '<tr><td>';
		echo $key;
		echo '</td><td>';
		echo $value;
		echo '</td></tr>';
	}
}
 
//cloes your table
echo '</table>';
 
<?php

//do your query
$result = mysql_query("SELECT * FROM bookings WHERE bookingID='".$last_insert_booking_id."'");

//because your query uses a primary key, we only have one result and so don't have to use the while function. instead, save all the query results into an array.
$result_array = mysql_fetch_array($result);

//setup your table
echo '<table>';

//for each piece of information saved in the array, display it to the user.
foreach($result_array as $key => $value)
{
	if( !is_int($key) )
	{
		echo '<tr><td>';
		echo $key;
		echo '</td><td>';
		echo $value;
		echo '</td></tr>';
	}
}

//cloes your table
echo '</table>';

MaxMumford

Reputation Points: 32

Solved Threads: 3

Posting Whiz in Training

Offline

228 posts
since Oct 2006

Permalink

Feb 28th, 2009

Re: php/mysql display data in row and column

Hi

You almost solved my thread, what I meant by (It does bring back everything from the table ) is it will bring back the information from the first tow row as well in my table which I don’t want them to be retrieved and showed to the client. i only want the last 6 row to show.

BTW is it possible for my to write the row titles rather than retrieving them from the table.

Thanks for your help and as I am new PHP and Mysql.
Regards
HB25

Last edited by HB25; Feb 28th, 2009 at 7:44 pm.

HB25

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

74 posts
since Dec 2007

Permalink

Feb 28th, 2009

Re: php/mysql display data in row and column

php Syntax (Toggle Plain Text)
<?php
 
//do your query
$result = mysql_query("SELECT * FROM bookings WHERE bookingID='".$last_insert_booking_id."'");
 
//because your query uses a primary key, we only have one result and so don't have to use the while function. instead, save all the query results into an array.
$result_array = mysql_fetch_array($result);
 
echo '<table>';
//for each piece of information saved in the array, display it to the user.
foreach($result_array as $key => $value)
{
	echo '<tr><td>Start Date</td><td>'.$result_array['startdate'].'</td></tr>';
	echo '<tr><td>End Date</td><td>'.$result_array['enddate'].'</td></tr>';
	echo '<tr><td>Adults</td><td>'.$result_array['adults'].'</td></tr>';
	echo '<tr><td>Children</td><td>'.$result_array['children'].'</td></tr>';
	echo '<tr><td>Roomtype</td><td>'.$result_array['roomtype'].'</td></tr>';
	echo '<tr><td>Requirements</td><td>'.$result_array['requirements'].'</td></tr>';
}
echo '</table>';
 
?>
<?php

//do your query
$result = mysql_query("SELECT * FROM bookings WHERE bookingID='".$last_insert_booking_id."'");

//because your query uses a primary key, we only have one result and so don't have to use the while function. instead, save all the query results into an array.
$result_array = mysql_fetch_array($result);

echo '<table>';
//for each piece of information saved in the array, display it to the user.
foreach($result_array as $key => $value)
{
	echo '<tr><td>Start Date</td><td>'.$result_array['startdate'].'</td></tr>';
	echo '<tr><td>End Date</td><td>'.$result_array['enddate'].'</td></tr>';
	echo '<tr><td>Adults</td><td>'.$result_array['adults'].'</td></tr>';
	echo '<tr><td>Children</td><td>'.$result_array['children'].'</td></tr>';
	echo '<tr><td>Roomtype</td><td>'.$result_array['roomtype'].'</td></tr>';
	echo '<tr><td>Requirements</td><td>'.$result_array['requirements'].'</td></tr>';
}
echo '</table>';

?>

what do you mean:
BTW is it possible for my to write the row titles rather than retrieving them from the table.

Max

Last edited by peter_budo; Mar 1st, 2009 at 6:12 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

MaxMumford

Reputation Points: 32

Solved Threads: 3

Posting Whiz in Training

Offline

228 posts
since Oct 2006

Permalink

Mar 1st, 2009

Re: php/mysql display data in row and column

Hi
Thanks for sorting out my thread we almost there, but there is a very small problem which is after retrieving the information it will repeat the table more than 9 times, I was wondering how this could be fixed.

For your information I have attached image with this thread for you to see what is happening.

Thanks for your kind help.
HB25

Attached Images

table.bmp (395.4 KB, 70 views)

HB25

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

74 posts
since Dec 2007

Permalink

Mar 1st, 2009

Re: php/mysql display data in row and column

Ah okay I understand.

the only possible reason for this, is that in your database, you are searching for all records where the bookingID is 33. You must have 9 different records that fit this description.

You have 2 options here:
- Add "LIMIT 1" to the end of your query, however this will limit the result to one row, but might get the wrong one...
- Change the bookingID column in your table to an auto increment primary key (http://grafax.co.uk/OTHER/max/daniwe...oincrement.bmp)

It would also help if you sent me your whole code now too, and mayeb a screenshot of your database setup.

Max

Last edited by MaxMumford; Mar 1st, 2009 at 8:06 am.

MaxMumford

Reputation Points: 32

Solved Threads: 3

Posting Whiz in Training

Offline

228 posts
since Oct 2006

Page 1
2
3
4
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Create a trigger for doing a insert from a remote database/server

Next Thread in MySQL Forum Timeline: MySQL num rows error

DaniWeb Message

Cancel Changes

User Name
Password