943,621 Members | Top Members by Rank

Web Development > Databases > MySQL > php/mysql display data in row and column
Ad:
  • MySQL Discussion Thread
  • Marked Solved
  • Views: 8915
  • MySQL RSS
You are currently viewing page 2 of this multi-page discussion thread; Jump to the first page
Permalink
Mar 1st, 2009
0

Re: php/mysql display data in row and column

No there is only one record in my table with Booking ID 33 and the bookingID column in my table is already an auto increment primary key.

Any more suggestions?

Thanks
Reputation Points: 10
Solved Threads: 0
Junior Poster in Training
HB25 is offline Offline
74 posts
since Dec 2007
Permalink
Mar 1st, 2009
0

Re: php/mysql display data in row and column

BTW this is the query which I am using just in case if it does make deference.

MySQL Syntax (Toggle Plain Text)
  1. $result = mysql_query("SELECT * FROM bookings WHERE bookingID='$_POST[bookingID]'");
Reputation Points: 10
Solved Threads: 0
Junior Poster in Training
HB25 is offline Offline
74 posts
since Dec 2007
Permalink
Mar 1st, 2009
0

Re: php/mysql display data in row and column

try using this:

MySQL Syntax (Toggle Plain Text)
  1. $result = mysql_query("SELECT * FROM bookings WHERE bookingID='".$_POST['bookingID']."'");

sometimes when you don't encapsulate the second part of the where condition in speech marks it treats it like a search term when your saying "select * from bookings where bookingID like '%term%'
Last edited by MaxMumford; Mar 1st, 2009 at 10:11 am.
Reputation Points: 32
Solved Threads: 3
Posting Whiz in Training
MaxMumford is offline Offline
228 posts
since Oct 2006
Permalink
Mar 1st, 2009
0

Re: php/mysql display data in row and column

same thing hapning
Reputation Points: 10
Solved Threads: 0
Junior Poster in Training
HB25 is offline Offline
74 posts
since Dec 2007
Permalink
Mar 1st, 2009
0

Re: php/mysql display data in row and column

Thats really wierd..

Can you send the entire code to me?
Reputation Points: 32
Solved Threads: 3
Posting Whiz in Training
MaxMumford is offline Offline
228 posts
since Oct 2006
Permalink
Mar 1st, 2009
0

Re: php/mysql display data in row and column

This is my code

MySQL Syntax (Toggle Plain Text)
  1. <?php
  2.  
  3. // open database connection code AND THEN my code as follows
  4.  
  5. $sql="UPDATE bookings SET startdate ='$_POST[startdate]',enddate='$_POST[enddate]',adults='$_POST[adults]',children='$_POST[children]',roomtype='$_POST[roomtype]', requirements='$_POST[requirements]'
  6. WHERE bookingID = '$_POST[bookingID]'";
  7.  
  8. if (!mysql_query($sql,$con))
  9.   {
  10.   die('Error:  ' . mysql_error());
  11.   } 
  12.  
  13.   print "Your booking ID ".$bookingID;
  14.  
  15.   echo "    has been changed";
  16.  
  17.  
  18. $result = mysql_query("SELECT * FROM bookings WHERE bookingID='".$_POST['bookingID']."'");
  19.  
  20.  
  21. $result_array = mysql_fetch_array($result);
  22.  
  23. echo '<table border=1>';
  24.  
  25. foreach($result_array as $key => $value)
  26. {
  27. echo '<tr><td>Start Date</td><td>'.$result_array['startdate'].'</td></tr>';
  28. echo '<tr><td>End Date</td><td>'.$result_array['enddate'].'</td></tr>';
  29. echo '<tr><td>Adults</td><td>'.$result_array['adults'].'</td></tr>';
  30. echo '<tr><td>Children</td><td>'.$result_array['children'].'</td></tr>';
  31. echo '<tr><td>Roomtype</td><td>'.$result_array['roomtype'].'</td></tr>';
  32. echo '<tr><td>Requirements</td><td>'.$result_array['requirements'].'</td></tr>';
  33. }
  34. echo '</table>';
  35.  
  36.  
  37. mysql_close($con);
  38. ?>

Thanks for your help
Reputation Points: 10
Solved Threads: 0
Junior Poster in Training
HB25 is offline Offline
74 posts
since Dec 2007
Permalink
Mar 1st, 2009
0

Re: php/mysql display data in row and column

Ahhh i know whats going wrong. This bit:

MySQL Syntax (Toggle Plain Text)
  1. foreach($result_array as $key => $value)
  2. {
  3. echo '<tr><td>Start Date</td><td>'.$result_array['startdate'].'</td></tr>';
  4. echo '<tr><td>End Date</td><td>'.$result_array['enddate'].'</td></tr>';
  5. echo '<tr><td>Adults</td><td>'.$result_array['adults'].'</td></tr>';
  6. echo '<tr><td>Children</td><td>'.$result_array['children'].'</td></tr>';
  7. echo '<tr><td>Roomtype</td><td>'.$result_array['roomtype'].'</td></tr>';
  8. echo '<tr><td>Requirements</td><td>'.$result_array['requirements'].'</td></tr>';
  9. }
  10. echo '</table>';

is saying that for every piece of information saved inside the results array, write a table with the results. Take that out of the foreach function and have it on its own. Here how the whole code should look:

MySQL Syntax (Toggle Plain Text)
  1. <?php
  2.  
  3. // open database connection code AND THEN my code as follows
  4.  
  5. $sql="UPDATE bookings SET startdate ='$_POST[startdate]',enddate='$_POST[enddate]',adults='$_POST[adults]',children='$_POST[children]',roomtype='$_POST[roomtype]', requirements='$_POST[requirements]'
  6. WHERE bookingID = '$_POST[bookingID]'";
  7.  
  8. if (!mysql_query($sql,$con))
  9.   {
  10.   die('Error:  ' . mysql_error());
  11.   } 
  12.  
  13.   print "Your booking ID ".$bookingID;
  14.  
  15.   echo "    has been changed";
  16.  
  17.  
  18. $result = mysql_query("SELECT * FROM bookings WHERE bookingID='".$_POST['bookingID']."'");
  19.  
  20.  
  21. $result_array = mysql_fetch_array($result);
  22.  
  23. echo '<table border=1>';
  24. echo '<tr><td>Start Date</td><td>'.$result_array['startdate'].'</td></tr>';
  25. echo '<tr><td>End Date</td><td>'.$result_array['enddate'].'</td></tr>';
  26. echo '<tr><td>Adults</td><td>'.$result_array['adults'].'</td></tr>';
  27. echo '<tr><td>Children</td><td>'.$result_array['children'].'</td></tr>';
  28. echo '<tr><td>Roomtype</td><td>'.$result_array['roomtype'].'</td></tr>';
  29. echo '<tr><td>Requirements</td><td>'.$result_array['requirements'].'</td></tr>';
  30. echo '</table>';
  31.  
  32.  
  33. mysql_close($con);
  34. ?>


The query returned 9 pieces of information and stored them inside an array. Then the foreach function was going through all nine items in the array and printing your table of results
Reputation Points: 32
Solved Threads: 3
Posting Whiz in Training
MaxMumford is offline Offline
228 posts
since Oct 2006
Permalink
Mar 1st, 2009
0

Re: php/mysql display data in row and column

Hi MaxMumford
This did work; I just wanted to say a big thank you for solving my thread
My last problem is at the moment user will have to type date in this format (YYYY-MM-DD) as my phpMyadmin will only store date in this format any idea how I could make it to store date in this format (DD-MM-YYYY)
Once again thanks ever so much for your help.
Regards
HB25
Reputation Points: 10
Solved Threads: 0
Junior Poster in Training
HB25 is offline Offline
74 posts
since Dec 2007
Permalink
Mar 2nd, 2009
0

Re: php/mysql display data in row and column

php Syntax (Toggle Plain Text)
  1. <?php
  2.  
  3. // open database connection code and then my code as follows
  4.  
  5. $sql="UPDATE bookings SET startdate ='$_POST[startdate]',enddate='$_POST[enddate]',adults='$_POST[adults]',children='$_POST[children]',roomtype='$_POST[roomtype]', requirements='$_POST[requirements]'
  6. WHERE bookingID = '$_POST[bookingID]'";
  7.  
  8. if (!mysql_query($sql,$con))
  9.   {
  10.   die('Error:  ' . mysql_error());
  11.   } 
  12.  
  13.   print "Your booking ID ".$bookingID;
  14.  
  15.   echo "    has been changed";
  16.  
  17.  
  18. $result = mysql_query("SELECT * FROM bookings WHERE bookingID='".$_POST['bookingID']."'");
  19. if(mysql_num_rows($result) > 0 ) { 
  20.  //only print this if there is atleast 1 row. This will avoid a lot of problems which could arise from mysql_fetch_array if there are no rows in the table
  21. //I would also declare the table headers when I am sure at least 1 record exist. If there aren't any records, it wont print the html table 
  22. echo '<table border=1>
  23. <tr><td>Start Date</td><td>End Date</td><td>Adults</td><td>Children</td><td>Roomtype</td><td>Requirements</td></tr>';
  24. $result_array = mysql_fetch_array($result);
  25.  
  26. echo '<td>'.$result_array['startdate'].'</td>';
  27. echo '<td>'.$result_array['enddate'].'</td>';
  28. echo '<td>'.$result_array['adults'].'</td>';
  29. echo '<td>'.$result_array['children'].'</td>';
  30. echo '<td>'.$result_array['roomtype'].'</td>';
  31. echo '<td>'.$result_array['requirements'].'</td>';
  32. echo '</tr>';
  33. echo '</table>'; // this way it looks more neat
  34. }
  35. mysql_close($con);
  36. ?>
Also, mysql stores date in the default format yyyy-mm-dd . If you want to change this, you have to specify the column datatype as varchar. I don't recommend it since you can do so many date functions if the column is of date datatype.
Also, php's date function is so vast, you can modify any of your date format to whatever format you want.
Check here.
http://in.php.net/date
Moderator
Featured Poster
Reputation Points: 524
Solved Threads: 356
Purple hazed!
nav33n is offline Offline
3,878 posts
since Nov 2007
Permalink
Mar 2nd, 2009
0

Re: php/mysql display data in row and column

Hi
I have gathered from searching the net that I could use the code below to format the date as (DD-MM-YYYY)
MySQL Syntax (Toggle Plain Text)
  1. INSERT INTO table ( FIELD ) VALUES ( STR_TO_DATE($date,'%d-%m-%Y') );
The code below is the query which I am using, could you please let me know how I could embed the above code to this query?
MySQL Syntax (Toggle Plain Text)
  1. $sql="INSERT INTO bookings (clientID, roomID, startdate, enddate, adults, children, roomtype,  requirements) VALUES ('$last_insert_client_id','NULL','$_POST[startdate]','$_POST[enddate]','$_POST[adults]','$_POST[children]','$_POST[roomtype]', '$_POST[requirements]')";
Thanks for your help.
HB25
Reputation Points: 10
Solved Threads: 0
Junior Poster in Training
HB25 is offline Offline
74 posts
since Dec 2007

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