php/mysql display data in row and column

Question

HB25 0 Junior Poster in Training

15 Years Ago

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.

$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

mysql

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with x-ms-bmp files.

display.bmp (275.68 KB)

4 Contributors
36 Replies
3K Views
2 Years Discussion Span
Latest Post 12 Years Ago Latest Post by minilik

All 36 Replies

MDGM 22 Posting Whiz in Training

15 Years Ago

I think generally to store dates into the database its best to use a timestamp. A timestamp shows the number of seconds that have passed since January 1, 1970 00:00:00 GMT. You get the current timestamp in php by using: time(); //http://uk.php.net/manual/en/function.time.php You can convert a date into a timestamp using the following: mktime('','','',$month,$day,$year); //http://uk.php.net/manual/en/function.mktime.php And you can convert timestamp into a date by using this: date('D-M-Y',$timestamp); Using these functions, get the user to enter in their date in the format you want (DD/MM/YY) using 3 different text boxes like this:

<form action="process.php" method="post">
<input type="text" name="day" value="day"/><br />
<input type="text" name="month" value="month"/><br />
<input type="text" name="year" value="year"/><br />
<input type="submit" /><br />
</form>

then on the next page process it like this:

<?php
$day = $_POST['day'];
$month = $_POST['month'];
$year = $_POST['year'];

$timestamp = mktime('','','',$month,$day,$year);

//then store the timestamp into the database 
?>

Then later if you want to get the timestamp from the database do something like this:

<?php
$get_date = mysql_query('SELECT timestamp FROM table WHERE bookingID = "'.$bookingID.'"');
$timestamp = mysql_fetch_array($get_date);
$timestamp = $date['timestamp'];

$timestamp = date('D-M-Y',$timestamp);
//$timestamp is now a readable version of the timestamp date in the database
?>

the good thing about using timestamps is that if you want to order the items in your database by date you can do it easily. Also if you want to get bookings within the last month, do something like this:

$now = time();
$oneMonthInSeconds = 2629743;
$lastmonth = $now - $oneMonthInSeconds;

$query = 'SELECT * FROM table WHERE timestamp > "'.$lastmonth.'" ';

And no problem about the help - iv had so much help from people like "nav33n" from this forum over the years, its good to give something back. I hope what im saying isnt too complicated - I get a bit excited xD

nav33n 472 Purple hazed!

15 Years Ago

Hi
So how can we apply this to my query if you think it’s going to work?
Regards
HB25

It wont. You have to save it in varchar datatype, which again would cause you problems. For example, you can't sort the fields on date. You can't calculate the difference (or add) x number of days to this date field , etc.
Why do you want to store it in that format anyway ?

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

MDGM 22 Posting Whiz in Training · Answer 1 · 2009-03-01T04:02:33+00:00

try this:

<?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

HB25 0 Junior Poster in Training · Answer 2 · 2009-03-01T04:12:52+00:00

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

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

MDGM 22 Posting Whiz in Training · Answer 3 · 2009-03-01T04:21:21+00:00

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;

would write that user's booking id.

doing this:

echo $result_array;

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

Hope that helps. Max

HB25 0 Junior Poster in Training · Answer 4 · 2009-03-01T04:39:15+00:00

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:

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'] ;

MDGM 22 Posting Whiz in Training · Answer 5 · 2009-03-01T04:46:58+00:00

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

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

<?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>';

HB25 0 Junior Poster in Training · Answer 6 · 2009-03-01T05:39:05+00:00

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

MDGM 22 Posting Whiz in Training · Answer 7 · 2009-03-01T05:52:29+00:00

<?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

HB25 0 Junior Poster in Training · Answer 8 · 2009-03-01T16:34:00+00:00

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

MDGM 22 Posting Whiz in Training · Answer 9 · 2009-03-01T18:01:49+00:00

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/daniweb/primarykey_AI-autoincrement.bmp)

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

Max

HB25 0 Junior Poster in Training · Answer 10 · 2009-03-01T18:45:25+00:00

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

HB25 0 Junior Poster in Training · Answer 11 · 2009-03-01T19:59:09+00:00

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

$result = mysql_query("SELECT * FROM bookings WHERE bookingID='$_POST[bookingID]'");

MDGM 22 Posting Whiz in Training · Answer 12 · 2009-03-01T20:08:51+00:00

try using this:

$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%'

HB25 0 Junior Poster in Training · Answer 13 · 2009-03-01T23:05:22+00:00

HB25 0 Junior Poster in Training

15 Years Ago

same thing hapning :icon_sad:

MDGM 22 Posting Whiz in Training · Answer 14 · 2009-03-01T23:08:28+00:00

Thats really wierd..

Can you send the entire code to me?

HB25 0 Junior Poster in Training · Answer 15 · 2009-03-01T23:20:46+00:00

This is my code

<?php

// open database connection code and then my code as follows

$sql="UPDATE bookings SET startdate ='$_POST[startdate]',enddate='$_POST[enddate]',adults='$_POST[adults]',children='$_POST[children]',roomtype='$_POST[roomtype]', requirements='$_POST[requirements]'
WHERE bookingID = '$_POST[bookingID]'";
 
if (!mysql_query($sql,$con))
  {
  die('Error:  ' . mysql_error());
  } 

  print "Your booking ID ".$bookingID;
  
  echo "    has been changed";


$result = mysql_query("SELECT * FROM bookings WHERE bookingID='".$_POST['bookingID']."'");


$result_array = mysql_fetch_array($result);

echo '<table border=1>';

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>';


mysql_close($con);
?>

Thanks for your help

MDGM 22 Posting Whiz in Training · Answer 16 · 2009-03-02T00:29:47+00:00

Ahhh i know whats going wrong. This bit:

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>';

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:

<?php

// open database connection code and then my code as follows

$sql="UPDATE bookings SET startdate ='$_POST[startdate]',enddate='$_POST[enddate]',adults='$_POST[adults]',children='$_POST[children]',roomtype='$_POST[roomtype]', requirements='$_POST[requirements]'
WHERE bookingID = '$_POST[bookingID]'";
 
if (!mysql_query($sql,$con))
  {
  die('Error:  ' . mysql_error());
  } 

  print "Your booking ID ".$bookingID;
  
  echo "    has been changed";


$result = mysql_query("SELECT * FROM bookings WHERE bookingID='".$_POST['bookingID']."'");


$result_array = mysql_fetch_array($result);

echo '<table border=1>';
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>';


mysql_close($con);
?>

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

HB25 0 Junior Poster in Training · Answer 17 · 2009-03-02T04:44:53+00:00

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

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 18 · 2009-03-02T15:39:58+00:00

<?php

// open database connection code and then my code as follows

$sql="UPDATE bookings SET startdate ='$_POST[startdate]',enddate='$_POST[enddate]',adults='$_POST[adults]',children='$_POST[children]',roomtype='$_POST[roomtype]', requirements='$_POST[requirements]'
WHERE bookingID = '$_POST[bookingID]'";
 
if (!mysql_query($sql,$con))
  {
  die('Error:  ' . mysql_error());
  } 

  print "Your booking ID ".$bookingID;
  
  echo "    has been changed";


$result = mysql_query("SELECT * FROM bookings WHERE bookingID='".$_POST['bookingID']."'");
if(mysql_num_rows($result) > 0 ) { 
 //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
//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 
echo '<table border=1>
<tr><td>Start Date</td><td>End Date</td><td>Adults</td><td>Children</td><td>Roomtype</td><td>Requirements</td></tr>';
$result_array = mysql_fetch_array($result);

echo '<td>'.$result_array['startdate'].'</td>';
echo '<td>'.$result_array['enddate'].'</td>';
echo '<td>'.$result_array['adults'].'</td>';
echo '<td>'.$result_array['children'].'</td>';
echo '<td>'.$result_array['roomtype'].'</td>';
echo '<td>'.$result_array['requirements'].'</td>';
echo '</tr>';
echo '</table>'; // this way it looks more neat
}
mysql_close($con);
?>

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

HB25 0 Junior Poster in Training · Answer 19 · 2009-03-02T16:04:58+00:00

Hi
I have gathered from searching the net that I could use the code below to format the date as (DD-MM-YYYY)

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?

$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

MDGM 22 Posting Whiz in Training · Answer 20 · 2009-03-02T16:33:45+00:00

Sure,

$sql="INSERT INTO bookings 
(clientID, roomID, startdate, enddate, adults, children, roomtype,  requirements) 
VALUES 
('$last_insert_client_id',
'NULL',
'  str_to_date($_POST[startdate],'%d-%m-%Y')',
'  str_to_date($_POST[enddate],'%d-%m-%Y')',
'$_POST[adults]',
'$_POST[children]',
'$_POST[roomtype]', 
'$_POST[requirements]')
";

That should give you what you want.

HB25 0 Junior Poster in Training · Answer 21 · 2009-03-02T16:48:51+00:00

Hi
This is what I get:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%m-%Y')','str_to_date(05/04/2009,'%d-%m-%Y')','1', '0','Single'

Any more advice?
Thanks
HB25

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 22 · 2009-03-02T16:51:26+00:00

echo your query. Execute it in phpmyadmin. See what's the exact error.
P.S. I tried to work with str_to_date and I simply can't get it to work ! :-/

HB25 0 Junior Poster in Training · Answer 23 · 2009-03-02T17:17:39+00:00

Hi
I have opened this thread in another form and this is what I have been giving as a code but it will return an error saying (Error: Column 'startdate' cannot be null) any advice?

$startdate=mysql_real_escape_string($_POST[startdate]);
$enddate=mysql_real_escape_string($_POST[enddate]);
$adults=mysql_real_escape_string($_POST[adults]);
$children=mysql_real_escape_string($_POST[children]);
$roomtype=mysql_real_escape_string($_POST[roomtype]);
$requirements=mysql_real_escape_string($_POST[requirements]);

$sql="INSERT INTO bookings (clientID, roomID, startdate, enddate, adults, children, roomtype,  requirements) VALUES ('$last_insert_client_id','NULL',str_to_date('$startdate','%d-%m-%Y'),str_to_date('$enddate','%d-%m-%Y'),'$adults','$children','$roomtype','$requirements')";

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 24 · 2009-03-02T17:30:02+00:00

Hmm.. Okay! From what I have read so far, this is what STR_TO_DATE do.

STR_TO_DATE(date_string, format);

STR_TO_DATE function will format string and insert it to the date column. The second parameter 'format' is just to let mysql know in what format is the first parameter 'date_string' is in.

insert into table123 (reg_date) values (STR_TO_DATE('31/2004/04', '%d/%Y/%m'))

In the above example, date_string is 31/2004/04. I am asking mysql to insert this value to the table and the format in which I have given the date is, %d/%Y/%m. ie., date/Year/month .

HB25 0 Junior Poster in Training · Answer 25 · 2009-03-02T17:35:52+00:00

Hi
So how can we apply this to my query if you think it’s going to work?
Regards
HB25

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 26 · 2009-03-02T17:55:55+00:00

My suggestion is, store date in YYYY-mm-dd format itself. You can change the format using php's date function. As maxmumford has already mentioned, you can convert that date to unixtimestamp and use it in whatever format you want.

HB25 0 Junior Poster in Training · Answer 27 · 2009-03-02T18:16:01+00:00

As i live in the UK and the format for date is like this.
I think i will go with your advice i shoud leve it as its.

BTW MaxMumford has solved my problem this time how i could mark him as a solver for my thread?

Once again thank you for all your kind help.

Best wishes
HB 25

php/mysql display data in row and column

Recommended Answers Collapse Answers

All 36 Replies

Recommended Answers