943,875 Members | Top Members by Rank

Ad:
  • MySQL Discussion Thread
  • Marked Solved
  • Views: 8925
  • MySQL RSS
You are currently viewing page 1 of this multi-page discussion thread
Feb 28th, 2009
0

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)
  1. $result1 = mysql_query("SELECT * FROM bookings WHERE bookingID='$last_insert_booking_id'");
  2.  
  3. echo "<table border='1'>
  4. <tr>
  5. <th>ID</th>
  6. <th>Firstname</th>
  7. <th>Lastname</th>
  8. <th>address1</th>
  9. <th>address2</th>
  10. <th>town</th>
  11. <th>postcode</th>
  12. <th>telephone</th>
  13. <th>email</th>
  14. <th>Arrival</th>
  15. <th>Departure</th>
  16. <th>Adults</th>
  17. <th>children</th>
  18. <th>Room Type</th>
  19. <th>Requirements</th>
  20.  
  21. </tr>";
  22.  
  23. while($row = mysql_fetch_array($result))
  24. {
  25. echo "<tr>";
  26. echo "<td>" . $row['clientID'] . "</td>";
  27. echo "<td>" . $row['firstname'] . "</td>";
  28. echo "<td>" . $row['surname'] . "</td>";
  29. echo "<td>" .$row['address1'] . "</td>";
  30. echo "<td>" .$row['address2'] . "</td>";
  31. echo "<td>" .$row['town'] . "</td>";
  32. echo "<td>" .$row['postcode'] . "</td>";
  33. echo "<td>" .$row['telephone'] . "</td>";
  34. echo "<td>" .$row['email'] . "</td>";
  35. }
  36.  
  37. while($row = mysql_fetch_array($result1)) {
  38. echo "<td>" .$row['startdate'] . "</td>";
  39. echo "<td>" .$row['enddate'] . "</td>";
  40. echo "<td>" .$row['adults'] . "</td>";
  41. echo "<td>" .$row['children'] . "</td>";
  42. echo "<td>" .$row['roomtype'] . "</td>";
  43. echo "<td>" .$row['requirements'] . "</td>";
  44.  
  45. echo "</tr>";
  46. }
  47. echo "</table>";
  48.  
  49.  
  50.  
  51. echo "Booking is complete";
  52.  
  53. print "Thank you for booking with us and your booking ID number is ".$last_insert_booking_id;
  54. mysql_close($con);
  55. ?>

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
File Type: bmp display.bmp (275.7 KB, 110 views)
Similar Threads
Reputation Points: 10
Solved Threads: 0
Junior Poster in Training
HB25 is offline Offline
74 posts
since Dec 2007
Feb 28th, 2009
0

Re: php/mysql display data in row and column

try this:

MySQL Syntax (Toggle Plain Text)
  1. <?php
  2.  
  3. //DO your query
  4. $result = mysql_query("SELECT * FROM bookings WHERE bookingID='".$last_insert_booking_id."'");
  5.  
  6. //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.
  7. $result_array = mysql_fetch_array($result);
  8.  
  9. //for each piece of information saved in the array, display it to the user.
  10. foreach($result_array as $key => $value)
  11. {
  12. if( !is_int($key) )
  13. {
  14. echo $key.' | '.$value.'<br />';
  15. }
  16. }
  17.  
  18. ?>
  19.  

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.
Reputation Points: 32
Solved Threads: 3
Posting Whiz in Training
MaxMumford is offline Offline
228 posts
since Oct 2006
Feb 28th, 2009
0

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)
  1.  
  2. 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.
Reputation Points: 10
Solved Threads: 0
Junior Poster in Training
HB25 is offline Offline
74 posts
since Dec 2007
Feb 28th, 2009
0

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
Reputation Points: 32
Solved Threads: 3
Posting Whiz in Training
MaxMumford is offline Offline
228 posts
since Oct 2006
Feb 28th, 2009
0

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)
  1. foreach($result_array as $key => $value)
  2. {
  3. if( !is_int($key) )
  4. {
  5. echo $key.' | '.$value.'<br />';
  6. }
  7. }
  8.  
  9.  
  10. echo $result_array['startdate'];
  11. echo $result_array['enddate'];
  12. echo $result_array['adults'];
  13. echo $result_array['children'];
  14. echo $result_array['roomtype'] ;
  15. echo $result_array['requirements'] ;
Reputation Points: 10
Solved Threads: 0
Junior Poster in Training
HB25 is offline Offline
74 posts
since Dec 2007
Feb 28th, 2009
0

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)
  1. <?php
  2.  
  3. //DO your query
  4. $result = mysql_query("SELECT * FROM bookings WHERE bookingID='".$last_insert_booking_id."'");
  5.  
  6. //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.
  7. $result_array = mysql_fetch_array($result);
  8.  
  9. //setup your table
  10. echo '<table>';
  11.  
  12. //for each piece of information saved in the array, display it to the user.
  13. foreach($result_array as $key => $value)
  14. {
  15. if( !is_int($key) )
  16. {
  17. echo '<tr><td>';
  18. echo $key;
  19. echo '</td><td>';
  20. echo $value;
  21. echo '</td></tr>';
  22. }
  23. }
  24.  
  25. //cloes your table
  26. echo '</table>';
  27.  
Reputation Points: 32
Solved Threads: 3
Posting Whiz in Training
MaxMumford is offline Offline
228 posts
since Oct 2006
Feb 28th, 2009
0

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.
Reputation Points: 10
Solved Threads: 0
Junior Poster in Training
HB25 is offline Offline
74 posts
since Dec 2007
Feb 28th, 2009
0

Re: php/mysql display data in row and column

php Syntax (Toggle Plain Text)
  1. <?php
  2.  
  3. //do your query
  4. $result = mysql_query("SELECT * FROM bookings WHERE bookingID='".$last_insert_booking_id."'");
  5.  
  6. //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.
  7. $result_array = mysql_fetch_array($result);
  8.  
  9. echo '<table>';
  10. //for each piece of information saved in the array, display it to the user.
  11. foreach($result_array as $key => $value)
  12. {
  13. echo '<tr><td>Start Date</td><td>'.$result_array['startdate'].'</td></tr>';
  14. echo '<tr><td>End Date</td><td>'.$result_array['enddate'].'</td></tr>';
  15. echo '<tr><td>Adults</td><td>'.$result_array['adults'].'</td></tr>';
  16. echo '<tr><td>Children</td><td>'.$result_array['children'].'</td></tr>';
  17. echo '<tr><td>Roomtype</td><td>'.$result_array['roomtype'].'</td></tr>';
  18. echo '<tr><td>Requirements</td><td>'.$result_array['requirements'].'</td></tr>';
  19. }
  20. echo '</table>';
  21.  
  22. ?>


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.
Reputation Points: 32
Solved Threads: 3
Posting Whiz in Training
MaxMumford is offline Offline
228 posts
since Oct 2006
Mar 1st, 2009
0

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
File Type: bmp table.bmp (395.4 KB, 70 views)
Reputation Points: 10
Solved Threads: 0
Junior Poster in Training
HB25 is offline Offline
74 posts
since Dec 2007
Mar 1st, 2009
0

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.
Reputation Points: 32
Solved Threads: 3
Posting Whiz in Training
MaxMumford is offline Offline
228 posts
since Oct 2006

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.
Message:
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





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC